Оконные функции: ROW_NUMBER, RANK, OVER, PARTITION BY

Введение

Агрегатные функции (статья 399) сворачивают множество строк в одну итоговую — оконные функции, напротив, вычисляют значение для каждой строки на основе «окна» связанных с ней строк, не сворачивая результат в меньшее количество строк. В статье разберём ROW_NUMBER(), RANK(), и конструкцию OVER (PARTITION BY ... ORDER BY ...), открывающую целый класс запросов, нетривиально выразимых через обычные GROUP BY.

Концепция

OVER (PARTITION BY столбец ORDER BY столбец) определяет «окно» — для каждой строки результата оконная функция вычисляется на основе других строк, принадлежащих той же партиции (группе, определённой PARTITION BY, концептуально аналогично GROUP BY, но без сворачивания строк), упорядоченных согласно ORDER BY внутри окна. ROW_NUMBER() присваивает каждой строке внутри партиции уникальный, последовательный номер согласно порядку. RANK() похож, но строки с одинаковым значением по критерию сортировки получают одинаковый ранг, с пропуском следующих номеров (1, 2, 2, 4 — для двух строк, делящих второе место). DENSE_RANK() аналогичен RANK(), но без пропуска номеров (1, 2, 2, 3).

Пример кода: Оконные функции

-- ROW_NUMBER — нумерация заказов каждого клиента по дате (от самого нового к старому)
SELECT customer_id, id AS order_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rank
FROM orders;

-- Практичное применение: получить только САМЫЙ ПОСЛЕДНИЙ заказ каждого клиента
SELECT * FROM (
    SELECT customer_id, id AS order_id, order_date,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) ranked
WHERE rn = 1; -- только первая строка (самая последняя по дате) для каждого клиента

-- RANK против DENSE_RANK — различие при наличии "связанных" по значению строк
SELECT name, total_amount,
       RANK() OVER (ORDER BY total_amount DESC) AS rank_with_gaps,
       DENSE_RANK() OVER (ORDER BY total_amount DESC) AS rank_without_gaps
FROM orders;

-- LAG/LEAD — доступ к значению предыдущей/следующей строки в рамках партиции
SELECT customer_id, order_date, total_amount,
       LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_amount,
       total_amount - LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS amount_change
FROM orders;

Пояснения к коду

Подзапрос с ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) и последующая фильтрация WHERE rn = 1 — стандартный, широко используемый паттерн «получить топ-N строк в каждой группе», решающий задачу, которая до появления оконных функций требовала значительно более сложных и часто менее эффективных конструкций (коррелированные подзапросы с подсчётом, статья 400, или самообъединение таблицы с собой через сложное условие). LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) показывает доступ к значению «предыдущей» строки в рамках той же партиции по заданному порядку — это даёт прямую, эффективную возможность вычислить изменение между последовательными заказами одного клиента без необходимости в самостоятельном, более сложном самообъединении таблицы заказов с собой.

Подводные камни

Использование RANK() там, где логически нужен ROW_NUMBER() (или наоборот), без понимания разницы в обработке «связанных» по значению строк — для задачи «топ-3 заказа по сумме», если несколько заказов имеют ровно одинаковую сумму на границе третьего места, RANK() может вернуть больше трёх строк (все строки с одинаковым рангом 3), тогда как ROW_NUMBER() гарантированно вернёт ровно одну строку для каждого числового номера независимо от связанных значений — выбор между ними должен соответствовать желаемой бизнес-логике обработки таких граничных случаев.
Забытое ORDER BY внутри OVER() для функций, для которых порядок принципиально важен (ROW_NUMBER(), RANK(), LAG()/LEAD()) — без ORDER BY внутри окна порядок вычисления этих функций не определён, что для ROW_NUMBER() всё ещё дало бы какую-то (но непредсказуемую, недетерминированную между разными выполнениями того же запроса) нумерацию, а не ошибку, что особенно опасно, поскольку отсутствие ошибки маскирует логическую проблему запроса.
Путаница между PARTITION BY в оконных функциях и GROUP BY — несмотря на концептуальное сходство (оба группируют строки по значению столбца), PARTITION BY не сворачивает строки в одну на группу, как GROUP BY, а сохраняет все исходные строки, просто вычисляя оконную функцию в контексте каждой партиции отдельно; путаница между ними часто проявляется как недоумение, почему запрос с PARTITION BY возвращает столько же строк, сколько было в исходной таблице, а не меньше (как ожидалось бы от привычки к GROUP BY).
Использование оконных функций для задач, для которых обычной агрегации с GROUP BY было бы достаточно и эффективнее — оконные функции мощнее GROUP BY именно потому, что сохраняют детальные строки, но если итоговая задача действительно требует только агрегированного, свёрнутого результата (без сохранения детальных строк), обычная агрегация — более прямой, понятный и часто более эффективно оптимизируемый СУБД способ выразить ту же задачу, и оконные функции не должны применяться «по умолчанию» просто потому, что они более продвинутые или модные.