Что такое RANK и DENSE_RANK в SQL? Ранжирование с ничьими для начинающих

RANK и DENSE_RANK — это оконные функции для ранжирования. Главное отличие от ROW_NUMBER — они правильно обрабатывают ничьи: одинаковым значениям дают одинаковый ранг.

Разница между ними — в том, что происходит после ничьей:

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

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

Олимпийский счёт. Если двое спортсменов разделили первое место — оба получают «1», следующий — «3». Никакого «второго места» в реальности нет.

ROW_NUMBER тут не подходит — он бы дал 1, 2, 3, разделив одинаковых произвольно. Семантически неправильно.

RANK — это и есть «олимпийский» ранжир: «у тебя 1-е место, 2-е место, разделённое 2-е место, 4-е место...».

DENSE_RANK — другой смысл. Он отвечает на вопрос «на каком уровне значение?». Уровней — ровно столько, сколько уникальных значений.

Иллюстрация на одной таблице

WITH scores AS (
  SELECT 'Аня'   AS name, 95 AS score UNION ALL
  SELECT 'Боб',   90 UNION ALL
  SELECT 'Вера',  90 UNION ALL
  SELECT 'Гриша', 85 UNION ALL
  SELECT 'Дима',  80
)
SELECT
  name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM scores;

Результат:

name	score	rn	rk	drk
Аня	95	1	1	1
Боб	90	2	2	2
Вера	90	3	2	2
Гриша	85	4	4	3
Дима	80	5	5	4

Боб и Вера оба с 90 очками:

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

Когда использовать RANK

Олимпийский счёт. Если хочешь, чтобы при ничьей соответствующие позиции «съедались»:

-- Топ-3 участника по очкам (с правильным «олимпийским» ранжированием)
SELECT name, total_points, RANK() OVER (ORDER BY total_points DESC) AS place
FROM athletes;

Если двое набрали одинаково — оба получат одно place. Следующий «упадёт вниз» как положено.

RANK — это интуитивно «место в рейтинге», как мы привыкли думать.

Когда использовать DENSE_RANK

«Сколько уникальных уровней». Если хочешь группировать одинаковые значения:

-- Уровни цены товаров
SELECT
  name, price,
  DENSE_RANK() OVER (ORDER BY price DESC) AS price_tier
FROM products;

tier = 1 — самые дорогие, tier = 2 — следующая по цене группа. Никаких пропусков, ровные уровни.

Полезно для условий «топ-3 ценовых уровня»:

WITH ranked AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY price DESC) AS dr
  FROM products
)
SELECT * FROM ranked WHERE dr <= 3;

С RANK поведение было бы другое — WHERE rk <= 3 пропустил бы товары со «второго места», если их 5 штук, потому что после них ранг прыгает на 7. С DENSE_RANK все три уровня попадут.

PARTITION BY работает у всех

Все три функции принимают PARTITION BY точно так же:

-- Ранг внутри каждой категории
SELECT
  category, name, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;

Удобно для отчётов «топ-3 в каждой категории», «лучший в каждом регионе».

RANK для leaderboard

-- Топ-10 игроков с правильной обработкой ничьих
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS place
FROM players
LIMIT 10;

LIMIT 10 оставит первые 10 строк, а RANK правильно покажет «место» с учётом ничьих. Если на 1 месте трое — лимит покажет ix их же + 7 следующих.

RANK + PERCENT_RANK / CUME_DIST (бонус)

Postgres также имеет:

• PERCENT_RANK() — дробное от 0 до 1: где значение в распределении.
• CUME_DIST() — кумулятивная доля: какая часть строк имеет значение ≤ текущего.

SELECT
  name, score,
  PERCENT_RANK() OVER (ORDER BY score DESC) AS pr,
  CUME_DIST()    OVER (ORDER BY score DESC) AS cd
FROM scores;

Применяется для перцентилей и распределений. Нужно реже, но знай, что есть.

Частые ошибки новичков

1. Путаница ROW_NUMBER / RANK / DENSE_RANK. Простое правило:

• ROW_NUMBER — всегда уникальные, ничьи разводятся произвольно.
• RANK — олимпийский, после ничьей пропуск.
• DENSE_RANK — уровни, после ничьей следующий ранг.

2. RANK без ORDER BY в OVER. Без сортировки ранжирование бессмысленно. Все строки получат RANK = 1. Всегда указывай ORDER BY.

3. RANK по нестабильному ключу. Если двое равны по score, но разные по created_at — RANK ORDER BY score даст обоим один ранг. Может, ты этого хочешь, может — нет. Если ничью нужно ломать по второму критерию — ORDER BY score DESC, created_at.

4. RANK в WHERE. Как и ROW_NUMBER — нельзя прямо. Оборачивай в подзапрос/CTE.

5. RANK ≠ ROW_NUMBER при отсутствии ничьих. Многие пишут RANK, думая что это «номер строки». На уникальных значениях оба возвращают одно и то же. Ломается на первой ничьей. Если уникальность гарантирована — ROW_NUMBER явнее по смыслу.

6. NULL в ORDER BY. В Postgres NULL по умолчанию в конце при DESC, в начале при ASC. Это влияет на ранги. Явно — ORDER BY score DESC NULLS LAST.

Мини-резюме

• ROW_NUMBER — всегда уникальный номер. Ничьи разводятся произвольно.
• RANK — одинаковые значения → одинаковый ранг, потом пропуск (1, 2, 2, 4).
• DENSE_RANK — одинаковые значения → одинаковый ранг, без пропуска (1, 2, 2, 3).
• Все три принимают PARTITION BY и ORDER BY внутри OVER.
• Используй RANK для «места в рейтинге», DENSE_RANK — для «уровней качества».
• Для фильтрации по рангу — оборачивай в подзапрос, прямо в WHERE нельзя.