Как решить задачу sessionization — сгруппировать события пользователя в сессии, если между событиями прошло больше 30 минут?

Короткий ответ

Нужно с помощью LAG посчитать разницу во времени с предыдущим событием того же пользователя, отметить начало новой сессии там, где разница больше 30 минут, а затем накопительной суммой этих отметок (через SUM(...) OVER) получить номер сессии для каждого события — это классический паттерн «gaps and islands».

Как это работает подробнее

Как это работает: на первом шаге LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) даёт время предыдущего события; на втором шаге условие event_time - prev_event_time > interval '30 minutes' (или prev_event_time IS NULL для самого первого события) помечается как 1 — начало новой сессии, иначе 0; на третьем шаге SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) превращает эти отметки в растущий номер сессии.

Пример кода

PostgreSQL: разбиение событий на сессииКопировать

WITH ordered AS (
  SELECT user_id, event_id, event_time,
         LAG(event_time) OVER (
           PARTITION BY user_id ORDER BY event_time, event_id
         ) AS prev_event_time
  FROM events
), boundaries AS (
  SELECT *,
         CASE WHEN prev_event_time IS NULL
                   OR event_time - prev_event_time > INTERVAL '30 minutes'
              THEN 1 ELSE 0 END AS is_new_session
  FROM ordered
)
SELECT user_id, event_id, event_time,
       SUM(is_new_session) OVER (
         PARTITION BY user_id ORDER BY event_time, event_id
         ROWS UNBOUNDED PRECEDING
       ) AS session_number
FROM boundaries;

Что использовать на практике

• На практике: порог в 30 минут — это условность, принятая во многих продуктовых аналитических системах по умолчанию, но не универсальный стандарт; для конкретного продукта имеет смысл подбирать порог на основе распределения интервалов между событиями, а не слепо копировать общепринятое число.
• Проверка: Проверка: для пользователя с известной последовательностью событий вручную посчитать ожидаемое число сессий и сравнить с MAX(session_id).

Подводные камни

• Частые ошибки: забывают учесть первое событие пользователя (где LAG даёт NULL) как начало новой сессии; не разбивают расчёт на отдельные CTE-шаги, пытаясь сделать всё в одном выражении, из-за чего получают сложный для проверки и отладки запрос.