SQL для практиков: оконные функции (WINDOW) для рейтингов, удержаний и сложной аналитики без подзапросов

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

Практический смысл такой: если вы видите запрос, где нужно “поставить рейтинг”, “посчитать накопление”, “вычислить удержание”, “сделать витрину без фантомных дублей”, то window functions обычно дают:

• выигрыш по читаемости: логика “что считать в группе и как это привязать к текущей строке” явно выражена в PARTITION BY ... ORDER BY ...;
• часто — выигрыш по производительности: движок может оптимизировать вычисления, потому что окно — это единый план вычислений, а не цепочка вложенных запросов;
• устойчивость к ошибкам: меньше шанс “случайно” изменить зерно данных при переподключениях.

В этой статье разберём, когда window functions дают реальный плюс, а когда лучше выбрать другой подход. Пойдём от типовых задач аналитики (рейтинги, скользящие метрики, кумулятивные значения) к построению “стабильных” витрин и типичным ловушкам.

Когда оконные функции реально выигрывают

Подзапросы vs окна: что именно мы заменяем

Условный плохой паттерн для аналитики:

1. Сначала вычисляем агрегат в подзапросе.
2. Потом соединяем обратно, чтобы привязать результат к строкам.
3. Если нужно ранжирование — снова добавляем слой.

Оконные функции позволяют выразить эту цепочку в одном проходе описания: агрегат/ранг вычисляется “в контексте окна”, и результат кладётся рядом с текущей строкой.

Ключевая мысль: зерно (grain) результата должно быть понятным. Обычно это “одна строка = один событие/пользователь/день в витрине”. Window functions помогают не потерять это зерно.

Три сигнала, что пора думать про WINDOW

1. Нужно сравнить текущую строку с агрегатом по группе
   Например: “в каждом регионе отсортировать пользователей по выручке и проставить места” или “для каждого дня посчитать накопление выручки”.

2. Нужно вычислить кумулятивные/скользящие значения
   Например: “скользящая 7-дневная сумма” или “накопленное удержание”.

3. Нужно ранжировать с разными правилами при одинаковых значениях
   ROW_NUMBER(), RANK(), DENSE_RANK() — это не просто удобство, а корректность бизнес-логики.

Рейтинги: ранжирование без самосоединений

Рейтинги — один из самых частых кейсов. Ошибка обычно в том, что пытаются собрать “топ” через GROUP BY и потом снова “вернуть” строки. Оконки дают правильный и читаемый способ.

ROW_NUMBER() / RANK() / DENSE_RANK() — различия и бизнес-семантика

Предположим, у нас таблица покупок:

• user_id
• shop_id
• amount
• purchase_ts

Мы хотим для каждого магазина построить топ-5 пользователей по сумме.

Вариант: уникальные позиции (ROW_NUMBER)

Если нужно, чтобы каждому пользователю в рамках окна досталась уникальная позиция, даже если суммы одинаковые:

WITH base AS (
  SELECT
    shop_id,
    user_id,
    SUM(amount) AS revenue
  FROM purchases
  GROUP BY shop_id, user_id
)
SELECT
  shop_id,
  user_id,
  revenue,
  ROW_NUMBER() OVER (
    PARTITION BY shop_id
    ORDER BY revenue DESC, user_id
  ) AS rn
FROM base
QUALIFY rn <= 5;

Примечание: QUALIFY поддерживается не везде. В PostgreSQL вместо него обычно используют подзапрос. Здесь важно, что смысл — фильтрация по результату оконной функции, а не “сначала топ, потом ранжирование”.

RANK() — одинаковые суммы получают одинаковое место, с “пробелами”

Если бизнесу важно: “если две команды набрали одинаковые очки — они обе 1-е, следующая — 3-я”:

SELECT
  shop_id,
  user_id,
  revenue,
  RANK() OVER (
    PARTITION BY shop_id
    ORDER BY revenue DESC
  ) AS rnk
FROM base
WHERE RANK() OVER (PARTITION BY shop_id ORDER BY revenue DESC) <= 5;

В PostgreSQL так нельзя напрямую (нельзя в WHERE использовать оконку без подзапроса), но концепт сохраняется: RANK() оставляет дырки.

DENSE_RANK() — одинаковые суммы одинаковые места, “без пробелов”

