SQL для роста навыка: как писать окна (window functions) для ранжирования и кумулятивных метрик

Окна (window functions) в SQL — тот самый инструмент, который часто отделяет «я умею писать SELECT и JOIN» от уровня «я умею решать аналитические задачи». Если JOIN закрывает большинство задач связи таблиц, то оконные функции нужны, когда:

• результат зависит от порядка строк (например, «топ-10 в каждом регионе»);
• нужна относительная позиция (ранг, номер, сравнение с предыдущей строкой);
• требуется расчёт кумулятивных или помесячных/покомпонентных метрик по времени;
• важно посчитать метрики, не «схлопывая» набор строк в одну агрегатную строку (как это делает GROUP BY).

В этой статье разберём практику: ROW_NUMBER, RANK, LAG/LEAD, а также типовые кумулятивные расчёты по времени. Пойдём от ясных задач к более реальным — и разберём типичные ошибки, которые встречаются у разработчиков и аналитиков.

Почему JOIN иногда недостаточно: аналитика — это «зависимость от контекста»

JOIN соединяет строки по ключам. Но аналитика часто требует связать строку не только с «данными из другой таблицы», а с другими строками внутри того же набора.

Примеры:

1. «В каждом магазине показать номер сделки по времени» — это не соединение, это зависимость от сортировки.
2. «Показать топ-3 продавцов в каждом отделе по выручке, но с учётом равенств» — нужен ранжирующий алгоритм.
3. «Сколько пользователей пришло за последние N дней» — это кумулятивная метрика, зависящая от временного ряда.

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

Ключевой синтаксис:

• OVER (...) определяет «окно» и способ разбиения/упорядочивания.
• PARTITION BY разбивает данные на независимые группы.
• ORDER BY задаёт порядок внутри группы.
• Функции вроде SUM(...) OVER или LAG(...) OVER берут значения в пределах окна.

База: структура OVER, порядок и разбиение

PARTITION BY и ORDER BY: что именно вы задаёте

Обычно оконная функция выглядит так:

function_name(...) OVER (
  PARTITION BY key1, key2
  ORDER BY ts_column
)

• PARTITION BY — аналог «в каждом сегменте данных…».
• ORDER BY — определяет последовательность внутри сегмента. Без него функции типа LAG/LEAD и ROW_NUMBER не имеют смысла для временного ряда.

Важно: если вы используете оконную функцию с ORDER BY, но при этом в данных есть равные значения, результат будет зависеть от выбранного порядка. Поэтому часто нужно добавить дополнительный стабильный ключ (например, ORDER BY event_time, id).

Подводные камни сортировки

Если в качестве сортировки взять только event_time, а у двух событий одинаковое время, то порядок между ними не гарантирован (в разных СУБД или при разной оптимизации плана). Для детерминированного результата используйте уникальный/стабильный признак:

ORDER BY event_time, event_id

Ранжирование: ROW_NUMBER() и RANK() в задачах «топ и позиции»

Сценарий 1: «номер заказа по времени в каждом клиенте»

Допустим, у нас есть таблица orders:

• order_id — уникальный идентификатор
• customer_id — клиент
• order_ts — время заказа
• amount — сумма

Хотим вывести для каждого заказа: порядковый номер клиента и сумму заказа.

SELECT
  o.*,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_ts, order_id
  ) AS rn_customer_order
FROM orders o;

Почему ROW_NUMBER?
Потому что он присваивает уникальный номер каждой строке внутри разбиения, даже если значения сортировки одинаковы. Он не учитывает «равенство» в ранге — он просто нумерует строки по порядку.

Типичная ошибка

Часто используют ORDER BY order_ts без второго критерия. Если в данных встречается одинаковое order_ts у разных заказов, вы получите недетерминированный порядок и «прыгающие» значения ранга.

Сценарий 2: RANK() и равенства — «места в таблице результатов»

Пусть есть таблица player_stats:

• league_id
• player_id
• score — баллы

Нужно определить место игрока в лиге по score, при равных баллах — одинаковое место.

SELECT
  league_id,
  player_id,
  score,
  RANK() OVER (
    PARTITION BY league_id
    ORDER BY score DESC
  ) AS place
FROM player_stats;

Что будет при равных score?

• если два игрока имеют одинаковый score и должны быть «1 место», оба получат place = 1;
• следующий игрок получит place = 3 (поскольку RANK «резервирует» позиции).

Если вам нужен «плотный» ранг без пропусков, используйте DENSE_RANK():

DENSE_RANK() OVER (
  PARTITION BY league_id
  ORDER BY score DESC
) AS dense_place

Когда ROW_NUMBER vs RANK действительно важны

• Для «первый/второй/третий заказ» — обычно ROW_NUMBER (строка должна иметь конкретную позицию).
• Для «места в турнирной таблице» — RANK или DENSE_RANK в зависимости от того, нужны ли пропуски при равенствах.

