Поиск разрывов в цепи следования.

← →
Kinda (2003-09-30 22:31) [0]

Есть две таблицы с данными о движении вагонов.
В одной операции внутри страны, в другой - за
ее пределами. Необходимо отыскать разрывы в цепи следования,
например вагон выехал с одной страны, но не заехал в другую и т.п.

Я решил написать две ХП в одной сливаю данные двух таблиц, а в другой оранизован цикл по первой и внутри этого цикла
мне приходится опять же делать select из первой ХП чтобы посмотреть дату следующей операции и увидеть есть ли разрыв.

В результате все очень сильно тормозит, на 200 000 записей.
Скажите как можно сделать по-другому, только очень не хочется делать специальную таблицу для слива данных.

Заранее спасибо.

← →
Johnmen (2003-09-30 23:17) [1]

"Ты не умничай, ты рукой покажи" (c) известный анекдот

← →
Kinda (2003-09-30 23:31) [2]

CREATE PROCEDURE RAZRIV_PLATA
RETURNS (
NVAGON DOUBLE PRECISION,
DATAOP TIMESTAMP,
NDEPO VARCHAR (1),
STANPOGR VARCHAR (72),
STANNAZ VARCHAR (72),
STANPRIEM VARCHAR (72),
STANSDACH VARCHAR (72),
VES DOUBLE PRECISION,
DORZVIT VARCHAR (50),
SUMGRN DOUBLE PRECISION,
KSTRAN VARCHAR (2),
OPERATION VARCHAR (50),
PRIORITY INTEGER,
MESOT INTEGER,
GODOT INTEGER)
AS
DECLARE VARIABLE v_NVAGON DOUBLE PRECISION;
DECLARE VARIABLE v_DATAOP TIMESTAMP;
DECLARE VARIABLE v_NDEPO VARCHAR (1);
DECLARE VARIABLE v_STANPOGR VARCHAR (72);
DECLARE VARIABLE v_STANNAZ VARCHAR (72);
DECLARE VARIABLE v_STANPRIEM VARCHAR (72);
DECLARE VARIABLE v_STANSDACH VARCHAR (72);
DECLARE VARIABLE v_VES DOUBLE PRECISION;
DECLARE VARIABLE v_DORZVIT VARCHAR (50);
DECLARE VARIABLE v_SUMGRN DOUBLE PRECISION;
DECLARE VARIABLE v_KSTRAN VARCHAR (2);
DECLARE VARIABLE v_OPERATION VARCHAR (50);
DECLARE VARIABLE v_PRIORITY INTEGER;
DECLARE VARIABLE v_MESOT INTEGER;
DECLARE VARIABLE v_GODOT INTEGER;
DECLARE VARIABLE v_DATA_SLED TIMESTAMP;
DECLARE VARIABLE v_KSTRAN_SLED VARCHAR(2);
DECLARE VARIABLE v_MINPRIOR INTEGER;
BEGIN
/* Procedure body */
FOR SELECT NVAGON,DATAOP,NDEPO,STANPOGR,STANNAZ,STANPRIEM,STANSDACH,VES,DORZVIT,
SUMGRN,KSTRAN,OPERATION,PRIORITY,MESOT,GODOT FROM TRANSFORM_PLATA where
DATAOP BETWEEN "01.01.2003" AND "01.05.2003" ORDER BY NVAGON,DATAOP,PRIORITY
INTO :v_NVAGON,:v_DATAOP,:v_NDEPO,:v_STANPOGR,:v_STANNAZ,:v_STANPRIEM,
:v_STANSDACH,:v_VES,:v_DORZVIT,:v_SUMGRN,:v_KSTRAN,:v_OPERATION,
:v_PRIORITY,:v_MESOT,:v_GODOT
DO BEGIN
SELECT MIN(DATAOP),MIN(PRIORITY) FROM TRANSFORM_PLATA WHERE DATAOP>:v_DATAOP INTO :v_DATA_SLED,:v_MINPRIOR;
SELECT MIN(KSTRAN) FROM TRANSFORM_PLATA WHERE DATAOP=:v_DATA_SLED AND PRIORITY=:v_MINPRIOR
INTO :v_KSTRAN_SLED;
if ........ then
SUSPEND;
END
END

