Чем ROW_NUMBER, RANK и DENSE_RANK отличаются?

Вопрос, где всё решают ничьи. Пока значения уникальны, три функции неотличимы — разница вылезает ровно там, где две строки равны.

Как рассуждать

Держи в голове одну картинку: список, отсортированный по ORDER BY внутри окна, и две строки с одинаковым значением. Всё сводится к вопросу «что делать с равными?».

• ROW_NUMBER — «мне всё равно, нумеруй подряд». Даже равным строкам номера будут разные (какая раньше — зависит от сортировки, при полном равенстве недетерминированно).
• RANK — «равные делят место, но следующий за ними теряет пропущенные номера». Как в спорте: два вторых места — следующий четвёртый.
• DENSE_RANK — «равные делят место, но нумерация плотная, без дыр»: два вторых — следующий третий.

Запомни по эффекту на последовательности при ничьей на второй позиции: ROW_NUMBER → 1,2,3,4; RANK → 1,2,2,4; DENSE_RANK → 1,2,2,3.

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

Все три — оконные ранжирующие функции, требуют ORDER BY внутри OVER. Отличие — в реакции на одинаковые значения. Пример: ранжируем категории по выручке.

SELECT
  c.name,
  sum(p.amount) AS revenue,
  row_number() OVER (ORDER BY sum(p.amount) DESC) AS rn,
  rank()       OVER (ORDER BY sum(p.amount) DESC) AS rnk,
  dense_rank() OVER (ORDER BY sum(p.amount) DESC) AS drnk
FROM payment p
JOIN rental r    ON r.rental_id = p.rental_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film_category fc ON fc.film_id = i.film_id
JOIN category c  ON c.category_id = fc.category_id
GROUP BY c.name;

Пока выручки категорий различаются — все три столбца совпадают. Но если две категории собрали одинаковую сумму, row_number всё равно проставит им разные номера (2 и 3), rank — оба 2, а следующей категории 4, dense_rank — оба 2, следующей 3.

Практика: для «выбрать топ-1 в каждой группе» и дедупликации бери ROW_NUMBER — он гарантирует ровно одну строку. Для честного рейтинга с призовыми местами — RANK. Для «сколько всего уровней значений» — DENSE_RANK. Что такое окно вообще — в разборе про оконные функции.

Сильный ответ упомянет: чтобы row_number был детерминированным при равных ключах, добавь в ORDER BY уникальный тай-брейкер (например, film_id).

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

• Считают RANK и DENSE_RANK взаимозаменяемыми — забывают про пропуск номеров у RANK.
• Берут RANK для дедупликации и получают несколько строк с рангом 1 при ничьей — дубликаты не убираются. Нужен ROW_NUMBER, как в разборе про поиск и удаление дубликатов.
• Забывают ORDER BY внутри OVER — без него ранжирование бессмысленно (все строки в одном «месте»).
• Полагаются на порядок ROW_NUMBER при равных значениях без тай-брейкера — результат может «прыгать» между запусками.