ROW_NUMBER, RANK, DENSE_RANK и NTILE

Урок 7.2 · Время чтения: ~8 мин · Задачи для практики ↓

Функции ранжирования SQL нумеруют строки в заданном порядке: ROW_NUMBER, RANK и DENSE_RANK присваивают позиции, а NTILE делит строки на группы. Они различаются только тем, как обрабатывают равные значения, и именно от этого зависит, будет ли отчёт правильным. В этом уроке вы сравните все функции на одних и тех же реальных данных, увидите, зачем ROW_NUMBER нужен дополнительный критерий сортировки, и узнаете, какая функция подходит для какой задачи.

В предыдущем уроке вы нумеровали платежи с помощью ROW_NUMBER(). Функции ранжирования устроены так же: функция, затем OVER с ORDER BY, который определяет, что значит «первый», и необязательный PARTITION BY, который начинает ранжирование заново для каждой группы. Рамки окна функции ранжирования игнорируют: они всегда смотрят на весь раздел.

Примеры работают на учебной базе Sakila (MySQL 8.0).

Три функции рядом

Клиент 1 сделал 32 платежа. Некоторые из них равны: четыре платежа по 5.99 и восемь по 4.99. Ранжируем платежи от самого крупного:

SELECT
    payment_id,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC, payment_id) AS row_num,
    RANK()       OVER (ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM payment
WHERE customer_id = 1
ORDER BY row_num
LIMIT 8;

Результат: 8 крупнейших платежей клиента 1.

• ROW_NUMBER даёт каждой строке свой номер: 1, 2, 3, 4, … Равные значения получают разные номера.
• RANK даёт равным значениям одинаковый ранг и затем пропускает номера: четыре платежа делят ранг 3, поэтому следующий ранг - 7. Это «спортивное» ранжирование, как места в забеге.
• DENSE_RANK даёт равным значениям одинаковый ранг без пропусков: после ранга 3 идёт ранг 4. Число различных рангов равно числу различных значений.

ROW_NUMBER нужен дополнительный критерий

Среди равных значений ROW_NUMBER всё равно должен выбрать порядок, и база данных вправе выбрать любой. Без payment_id в ORDER BY выше MySQL пронумеровал четыре платежа по 5.99 как 4, 5, 6, 3, а при другом запуске или на другой версии сервера порядок может быть иным. Отчёт, который зависит от «строки номер 3», будет меняться от запуска к запуску.

Всякий раз, когда от ROW_NUMBER что-то зависит, например постраничный вывод или «оставить первую строку», добавляйте в ORDER BY внутри OVER уникальный столбец, например первичный ключ. RANK и DENSE_RANK в этом не нуждаются: равные значения и так получают равные ранги.

NTILE: деление строк на группы

NTILE(n) не ранжирует значения, а распределяет упорядоченные строки по n группам почти равного размера и возвращает номер группы. Это стандартный способ построить квартили, децили или сегмент «лучшие 10%».

Разделим клиентов каждого магазина на квартили по сумме платежей:

WITH customer_total AS (
    SELECT c.store_id, c.customer_id, SUM(p.amount) AS total_paid
    FROM customer c
    JOIN payment p ON p.customer_id = c.customer_id
    GROUP BY c.store_id, c.customer_id
),
segmented AS (
    SELECT
        store_id,
        total_paid,
        NTILE(4) OVER (PARTITION BY store_id ORDER BY total_paid DESC) AS quartile
    FROM customer_total
)
SELECT
    store_id,
    quartile,
    COUNT(*) AS customers,
    MIN(total_paid) AS min_paid,
    MAX(total_paid) AS max_paid
FROM segmented
GROUP BY store_id, quartile
ORDER BY store_id, quartile;

Результат: 8 строк; у магазина 1 326 клиентов, разделённых на 82, 82, 81 и 81; верхний квартиль заплатил от 128.73 до 216.54.

Когда строки не делятся поровну, первые группы получают на одну строку больше. NTILE делит по числу строк, а не по значениям: равные значения на границе могут попасть в разные группы, как 128.73 здесь.

PERCENT_RANK и CUME_DIST

Ещё две функции выражают позицию долей от 0 до 1:

• PERCENT_RANK() = (ранг − 1) / (строк − 1): доля строк, стоящих строго выше. Первая строка получает 0.
• CUME_DIST() = строки со значением до текущего включительно / все строки: доля строк на этой позиции и выше.

SELECT
    payment_id,
    amount,
    ROUND(PERCENT_RANK() OVER (ORDER BY amount DESC), 2) AS pct_rank,
    ROUND(CUME_DIST() OVER (ORDER BY amount DESC), 2) AS cumulative_share
FROM payment
WHERE customer_id = 1
ORDER BY amount DESC, payment_id
LIMIT 4;

Результат: у платежа 9.99 pct_rank равен 0, а cumulative_share - 0.03; у каждого платежа 5.99 pct_rank 0.06 и cumulative_share 0.19, потому что 6 из 32 платежей равны 5.99 или больше.

Они удобны для фильтров вида «лучшие 10% клиентов»: WHERE cumulative_share <= 0.1 во внешнем запросе. Не называйте столбец cume_dist или rank: в MySQL 8 это зарезервированные слова.

Какую функцию выбрать

Две задачи на основе этих функций подробно разобраны в уроках с задачами с собеседований: топ-N строк в каждой группе (урок 12.6) и N-я по величине зарплата (урок 12.4).

Ключевые выводы:

• ROW_NUMBER, RANK и DENSE_RANK различаются только при равных значениях: уникальные номера, общие ранги с пропусками, общие ранги без пропусков.
• Для значений 9.99, 7.99, 5.99, 5.99, 4.99 они возвращают 1-2-3-4-5, 1-2-3-3-5 и 1-2-3-3-4.
• С ROW_NUMBER добавляйте в ORDER BY уникальный критерий, иначе нумерация равных значений произвольна.
• NTILE(n) делит упорядоченные строки на n почти равных групп по числу строк.
• PERCENT_RANK и CUME_DIST дают позицию в виде доли от 0 до 1.

Часто задаваемые вопросы

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

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

Когда использовать RANK, а когда DENSE_RANK?

Используйте RANK, когда позиция должна отражать, сколько строк стоит выше, как в спортивных результатах: двое победителей делят 1-е место, а следующий - 3-й. Используйте DENSE_RANK, когда нужны последовательные уровни различных значений, например «вторая по величине цена».

Как NTILE обрабатывает строки, которые не делятся поровну?

Первые группы получают по одной лишней строке. При 326 строках и NTILE(4) в группах 82, 82, 81 и 81 строка. NTILE считает строки, поэтому равные значения могут попасть в две соседние группы.

Почему ROW_NUMBER даёт разные результаты для одного и того же запроса?

Потому что в ORDER BY внутри OVER есть равные значения, и база данных может упорядочивать равные строки по-разному от запуска к запуску. Добавьте последним ключом сортировки уникальный столбец, например первичный ключ, чтобы нумерация стала детерминированной.