Сессионизация событий в SQL: оконные функции

Q: Как правильно работать с первым событием пользователя, где gap равен NULL?

Первое событие всегда открывает сессию, поэтому NULL-разрыв должен давать флаг $1$. Ловушка в том, что сравнение NULL > 30 в SQL возвращает не FALSE, а NULL, и без явной проверки первое событие останется без флага. Правильно писать условие как gap_minutes IS NULL OR gap_minutes > 30 — сначала отдельно ловим NULL, потом проверяем порог. Это одна из самых частых ошибок в таких задачах на собесе.

Q: А если между сессиями нужно вставить «пустое» окно или посчитать межсессионный интервал?

Тот же LAG, только на уровне сессий, а не событий. Сначала агрегируешь события в сессии (шаг 5), потом поверх готовых сессий берёшь LAG(session_start) OVER (PARTITION BY user_id ORDER BY session_start) и считаешь разрыв между визитами. Так получаешь метрику «сколько прошло между заходами» — полезно для оценки частоты возвращений. Разбор оконных функций во всех вариациях — в гайде /sql/window.

Сессионизация — это когда у тебя есть плоский поток событий (клики, просмотры, добавления в корзину), а продукту нужно понять: сколько раз пользователь заходил, что делал за один «присест» и когда ушёл. В аналитике почти нет метрик, которые не упираются в сессии: конверсия внутри визита, глубина просмотра, длительность, доля отказов. И почти всегда логика одна — новая сессия начинается, когда между двумя событиями прошло больше N минут (стандарт — 30). Ниже разбираю, как собрать сессии одним SQL-запросом на оконных функциях: LAG для разницы во времени, флаг новой сессии, нарастающая сумма для session_id, а сверху — метрики.

Зачем вообще сессионизировать на SQL

Логи событий приходят как есть: user_id, event_type, event_time. Никакого поля «сессия» в них нет — его нужно вычислить. Можно тащить сырые события в Python и гонять groupby, но если данные лежат в хранилище (ClickHouse, PostgreSQL, Greenplum), считать сессии прямо там дешевле: не надо выгружать миллионы строк на клиент, а результат сразу ложится в витрину.

Правило таймаута простое: если пользователь ничего не делал 30 минут, считаем, что он ушёл, а следующее событие — уже новый визит. Число 30 — не догма, это исторический дефолт из веб-аналитики. Для мобильного приложения с фоновыми пушами берут 15 минут, для B2B-дашборда, где сессия длинная и с паузами на созвоны, — час. Порог всегда согласуют с продуктом, а не берут с потолка.

Пример данных

Соберём таблицу событий для двух пользователей. У первого — два явных визита с большим разрывом посередине, у второго — один непрерывный.

CREATE TABLE events (
    user_id    INT,
    event_type TEXT,
    event_time TIMESTAMP
);

INSERT INTO events (user_id, event_type, event_time) VALUES
(1, 'open',        '2026-06-01 10:00:00'),
(1, 'search',      '2026-06-01 10:02:30'),
(1, 'view_item',   '2026-06-01 10:05:10'),
(1, 'add_to_cart', '2026-06-01 10:07:45'),
-- разрыв 50 минут -> новая сессия
(1, 'open',        '2026-06-01 10:58:00'),
(1, 'search',      '2026-06-01 10:59:20'),
(1, 'checkout',    '2026-06-01 11:03:00'),
(2, 'open',        '2026-06-01 12:00:00'),
(2, 'view_item',   '2026-06-01 12:04:00'),
(2, 'view_item',   '2026-06-01 12:09:30'),
(2, 'add_to_cart', '2026-06-01 12:15:00');

У первого пользователя разрыв между add_to_cart в 10:07:45 и open в 10:58:00 — почти 51 минута. Это больше 30, значит между ними граница сессий. Всё остальное внутри 30-минутных промежутков, поэтому склеивается в один визит.

Шаг 1. Разница во времени между соседними событиями через LAG

LAG возвращает значение из предыдущей строки в рамках окна. Разбиваем по user_id (сессии считаются независимо для каждого пользователя) и сортируем по времени — тогда «предыдущее» событие для каждой строки будет реально предыдущим по хронологии.

SELECT
    user_id,
    event_type,
    event_time,
    LAG(event_time) OVER (
        PARTITION BY user_id
        ORDER BY event_time
    ) AS prev_time
FROM events;

prev_time для самого первого события пользователя будет NULL — предыдущей строки просто нет. Это важно: первое событие всегда открывает новую сессию, и NULL нам как раз на руку.

Теперь считаем разницу. В PostgreSQL вычитание двух timestamp даёт interval, а мне удобнее работать с секундами или минутами — переведу через EXTRACT(EPOCH ...):

SELECT
    user_id,
    event_type,
    event_time,
    EXTRACT(EPOCH FROM (
        event_time - LAG(event_time) OVER (
            PARTITION BY user_id ORDER BY event_time
        )
    )) / 60 AS gap_minutes
FROM events;

Для первого пользователя gap_minutes пойдёт как NULL, 2.5, 2.67, 2.58, 50.25, .... Вот эти 50.25 минут — и есть граница, которую мы ищем.