Выбор конкретных строк: «топ-Н в каждой группе» без лишних подзапросов

Топ-3 продавцов в каждом магазине по выручке

Считаем выручку по продавцам и магазинам, затем выбираем топ-3.

Вариант через оконную функцию:

WITH revenue AS (
  SELECT
    store_id,
    seller_id,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY store_id, seller_id
),
ranked AS (
  SELECT
    *,
    DENSE_RANK() OVER (
      PARTITION BY store_id
      ORDER BY revenue DESC
    ) AS seller_rank
  FROM revenue
)
SELECT
  store_id,
  seller_id,
  revenue,
  seller_rank
FROM ranked
WHERE seller_rank <= 3
ORDER BY store_id, seller_rank, seller_id;

Здесь есть важное решение: DENSE_RANK() допускает, что если два продавца разделили второе место, «топ-3 по рангу» может содержать больше трёх строк. Это часто именно то, что нужно в бизнес-логике.

Подводный камень: топ-Н строк vs топ-Н позиций

Иногда требуется строго «три строки» (топ-3 продавца, независимо от равных выручек). Тогда вместо DENSE_RANK используйте ROW_NUMBER():

ROW_NUMBER() OVER (
  PARTITION BY store_id
  ORDER BY revenue DESC, seller_id
) AS rn

LAG() / LEAD(): метрики и сравнения «с предыдущей/следующей строкой»

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

Допустим, есть агрегированная таблица daily_sales:

• day (дата)
• region_id
• sales_amount

Нужно показать продажи текущего дня, продажи предыдущего дня и изменение.

SELECT
  day,
  region_id,
  sales_amount,
  LAG(sales_amount) OVER (
    PARTITION BY region_id
    ORDER BY day
  ) AS prev_sales_amount,
  sales_amount - LAG(sales_amount) OVER (
    PARTITION BY region_id
    ORDER BY day
  ) AS delta_sales_amount
FROM daily_sales
ORDER BY region_id, day;

Да, LAG повторяется дважды. На практике можно вынести в подзапрос, чтобы не дублировать расчёт (ниже покажем компактный шаблон).

Аккуратность с NULL

В первый день у каждого региона LAG вернёт NULL. Это нормальное поведение — метрики «относительно предыдущего» не определены для первой строки. Если вам нужно трактовать NULL как 0, используйте COALESCE:

COALESCE(LAG(sales_amount) OVER (...), 0)

Сценарий: «сколько дней прошло между событиями»

Есть таблица событий events:

• user_id
• event_ts
• event_type

Хотим посчитать длительность между текущим и предыдущим событием пользователя.

SELECT
  user_id,
  event_ts,
  event_type,
  LAG(event_ts) OVER (
    PARTITION BY user_id
    ORDER BY event_ts, event_id
  ) AS prev_event_ts,
  EXTRACT(EPOCH FROM (event_ts - LAG(event_ts) OVER (
    PARTITION BY user_id
    ORDER BY event_ts, event_id
  )))/86400.0 AS days_since_prev
FROM events;

Здесь важны две вещи:

1. ORDER BY event_ts, event_id для детерминизма.
2. В некоторых СУБД выражения для разницы дат могут отличаться; идея одна — вы берёте предыдущий event_ts через LAG и вычитаете.

Как избежать дублирования LAG: шаблон через CTE

WITH base AS (
  SELECT
    day,
    region_id,
    sales_amount,
    LAG(sales_amount) OVER (
      PARTITION BY region_id
      ORDER BY day
    ) AS prev_sales_amount
  FROM daily_sales
)
SELECT
  day,
  region_id,
  sales_amount,
  prev_sales_amount,
  sales_amount - prev_sales_amount AS delta_sales_amount
FROM base
ORDER BY region_id, day;

Этот подход не только читаемее — иногда помогает СУБД оптимизировать план.

Кумулятивные метрики: SUM(...) OVER и «скользящие» расчёты по времени

Кумулятивная (накопительная) метрика — это когда значение для текущей строки зависит от всех предыдущих строк в окне.

SUM(...) OVER как накопление

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

