Ранжирование: ROW_NUMBER, RANK, DENSE_RANK

В прошлом уроке ORDER BY внутри OVER превращал агрегат в нарастающий итог — каждая строка «видела» всё накопленное до неё. Ранжирующие функции используют тот же механизм окна, но не суммируют и не усредняют: они присваивают каждой строке номер её позиции внутри секции.

Синтаксис ранжирующих функций: OVER (PARTITION BY … ORDER BY …)

Три функции — ROW_NUMBER(), RANK(), DENSE_RANK() — вызываются без аргументов. Всё, что нужно функции, она берёт из OVER-клауза:

ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_amount DESC)

PARTITION BY разрезает строки на независимые секции: нумерация внутри каждой секции начинается заново с 1, строки из разных секций друг на друга не влияют. Если PARTITION BY опустить, всё множество строк становится одной секцией.

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

Важная деталь: ORDER BY внутри OVER никак не связан с ORDER BY в конце всего запроса. Первый управляет тем, как функция нумерует строки; второй — порядком строк в итоговом результате. Можно одновременно ранжировать по убыванию суммы и возвращать строки в алфавитном порядке по имени.

Результат функции — целое число в новом столбце. Число строк в результате не меняется: оконная функция добавляет значение к каждой строке, а не схлопывает их, как GROUP BY.

Один запрос может содержать несколько оконных функций с разными OVER-клаузами:

SELECT
    order_id,
    category,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_amount DESC) AS rn,
    RANK()       OVER (PARTITION BY category ORDER BY total_amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY total_amount DESC) AS drnk
FROM orders;

Каждая функция обрабатывает своё окно независимо, хотя здесь все три окна одинаковы. Как именно rn, rnk и drnk различаются при одинаковых total_amount — следующий вопрос.

ROW_NUMBER, RANK, DENSE_RANK: поведение при одинаковых значениях сортировки

Разница между тремя функциями видна только тогда, когда несколько строк имеют одинаковое значение ключа сортировки. Возьмём таблицу scores с четырьмя игроками:

CREATE TABLE scores (
    id     INTEGER PRIMARY KEY,
    player TEXT,
    score  INTEGER
);
INSERT INTO scores VALUES
    (1, 'Аня',  90),
    (2, 'Боря', 85),
    (3, 'Вася', 85),
    (4, 'Галя', 70);

Запрос:

SELECT
    id,
    player,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC, id) AS rn,
    RANK()       OVER (ORDER BY score DESC)      AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC)      AS drnk
FROM scores
ORDER BY score DESC, id;

Результат:

player	score	rn	rnk	drnk
Аня	90	1	1	1
Боря	85	2	2	2
Вася	85	3	2	2
Галя	70	4	4	3

Посмотрим на каждую функцию отдельно.

ROW_NUMBER добавляет вторичный ключ id в своё ORDER BY — иначе Боря и Вася получили бы номера 2 и 3 в неизвестном порядке. С id порядок детерминирован: 1, 2, 3, 4 без пробелов и без дублей. Функции безразлично, что у Бори и Васи одинаковый score; она просто нумерует строки по указанному порядку.

RANK не получает вторичного ключа, поэтому Боря и Вася с одинаковым score делят одно место и оба получают ранг 2. Следующий ранг вычисляется по формуле «1 + число строк, стоящих раньше»: перед Галей стоят три строки (Аня, Боря, Вася), поэтому её ранг —

4. Число 3 пропущено навсегда.

DENSE_RANK тоже ставит Борю и Васю на ранг 2, но следующий ранг всегда увеличивается на 1 независимо от размера группы равных. Галя получает 3, а не 4. Последовательность рангов плотная: пробелов нет.

Практически это означает: если вам нужно выбрать строки с «топ-3 позициями», DENSE_RANK() <= 3 может вернуть больше трёх строк (когда несколько строк делят одно место), а RANK() <= 3 — то же самое, но ранг 3 при наличии группы равных на 2-м месте уже недостижим и вы потеряете целую позицию. ROW_NUMBER() <= 3 возвращает не более трёх строк; если строк хотя бы три, их будет ровно три. При равных значениях выбор между ними произволен, если в оконном ORDER BY нет дополнительного уникального критерия.

Фильтрация топ-N строк по рангу через CTE

Допустим, нужно получить три заказа с наибольшей суммой в каждой категории. Первый порыв — написать WHERE DENSE_RANK() OVER (...) <= 3 прямо в том же SELECT. Это не работает: SQL вычисляет WHERE до того, как оконные функции получают доступ к данным, и интерпретатор отклонит такой запрос с ошибкой.

Решение — вынести ранжирование в CTE, а фильтр поставить во внешнем SELECT:

WITH ranked AS (
    SELECT
        order_id,
        category,
        total_amount,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY total_amount DESC
        ) AS dr
    FROM orders
)
SELECT order_id, category, total_amount, dr
FROM ranked
WHERE dr <= 3;

CTE ranked — это именованный подзапрос. Внутри него оконная функция выполняется по всему набору строк orders и кладёт готовый ранг в столбец dr. Внешний SELECT уже видит dr как обычный числовой столбец и может фильтровать по нему без ограничений.

Почему DENSE_RANK, а не RANK? Если в категории два заказа с одинаковой максимальной суммой, RANK отдаст им оба ранг 1, а следующему — ранг 3. При фильтре <= 3 мы всё равно вернём нужные строки, но логика рванёт при другом граничном значении: например, при двух равных на 3-м месте RANK = 3 вернёт обоих, а RANK = 4 уже не существует, и следующая строка имеет ранг

5. DENSE_RANK держит последовательность плотной, поэтому <= 3 стабильно означает «первые три уникальные позиции по значению».

Если же требуется ровно три строки без исключений — подойдёт ROW_NUMBER() <= 3, но тогда при равных суммах победитель выбирается произвольно. Выбор функции зависит от того, что важнее: стабильное число строк или честное отражение равенства позиций.