Оконные функции 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 и случайно оставить слишком детальный результат.

Оконная функция после агрегации

Оконная функция работает с теми строками, которые пришли в её слой. Если нужно сравнить DAU по дням, сначала агрегируй события до одной строки на дату, а уже затем применяй LAG. Если поставить LAG на сырые events, предыдущей строкой станет событие, а не предыдущий день.

Это правило помогает разделить detail и metric layers. На детальном уровне нужны user_id и timestamp, на уровне метрики — date и dau. Оконная функция не меняет grain, поэтому перед ней нужно явно получить тот уровень, который сравнивается.

После окна проверь первые строки, количество строк и диапазон дат. Если метрика должна быть одной строкой на день, сравни count(*) с count(distinct event_date). Такой простой sanity-check ловит случайный JOIN и лишнее поле в GROUP BY.

with daily as (
  select
    event_date,
    count(distinct user_id) as dau
  from events
  where event_name = 'core_action'
  group by event_date
), compared 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 change
from compared
order by event_date;

Одинаковые timestamp требуют tie-breaker

Если два события имеют одинаковое время, row_number может выбрать первую строку по-разному между запусками, если в ORDER BY нет уникального поля. Добавь event_id, ingestion_id или другой стабильный ключ. Если такого ключа нет, честно напиши, что порядок внутри одной секунды неизвестен, и не делай вывод о последовательности.

Для rank и dense_rank одинаковые значения получают одинаковое место, а row_number всегда выдаёт разные номера. Выбирай функцию по смыслу: место в рейтинге и первое событие — разные задачи. В отчёте назови, как обрабатываются ничьи.

У LAG первая строка partition закономерно получает NULL. Не превращай её в ноль до того, как посчитаешь долю изменений. Если это первый день истории, предыдущего значения просто нет; если внутри ряда пропущен день, нужно сначала восстановить календарную сетку.

Доля от группы: считаем без второго запроса

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

Приём работает и внутри разрезов. sum(revenue) OVER (PARTITION BY country) даст долю канала внутри своей страны, а без PARTITION BY — долю в общем итоге. Обе колонки можно поставить рядом, и тогда видно, что канал занимает 12% всей выручки, но при этом половину выручки одной страны.

Важно не перепутать уровни: агрегация по группам делается обычным GROUP BY, а окно применяется уже к результату группировки. Оконная функция здесь не заменяет GROUP BY, а надстраивается над ним.

SELECT
  u.channel,
  sum(p.amount)                                        AS revenue,
  sum(sum(p.amount)) OVER ()                           AS revenue_total,
  round(100.0 * sum(p.amount) / sum(sum(p.amount)) OVER (), 1) AS share_pct
FROM users u
JOIN payments p ON p.user_id = u.user_id
GROUP BY u.channel
ORDER BY revenue DESC;

Серии подряд: как посчитать непрерывную активность

Продуктовые команды любят вопрос «сколько дней подряд человек заходит в продукт». В лоб он не решается: в SQL нет функции «длина серии». Зато есть приём, который стоит знать, — его называют gaps and islands, острова и разрывы.

Идея в одном наблюдении. Если пронумеровать дни активности пользователя подряд и вычесть номер из самой даты, то у всех дней одной непрерывной серии разность окажется одинаковой. Как только в активности появляется пропуск, разность меняется — начинается новый «остров». Дальше остаётся сгруппировать по этой разности и посчитать длину каждой серии.

Приём работает и за пределами streak: так же ищут непрерывные периоды подписки, интервалы без ошибок в логах или дни, когда метрика держалась выше порога. Один раз разобравшись с ним, вы будете узнавать задачу по формулировке «сколько подряд».

WITH active_days AS (          -- зерно: пользователь + день
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day
  FROM events
),
marked AS (
  SELECT
    user_id,
    day,
    day - CAST(row_number() OVER (PARTITION BY user_id ORDER BY day) AS INTEGER) AS island
  FROM active_days
)
SELECT
  user_id,
  min(day)   AS streak_start,
  max(day)   AS streak_end,
  count(*)   AS streak_days
FROM marked
GROUP BY user_id, island
HAVING count(*) > 1
ORDER BY streak_days DESC, user_id;

Словарь окон: какой функцией решается какая задача

Оконных функций немного, и почти все аналитические задачи сводятся к десятку узнаваемых паттернов. Полезно один раз запомнить соответствие «задача → функция», чтобы не изобретать подзапрос там, где хватает одной строки.

Отдельно стоит группа first_value и last_value: они достают значение с края окна и удобны, когда нужно сравнить каждую строку с исходным состоянием — например, показать выручку месяца рядом с выручкой первого месяца когорты.

Цена окна: сортировка, память и место расчёта

Оконная функция требует упорядочить данные внутри каждой секции. На небольших выборках это незаметно, но на десятках миллионов строк сортировка становится главной статьёй расходов запроса, особенно если секций мало, а строк в каждой много.