SELECT
  day,
  region_id,
  sales_amount,
  SUM(sales_amount) OVER (
    PARTITION BY region_id
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_sales
FROM daily_sales
ORDER BY region_id, day;

Часть ROWS BETWEEN ... можно опустить в некоторых диалектах SQL, потому что по умолчанию используется «от начала до текущей строки» для ORDER BY в оконной функции, но в промышленном коде лучше задавать явно — чтобы избежать сюрпризов при переносе и изменения настроек парсинга.

Почему иногда важно различать ROWS и RANGE

• ROWS означает «фиксированное число строк» в окне.
• RANGE обычно означает «значения в пределах диапазона по ORDER BY».

Для временных рядов это критично. Если вы упорядочиваете по дате и используете RANGE, то одинаковая дата может «расширять» окно. Поэтому для накопительных сумм чаще подходит ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Скользящие (rolling) метрики: окно фиксированной ширины по времени

«Сумма за последние 7 дней» на уровне строк

Если daily_sales содержит по одной строке на каждый день (без пропусков дат), то удобно считать rolling sum по числу строк:

SELECT
  day,
  region_id,
  sales_amount,
  SUM(sales_amount) OVER (
    PARTITION BY region_id
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_sales
FROM daily_sales
ORDER BY region_id, day;

Окно включает текущую строку и 6 предыдущих.

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

Если у вас нет строк за некоторые дни, «последние 7 строк» ≠ «последние 7 календарных дней». В таких кейсах нужно сначала нормализовать временную шкалу: заполнить отсутствующие даты нулями (или другим значением), чтобы rolling window по ROWS соответствовал реальному времени.

Rolling среднее vs rolling сумма

Среднее делается аналогично:

AVG(sales_amount) OVER (
  PARTITION BY region_id
  ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg_sales

Сумма и среднее — базовые кирпичики для более сложных метрик: темпов роста, SLA-приближений, нормализованных коэффициентов.

Кумулятивные метрики по «событиям»: метрика не только по дням

Иногда данные приходят как события, а аналитика требует расчёта накоплений по времени.

Пример: накопительное число активных пользователей в разрезе дней

Есть таблица user_events:

• user_id
• event_ts

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

Здесь важно: «накопительное число уникальных» — не то же самое, что SUM(1) по дням. Нельзя просто суммировать ежедневные активные количества, потому что пользователь может активироваться несколько дней.

Решение зависит от диалекта SQL и политики по уникальностям. Один из распространённых подходов — считать «первый день активности» для пользователя и затем накапливать количество пользователей, чей первый день ≤ текущей даты.

WITH first_activity AS (
  SELECT
    user_id,
    MIN(DATE(event_ts)) AS first_day
  FROM user_events
  GROUP BY user_id
),
calendar AS (
  SELECT DISTINCT DATE(event_ts) AS day
  FROM user_events
)
SELECT
  c.day,
  COUNT(fa.user_id) FILTER (WHERE fa.first_day <= c.day) AS cumulative_unique_users
FROM calendar c
LEFT JOIN first_activity fa ON fa.first_day <= c.day
GROUP BY c.day
ORDER BY c.day;

Если ваша СУБД не поддерживает FILTER, можно переписать условие через CASE WHEN и SUM:

SUM(CASE WHEN fa.first_day <= c.day THEN 1 ELSE 0 END) AS cumulative_unique_users

Подводный камень

Это решение предполагает, что «активность» определяется минимумом дня события. Если вы хотите другой критерий (например, «активным считается пользователь, активировавшийся в последние 30 дней»), логика будет совсем другой — там нужны rolling окна по факту активностей, а не по первому дню.

Практические стратегии: как превратить оконные функции в навык

1) Начните с «разметки» строк

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

Например:

SELECT
  user_id,
  event_ts,
  event_type,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_ts, event_id
  ) AS rn,
  LAG(event_ts) OVER (
    PARTITION BY user_id
    ORDER BY event_ts, event_id
  ) AS prev_ts
FROM events;

Этот шаг ускоряет диагностику: вы сразу видите, правильно ли работает группировка и порядок.

2) Детерминизм — не прихоть

Добавляйте вторичные ключи в ORDER BY, если возможно равенство:

• ORDER BY day, region_id (если day не уникален внутри partition)
• ORDER BY order_ts, order_id
• ORDER BY event_ts, event_id

3) Учитывайте «семантику топа»

Сформулируйте словами, что вы хотите:

• «три строки» → ROW_NUMBER
• «три места по рангу, с равенствами как в турнирной таблице» → RANK / DENSE_RANK

4) Не переоценивайте «простоту» rolling метрик

Rolling в реальности упирается в качество данных:

• нет ли пропусков дат?
• одинаковые таймстемпы?
• часовой пояс и округление к дате?
• как определена календарная шкала?

Если rolling посчитан по строкам, но данных нет за некоторые дни — результат математически корректен для «последних N строк», но бизнесически неверен для «последних N дней».

Типичные ошибки и как их избегать

Ошибка 1: забыли PARTITION BY

Тогда функция работает по всему набору данных целиком — и результаты становятся бессмысленными.

Например, LAG по всем пользователям даст предыдущий event_ts «какого-то другого пользователя», что легко сломает аналитику.

Ошибка 2: ORDER BY отсутствует

Для ROW_NUMBER, LAG/LEAD, SUM OVER с логикой накопления — ORDER BY критически важен. Без него вы теряете временной контекст.

Ошибка 3: недетерминированный порядок при равных ключах

Как следствие — «у нас одинаковые значения, но ранги пляшут».