Шаг 2. Флаг новой сессии

Событие открывает новую сессию, если выполняется одно из двух: либо это первое событие пользователя (gap_minutes IS NULL), либо разрыв больше порога. Заворачиваю логику из шага 1 в CTE, чтобы не дублировать оконную функцию, и ставлю флаг $1/0$:

WITH gaps AS (
    SELECT
        user_id,
        event_type,
        event_time,
        EXTRACT(EPOCH FROM (
            event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
            )
        )) / 60 AS gap_minutes
    FROM events
)
SELECT
    user_id,
    event_type,
    event_time,
    gap_minutes,
    CASE
        WHEN gap_minutes IS NULL OR gap_minutes > 30 THEN 1
        ELSE 0
    END AS is_new_session
FROM gaps;

Порядок условий в CASE не случаен: сначала проверяем NULL, потому что сравнение NULL > 30 вернёт не TRUE, а NULL, и без явной обработки первое событие пользователя не получит флаг. Для первого пользователя флаги встанут как 1, 0, 0, 0, 1, 0, 0 — ровно две единицы, две сессии.

Порог 30 стоит вынести — либо параметром в витрину, либо в конфиг оркестратора. Захардкоженная тридцатка по всему коду однажды аукнется, когда продукт попросит пересчитать сессии для мобилки на другом таймауте.

Шаг 3. Нарастающая сумма для session_id

Теперь фокус: если посчитать нарастающую сумму флага is_new_session в хронологическом порядке, каждое событие получит номер своей сессии. Единица прибавляется на каждой границе, а внутри сессии флаги нулевые — сумма не меняется. Это классический приём gaps-and-islands: границы задают «острова», а running sum их нумерует.

