Реальные паттерны окон: пропуски, острова, сессии

Сессионизация: LAG по timestamp, флаг разрывов, накопительный SUM флагов

Сессионизация назначает номер сессии каждому событию так, чтобы события внутри разрыва неактивности принадлежали одной сессии. Рецепт — три оконных шага над (PARTITION BY user_id ORDER BY ts):

1. LAG(ts) даёт timestamp предыдущего события для каждой строки.
2. Флаг границы равен 1, когда ts - prev_ts > interval '30 min' (или это первое событие), иначе 0.
3. Накопительный SUM флага превращает эти единицы в монотонно растущий номер сессии — каждый флаг толкает счётчик, каждый не-флаг держит его.

SELECT user_id, ts,
       SUM(is_new_session) OVER (
         PARTITION BY user_id ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_id
FROM (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
                   > interval '30 minutes'
               OR LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL
              THEN 1 ELSE 0 END AS is_new_session
  FROM events
) flagged;

Gaps-and-islands: трюк разности row_number

«Острова» — это серии последовательных значений; «пропуски» — дыры между ними. Классическая задача: по датам логинов найти каждую полосу подряд идущих дней. Трюк в том, что для серии последовательных целых (или дат) значение минус его номер строки постоянно внутри серии, потому что оба растут на 1 в ногу. Когда последовательность пропускает, разность меняется — новый остров.

SELECT user_id, MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end, COUNT(*) AS streak_len
FROM (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM daily_logins
) t
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

grp — это якорь: все строки одной непрерывной полосы делят одно значение login_date - row_number, так что GROUP BY grp схлопывает каждую полосу в одну итоговую строку. Это каноническое решение gaps-and-islands — ROW_NUMBER плюс арифметика плюс GROUP BY по производному ключу. Дедупликация (урок 4) и кумулятивные распределения (урок 5) — меньшие члены того же семейства: каждое — «используй окно, чтобы вывести ключ, затем агрегируй или фильтруй по нему».

Производительность: каждый отдельный ORDER BY окна стоит сортировки

Оконные функции не бесплатны. Каждое отдельное определение окна — конкретно каждый уникальный PARTITION BY ... ORDER BY ... — заставляет Postgres материализовать строки в этом порядке, что означает узел Sort, питающий узел WindowAgg в плане. Сложи три окна с тремя разными упорядочиваниями — получишь три сортировки; на большой таблице это доминирующая стоимость.

Оптимизация — заставить окна делить упорядочивание. Postgres переиспользует одну сортировку для всех оконных функций с одинаковым PARTITION BY/ORDER BY, так что запись SUM(...) OVER w, ROW_NUMBER() OVER w, LAG(...) OVER w против одного именованного WINDOW w AS (PARTITION BY user_id ORDER BY ts) позволяет им ехать на одной сортировке вместо трёх. Когда тебе действительно нужны разные упорядочивания, расставь клаузы окон так, чтобы совместимые сортировки были рядом — планировщик иногда может конвейеризовать одну сортировку в следующую, если та префикс. Всегда проверяй через EXPLAIN ANALYZE: считай узлы Sort и WindowAgg и следи за Sort Method: external merge Disk:, что значит, что сортировка вышла за work_mem и бьёт по диску — момент, когда оконный запрос идёт от миллисекунд к секундам.