Оконные функции: ROW_NUMBER, RANK, LAG

Задача: вывести каждого сотрудника с его зарплатой, средней зарплатой по отделу и рангом внутри отдела - одним запросом, без потери строк. До SQL:2003 это требовало трёх запросов или чёрной магии самосоединений. Оконные функции решают это в одну строку `OVER()`.

• **Финансовый анализ**: нарастающая выручка, скользящее среднее за 30 дней, процентные изменения относительно предыдущего периода - стандартный инструмент BI-дашбордов
• **Top-N из группы**: последняя транзакция каждого пользователя, лучший продукт каждой категории - ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) заменяет громоздкие подзапросы
• **A/B тесты**: PERCENT_RANK() и NTILE() для анализа распределений конверсий по когортам без выгрузки в Python

Что такое окно

Оконные функции - возможно, самый недооценённый инструмент SQL. До их появления для вычисления ранга сотрудника в отделе требовался коррелированный подзапрос или самосоединение. Оконная функция делает то же самое одной строкой, при этом **не сворачивая строки** - в отличие от GROUP BY. Каждая строка результата сохраняет свою идентичность, а функция вычисляется по «окну» соседних строк.

Ключевое отличие от агрегатов: `GROUP BY` сворачивает несколько строк в одну. Оконная функция с `OVER()` вычисляет агрегат по группе, но **возвращает значение для каждой строки** группы отдельно. Это позволяет одновременно видеть детальные данные и агрегат.

PARTITION BY и ORDER BY

`OVER()` - пустое окно: функция видит все строки таблицы. `OVER(PARTITION BY col)` разбивает строки на независимые группы - каждый раздел обрабатывается отдельно, как если бы это была отдельная таблица. `ORDER BY` внутри `OVER()` определяет порядок строк **внутри** раздела - это влияет на ранжирующие функции и frame clause, но не на порядок вывода результата.

`ORDER BY` в `OVER()` и `ORDER BY` в конце запроса - независимы. Первый определяет, как упорядочены строки внутри окна при вычислении функции. Второй определяет порядок вывода. Они могут быть разными.

ROW_NUMBER, RANK, DENSE_RANK

Три ранжирующие функции выглядят похоже, но ведут себя принципиально по-разному при совпадающих значениях. `ROW_NUMBER()` всегда уникален - даже при равных значениях порядок произволен. `RANK()` присваивает одинаковый ранг равным значениям, пропуская следующие номера. `DENSE_RANK()` тоже равные значения ставит на один ранг, но следующий ранг - без пропуска. Выбор зависит от бизнес-логики.

Частый паттерн: `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)` + WHERE row_num = 1 в CTE - это эффективный способ выбрать по одной строке из каждой группы (например, последнюю транзакцию каждого пользователя). PostgreSQL также поддерживает `DISTINCT ON`, но ROW_NUMBER - переносимое решение.

LAG и LEAD

`LAG(col, n)` возвращает значение столбца из строки **на n позиций назад** в окне. `LEAD(col, n)` - **на n позиций вперёд**. По умолчанию n=1. Это стандартное решение для задач «сравни текущее значение с предыдущим» - расчёт изменения между периодами, разница дат, анализ временных рядов. Без них пришлось бы делать самосоединение таблицы по смещённому индексу.

`LAG(col, n, default)` принимает третий аргумент - значение по умолчанию, если предыдущей строки нет (первая строка в разделе). Без default функция возвращает NULL. Это важно при вычислении разниц: первый элемент ряда не имеет предыдущего значения.

Frame Clause

Frame clause уточняет, **какие именно строки** включаются в окно для вычисления функции. По умолчанию при наличии `ORDER BY` frame = `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` - от начала раздела до текущей строки. Без `ORDER BY` frame = весь раздел. Frame можно задать через `ROWS` (физические строки) или `RANGE` (строки с таким же значением сортировки). Именно frame clause позволяет вычислять скользящие средние.

`ROWS` vs `RANGE`: при дублирующихся значениях ORDER BY они ведут себя по-разному. `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` - ровно 3 физические строки. `RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING` - все строки, значение которых попадает в диапазон [current-1, current+1]. Для скользящих средних по окну фиксированного размера используйте `ROWS`.

Нарастающие итоги и практические паттерны

Нарастающий итог (running total, cumulative sum) - наиболее частое применение оконных функций в аналитике. Классический сценарий: кумулятивная выручка за год, нарастающий баланс счёта, процентиль по выборке. PostgreSQL также поддерживает `PERCENT_RANK()`, `CUME_DIST()` и `NTILE(n)` для статистического анализа распределений.

Оконные функции вычисляются **после** WHERE, GROUP BY и HAVING, но **до** внешнего SELECT и ORDER BY. Поэтому нельзя использовать оконную функцию в WHERE - нужен CTE или подзапрос. Это ключевое ограничение при отладке сложных запросов.

Ключевые идеи

• **OVER()** - признак оконной функции: вычисляет агрегат по окну строк, не сворачивая результат в отличие от GROUP BY
• **PARTITION BY** разбивает на независимые окна; **ORDER BY** внутри OVER определяет порядок для ранжирования и нарастающих итогов
• **Frame clause** (ROWS/RANGE BETWEEN ...) точно задаёт границы окна - основа скользящих средних; оконные функции вычисляются после WHERE, поэтому фильтрация по ним - только через CTE

Связанные темы

Оконные функции часто применяются в связке с другими инструментами PostgreSQL:

• CTE (Common Table Expressions) — CTE используются для фильтрации по результатам оконных функций, поскольку WHERE не видит оконный результат напрямую
• Агрегатные функции и GROUP BY — GROUP BY и оконные функции решают разные задачи: первый сворачивает строки, второй сохраняет детальность при добавлении агрегатов

Вопросы для размышления

• Как выбрать между ROW_NUMBER, RANK и DENSE_RANK при построении рейтинга с возможными ничьями?
• Почему ROWS BETWEEN предпочтительнее RANGE BETWEEN для скользящих средних по временным рядам с дублями дат?
• Как бы реализовать задачу 'топ-3 заказа по сумме для каждого клиента' без оконных функций, и насколько сложнее это решение?

Связанные уроки

• pg-08-aggregation — Window functions are non-collapsing aggregates; aggregation semantics must be understood first
• pg-10-cte — CTEs are the natural way to pre-filter data before applying window functions in analytics queries
• pg-12-btree — Window ORDER BY can use B-tree indexes to avoid sorting; index knowledge improves window query tuning
• pg-09-subqueries — Correlated subqueries vs window functions: both compute per-row context, but windows are far more efficient
• db-06-sql-advanced