Оконные функции SQL: ROW_NUMBER, LAG и накопительный итог

Коротко

Оконная функция выполняет расчёт по “окну” строк, заданному через `OVER`. В отличие от GROUP BY, она сохраняет исходные строки и добавляет к ним вычисленный столбец.

• `PARTITION BY` задаёт независимые группы, например одного пользователя.
• `ORDER BY` внутри окна задаёт порядок строк для нумерации и сравнений.
• `ROW_NUMBER()` нумерует строки внутри каждой группы.
• `LAG()` и `LEAD()` достают значение из предыдущей или следующей строки.
• `SUM(...) OVER` считает накопительный итог, не убирая отдельные операции.

Оконная функция не схлопывает строки

Сравни два подхода. `GROUP BY user_id` вернёт одну строку на пользователя и потеряет порядок отдельных событий. `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_date)` сохранит все события, но добавит к каждому номер внутри истории пользователя.

ROW_NUMBER: найти первое событие пользователя

Один из самых полезных паттернов — найти первое событие в истории пользователя. Сначала нумеруем события по времени внутри каждого user_id, затем оставляем строки с номером один. Если у двух событий одинаковое время, добавь второй стабильный ключ в ORDER BY, например event_id.

with numbered_events as (
  select
    user_id,
    event_date,
    event_name,
    row_number() over (
      partition by user_id
      order by event_date, event_name
    ) as event_number
  from events
  where event_name = 'core_action'
)
select
  user_id,
  event_date as first_core_action_date
from numbered_events
where event_number = 1
order by first_core_action_date, user_id;

LAG: сравнить строку с предыдущей

`LAG` возвращает значение из предыдущей строки в заданном порядке. Для дневного DAU сначала получи одну строку на день, затем добавь предыдущий DAU и разницу. Так можно увидеть не только саму метрику, но и день, когда изменение стало заметным.

У первой даты `previous_dau` будет NULL: до неё нет предыдущей строки. Это нормальный результат, а не ноль. Если в отчёте нужен ноль, преобразуй его явно через `COALESCE`, но не смешивай “нет сравнения” и “предыдущий день действительно был нулевым”.

with daily as (
  select
    event_date,
    count(distinct user_id) as dau
  from events
  where event_name = 'core_action'
  group by event_date
),
, with_previous as (
  select
    event_date,
    dau,
    lag(dau) over (order by event_date) as previous_dau
  from daily
)
select
  event_date,
  dau,
  previous_dau,
  dau - previous_dau as dau_change
from with_previous
order by event_date;

SUM OVER: накопительная выручка

Обычный `SUM(amount)` с GROUP BY даёт одну строку на день или пользователя. Оконный `SUM(amount) OVER (ORDER BY paid_at, user_id)` оставляет каждый платёж и показывает, как растёт итог после каждой операции. Это удобно для финансового графика и проверки, в какой момент достигнут план.

select
  paid_at,
  user_id,
  amount,
  sum(amount) over (
    order by paid_at, user_id
    rows between unbounded preceding and current row
  ) as cumulative_revenue
from payments
order by paid_at, user_id;

Окно по пользователю и окно по всей таблице

Место `PARTITION BY` меняет смысл результата. С `PARTITION BY user_id` нумерация начинается заново для каждого пользователя. Без PARTITION вся таблица считается одной группой. Это удобно для общего накопительного итога, но неверно, если нужно найти первое событие каждого пользователя.

Порядок тоже часть определения метрики. Если сортировать историю только по дате, а на одну дату приходится несколько событий, порядок между ними может быть нестабильным. Добавляй tie-breaker: event_id, payment_id или другой уникальный ключ.

• Окно по user_id — отдельная история каждого пользователя.
• Окно без PARTITION — вся таблица как одна последовательность.
• Окно по channel — сравнение позиций внутри канала.
• `ORDER BY` в окне не обязательно сортирует финальный результат, поэтому внешний ORDER BY всё равно нужен.

Частые ошибки в оконных функциях

Оконные запросы становятся понятными, если всегда отделять два вопроса: какие строки должны остаться и по какой последовательности считается новое поле. Большинство ошибок появляется, когда эти уровни смешивают.

• Использовать ROW_NUMBER без PARTITION BY и получить одну глобальную нумерацию вместо нумерации по пользователю.
• Ожидать, что LAG вернёт ноль для первой строки: там корректно будет NULL.
• Забыть внешний ORDER BY и увидеть строки в порядке, который не обязан совпадать с окном.
• Считать накопительный итог без уникального порядка при одинаковых датах.
• Применить оконную функцию до агрегации, хотя сравнивать нужно уже дневные метрики.
• Использовать окно как замену GROUP BY и случайно оставить слишком детальный результат.

Мини-практика и следующий шаг

Начни с таблицы events: добавь ROW_NUMBER по user_id, затем оставь первые события. После этого собери дневной DAU и добавь LAG. В финале попробуй SUM OVER на payments и сравни накопительный итог с обычной суммой по дням.