Как посчитать ранг в SQL

Зачем нужно уметь ранжировать

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

Ловушка в том, что они выглядят одинаково, но ведут себя по-разному при связках. Для зарплат [400, 200, 200, 100]: ROW_NUMBER вернёт 1, 2, 3, 4 (случайно выбирая из двух двухсот), RANK — 1, 2, 2, 4 (с пропуском 3-го), DENSE_RANK — 1, 2, 2, 3 (без пропуска). Если для топ-3 использовать ROW_NUMBER — пропустите одного из кандидатов.

В статье — все рабочие паттерны:

• Топ-N во всей таблице и в каждой группе (PARTITION BY)
• Вторая максимальная зарплата (классика собесов)
• N-й заказ каждого пользователя
• Ранжирование с фильтром (только paid заказы)
• Топ-10% через NTILE и PERCENT_RANK
• Медаль (gold / silver / bronze) через CASE
• Типичные ошибки: RANK для топ-1 при связках

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

• ROW_NUMBER() — уникальный номер каждой строки
• RANK() — ранг с пропусками при равных значениях
• DENSE_RANK() — ранг без пропусков

Пример для salary = [100, 200, 200, 400]:

1. Ранжирование всех строк

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

2. Ранжирование по группам (PARTITION BY)

Ранг по отделу:

SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

У каждого отдела свой ранг.

3. Топ-N в каждой группе

Топ-3 самых высокооплачиваемых в каждом отделе:

SELECT * FROM (
    SELECT
        department,
        name,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk <= 3;

Важно: DENSE_RANK, не ROW_NUMBER — чтобы не пропустить связки.

4. Топ-3 (ровно 3 строки), игнорируя связки

SELECT * FROM (
    SELECT
        department,
        name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

ROW_NUMBER выдаст ровно 3 строки (любые, при связке — произвольные).

5. Пользователи с N-м по счёту заказом

Найти всех, у кого 5-й заказ:

SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders
) t
WHERE order_num = 5;

6. Вторая максимальная зарплата

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 2;

Если есть несколько на 2-м месте — вернутся все.

7. Перцентильный ранг

SELECT
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank,
    CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution,
    NTILE(10) OVER (ORDER BY salary) AS decile
FROM employees;

• PERCENT_RANK — ранг в долях (0 до 1)
• CUME_DIST — доля строк с меньшим или равным значением
• NTILE(n) — номер из n групп

8. Ранг с фильтром

Ранжировать только оплаченные заказы:

SELECT
    user_id,
    total,
    RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rnk
FROM orders
WHERE status = 'paid';

9. Топ N % клиентов

Выделить топ-10% клиентов по выручке:

SELECT * FROM (
    SELECT
        user_id,
        SUM(total) AS total_spent,
        NTILE(10) OVER (ORDER BY SUM(total) DESC) AS decile
    FROM orders
    GROUP BY user_id
) t
WHERE decile = 1;

10. Последний заказ каждого пользователя

SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

В Postgres также работает DISTINCT ON:

SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;

11. Пять самых активных пользователей

SELECT * FROM (
    SELECT
        user_id,
        COUNT(*) AS events_cnt,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS activity_rank
    FROM events
    GROUP BY user_id
) t
WHERE activity_rank <= 5;

12. Медаль: золото / серебро / бронза

Оконные функции нельзя использовать в WHERE, поэтому сначала считаем ранг в подзапросе, а уже потом фильтруем:

WITH ranked AS (
    SELECT
        name,
        score,
        DENSE_RANK() OVER (ORDER BY score DESC) AS rnk
    FROM athletes
)
SELECT
    name,
    score,
    CASE rnk
        WHEN 1 THEN 'gold'
        WHEN 2 THEN 'silver'
        WHEN 3 THEN 'bronze'
    END AS medal
FROM ranked
WHERE rnk <= 3
ORDER BY rnk;

Частые ошибки

Ошибка 1. RANK для «топ-1»

Если два пользователя равны, WHERE rnk = 1 вернёт обоих. Для уникального топа — ROW_NUMBER.

Ошибка 2. Забыть ORDER BY в OVER

-- не работает как ожидается
ROW_NUMBER() OVER (PARTITION BY user_id)

-- правильно
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)

Ошибка 3. Использовать в WHERE

Оконные функции нельзя в WHERE — только в подзапросе / CTE.

Ошибка 4. DENSE_RANK vs RANK для топ-N

DENSE_RANK корректно работает при связках. RANK пропускает номера.

В каких СУБД работает

Во всех современных: PostgreSQL, MySQL 8+, SQL Server, Oracle, BigQuery, Snowflake, ClickHouse.

В MySQL 5.7 — нет оконных функций, нужны переменные (@rank := @rank + 1).

Связанные темы

• ROW_NUMBER vs RANK vs DENSE_RANK — шпаргалка
• Оконные функции SQL — шпаргалка
• PARTITION BY — шпаргалка
• Задачи на оконные функции

FAQ

Какая функция для топ-N?

DENSE_RANK — корректная. ROW_NUMBER — если связки не важны и нужно ровно N.

Как вернуть топ-1 одну строку?

ROW_NUMBER. Если нужен детерминистический ответ при связках, добавьте ID в ORDER BY: ORDER BY salary DESC, id.

Что такое PERCENT_RANK?

Ранг в виде доли (0-1). Полезно для определения, где пользователь среди всех.

NTILE или PERCENT_RANK?

NTILE — для деления на N групп (децили, квартили). PERCENT_RANK — для точной позиции.