firebird - самообъединение на одной таблице

У меня есть таблица STARTSTOP

ACTION  DATA                    ID_PPSTARTSTOPPOZ
0   2013-03-18 08:38:00 10451
1   2013-03-18 09:00:00 10453
0   2013-03-18 09:50:00 10466
1   2013-03-18 10:38:00 10467
0   2013-03-19 11:54:00 10499
1   2013-03-19 12:32:00 10505

Действие 0 -> НАЧАТЬ ДЕЙСТВИЕ Действие 1 -> ОСТАНОВИТЬ ДЕЙСТВИЕ ДАННЫЕ — метка времени действия

Я хотел бы запустить оператор select, который будет возвращать записи примерно так:

ACTION_1   ACTION_2    DURATION
10451        10453       22
10466        10466       48
             ...

ИЛИ сводка по продолжительности всех действий в одной строке.

Возможно ли это с помощью одного запроса к базе данных? (без создания дополнительных таблиц)


sql join firebird self-join
person zeno33    schedule 09.07.2013    source источник

Ответы (3)


arrow_upward
2
arrow_downward 

select A1.ID_PPSTARTSTOPPOZ as Action_0,
       A2.Action_1,
       datediff (minute, A1.DATA ,A2.DATA)

from STARTSTOP A1
JOIN 
(
  select ID_PPSTARTSTOPPOZ as Action_1,
         DATA,
         (select max(ID_PPSTARTSTOPPOZ)
           FROM STARTSTOP 
          where ID_PPSTARTSTOPPOZ<T.ID_PPSTARTSTOPPOZ
                AND
                ACTION=0) AS PREV_ACTION
  from STARTSTOP T
  where ACTION=1 

) A2 on A1.ID_PPSTARTSTOPPOZ=A2.PREV_ACTION

where ACTION = 0
order by A1.ID_PPSTARTSTOPPOZ

Функция DATEDIFF

Пример SQLFiddle для MSSQL, но он должен работать и под Firebird

person valex    schedule 09.07.2013

arrow_upward
0
arrow_downward

Это можно сделать с помощью одного выбора, но алгоритмический EXECUTE BLOCK сделает это намного быстрее:

EXECUTE BLOCK
  RETURNS (ACTION_1 INTEGER, ACTION_2 INTEGER, DURATION INTEGER)
AS
  DECLARE VARIABLE act INTEGER;
  DECLARE VARIABLE act_id INTEGER;
  DECLARE VARIABLE d TIMESTAMP = NULL;
  DECLARE VARIABLE d1 TIMESTAMP = NULL;
BEGIN
  FOR
    SELECT action, data, id_ppstartstoppoz
    FROM startstop
    ORDER BY data ASC
    INTO :act, :d, :act_id
  DO BEGIN
    IF (:act = 0) THEN
    BEGIN
      d1 = :d;
      action_1 = :act_id;
    END ELSE
    BEGIN
      IF (NOT :d1 IS NULL) THEN
      BEGIN
        action_2 = :act_id;
        duration = DATEDIFF(SECOND, :d1, :d);
        SUSPEND;
        d1 = NULL; 
      END
    END
  END
END
person Andrej Kirejeŭ    schedule 09.07.2013
comment
Спасибо . Попробую сравнить с решением @valex. - person zeno33; 09.07.2013

arrow_upward
0
arrow_downward

Это можно сделать намного проще

SELECT TAB1.ID AS ACTION_1,TAB2.ID AS ACTION_2,  
(TAB2.DATA_TS - TAB1.DATA_TS)   MINUTE (4) TO SECOND(6) AS DURATION
FROM 

 (SELECT  ID, DATA_TS , ROW_NUMBER () OVER ( ORDER BY ID )AS RNUM FROM   
 PROCESS WHERE ACTION=0
 )TAB1

 INNER JOIN 

 (SELECT  ID, DATA_TS , ROW_NUMBER () OVER ( ORDER BY ID ) AS RNUM  FROM    
  PROCESS WHERE ACTION=1 
 ) TAB2

 ON ( TAB1.RNUM=TAB2.RNUM)
 ORDER BY 1

  ACTION_1  ACTION_2   DURATION
  10,451    10,453     22:00.000000
  10,466    10,467     48:00.000000
  10,499    10,505     38:00.000000
person Rohan Nayak    schedule 14.05.2015