Как использовать функцию LEAD для расчета времени между текущим и последующим событием?

Question

У меня есть таблица test1, в которой находятся данные о расписании поездов:

ID  NEXT_ID TRAIN_ID DATE_START             DATE_END
1   2       5        06.11.2019 5:00:00     06.11.2019 7:40:00
2   3       5        06.11.2019 8:00:00     06.11.2019 9:00:00
3   4       5        06.11.2019 10:00:00    06.11.2019 10:15:00
4   5       5        06.11.2019 23:05:00    07.11.2019 0:15:00
5   6       5        07.11.2019 2:05:00     07.11.2019 4:30:00
6   7       5        07.11.2019 5:05:00     07.11.2019 7:05:00
7   8       5        07.11.2019 9:05:00     07.11.2019 10:05:00
8           5        07.11.2019 12:05:00    07.11.2019 14:05:00

Где ID - идентификатор поездки, NEXT_ID - отсылка на следующую поездку в расписании, TRAIN_ID - номер поезда (в данном вопросе для простоты я оставил один, но может быть несколько поездов), DATE_START и DATE_END значат время начала и конца поездки соответственно.

Надо найти время "простоев" между поездками. Например, между поездками с ID 1 и 2 простой будет 8:00-7:40 = 20 минут, с ID 2 и 3 10:00-9:00 = 60 минут, и так далее.

Пробовал сделать эту задачу с функцией LEAD, но я явно использую ее как-то не так:

SELECT  id, train_id, next_id, 
LEAD(next_id) OVER (ORDER BY next_id) next_code,
dt_start, dt_end,
Round(( date_end - date_start) * 24) as prost_hour
FROM test1

Прошу помочь разобраться.

Answer 1

SELECT *,
       LEAD(DATE_START) OVER (PARTITION BY TRAIN_ID ORDER BY DATE_START) next_start,
       TIMEDIFF(LEAD(DATE_START) OVER (PARTITION BY TRAIN_ID ORDER BY DATE_START), DATE_END) delta
FROM test
ORDER BY train_id, date_start;

fiddle

PS fiddle сделан в MySQL. Для Oracle придётся разве что подправить TIMEDIFF, всё остальное один-в-один.

PPS Всё-таки колонка NEXT_ID не нужна, т.к. порядок чётко определяется временем. Конечно при условии, что в данных нет ошибок.

Answer 2

Вот воспроизводимый пример для Oracle (тестовые данные на db<>fiddle):

select train_id, date_start, date_end, 
    lead (date_start) over (partition by train_id order by date_start) next_start,
    to_char (
        date'1970-01-01' +
        (lead (date_start) over (partition by train_id order by date_start)-date_end), 
        'hh24:mi:ss') diff
from test t
order by train_id, date_start;

Результат:

  TRAIN_ID DATE_START          DATE_END            NEXT_START          DIFF    
---------- ------------------- ------------------- ------------------- --------
         5 2019-11-06 05:00:00 2019-11-06 07:40:00 2019-11-06 08:00:00 00:20:00
         5 2019-11-06 08:00:00 2019-11-06 09:00:00 2019-11-06 10:00:00 01:00:00
         5 2019-11-06 10:00:00 2019-11-06 10:15:00 2019-11-06 23:05:00 12:50:00
         5 2019-11-06 23:05:00 2019-11-07 00:15:00 2019-11-07 02:05:00 01:50:00
         5 2019-11-07 02:05:00 2019-11-07 04:30:00 2019-11-07 05:05:00 00:35:00
         5 2019-11-07 05:05:00 2019-11-07 07:05:00 2019-11-07 09:05:00 02:00:00
         5 2019-11-07 09:05:00 2019-11-07 10:05:00 2019-11-07 12:05:00 02:00:00
         5 2019-11-07 12:05:00 2019-11-07 14:05:00                             
         6 2019-11-06 06:00:00 2019-11-06 07:40:00 2019-11-06 09:00:00 01:20:00
         6 2019-11-06 09:00:00 2019-11-06 09:50:00 2019-11-06 12:00:00 02:10:00
         6 2019-11-06 12:00:00 2019-11-06 13:15:00 2019-11-06 14:05:00 00:50:00
         6 2019-11-06 14:05:00 2019-11-07 00:15:00

_{Перевод ответа от уважаемого коллеги @Akina.}

БЛОГ НА HUSL

Как использовать функцию LEAD для расчета времени между текущим и последующим событием?

Ответы (2 шт):