Это текст второй ХП.
Первая ХП (TRANSFORM_PLATA) объединяет данные двух таблиц
(в одной 150 000 в другой 50 000).

← →
Johnmen (2003-09-30 23:41) [3]

Да нет же. Приведи часть структур таблиц, описывающую следование вагонов. А также, что в содержательной части таблиц является разрывом, как он определяется ?

← →
Kinda (2003-09-30 23:56) [4]

Значит так:

Таблица 1:
NVAGON - номер вагона
DATAOP - дата операции
OPERATION - операция с вагоном (погрузка, выгрузка и т.д.)
....

Таблица 2:
NVAGON - номер вагона
DATEVH - дата входа в страну
DATEVIH - дата выхода из страны
KSTRAN - код страны.
....

Если с момента выхода вагона за пределы страны (табл. 1)
и до момента его возвращения между датами операций
более чем 1 сутки - то считать это разрывом цепи.

← →
Johnmen (2003-10-01 00:07) [5]

Давай дальше уточнять, а то нет однозначной ясности...
>Если с момента выхода вагона за пределы страны (табл. 1)
Где в табл.1 этот момент ? О какой стране речь - нашей/не нашей ?
>и до момента его возвращения
Где этот момент в табл. ?
Где производятся операции ? У нас или за бугром ? :)

← →
Kinda (2003-10-01 00:28) [6]

В таблице 1:
OPERATION=1 - момент выхода за пределы страны
OPERATION=6 - момент возвращения

По сути таблица 2 содержит в каждой записи 2 операции с вагоном,
а именно вход в страну и выход из нее.

Вот еще поясню если вагон вышел за пределы страны, то он может проследовать N стран до своего возвращения вот эту цепь нужно и проследить, чтоб она не разрывалась. Т.е.:
Момент выхода -страна1 вход-страна1 выход-.....-момент возвращения.

Конечно немного закучено, но по-другому не получается.

← →
Kinda (2003-10-01 00:47) [7]

Извиняюсь, но должен удалиться.
Очень буду благодарен за дельный совет.

← →
Anatoly Podgoretsky (2003-10-01 07:46) [8]

Johnmen © (01.10.03 00:07) [5]
А ты еще не удалился :-)

← →
Johnmen (2003-10-01 12:21) [9]

>Anatoly Podgoretsky © (01.10.03 07:46)

Да, удалился вчера :0) Мама прогнала в кровать...

>Kinda ©

Я тут на досуге попытался осмыслить задачу...
Осмыслил. Вывод - неверно спроектированы таблицы, т.к. :
Либо НЕВОЗМОЖНО однозначно определить разрыв,
либо необоснованная избыточность данных, что приводит к запутанному алгоритму определения разрывов.
И этот алгоритм ты так и не объяснил. На пальцах.

← →
Sandman25 (2003-10-01 13:09) [10]

ИМХО, лучше была бы структура такая:

table2

vagon - id вагона
nomer - номер операции (связка vagon, nomer уникальна)
date1 - дата операции
country - страна
type_operation - флаг (например 0 - вход в страну, 1 - выход из страны).

Тогда было бы легко найти разрыв с помощью

select distinct vagon from vagons where exists (select * from table2 a, table2 b where a.vagon = vagons.vagon and b.vagon = a.vagon and b.nomer = a.nomer + 1 and b.date1 >= a.date1 + 1)

А с текущей структурой придется во вложенном select вместо b.nomer = a.nomer + 1 писать конструкцию для нахождения следующей операции.

← →
Kinda (2003-10-01 13:11) [11]

> Johnmen © (01.10.03 12:21) [9]
Алгоритм действительно запутанный.
Таблицы к сожалению проектированы не мной, т.е
я в таком виде получаю данные из внешнего мира.
Таблица 1 данные обновляется каждый день,
таблица 2 - каждый месяц.