WITH gaps AS (
    SELECT
        user_id,
        event_type,
        event_time,
        EXTRACT(EPOCH FROM (
            event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
            )
        )) / 60 AS gap_minutes
    FROM events
),
flags AS (
    SELECT
        user_id,
        event_type,
        event_time,
        CASE
            WHEN gap_minutes IS NULL OR gap_minutes > 30 THEN 1
            ELSE 0
        END AS is_new_session
    FROM gaps
)
SELECT
    user_id,
    event_type,
    event_time,
    SUM(is_new_session) OVER (
        PARTITION BY user_id
        ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_num
FROM flags;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — это рамка окна: от первой строки пользователя до текущей включительно. Для нарастающей суммы её можно опустить (при наличии ORDER BY по умолчанию окно и так RANGE ... UNBOUNDED PRECEDING), но я пишу явно по двум причинам. Во-первых, читается однозначно. Во-вторых, дефолт RANGE схлопывает строки с одинаковым временем в одну группу — если у двух событий совпал event_time до секунды, RANGE даст им одинаковую сумму, а ROWS посчитает построчно. Для сессий это может исказить нумерацию, так что ROWS надёжнее.

Внутри одного пользователя session_num будет 1, 1, 1, 1, 2, 2, 2 для первого и 1, 1, 1, 1 для второго. Локальный номер готов.

Шаг 4. Глобальный session_id

session_num уникален только внутри пользователя: у первого и второго есть своя «сессия 1». Чтобы получить глобальный ключ, склеиваю user_id и session_num. Проще всего — конкатенация в строку, но для витрины лучше стабильный хеш или сурогатный ключ. Строковый вариант читаемый и его достаточно для аналитики:

... 
SELECT
    user_id,
    event_type,
    event_time,
    user_id || '-' || SUM(is_new_session) OVER (
        PARTITION BY user_id
        ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_id
FROM flags;

Получаем session_id вида 1-1, 1-2, 2-1. Дальше по этому ключу считаем всё что угодно. Если хочется потренировать эту цепочку на живых данных с автопроверкой — такие задачи собраны в /sql-sandbox, там как раз есть блок с оконными функциями и сессиями.

Шаг 5. Метрики сессий

Когда каждое событие размечено session_id, метрики — это просто GROUP BY session_id. Соберём три главные: длительность, глубину (число событий) и время старта.

WITH gaps AS (
    SELECT user_id, event_type, event_time,
        EXTRACT(EPOCH FROM (
            event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time)
        )) / 60 AS gap_minutes
    FROM events
),
flags AS (
    SELECT user_id, event_type, event_time,
        CASE WHEN gap_minutes IS NULL OR gap_minutes > 30
             THEN 1 ELSE 0 END AS is_new_session
    FROM gaps
),
sessions AS (
    SELECT user_id, event_type, event_time,
        user_id || '-' || SUM(is_new_session) OVER (
            PARTITION BY user_id ORDER BY event_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_id
    FROM flags
)
SELECT
    session_id,
    user_id,
    MIN(event_time)                                        AS session_start,
    MAX(event_time)                                        AS session_end,
    COUNT(*)                                               AS events_count,
    EXTRACT(EPOCH FROM (MAX(event_time) - MIN(event_time)))
        / 60                                               AS duration_minutes,
    COUNT(*) FILTER (WHERE event_type = 'checkout')        AS did_checkout
FROM sessions
GROUP BY session_id, user_id
ORDER BY session_id;

Что здесь считается:

Длительность — разница между последним и первым событием сессии. У сессии из одного события длительность будет 0, и это нормально: пользователь зашёл и сразу ушёл. Такие «одноударные» сессии часто отделяют — они кандидаты в отказы.
Глубина — COUNT(*), число событий за визит. Прокси вовлечённости: чем больше действий, тем активнее сессия.
Конверсия — COUNT(*) FILTER (WHERE event_type = 'checkout') возвращает 1, если внутри сессии был чекаут. FILTER в PostgreSQL — чистый способ считать условные агрегаты, читается лучше, чем SUM(CASE WHEN ...).

Для нашего примера первая сессия первого пользователя (1-1) — 4 события, длительность ~7.75 минут, чекаута нет. Вторая (1-2) — 3 события, ~5 минут, чекаут был. У второго пользователя одна сессия на 4 события без чекаута.

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

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

Дубли событий. Если трекер шлёт одно и то же событие дважды с одинаковым временем, разрыв между ними — 0, на сессионизацию не влияет. Но глубину раздувает. Дедуп по (user_id, event_type, event_time) перед сессионизацией снимает вопрос.

Часовые пояса. Если события пишутся в UTC, а порог считается по локальному времени пользователя — на границах суток и переходах на летнее время вылезут артефакты. Держи всё в одном поясе (обычно UTC) до самого конца, конвертируй только на отображении.

Сортировка при равном времени. Если у двух событий совпал event_time, ORDER BY event_time не гарантирует стабильный порядок между запусками. Добавь тайбрейкер: ORDER BY event_time, event_id. Иначе нумерация сессий может «плавать» от прогона к прогону.

Производительность. Три оконные функции с PARTITION BY user_id — это сортировка по пользователю и времени. На больших объёмах помогает индекс (user_id, event_time) или предварительная сортировка данных в хранилище. В ClickHouse эту же задачу решают через neighbor() или arrayJoin по массивам событий, но сама идея — разрыв, флаг, running sum — переносится один в один.

Частые вопросы

Почему именно 30 минут таймаута?

Это дефолт из веб-аналитики, прижившийся исторически, а не физический закон. Логика: 30 минут — разумная оценка того, что человек отвлёкся и вернулся уже с новой задачей. Для мобильных приложений часто берут 15 минут (короче сессии, чаще заходы), для тяжёлых B2B-инструментов — час и больше. Порог всегда согласуют с продуктом и обязательно выносят в параметр, чтобы пересчитать без переписывания запроса.

Чем сессионизация на SQL лучше, чем в Python?

Не «лучше», а «ближе к данным». Если события уже в хранилище, считать сессии на SQL дешевле — не надо выгружать миллионы строк на клиент, результат сразу становится витриной, которую переиспользуют другие отчёты. Python выигрывает, когда логика сложная (например, вложенные под-сессии или машинное определение границ), когда данные и так уже в памяти или когда нужна интеграция с ML-пайплайном. На собеседовании чаще спрашивают именно SQL-вариант через оконные функции — потренировать можно в /python-sandbox для сравнения подходов.

Как правильно работать с первым событием пользователя, где gap равен NULL?

Первое событие всегда открывает сессию, поэтому NULL-разрыв должен давать флаг $1$. Ловушка в том, что сравнение NULL > 30 в SQL возвращает не FALSE, а NULL, и без явной проверки первое событие останется без флага. Правильно писать условие как gap_minutes IS NULL OR gap_minutes > 30 — сначала отдельно ловим NULL, потом проверяем порог. Это одна из самых частых ошибок в таких задачах на собесе.

А если между сессиями нужно вставить «пустое» окно или посчитать межсессионный интервал?

Тот же LAG, только на уровне сессий, а не событий. Сначала агрегируешь события в сессии (шаг 5), потом поверх готовых сессий берёшь LAG(session_start) OVER (PARTITION BY user_id ORDER BY session_start) и считаешь разрыв между визитами. Так получаешь метрику «сколько прошло между заходами» — полезно для оценки частоты возвращений. Разбор оконных функций во всех вариациях — в гайде /sql/window.

Что запомнить

Сессионизация — это одна и та же цепочка из четырёх шагов, которую стоит довести до автоматизма:

LAG по event_time с PARTITION BY user_id ORDER BY event_time — разрыв до предыдущего события.
CASE-флаг новой сессии: gap IS NULL OR gap > порог.
Running sum флага (SUM(...) OVER (... ROWS UNBOUNDED PRECEDING)) — локальный номер сессии.
Склейка с user_id в глобальный session_id, дальше GROUP BY для метрик.

Всё остальное — вариации: другой порог, тайбрейкер в сортировке, дедуп на входе. Если задача про сессии всплывёт на собеседовании — а она всплывает часто — ты соберёшь запрос по этой схеме за пару минут. Больше похожих продуктовых SQL-задач с автопроверкой лежит в /sql-sandbox, а с оконными функциями с нуля поможет разобраться бесплатный курс.