Нумерация и ранжирование: ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER, RANK и DENSE_RANK нумеруют строки — но по-разному обходятся с равными значениями.

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

Все три рядом

Дадим игрокам очки, где есть совпадения, и посмотрим на разницу в одной таблице.

CREATE TABLE score(player TEXT, points INTEGER);
INSERT INTO score VALUES ('Аня',100),('Борис',100),('Вика',90),('Глеб',90),('Дина',80);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dense
FROM score;

Вывод:

player  points  rn  rnk  dense
------  ------  --  ---  -----
Аня     100     1   1    1
Борис   100     2   1    1
Вика    90      3   3    2
Глеб    90      4   3    2
Дина    80      5   5    3

Читаем колонки

• ROW_NUMBER (rn): 1,2,3,4,5 — строго уникальные номера, даже у Ани и Бориса со 100 очками. Кто из них «первый» при равенстве — выбирается произвольно (стабилизировать можно, добавив второй ключ в ORDER BY).
• RANK (rnk): 1,1,3,3,5 — равные значения получают одинаковый ранг, но следующий ранг «перепрыгивает» (после двух первых мест идёт 3-е, не 2-е). Это привычная спортивная таблица.
• DENSE_RANK (dense): 1,1,2,2,3 — равные тоже делят ранг, но «дыр» нет: после первого места сразу второе.

Что выбрать

Подводный камень: ORDER BY обязателен

Для функций ранжирования ORDER BY внутри OVER обязателен — без него «номер строки» не имеет смысла. И помните: при ничьих ROW_NUMBER расставляет соседей недетерминированно, поэтому для воспроизводимости добавляйте уточняющий ключ, например ORDER BY points DESC, player.

Итог

• ROW_NUMBER — уникальные 1,2,3,4,5; при равенстве порядок произволен.
• RANK — равным один ранг, дальше с «дырой» (1,1,3).
• DENSE_RANK — равным один ранг, без «дыр» (1,1,2).