Данные таблицы 1 используются не только для этой задачи,
поэтому операций с вагонами там больше, и как следствие
возникает избыточность данных (в контексте этой задачи).

> "Либо НЕВОЗМОЖНО однозначно определить разрыв,"
Что касается этого, то согласен с тем, что хорошо бы выделить рейс вагона (от момента погрузки до момента выгрузки, либо возвращения на территорию страны), но к сожалению такой возможности я не вижу.
Поэтому нужно привязаться к датам и операциям.

>И этот алгоритм ты так и не объяснил. На пальцах.
Алоритм таков:
1.Вагон вышел за пределы страны (Табл.1. OPERATION=1)
2.Далее в Табл.2 ДОЛЖНА (вот эти разрывы и надо найти если они есть) быть запись о входе этого вагона
на территорию другой страны(DATAVH=DATAOP хотябы в пределах суток).
3.Потом в той же записи Табл. 2 есть дата выхода с этой страны (DATAVIH) и нужно найти следующую запись в табл.2
(вход в следующую страну DATAVIH,KSTRAN1=DATAVH,KSTRAN2 и т.д.)
или в табл.1 (если он вернулся OPERATION=6)

Вот такой вот алгоритм.

Я не говорю, что все уже спроектировано, я прошу помочь мне это сделать чтобы устроить такую проверку.
Есть и еще кое-какие нюансы, сейчас их не привел, чтоб не запутаться.

← →
Johnmen (2003-10-01 13:20) [12]

>Kinda © (01.10.03 13:11)

Теперь вроде бы понял окончательно...:)
Делаю вывод - НЕВОЗМОЖНО однозначно определить желаемое, т.к. нет № рейса, о чем ты уже упомянул, или к.-л. сущность ему аналогичная. Ведь один и тот же вагон может много раз пересекать границу...

← →
Sandman25 (2003-10-01 13:23) [13]

Возможно, вместо вызова хранимой изнутри другой лучше написать прямой select.

← →
Kinda (2003-10-01 13:27) [14]

> Johnmen © (01.10.03 13:20) [12]

Но ведь есть последовательность дат операций с вагоном!

← →
Kinda (2003-10-01 13:37) [15]

>Sandman25 © (01.10.03 13:23) [13]
Данные возвращаемые процедурой надо сортировать!
GROUP BY NVAGON,DATAOP,OPERATION

← →
Sandman25 (2003-10-01 13:42) [16]

[15] Kinda © (01.10.03 13:37)

Тем более. Зачем вызывать одну хранимую для каждого вагона, если она каждый раз находит данные по всем вагонам?

← →
Kinda (2003-10-01 13:56) [17]

>Sandman25 © (01.10.03 13:42) [16]

Потому что, как можно select"om объединить данные из двух
таблиц, причем с трансформацией структуры одной из них?

← →
Johnmen (2003-10-01 15:10) [18]

Модифицировав запрос Sandman25 © (01.10.03 13:09), получаем вагоны, для которых есть разрыв при покидании Отчизны :)

SELECT DISTINCT T1.NVAGON FROM Table1 T1 WHERE NOT EXISTS (SELECT * FROM Table2 T2 WHERE (T1.NVAGON=T2.NVAGON) AND (T2.OPERATION=1) AND (T2.DATEVH-T1.DATAOP<=1))

А для получения более развернутой информации надо написать ХП, в которой делается последовательный обход записей запроса
SELECT * FROM Table1 T1 JOIN Table2 ON T1.NVAGON=T2.NVAGON ORDER BY <вагоны, даты и т.д.>
с анализом и принятием решения для каждой на основании каких-либо уже полученных значений, в рамках алгоритма...

← →
Kinda (2003-10-01 15:34) [19]

>Johnmen © (01.10.03 15:10) [18]

Я не совсем понял как это мы так получим?
Ведь в табл.2 нет поля OPERATION, к тому же
за пределами страны анализироваться должны
DATAVH и DATAVIH между странами.

