Ранжирование: ROW_NUMBER, RANK, DENSE_RANK
Содержание курса
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 + число строк, стоящих раньше»: перед Галей стоят три строки (Аня, Боря, Вася), поэтому её ранг —
- Число 3 пропущено навсегда.
DENSE_RANK тоже ставит Борю и Васю на ранг 2, но следующий ранг всегда увеличивается на 1 независимо от размера группы равных. Галя получает 3, а не 4. Последовательность рангов плотная: пробелов нет.
Практически это означает: если вам нужно выбрать строки с «топ-3 позициями», DENSE_RANK() <= 3 может вернуть больше трёх строк (когда несколько строк делят одно место), а RANK() <= 3 — то же самое, но ранг 3 при наличии группы равных на 2-м месте уже недостижим и вы потеряете целую позицию. ROW_NUMBER() <= 3 возвращает не более трёх строк; если строк хотя бы три, их будет ровно три. При равных значениях выбор между ними произволен, если в оконном ORDER BY нет дополнительного уникального критерия.
