DataLearn SQL-101: ROW_NUMBER vs RANK vs DENSE_RANK

Условие

Таблица orders(order_id, user_id, dt, amount). Напишите:

1. Топ-3 самых дорогих заказа на каждого пользователя.
2. Топ-3 пользователей по сумме заказов в каждой стране (users.country).
3. Объясните разницу ROW_NUMBER / RANK / DENSE_RANK на примере, когда у трёх пользователей одинаковый amount.

Решение

Разница трёх функций

При ties (одинаковых значениях) ведут себя по-разному:

amount  ROW_NUMBER  RANK  DENSE_RANK
1000        1         1        1
900         2         2        2
900         3         2        2
900         4         2        2
800         5         5        3      <- разница тут
700         6         6        4

• ROW_NUMBER — всегда уникальные номера 1, 2, 3, ... Произвольный выбор внутри ties (если не задан ORDER BY с tie-breaker).
• RANK — одинаковый ранг при ties; пропускает следующие места (1, 2, 2, 2, 5, ...).
• DENSE_RANK — одинаковый ранг при ties; не пропускает (1, 2, 2, 2, 3, ...).

1) Топ-3 заказов на пользователя

WITH ranked AS (
    SELECT
        order_id, user_id, dt, amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, dt) AS rn
    FROM orders
)
SELECT order_id, user_id, dt, amount
FROM ranked
WHERE rn <= 3
ORDER BY user_id, rn;

ROW_NUMBER — потому что нужно ровно 3 строки даже при равных amount.

Если по бизнес-смыслу нужны все заказы с топ-3 значениями (т.е. при ties — больше 3 строк), используем DENSE_RANK:

WHERE dr <= 3   -- где dr = DENSE_RANK() OVER (...)

2) Топ-3 пользователей в каждой стране

WITH user_sum AS (
    SELECT u.user_id, u.country, SUM(o.amount) AS total_amount
    FROM users u
    JOIN orders o ON o.user_id = u.user_id
    GROUP BY u.user_id, u.country
),
ranked AS (
    SELECT *,
           RANK() OVER (PARTITION BY country ORDER BY total_amount DESC) AS rk
    FROM user_sum
)
SELECT country, user_id, total_amount, rk
FROM ranked
WHERE rk <= 3
ORDER BY country, rk;

Используем RANK, потому что при равных суммах в стране — пусть будут все на одном месте.

Топ-N в Postgres-12+: FETCH WITH TIES

SELECT user_id, total_amount
FROM user_sum
WHERE country = 'RU'
ORDER BY total_amount DESC
FETCH FIRST 3 ROWS WITH TIES;

— альтернатива через WITH TIES, но работает только для одного среза (без partition).

NTILE — на квартили / децили

SELECT user_id, total_amount,
       NTILE(4) OVER (ORDER BY total_amount DESC) AS quartile
FROM user_sum;

NTILE(4) разбивает выборку на 4 ~равные группы. Удобно для сегментации (топ-25% / 50-75% / ...).

Подводные камни

1. ROW_NUMBER без ORDER BY с tie-breaker — порядок строк при ties не определён, запрос между запусками может вернуть разное. Всегда добавлять стабильный tie-breaker (order_id, dt).
2. WHERE rn <= 3 в той же CTE, что и оконная функция — нельзя: оконные считаются после WHERE. Нужен под-CTE или подзапрос.
3. RANK() с пропусками мест удивляет бизнес: «было трое на 2 месте → следующий на 5». Если хотят «без пропусков» — DENSE_RANK.
4. NULLS FIRST/LAST в ORDER BY: разный default в Postgres (NULLS LAST для ASC) и MySQL/SQL Server. Явно указывать.
5. PARTITION BY со многими колонками: проверяйте грануляцию — может неожиданно «убить» партицию.
6. Top-N + ties: бизнес часто хочет «топ-3 покупателей», а у троих одинаковая сумма — RANK вернёт 5+ строк. Уточнять.
7. Производительность: для top-K на партицию ROW_NUMBER + WHERE rn <= K обычно быстрее LATERAL JOIN, но движок может сделать иначе.

Эталонный ответ

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

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

Выбор зависит от того, что бизнес считает «топ-3»: ровно 3 строки → ROW_NUMBER; все с топ-3 значениями → DENSE_RANK.