DataДжунгли🌳: “🏝️ Gaps & Islands: как из потока событий собрать сессии одним оконным…”

🏝️ Gaps & Islands: как из потока событий собрать сессии одним оконным трюком #SQLWednesday

Задача, которая отделяет «пишу SQL» от «думаю окнами». Есть поток событий пользователя — надо склеить их в сессии (островки активности) и найти паузы между ними. На собесе спрашивают, а в проде это сессионизация, стрики и SLA-даунтаймы.

❓ Задача Таблица событий. Сессия = подряд идущие события одного юзера, где пауза между соседними ≤ 30 минут. Нужно вернуть: id сессии, начало, конец, число событий, длительность.

📋 Таблица и данные


CREATE TABLE events (
    user_id    BIGINT,
    event_time TIMESTAMP
);
user_id | event_time        | комментарий
   1     | 2026-07-20 10:00  | сессия 1
   1     | 2026-07-20 10:10  | сессия 1 (гэп 10 мин)
   1     | 2026-07-20 10:25  | сессия 1 (гэп 15 мин)
   1     | 2026-07-20 12:00  | пауза 95 мин -> сессия 2
   1     | 2026-07-20 12:20  | сессия 2
   2     | 2026-07-20 09:00  | Bob, сессия 1
   2     | 2026-07-20 09:45  | пауза 45 мин -> сессия 2

🔴 Как НЕ надо Self-join «каждое событие с каждым», чтобы найти соседнее → O(n²). На миллионах событий встаёт колом.

🟢 Трюк Gaps & Islands — 3 шага

LAG(event_time) — берём время предыдущего события того же юзера.
Флаг новой сессии: если предыдущего нет ИЛИ разрыв > 30 мин → 1, иначе 0.
Кумулятивная SUM(флаг) OVER (... ORDER BY time) — это и есть номер сессии (island id). Дальше обычный GROUP BY.


WITH ordered AS (
    SELECT user_id, event_time,
           LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
    FROM events
),
flagged AS (
    SELECT user_id, event_time,
           CASE WHEN prev_time IS NULL
                     OR event_time - prev_time > INTERVAL '30 minutes'
                THEN 1 ELSE 0 END AS is_new
    FROM ordered
),
sessions AS (
    SELECT user_id, event_time,
           SUM(is_new) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
    FROM flagged
)
SELECT user_id, session_id,
       MIN(event_time) AS session_start,
       MAX(event_time) AS session_end,
       COUNT(*)        AS events,
       date_diff('minute', MIN(event_time), MAX(event_time)) AS duration_min
FROM sessions
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

⚙️ Через призму эффективности

Один проход + оконные функции вместо self-join. По сути один sort по (user_id, event_time).
В lakehouse: если данные уже отсортированы/партиционированы по этому ключу — движок пропускает сортировку. Приём один-в-один масштабируется в Spark/BigQuery.
В стриминге это вообще встроено: session window в Spark/Flink делает ровно это на лету.

🧩 Тот же приём — другие задачи

Найти паузы (gaps): те же prev_time, но фильтр «разрыв > 30 мин» → окна простоя.
Стрики «N дней подряд заходил»: island по разнице дат.
Аптайм/даунтайм сервиса по логам.

🔴 Ловушки

RANGE vs ROWS во фрейме: по умолчанию окно работает как RANGE. Для нумерации сессий это ок, но при равных event_time знай разницу.
Тай-брейк: два события в одну секунду → добавь второй ключ в ORDER BY (event_id), иначе порядок недетерминирован.
Часовые пояса: «30 минут» на timestamptz считай аккуратно.
Диалекты: Postgres/DuckDB — event_time - prev_time > interval '30 minutes'; в других — date_diff / timestampdiff по минутам.

Проверил на DuckDB: 7 событий двух юзеров → 4 сессии. У Alice(имя выдуманное 🙂 ) первая — 3 события / 25 мин, вторая — 2 / 20 мин. Паузы 95 и 45 мин ловятся тем же LAG.

Как режете сессии на проде — этим трюком, session_window в Spark/Flink, или отдаёте стриминг-движку? И какой порог берёте — 30 мин, 15? 👇

#SQL #SQLWednesday #window_functions #sessionization #DataJungle