Если важно: “если равенство — места идут подряд”:

SELECT
  shop_id,
  user_id,
  revenue,
  DENSE_RANK() OVER (
    PARTITION BY shop_id
    ORDER BY revenue DESC
  ) AS drnk
FROM base
;

Типовые ловушки в рейтингах

1) Непредсказуемый порядок при равенствах

Если вы используете ORDER BY revenue DESC, а revenue одинаков — порядок будет зависеть от плана выполнения/физического порядка данных. Это особенно критично для витрин “топ N”.

Решение: добавляйте детерминирующий вторичный ключ (например, user_id).

2) Неправильное окно (ошибка в PARTITION BY)

Случай из практики: пытаются ранжировать “в рамках даты” и забывают про shop_id или наоборот. В результате топы перемешиваются между сущностями, и визуально это может выглядеть как “случайные пики”.

3) Ранжирование по агрегату требует отдельного слоя

Если нужно ранжировать не по исходным строкам, а по сумме — агрегат сначала строят в CTE/подзапросе, а уже потом применяют ROW_NUMBER().

Это не считается “плохим подзапросом” — это разделение шагов по смыслу. Важно, чтобы подзапрос не был “мусорным” и не менял зерно неочевидно.

Скользящие метрики: окна как механизм “окна времени”

Скользящие метрики — частный случай аналитики по времени: rolling 7 days, rolling conversion rate, MA (moving average), сглаживание.

Пример: скользящая 7-дневная сумма

Таблица daily_revenue:

• dt (дата)
• shop_id
• revenue

Нужно посчитать revenue_7d для каждого магазина.

SELECT
  shop_id,
  dt,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY shop_id
    ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d
FROM daily_revenue;

Почему ROWS и RANGE имеют значение

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — берёт ровно 7 строк (если в данных нет пропусков дат — это будет 7 дней).
• RANGE ... зависит от типа ORDER BY и может иначе трактоваться при одинаковых dt.

Если в таблице могут отсутствовать даты (например, нет выручки в выходные), то “ровно 6 preceding” по строкам будет не то, что “6 календарных дней”.

В таких случаях лучше:

• либо заполнить календарь (data densification),
• либо использовать RANGE по интервалам (если поддерживается корректно),
• либо перейти на вычисления через календарную таблицу.

Пример: скользящая конверсия (quotient)

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

• purchases_7d / sessions_7d

SELECT
  shop_id,
  dt,
  sessions,
  purchases,
  CASE
    WHEN SUM(sessions) OVER (
      PARTITION BY shop_id
      ORDER BY dt
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) = 0 THEN NULL
    ELSE
      SUM(purchases) OVER (
        PARTITION BY shop_id
        ORDER BY dt
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
      )::DECIMAL
      / SUM(sessions) OVER (
        PARTITION BY shop_id
        ORDER BY dt
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
      )
  END AS conversion_7d
FROM daily_metrics;

Ошибка, которую регулярно встречают

Делят “покупки текущего дня” на “сессии за 7 дней” — потому что не синхронизировали окна в числителе и знаменателе. В запросе выше окна совпадают: одно и то же окно, одна и та же логика.

Ещё одна ловушка: деление на 0 и типы

Если sessions и purchases целочисленные, то деление может быть целочисленным. Приводите тип, используйте DECIMAL, FLOAT или каст согласно диалекту.

Кумулятивные значения: накопления без “лесенки” подзапросов

Кумулятивные метрики (running total, cumulative sum) обычно строятся через SUM(...) OVER (ORDER BY ...).

Пример: накопленная выручка по пользователю

Таблица user_events:

• user_id
• event_ts
• event_revenue

SELECT
  user_id,
  event_ts,
  event_revenue,
  SUM(event_revenue) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS revenue_cum
FROM user_events;

Обычно ROWS BETWEEN UNBOUNDED PRECEDING ... можно опустить, потому что это дефолт для SUM с ORDER BY, но явность иногда полезнее для читателя (и для ревью).

Пример: кумулятивный “активный статус”

Допустим, у нас есть флаг активности за событие (is_active_event). Накопительно можно оценивать “сколько дней/событий человек был активен” — зависит от ваших правил.

Если is_active_event 0/1:

SELECT
  user_id,
  dt,
  is_active_day,
  SUM(is_active_day) OVER (
    PARTITION BY user_id
    ORDER BY dt
  ) AS active_days_cum
