LatornLatorn
Войти

Как разбить события на сессии в SQL по паузе между действиями

Для внутреннего продукта требуется собственное определение сессии: новая начинается после паузы больше тридцати минут. Количество событий ещё не даёт количество посещений; сначала нужно обнаружить границы внутри последовательности каждого пользователя.

Как разобрать ситуацию

Возьмём время в целых минутах от общего начала. LAG показывает предыдущее действие. Первое событие и разрыв больше порога становятся маркерами новой сессии. Накопительная сумма этих маркеров даёт номер сессии. Две оконные операции разнесены по CTE, чтобы расчёт был понятен и выполнялся последовательно.

Пример и ожидаемый результат

Пользователь действовал на минутах 0, 10, 45 и 60. Между вторым и третьим событием 35 минут, поэтому ожидаем две сессии с номерами 1, 1, 2 и 2.

WITH e(t) AS(VALUES(0),(10),(45),(60)), prev AS(
SELECT t,LAG(t) OVER(
ORDER BY t) p 
FROM e), marks AS(
SELECT t,CASE WHEN p IS NULL OR t-p>30 THEN 1 ELSE 0 END start 
FROM prev) 
SELECT t,SUM(start) OVER(
ORDER BY t ROWS UNBOUNDED PRECEDING) 
FROM marks 
ORDER BY t;

Получаются пары минут и сессий: (0, 1), (10, 1), (45, 2), (60, 2). Каждое событие принадлежит ровно одной сессии.

Проверка перед применением

  • Проверьте паузу ровно 30 минут: при выбранном правиле это продолжение прежней сессии.
  • Добавьте второго пользователя с теми же минутами: нумерация должна начаться заново.
  • Уберите повторные события или задайте порядок для равного времени, если они влияют на дальнейшие действия.

Сохраните рядом с отчётом выбранный порог и знак сравнения. Когда продукт меняет правило с тридцати до шестидесяти минут, это новая версия метрики. Сравнивать старое и новое количество сессий без пересчёта истории нельзя: скачок может возникнуть исключительно из-за определения.

Границы решения

Это пользовательское правило, а не точное воспроизведение конкретного счётчика аналитики. Часовые пояса, поздняя доставка и события вне порядка требуют отдельной обработки до расчёта.

Если запросы уже пишутся, а уверенности в кардинальности, оконных рамках и проверке результата не хватает, продолжить практику можно в нашем платном курсе

Продвинутый SQL: сложные запросы и задачи для собеседований. Перед записью сравните программу со своей задачей и подготовкой.

Техническая справка: www.sqlite.org: документация.

Следующий шаг: Сложные SQL-запросы: разборы расчётов и ошибок. По смежным вопросам: Подписка на часть месяца: как посчитать пропорциональную сумму в SQL, Как распределить скидку заказа по товарам в SQL без потерянной копейки.