ROW_NUMBER vs RANK vs DENSE_RANK: разбираем разницу с примерами

ROW_NUMBER, RANK и DENSE_RANK — три оконные функции для нумерации строк. На собеседованиях их почти всегда спрашивают вместе: «В чём разница?». Разберём на конкретных примерах.

Данные для примера

Таблица с результатами тестирования:

Обратите внимание: Борис и Вера набрали одинаково (85), Григорий и Денис тоже (78).

ROW_NUMBER — уникальный порядковый номер

Каждой строке присваивается уникальный номер. При одинаковых значениях порядок произвольный (зависит от БД).

SELECT
    student,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM test_results;

Номера уникальны. Среди равных (Борис/Вера, Григорий/Денис) — произвольный порядок.

RANK — позиция с пропусками

При одинаковых значениях присваивается одинаковый ранг, следующий ранг пропускается.

SELECT
    student,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM test_results;

Борис и Вера оба занимают 2-е место. Третьего места нет — сразу 4-е (2 + количество участников с рангом 2).

Аналогия: чемпионат мира. Два золота → третье место остаётся вакантным.

DENSE_RANK — позиция без пропусков

При одинаковых значениях — одинаковый ранг, но следующий ранг не пропускается.

SELECT
    student,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM test_results;

Нет «дыр» в нумерации: 1, 2, 2, 3, 3, 4. Сколько уникальных значений — столько рангов.

Сравнение всех трёх

SELECT
    student,
    score,
    ROW_NUMBER()  OVER (ORDER BY score DESC) AS rn,
    RANK()        OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK()  OVER (ORDER BY score DESC) AS dr
FROM test_results;

С PARTITION BY — по группам

Нумерация внутри каждой группы:

-- Ранг студента внутри своей группы
SELECT
    student,
    class,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM test_results;

Каждый класс нумеруется независимо.

Практические задачи

Задача 1: Найти топ-3 студента

SELECT student, score
FROM (
    SELECT student, score,
           RANK() OVER (ORDER BY score DESC) AS rnk
    FROM test_results
) t
WHERE rnk <= 3;
-- Вернёт Алису, Бориса, Веру (все трое с рангом ≤ 3)
-- Если бы использовали ROW_NUMBER, Вера не попала бы

Задача 2: Первый заказ каждого пользователя

SELECT user_id, order_id, created_at
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM orders
) t
WHERE rn = 1;
-- ROW_NUMBER гарантирует одну строку — если нужен ровно один результат

Задача 3: Дедупликация

-- Удалить дубликаты email, оставить самую раннюю запись
DELETE FROM users
WHERE id IN (
    SELECT id FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
        FROM users
    ) t
    WHERE rn > 1
);

Задача 4: Медиана через NTILE

Смежная функция NTILE(n) делит на n равных групп:

SELECT
    score,
    NTILE(4) OVER (ORDER BY score) AS quartile
FROM test_results;
-- quartile = 1: нижние 25%, quartile = 4: верхние 25%

На собеседовании: типичный вопрос

Вопрос: Есть таблица продаж. Нужно найти второй по величине объём продаж для каждого менеджера.

-- Через DENSE_RANK (лучше: работает при одинаковых суммах)
WITH ranked AS (
    SELECT
        manager_id,
        product_id,
        SUM(amount) AS total,
        DENSE_RANK() OVER (PARTITION BY manager_id ORDER BY SUM(amount) DESC) AS dr
    FROM sales
    GROUP BY manager_id, product_id
)
SELECT manager_id, product_id, total
FROM ranked
WHERE dr = 2;

Итог: когда что использовать

Правило: если нужна одна строка на группу — ROW_NUMBER. Если нужны «все с первым местом» — RANK или DENSE_RANK. Если нумерация не должна иметь дыр — DENSE_RANK.