FROM daily_user_status;

Типовая ошибка

Накопление делается по “сырым событиям”, а витрина ожидает “по дням”. Тогда вы получите умножение эффекта: несколько событий в день дадут несколько единиц активности. Решение — агрегировать к нужному зерну до окна.

Удержание (retention): как считать без “адских” джойнов

Retentоn обычно требует сравнения “когорт” и “последующих периодов”. На SQL часто смотрят как на “джойн + фильтры”, но оконные функции могут упростить и сделать результаты стабильнее.

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

Допустим:

• user_id
• activity_dt
• active (1/0)

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

Шаг 1: найти first_activity_dt для пользователя.

WITH first_activity AS (
  SELECT
    user_id,
    MIN(activity_dt) AS first_activity_dt
  FROM user_activity
  GROUP BY user_id
),
base AS (
  SELECT
    ua.user_id,
    ua.activity_dt,
    fa.first_activity_dt,
    DATE_TRUNC('month', fa.first_activity_dt) AS cohort_month,
    DATE_TRUNC('month', ua.activity_dt) AS activity_month
  FROM user_activity ua
  JOIN first_activity fa USING (user_id)
)
SELECT
  user_id,
  cohort_month,
  activity_month,
  (EXTRACT(YEAR FROM activity_month) - EXTRACT(YEAR FROM cohort_month)) * 12
  + (EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM cohort_month)) AS months_after
FROM base;

На этом этапе окно не использовалось — потому что задача “определить первую активность” хорошо решается MIN. Но дальше начинается то, где window functions помогают.

Retention-профиль: “есть активность в M+K” как индикатор

Теперь агрегируем по когортам и периоду, но аккуратно: пользователи должны учитываться ровно один раз на months_after. Для этого применяют MAX(1) или используют оконку для “первой записи в каждом месяце после когорты”.

Например, делаем уникальность:

WITH first_activity AS (
  SELECT user_id, MIN(activity_dt) AS first_activity_dt
  FROM user_activity
  GROUP BY user_id
),
labeled AS (
  SELECT
    ua.user_id,
    DATE_TRUNC('month', fa.first_activity_dt) AS cohort_month,
    DATE_TRUNC('month', ua.activity_dt) AS activity_month,
    (EXTRACT(YEAR FROM DATE_TRUNC('month', ua.activity_dt)) - EXTRACT(YEAR FROM DATE_TRUNC('month', fa.first_activity_dt))) * 12
    + (EXTRACT(MONTH FROM DATE_TRUNC('month', ua.activity_dt)) - EXTRACT(MONTH FROM DATE_TRUNC('month', fa.first_activity_dt))) AS months_after
  FROM user_activity ua
  JOIN first_activity fa ON fa.user_id = ua.user_id
),
dedup AS (
  SELECT
    user_id,
    cohort_month,
    months_after,
    1 AS retained_flag,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, cohort_month, months_after
      ORDER BY activity_month
    ) AS rn
  FROM labeled
)
SELECT
  cohort_month,
  months_after,
  COUNT(*) FILTER (WHERE rn = 1) AS retained_users
FROM dedup
GROUP BY cohort_month, months_after
ORDER BY cohort_month, months_after;

Зачем ROW_NUMBER()? Чтобы при множественных активностях в одном месяце после когорты не “раздуть” удержание. В идеале вы заранее агрегируете активность до уровня “пользователь × месяц”, но если данные пришли иначе, оконка помогает привести результат к нужному зерну.

Кумулятивное удержание vs “точечное”

Иногда бизнес просит “доля пользователей, которые удержались хотя бы один раз после K месяцев”, а не “были активны ровно в K”. Здесь снова полезны окна.

Если у вас есть retained_flag по каждому months_after (0/1), то “хотя бы один раз” — это:

• MAX(retained_flag) OVER (PARTITION BY cohort_month, user_id ORDER BY months_after) на уровне пользователя,
• или “суммарно” на уровне когорты — в зависимости от определения.

Это уже требует аккуратной модели витрины: важно договориться, что считается “удержанием”. SQL не может “угадать бизнес-определение”, но оконки дают возможность выразить его напрямую.

“Стабильные витрины” и фантомные дубли: где window functions спасают

