SQL дата-инженера: окна, дубли, сессии

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

Оконные функции за минуту

функция() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...)

• PARTITION BY делит строки на группы, но, в отличие от GROUP BY, не схлопывает их: каждая строка остаётся.
• ORDER BY задаёт порядок внутри группы.
• Рамка задаёт, какие строки видит агрегат. Важно: если указан ORDER BY, а рамка нет, по умолчанию действует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, то есть нарастающий итог, причём строки с одинаковым значением сортировки попадают в рамку вместе.

Основные функции:

• Ранжирование: ROW_NUMBER (уникальный номер), RANK (с пропусками после равных), DENSE_RANK (без пропусков).
• Смещение: LAG, LEAD, FIRST_VALUE, LAST_VALUE (у последней ловушка с рамкой по умолчанию).
• Агрегаты: SUM, AVG, COUNT, MIN, MAX как оконные.

Дедупликация событий

События приходят повторно: ретраи продюсера, at-least-once доставка, перезапуск загрузки. Стандартный приём: пронумеровать дубли и оставить один.

SELECT *
FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at DESC) AS rn
  FROM raw_events e
) t
WHERE rn = 1;

Ключ дедупликации выбирают по смыслу: идентификатор события, если он есть, иначе набор полей. Сортировка определяет, какую копию оставить: последнюю полученную, последнюю версию по updated_at. В PostgreSQL короче через DISTINCT ON (event_id) ... ORDER BY event_id, received_at DESC.

Сессионизация

Сессия: последовательность событий пользователя, где пауза между соседними событиями не больше порога, например 30 минут.

1. LAG(ts) даёт время предыдущего события пользователя.
2. Флаг новой сессии: предыдущего нет или пауза больше порога.
3. Нарастающая сумма флагов по пользователю даёт номер сессии.

WITH f AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER w <= INTERVAL '30 minutes' THEN 0 ELSE 1 END AS new_session
  FROM events
  WINDOW w AS (PARTITION BY user_id ORDER BY ts)
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_no
FROM f;

Когда предыдущего события нет, LAG возвращает NULL, сравнение даёт NULL, и CASE уходит в ELSE 1: первая строка открывает сессию.

Пропуски и острова

Та же идея для непрерывных периодов: дни подряд с активностью, периоды одного статуса. Для последовательных дат удобен трюк: дата - ROW_NUMBER() одинакова внутри непрерывного отрезка.

SELECT user_id, MIN(d) AS start_day, MAX(d) AS end_day
FROM (
  SELECT user_id, d, d - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d)::int AS grp
  FROM activity
) t
GROUP BY user_id, grp;

Нарастающие итоги и скользящие окна

SUM(amount) OVER (PARTITION BY user_id ORDER BY day)                         -- нарастающий итог
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)   -- 7 строк
AVG(revenue) OVER (ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) -- 7 календарных дней

ROWS считает строки, RANGE значения сортировки. Если в данных пропущены дни, «7 строк» и «7 дней» дают разный результат. Надёжнее сначала построить ряд дат через generate_series и присоединить данные, чтобы пропущенные дни стали нулями.

Инкрементальная агрегация

Пересчитывать агрегаты за всю историю каждую ночь дорого. Обычно пересчитывают только затронутые периоды: удаляют и вставляют заново агрегаты за последние N дней (с запасом на опоздавшие события) или делают upsert по ключу периода. Главное свойство такой загрузки: идемпотентность, повторный запуск даёт тот же результат.

Частые ошибки

• Забыть, что рамка по умолчанию при ORDER BY даёт нарастающий итог.
• LAST_VALUE без явной рамки до конца раздела возвращает текущую строку.
• Дедуплицировать через DISTINCT по всем колонкам, когда копии отличаются временем получения.
• Сессионизация без PARTITION BY user_id: сессии разных пользователей перемешаются.
• Скользящее среднее через ROWS по ряду с пропущенными днями.

Что спросят на собесе

• Разница ROW_NUMBER, RANK, DENSE_RANK.
• Как удалить дубли событий, оставив последнюю версию.
• Как разбить события на сессии по паузе.
• ROWS против RANGE, рамка по умолчанию.
• Как сделать пересчёт агрегатов идемпотентным.