Практические выводы простые. Сначала сужайте данные — фильтр по периоду и агрегация до нужного зерна дешевле, чем окно поверх всей истории. Если по полям PARTITION BY и ORDER BY есть подходящий индекс, база может пропустить отдельную сортировку. И помните, что несколько окон с одинаковой спецификацией считаются за один проход, а с разными — за несколько: одинаковые OVER лучше выписывать одинаково или выносить в WINDOW.

Есть и вопрос места расчёта. Скользящее среднее и доли многие BI-инструменты умеют считать сами. Если метрика нужна только на одном графике, дешевле оставить это BI; если она попадает в несколько отчётов и должна везде совпадать — считайте в SQL и храните как витрину.

Рамка окна: где заканчивается «до текущей строки»

У окна есть не только PARTITION BY и ORDER BY, но и рамка — набор строк, которые участвуют в расчёте для текущей строки. Обычно её не пишут явно, и это работает до тех пор, пока не появляются повторяющиеся значения в сортировке.

Умолчание такое: если у окна есть ORDER BY, рамкой считается RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, а CURRENT ROW в режиме RANGE означает не одну строку, а все строки с тем же значением сортировки. Практический эффект: накопительный итог по дням при нескольких платежах в один день сразу включит весь день, а не остановится на текущей записи.

Если нужен именно построчный накопительный итог, рамку задают явно через ROWS. Разница между ROWS и RANGE — одна из тех вещей, которые незаметны на аккуратных данных и проявляются ровно в тот день, когда два события совпали по времени.

Та же рамка позволяет считать скользящие метрики: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW даёт среднее за последние семь строк — привычный сглаженный ряд для дневных метрик.

-- построчный накопительный итог: каждая строка добавляет свой платёж
SELECT payment_id, paid_at, amount,
       sum(amount) OVER (
         ORDER BY paid_at, payment_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM payments;

-- скользящее среднее DAU за семь дней
WITH daily AS (
  SELECT CAST(event_time AS DATE) AS day, count(DISTINCT user_id) AS dau
  FROM events GROUP BY day
)
SELECT day, dau,
       round(avg(dau) OVER (
         ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 1) AS dau_7d_avg
FROM daily
ORDER BY day;

Сквозной пример: путь пользователя от регистрации до оплаты

Соберём типичную задачу целиком на учебной базе «Маяка» — той же, что в SQL-тренажёре. Вопрос звучит так: сколько времени проходит от регистрации до первого создания рабочего пространства и до первого платежа, и у кого этот путь длиннее всего.

Без окон такую задачу решают через несколько подзапросов с min(). С окнами она разбивается на два понятных шага: пронумеровать события каждого пользователя по времени, оставить первые и посчитать разницу дат. Первый шаг — ровно ROW_NUMBER, второй — обычная арифметика.

Тонкость здесь одна, зато принципиальная: ROW_NUMBER обязан иметь детерминированную сортировку. Если у пользователя два события с одинаковым временем, без второго поля в ORDER BY результат будет меняться от запуска к запуску, и «первое действие» окажется случайным.

WITH first_activation AS (
  SELECT user_id, event_time,
         row_number() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS rn
  FROM events
  WHERE event_name = 'workspace_created'
),
first_payment AS (
  SELECT user_id, paid_at,
         row_number() OVER (PARTITION BY user_id ORDER BY paid_at, payment_id) AS rn
  FROM payments
)
SELECT
  u.user_id,
  u.channel,
  u.signup_date,
  CAST(a.event_time AS DATE)                       AS activated_on,
  p.paid_at                                        AS first_payment_on,
  CAST(a.event_time AS DATE) - u.signup_date       AS days_to_activation,
  p.paid_at - u.signup_date                        AS days_to_payment
FROM users u
LEFT JOIN first_activation a ON a.user_id = u.user_id AND a.rn = 1
LEFT JOIN first_payment    p ON p.user_id = u.user_id AND p.rn = 1
ORDER BY days_to_payment NULLS LAST, u.user_id;

Как читать такой результат

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

Обратите внимание на NULLS LAST в сортировке: без него пользователи без платежа в PostgreSQL окажутся в конце при возрастании и в начале при убывании, и «топ самых быстрых» неожиданно начнётся с тех, кто не заплатил никогда.

И главное ограничение: разница дат отвечает на вопрос «сколько прошло», а не «почему». Если время до оплаты в одном канале вдвое больше, это повод посмотреть на онбординг и на обещание рекламы, а не вывод о качестве канала. Окна дают точную хронологию — объяснение по-прежнему остаётся работой аналитика.

Чеклист перед тем, как поставить окно в отчёт

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

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

• В ORDER BY окна есть поле, разрывающее равенство, — иначе порядок недетерминирован.
• PARTITION BY перечисляет ровно те поля, которые вы называете группой словами.
• Для накопительных итогов рамка задана явно, если в сортировке возможны одинаковые значения.
• Ряд для lag и lead не имеет пропусков: отсутствующий день — это не «предыдущий», а дыра.
• Фильтр по результату окна стоит во внешнем запросе, а не в WHERE того же уровня.
• Результат сверен на одном пользователе, которого можно проверить руками.

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

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