Термин “витрина” в аналитике часто означает: таблица, которую используют отчёты и дашборды. Для витрин критичны два свойства:

1. Зерно (grain) фиксировано: например, “user_id × month”.
2. Стабильность: добавление данных не должно внезапно менять число строк на ключах.

Фантомные дубли появляются, когда:

• в джойне разная гранулярность,
• фильтры применены после неправильного join,
• подзапросы возвращают неуникальные ключи.

Оконные функции помогают, потому что дают возможность:

• “пронумеровать” строки внутри ключа и оставить одну,
• выбрать “последнее/первое” наблюдение на основе ORDER BY,
• устранить дубли без сложных self-join’ов.

Паттерн: оставить последнюю запись по пользователю

Например, таблица user_profile_events содержит изменения профиля.

Нужно построить витрину “последнее известное значение” на текущую дату.

WITH ranked AS (
  SELECT
    user_id,
    updated_at,
    email,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM user_profile_events
)
SELECT
  user_id,
  email,
  updated_at
FROM ranked
WHERE rn = 1;

Без оконки пришлось бы делать self-join с MAX(updated_at) и потом соединять обратно — это часто приводит к ошибкам, если есть одинаковые updated_at (тогда MAX вернёт значение, но джойн вернёт несколько строк).

Оконка решает и это: ROW_NUMBER() можно сделать детерминированным вторичным ключом.

Паттерн: dedup по составному ключу

Если зерно витрины — user_id × activity_day, а входные данные содержат несколько событий в день:

WITH dedup AS (
  SELECT
    user_id,
    activity_day,
    event_type,
    event_ts,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, activity_day
      ORDER BY event_ts DESC
    ) AS rn
  FROM user_events
)
SELECT
  user_id,
  activity_day,
  event_type AS last_event_type
FROM dedup
WHERE rn = 1;

В отчётах “будет по одному событию в день”, и исчезнут фантомные дубляжи.

Паттерн: “выберите лучший кандидат” в рейтинге

Если витрина — это топ-предложение/вариант/план для пользователя, то вместо сложного MAX с join можно использовать ROW_NUMBER():

WITH scored AS (
  SELECT
    user_id,
    offer_id,
    score,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY score DESC, offer_id
    ) AS rn
  FROM offer_candidates
)
SELECT
  user_id,
  offer_id,
  score
FROM scored
WHERE rn = 1;

Производительность: почему окна не всегда быстрее, но часто рациональнее

Window functions — не магическая палочка. Тем не менее, в большинстве реальных систем они лучше, чем многослойные подзапросы с повторным сканированием.

Что обычно ускоряется

• Снижается количество этапов: меньше CTE/подзапросов, меньше джойнов.
• Движок может переиспользовать сортировку: при правильном PARTITION BY/ORDER BY сортировки оптимизатор может сделать одну вместо нескольких.
• Согласованная логика: если окна в разных колонках совпадают по PARTITION BY и ORDER BY, план оптимизации становится проще.

Когда оконки могут стать дорогими

1. Огромные окна + отсутствие фильтрации по дате
   Например, rolling метрики на годы без ограничений.

2. Нет подходящих индексов/кластеризации
   Особенно когда окно требует сортировки по большим объёмам.

3. Слишком много разных окон в одном запросе
   Когда каждое поле считает окно с разным ORDER BY/PARTITION BY, движок может вынуждено сортировать несколько раз.

Практический совет: выровняйте окна

Если вы считаете и revenue_cum, и revenue_7d, но окна должны быть по одним и тем же ключам и сортировкам, формируйте их так, чтобы совпадали PARTITION BY и ORDER BY. Это повышает шанс, что оптимизатор распознает общий порядок.

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

Ошибка 1: использовать оконную функцию в WHERE

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

Правильная схема:

• вычислить окно в подзапросе/CTE,
• затем отфильтровать в верхнем запросе.

Ошибка 2: путать PARTITION BY и ORDER BY

• PARTITION BY задаёт “на какие независимые группы разбить данные”.
• ORDER BY задаёт “какой порядок внутри группы”.

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

Ошибка 3: rolling по “строкам”, когда нужна “календарность”

ROWS BETWEEN ... считает по строкам. Если у вас нет пропусков дат — ок. Если есть — rolling “на 7 дней” может превратиться в rolling “на 7 наблюдений”.

