Оконные функции: ROW_NUMBER, RANK, DENSE_RANK

Оконные функции это самая частая тема «посерьёзнее» на SQL-собеседовании. Первый вопрос про них почти всегда один: чем отличаются ROW_NUMBER, RANK и DENSE_RANK. Ответ виден только на одинаковых значениях, и в учебной базе блока они есть: у Бориса и Веры одна зарплата, 250 000.

Как спрашивают

Чем ROW_NUMBER, RANK и DENSE_RANK отличаются друг от друга? Покажи на данных, где есть одинаковые значения.

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

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

Разбор: забег и пьедестал

Представь итоги забега, где двое пришли одновременно вторыми.

• Номер в протоколе выдают всем подряд: 1, 2, 3, 4. Двум одновременным всё равно дадут разные номера, кто-то будет записан раньше. Это ROW_NUMBER.
• Места на олимпиаде: двое делят второе место, и третьего места нет, следующий сразу четвёртый. Это RANK.
• Ступени пьедестала: двое стоят на второй ступени, следующий на третьей. Ступени не пропускают. Это DENSE_RANK.

Оконная функция считает значение для каждой строки, глядя на соседние строки, которые попадают в её окно. Окно описывается в OVER (...).

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS row_number,
       RANK()       OVER (ORDER BY salary DESC)     AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC)     AS dense_rank
FROM employees
ORDER BY salary DESC, id;

name  | salary | row_number | rank | dense_rank
------+--------+------------+------+-----------
Анна  | 300000 |          1 |    1 |          1
Борис | 250000 |          2 |    2 |          2
Вера  | 250000 |          3 |    2 |          2
Глеб  | 200000 |          4 |    4 |          3
Жанна | 180000 |          5 |    5 |          4
Дина  | 150000 |          6 |    6 |          5
Егор  | 120000 |          7 |    7 |          6

Смотри на Глеба: у RANK он четвёртый, потому что третье место «съели» Борис и Вера, у DENSE_RANK третий.

В ROW_NUMBER добавлен id в сортировку. Без него порядок Бориса и Веры между собой не определён, и номера 2 и 3 могут поменяться от запуска к запуску. Для RANK и DENSE_RANK это неважно: равные получают одно место.