Чем ROW_NUMBER(), RANK() и DENSE_RANK() отличаются друг от друга при одинаковых значениях сортировки?

Короткий ответ

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

Как это работает подробнее

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

Пример кода

question-37.sqlКопировать

SELECT customer_id, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
       RANK() OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS drnk
FROM orders;

Что использовать на практике

• выбор функции зависит от задачи: для «каждому ряду — свой уникальный номер» (например, для дедупликации или пагинации) нужен ROW_NUMBER(); для «официального» рейтинга, где важно показать настоящее место с учётом ничьих (как в спорте), — RANK(); для рейтинга без «дырок» в номерах — DENSE_RANK(). Если два заказа имеют одинаковую максимальную сумму, rnk у обоих будет 1, у drnk — тоже 1, а следующий заказ получит rnk = 3, drnk = 2. Проверка: количество уникальных значений rn должно равняться количеству строк.

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

• используют ROW_NUMBER() для дедупликации без достаточно детерминированного ORDER BY (без вторичного уникального критерия), из-за чего при повторном запуске может удалиться другая строка из пары дублей; путают RANK() и DENSE_RANK() при объяснении бизнес-пользователю разницы в итоговых номерах мест.