Я хочу сказать, что DATAVIH-DATAVH по одной стране (KSTRAN)
запросто может быть (а оно так и есть) более 1 суток. Поэтому проанализировать надо DATAVIH,KSTRAN1 и DATAVH,KSTRAN2. И так
до возвращения на Родину.

Ух, блин, кажися придется все-таки сливать все это в какую-то временную таблицу :(

← →
kaif (2003-10-01 18:17) [20]

Часто вопросы скорости я решал путем многокаскадных запросов. Например, есть ХП, делающая запрос. Есть вторая ХП, "стоящая над ней" и делающая из нее запрос "2-го уровня". Сервер в таких случаях кеширует набор, возвращаемый первой процедурой и "вложенного перезапроса" не возникает.
Можно вместо временной таблицы соорудить при помощи ХП имитирующий эту таблицу набор (сколь угодно сложно), а затем над ним произвести вторую выборку с помощью SELECT. Это работает быстрее, чем вставка 200тыс записей в таблицу TMP.
Я в задачу не вникал, но это как вариант стратегии поиска технического решения.

← →
kaif (2003-10-01 18:24) [21]

Допустим, мы делаем что-то вроде:

/*Объединение всех операций в один набор*/
CREATE PROC1
RETURNS(NVAGON INTEGER, DATAOP DATE)
AS
BEGIN
FOR SELECT NVAGON, DATAOP
FROM T1 WHERE
OPERATION = 1
INTO :NVAGON, :DATAOP
DO SUSPEND;

FOR SELECT NVAGON, DATEVH
FROM T2
INTO :NVAGON, :DATAOP
DO SUSPEND;

FOR SELECT NVAGON, DATEVIH
FROM T2
INTO :NVAGON, :DATAOP
DO SUSPEND;
END

CREATE PROC2
....
AS
BEGIN
FOR SELECT NVAGON, DATAOP FROM PROCE1
ORDER BY NVAGON, DATAOP
INTO :NVAGON, :DATAOP
DO
/*какая-то обработка, ловящая "дырки"*/
END

Во второй процедуре запросы первой отработают 1 раз, затем сервер упорядочит набор по вагонам и датам и как-то задачу дальше можно будет решить. Разумеется, я не все нужные поля использовал, но это набросок...

← →
kaif (2003-10-01 18:26) [22]

На моем компьютере сервер обходит наборы в ХП со скоростью не хуже 100тыс строк/сек. Поэтому я ожидаю, что такая стратегия будет решать задачу за несколько секунд.

← →
Kinda (2003-10-01 18:33) [23]

Например, есть ХП, делающая запрос. Есть вторая ХП, "стоящая над ней" и делающая из нее запрос "2-го уровня". Сервер в таких случаях кеширует набор, возвращаемый первой процедурой и "вложенного перезапроса" не возникает.
Ты хочешь сказать, что если в ХП2 я создам
FOR SELECT * FROM ХП1 DO BEGIN
И здесь буду писать
SELECT ... FROM ХП1 WHERE...
....
END
То "вложенного перезапроса" из ХП1(200тыс. зап.) не возникнет?

У меня сейчас практически такое же решение, но на машине
Athlon XP 2400+ c 256Mb RAM ХП2 работала в течение 0,5 часа
и результатов не вернула, пришлось делать Shutdown!

← →
kaif (2003-10-02 01:31) [24]

Нет, я не это имею в виду.
Разумеется, запросы, вложенные в циклы FOR будут выполняться столько раз, сколько по ним FOR чешет.
FOR SELECT ...
DO
BEGIN
FOR SELECT ...
DO
BEGIN
END
END

Я говорю о случае, когда

FOR SELECT * FROM XP1 ORDER BY...
DO
BEGIN
bи здесь никаких запросов, а только сравнения с переменными
END

а сама XP1 возвращает набор, получаемый с помощью невложенных SELECT-ов, который упорядочивается перед тем, как цикл
FOR SELECT * FROM XP1
по нему пройдется.

Поиск разрывов в цепи следования. Найти похожие ветки