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

ROW_NUMBER, RANK, DENSE_RANK — три способа пронумеровать строки и в чём их разница.

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

Чем окно отличается от GROUP BY

GROUP BY сворачивает группу в одну строку. Оконная функция возвращает значение для каждой строки, но считает его по группе (окну). Поэтому рядом с зарплатой сотрудника можно показать его ранг в отделе — строки остаются на месте.

Три функции ранжирования

Все три нумеруют строки в порядке ORDER BY внутри окна, но по-разному ведут себя при ничьей:

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

CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO emp (name, salary) VALUES
    ('Аня',120000), ('Борис',100000), ('Вера',100000), ('Гена',90000);

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM emp
ORDER BY salary DESC, name;

Вывод:

Аня|120000|1|1|1
Борис|100000|2|2|2
Вера|100000|3|2|2
Гена|90000|4|4|3

Борис и Вера получают по 100000 — ничья. Смотрите, что вышло: ROW_NUMBER дал им 2 и 3 (разные), RANK — обоим 2, а следующему (Гена) 4 (тройка пропущена), DENSE_RANK — обоим 2, а Гене 3 (без пропуска). Это и есть классический вопрос «в чём разница между RANK и DENSE_RANK».

PARTITION BY — окна по группам

Добавив PARTITION BY, мы нумеруем строки внутри каждой группы отдельно — нумерация сбрасывается на каждой новой группе. Найдём ранг зарплаты внутри отдела:

CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp (name, dept, salary) VALUES
    ('Аня','IT',120000), ('Борис','IT',95000),
    ('Вера','HR',80000),  ('Гена','HR',90000);

SELECT dept, name, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rang_v_otdele
FROM emp
ORDER BY dept, rang_v_otdele;

Вывод:

HR|Гена|90000|1
HR|Вера|80000|2
IT|Аня|120000|1
IT|Борис|95000|2

В каждом отделе своя нумерация с единицы: в IT первая Аня, в HR первый Гена. Это фундамент для задачи «топ-N по группам», к которой мы вернёмся.

Итог

Оконная функция считает по окну, но не схлопывает строки — в отличие от GROUP BY.
ROW_NUMBER — уникальные номера; RANK — с пропусками при ничьей; DENSE_RANK — без пропусков.
PARTITION BY запускает отдельную нумерацию внутри каждой группы.