Показать прошлый рейс в записи
Существует таблица с расписанием поездок поездов:
create table atest (
TR_ID NUMBER(9),
TR_START DATE,
TR_END DATE,
TR_FROMSTART VARCHAR2(255),
TR_FROMEND VARCHAR2(255) )
TR_ID TR_START TR_END TR_FROMSTART TR_FROMEND
1 01.06.2020 4:50:00 01.06.2020 7:50:00 MSK KZN
1 01.06.2020 9:35:00 01.06.2020 13:00:00 КZN ANP
2 03.06.2020 15:35:00 03.06.2020 18:30:00 MSK SPB
2 05.06.2020 5:15:00 05.06.2020 10:30:00 SPB MSK
3 05.06.2020 12:05:00 05.06.2020 12:05:00 MSK NSB
3 08.06.2020 17:05:00 09.06.2020 1:50:00 NSB MSK
1 10.06.2020 12:30:00 10.06.2020 16:00:00 ANP KZN
1 12.06.2020 17:20:00 13.06.2020 5:35:00 KZN MSK
2 15.06.2020 5:15:00 15.06.2020 10:30:00 MSK ANP
2 15.06.2020 12:05:00 15.06.2020 17:50:00 ANP KZN
В таблице столбцы отвечают за следующие значения: TR_ID - идентификатор поезда, TR_START и TR_END - время выезда и приезда поезда, TR_FROMSTART и TR_FROMEND - пункты отбытия и прибытия поезда.
Задача следующая: Создать запрос, который выведет такие же столбцы, но добавит к ним столбцы с временем отправки и прибытия предыдущего рейса этого поезда, рейс обязательно должен быть в предыдущий день или раньше.
Пример того, что должно получиться:
TR_ID TR_START TR_END TR_FROMSTART TR_FROMEND LASTRACE_ST
1 01.06.2020 4:50:00 01.06.2020 7:50:00 MSK KZN 0
1 01.06.2020 9:35:00 01.06.2020 13:00:00 КZN ANP 01.06.2020 4:50:00
2 03.06.2020 15:35:00 03.06.2020 18:30:00 MSK SPB 0
2 05.06.2020 5:15:00 05.06.2020 10:30:00 SPB MSK 05.06.2020 5:15:00
3 05.06.2020 12:05:00 05.06.2020 12:05:00 MSK NSB 0
3 08.06.2020 17:05:00 09.06.2020 1:50:00 NSB MSK 08.06.2020 17:05:00
.................................................... и т.д.
Пробовал сделать задачу многими способами, самый близкий был с NTH_VALUE:
select distinct a.tr_id, trunc(a.tr_start), a.tr_fromstart, a.tr_fromend,
NTH_VALUE(a.tr_start, 2) OVER (PARTITION BY a.tr_id ORDER BY a.tr_start DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lastrace_st,
NTH_VALUE(a.tr_end, 2) OVER (PARTITION BY a.tr_id ORDER BY a.tr_start DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lastrace_end
from atest a
order by trunc(a.tr_start)
Однако, данные выбираются некорректно - берется предпоследний рейс по поезду в целом, а не предыдущий.
Ответы (1 шт):
Попробуйте так (на db<>fiddle):
select *
from atest t1
outer apply (
select
max (tr_start) keep (dense_rank last order by t2.tr_start asc) last_start,
max (tr_end) keep (dense_rank last order by t2.tr_end asc) last_end
from atest t2
where t2.tr_id = t1.tr_Id
and t2.tr_fromend = t1.tr_fromstart
and t2.tr_end < t1.tr_start
)
order by t1.tr_start
Результат:
TR_ID TR_START TR_END DEP ARR LAST_START LAST_END
----- ---------------- ---------------- --- --- ---------------- ----------------
1 01.06.2020 04:50 01.06.2020 07:50 MSK KZN
1 01.06.2020 09:35 01.06.2020 13:00 KZN ANP 01.06.2020 04:50 01.06.2020 07:50
2 03.06.2020 15:35 03.06.2020 18:30 MSK SPB
2 05.06.2020 05:15 05.06.2020 10:30 SPB MSK 03.06.2020 15:35 03.06.2020 18:30
3 05.06.2020 12:05 05.06.2020 12:05 MSK NSB
3 08.06.2020 17:05 09.06.2020 01:50 NSB MSK 05.06.2020 12:05 05.06.2020 12:05
1 10.06.2020 12:30 10.06.2020 16:00 ANP KZN 01.06.2020 09:35 01.06.2020 13:00
1 12.06.2020 17:20 13.06.2020 05:35 KZN MSK 10.06.2020 12:30 10.06.2020 16:00
2 15.06.2020 05:15 15.06.2020 10:30 MSK ANP 05.06.2020 05:15 05.06.2020 10:30
2 15.06.2020 12:05 15.06.2020 17:50 ANP KZN 15.06.2020 05:15 15.06.2020 10:30
Так как, описание не совсем совпадает с ожидаемым результатом, пара пояснений:
t2.tr_fromend = t1.tr_fromstart- рейс сначало должен прибыть на туже станцию, с которой он отправится в следующий рейсt2.tr_end < t1.tr_start- рейс может сразу же отправится до следующей станци назначения; для условия: рейс обязательно должен быть в предыдущий день или раньше, надо заменуть наt2.tr_end < trunc (t1.tr_start)