Объединить события, между которыми меньше 15 минут

Question

У меня есть таблица событий:

Необходимо объединить некоторые интервалы по следующему алгоритму:

Беру в последовательности первый интервал (сортировка по время_начала)
Далее ищу следующий интервал, который был в этой же зоне.
Если между концом первого интервала в зоне и началом второго интервала в этой же зоне прошло менее 15 мин
То делаю единый интервал пребывания в этой зоне и т.д.

create table events(subs_id int, start_time datetime, end_time datetime, zid int);
insert into events(555, date'2022-11-20 19:03:19', '2022-11-20 19:04:42', 46);
insert into events(555, date'2022-11-20 19:04:42', '2022-11-20 19:05:58', 42);
insert into events(555, date'2022-11-20 19:06:40', '2022-11-20 19:08:58', 5);
insert into events(555, date'2022-11-20 19:09:00', '2022-11-20 19:09:42', 9);
insert into events(555, date'2022-11-20 19:11:24', '2022-11-20 19:12:37', 46);
insert into events(555, date'2022-11-20 19:13:00', '2022-11-20 20:35:00', 42);

исходная таблица

Должна получиться

Чтобы найти следующую строку с той же зоной применяю группировку.

Делаю окно subs_id, zid.
Нахожу флаги is_start - начало нового интервала, is_end - окончание нового интервала.
Далее можно для строки начала обновить время окончания (ну не знаю, пригодится эта операция или нет).

select 
subs_id,
start_time, 
end_time, 
zid, 
prev_end, 
next_start,
case when start_time - prev_end < 15*60 then 0 else 1 end as is_start,
case when next_start - end_time < 15*60 then 0 else 1 end as is_end
from 
  ( select 
    subs_id,
    start_time, 
    end_time, 
    zid,
    lag(end_time)over(partitionBy subs_id, zid orderBy start_time) as prev_end,
    lead(start_time)over(partitionBy subs_id, zid orderBy start_time) as next_start
    from events)t_tmp

получается так:

Дальше не придумала.
сделала так: сначала нашла предыдущий end_time в окне (subs_id, zid). Если разница между start_time и prev_end меньше 15 минут, то ставлю 0, иначе 1. То есть обозначила начало нового интервала. Далее сделала накопительное суммирование флага из предыдущего шага, тем самым разделила интервалы на группы для объединения. Далее сделала группировку по subs_id, zid, group_id, нашла минимальный start_time, максимальный end_time - это новые границы интервалов.
Далее что делать с теми интервалами, которые из разных зидов и которые после объединения пересекаются или лежат внутри нового интервала. Для этого я нашла max_prev_end для окна subs_id, но добавила rowsBetween(UnbondedPreceding, -1), то есть инетервалы идут последовательно и end_time должно быть нарастающим, а где этот порядок нарушается, то такую строку надо убрать - это для интервалов, которые лежат внутри нового интервала. А для пересекающихся интервалов и нахожу новую start_time == prev_end.
И все бы хорошо, если бы интервалы шли без пропусков, но в моем случае есть пропуски, то есть prev_end != start_time (первый скрин). Поэтому после последней операции у нижнего интервала (из пересекающихся) s

БЛОГ НА HUSL

Объединить события, между которыми меньше 15 минут

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