Чем отличаются ROW_NUMBER, RANK и DENSE_RANK

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

• ROW_NUMBER игнорирует повторы: каждая строка получает свой номер — 1, 2, 3, 4.
• RANK даёт повторам одинаковый ранг, а следующий ранг назначает с пропуском: 1, 1, 3, 4.
• DENSE_RANK даёт повторам одинаковый ранг без пропусков: 1, 1, 2, 3.

Вся разница в одном запросе

Возьмём заказы сервиса доставки и проранжируем их по сумме всеми тремя функциями сразу. В данных есть дубли: два заказа по 99.96 и три по 74.97 — именно на них и видно различия:

MySQL 8.1
SELECT
    order_id,
    total_amount,
    ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS row_num,
    RANK() OVER (ORDER BY total_amount DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY total_amount DESC) AS dense_rnk
FROM orders
ORDER BY total_amount DESC
LIMIT 8;

Читаем построчно:

• Заказы 20 и 26 стоят одинаково. ROW_NUMBER всё равно раздал им номера 1 и 2 — причём какой из них первый, СУБД решает произвольно. RANK и DENSE_RANK честно поставили обоим ранг 1.
• Заказ 218 — следующий по сумме. RANK дал ему 3: два заказа выше «съели» места 1 и 2. DENSE_RANK дал 2: пропусков он не делает.
• На тройке заказов по 74.97 картина повторяется: RANK — 6, 6, 6, дальше было бы 9; DENSE_RANK — 5, 5, 5, дальше 6.

Запомнить легко: RANK считает как в спорте — после двух золотых медалей сразу бронза, второго места нет. DENSE_RANK («плотный ранг») — как нумерация уровней: следующее значение всегда на единицу больше.

Функция ранжирует значения 100, 100, 90 по убыванию. Какие ранги выдаст DENSE_RANK?

Когда какую использовать

ROW_NUMBER: топ-N в каждой группе

Самая частая практическая задача оконных функций — «выбери лучший элемент в каждой категории». Найдём самый прибыльный товар каждой категории:

MySQL 8.1
WITH ranked AS (
    SELECT
        p.category,
        p.name,
        SUM(o.total_amount) AS revenue,
        ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(o.total_amount) DESC) AS rn
    FROM orders o
    JOIN products p ON p.product_id = o.product_id
    GROUP BY p.category, p.name
)
SELECT category, name, revenue
FROM ranked
WHERE rn = 1
ORDER BY category;

PARTITION BY category запускает нумерацию заново в каждой категории, поэтому rn = 1 — это лидер своей группы. Замените условие на rn <= 3 — получите топ-3.

Здесь важно, что ROW_NUMBER гарантирует ровно одну строку на группу. RANK при равной выручке вернул бы двух «лидеров» — иногда это то, что нужно, но чаще ломает отчёт, рассчитанный на одну строку.

Нужно выбрать ровно по одному самому дорогому заказу каждого клиента, даже если у клиента два заказа с одинаковой максимальной суммой. Какая функция подойдёт?

ROW_NUMBER: дедупликация

Второй классический сценарий — удалить дубли, оставив по одной записи. Нумеруем строки внутри групп одинаковых значений и убираем всё, что дальше первой:

MySQL 8.1
WITH numbered AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM numbered WHERE rn > 1);

Для каждого email остаётся самая ранняя запись (ORDER BY created_at), остальные удаляются.

RANK и DENSE_RANK: места и уровни

RANK выбирают там, где работает спортивная логика мест: рейтинги, конкурсы, «какое место занял спортсмен». DENSE_RANK — там, где нужны уровни без дыр: тарифные сетки, группировка по порогам, «N-е по величине значение».

Бонус: NTILE делит строки на корзины

Из той же семьи ранжирующих функций — NTILE(n): она делит строки на n примерно равных частей и возвращает номер части. Так за один запрос клиенты делятся на квартили по тратам:

MySQL 8.1
SELECT
    user_id,
    SUM(total_amount) AS spent,
    NTILE(4) OVER (ORDER BY SUM(total_amount) DESC) AS quartile
FROM orders
GROUP BY user_id
ORDER BY spent DESC
LIMIT 5;

Клиенты с quartile = 1 — верхняя четверть по тратам: готовый сегмент для программы лояльности.

Задача с собеседования: вторая по величине сумма

«Найдите вторую по величине зарплату» — вопрос, который десятилетиями кочует по собеседованиям. С DENSE_RANK он решается прозрачно:

MySQL 8.1
WITH ranked AS (
    SELECT order_id, total_amount, DENSE_RANK() OVER (ORDER BY total_amount DESC) AS rnk
    FROM orders
)
SELECT DISTINCT total_amount
FROM ranked
WHERE rnk = 2;

Почему именно DENSE_RANK? У нас две первых суммы по 99.96. RANK дал бы им ранг 1, а следующей — ранг 3, и условие rnk = 2 не нашло бы ничего. DENSE_RANK без пропусков ставит 91.81 ранг 2 — это и есть «вторая по величине», как её понимает спрашивающий.

Что дальше

• как устроены оконные функции, OVER и PARTITION BY — в уроке Оконные функции;
• виды оконных функций и их синтаксис — в уроке Виды оконных функций;
• сортировка внутри окна — в уроке Сортировка в оконных функциях;
• задачи на ранжирование регулярно попадаются в тренажёре и на собеседованиях.