Решение:

• либо нормализуйте календарь до “каждый день есть строка”,
• либо используйте подходящий RANGE/интервальный подход (если ваш движок адекватно его поддерживает),
• либо предварительно агрегируйте данные до дневного/часового уровня.

Ошибка 4: отсутствие детерминизма в сортировках

Всегда добавляйте вторичные ключи в ORDER BY для ранжирования, когда возможны равные значения. Особенно для витрин “top 1”, “последнее значение” и т.п.

Конструктор паттернов: как “думать в окнах”

Чтобы применять window functions уверенно, полезно держать в голове “шаблоны мышления”.

Шаблон A: рейтинг в группе

• Нужно: “поставить место/отсечь топ N”
• Решение: ROW_NUMBER() / RANK / DENSE_RANK() с корректным PARTITION BY и детерминированным ORDER BY.

Шаблон B: накопление

• Нужно: “сколько накопилось до текущей точки”
• Решение: SUM(...) OVER (PARTITION BY ... ORDER BY ... )

Шаблон C: rolling

• Нужно: “за последние N периодов”
• Решение: SUM(...) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...) + проверка соответствия календарности.

Шаблон D: dedup до нужного зерна

• Нужно: “убрать дубли, оставить одну строку на ключ”
• Решение: ROW_NUMBER() с PARTITION BY равным зерну витрины, ORDER BY по бизнес-правилу (последнее/первое/наибольшее значение).

Практический сценарий: строим витрину “пользователь × месяц” без фантомных дублей

Соберём всё в один пример, ближе к реальности.

Допустим:

• Пользователь активен в отдельные дни.
• Нужно витрину user_month, где:
  • cohort_month = месяц первой активности пользователя,
  • active_in_month = 1/0,
  • есть retention_month_index = сколько месяцев прошло от когорты,
  • и мы хотим избежать дублей при множественных событиях в месяце.

WITH user_first AS (
  SELECT
    user_id,
    MIN(activity_dt) AS first_activity_dt
  FROM user_activity
  GROUP BY user_id
),
user_month_raw AS (
  SELECT
    ua.user_id,
    DATE_TRUNC('month', ua.activity_dt) AS activity_month,
    DATE_TRUNC('month', uf.first_activity_dt) AS cohort_month
  FROM user_activity ua
  JOIN user_first uf ON uf.user_id = ua.user_id
),
user_month_dedup AS (
  SELECT
    user_id,
    cohort_month,
    activity_month,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, cohort_month, activity_month
      ORDER BY activity_month
    ) AS rn
  FROM user_month_raw
),
user_month AS (
  SELECT
    user_id,
    cohort_month,
    activity_month,
    1 AS active_in_month
  FROM user_month_dedup
  WHERE rn = 1
),
final AS (
  SELECT
    user_id,
    cohort_month,
    activity_month,
    active_in_month,
    (EXTRACT(YEAR FROM activity_month) - EXTRACT(YEAR FROM cohort_month)) * 12
    + (EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM cohort_month)) AS retention_month_index
  FROM user_month
)
SELECT *
FROM final;

Что здесь “window-based”:

• дедуп по месяцу через ROW_NUMBER(), чтобы гарантировать зерно “user_id × month”.
• затем простая формула индекса.

Дальше из этой витрины уже легко строить:

• когортные таблицы (GROUP BY cohort_month, retention_month_index),
• графики retention,
• накопительные варианты (например, “хотя бы один раз за окно K”).

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

Вывод: окна — это не “ещё одна фича”, а способ удержать контроль над зерном и логикой

Оконные функции — практичный инструмент, особенно в задачах:

• ранжирование и топы (ROW_NUMBER, RANK, DENSE_RANK);
• скользящие метрики (rolling суммы/средние) — с учётом календарности;
• кумулятивные значения (running totals);
• стабильные витрины без фантомных дублей — через dedup-паттерны (ROW_NUMBER() по ключам зерна).

Ключевой критерий успеха — не “использовать WINDOW ради WINDOW”, а строить запрос так, чтобы:

1. окна считались в правильном контексте (PARTITION BY + ORDER BY),
2. результат сохранял нужное зерно,
3. сортировки и рамки окон были согласованы с бизнес-определениями (особенно для rolling и retention).

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