RANK() vs DENSE_RANK(): ошибка, которая ломает топ-N в проде

При работе с данными в SQL рано или поздно возникает задача ранжирования: топ-5 продуктов по продажам, рейтинг сотрудников по KPI, распределение клиентов по категориям.

На первый взгляд RANK() и DENSE_RANK() делают почти одно и то же. На тестовых данных разница может быть вообще незаметна. Но в проде именно здесь часто начинаются ошибки: — топ-3 внезапно возвращает 5 строк; — дашборд "врёт"; — backend-логика начинает вести себя не так, как ожидалось; — запрос, который вчера работал быстро, сегодня уходит в disk spill.

Две самые популярные функции для ранжирования — RANK() и DENSE_RANK(). Ниже разберём, чем они отличаются, где именно ошибаются разработчики и аналитики, и что важно понимать: не только что делает оконная функция, но и сколько она стоит на больших объёмах данных.

1. Как работает RANK()

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

Аналогия: представьте Олимпийские игры. Если два спортсмена финишировали одновременно и оба получили золото — серебра не будет. Следующий спортсмен получит бронзу (3-е место), хотя фактически стал вторым по счёту.

Пример:

Значения	Результат RANK()
100, 90, 90, 80	1, 2, 2, 4

2. Как работает DENSE_RANK()

DENSE_RANK() также присваивает одинаковый ранг дублирующимся строкам, но без пропуска позиций. Ранги идут плотно: 1, 2, 2, 3, 4 — никаких "дыр".

Аналогия: школьная система оценок. Два ученика с одинаковым баллом получают одну и ту же позицию, но следующий ученик идёт сразу за ними — без пустых мест.

Пример:

Значения	Результат DENSE_RANK()
100, 90, 90, 80	1, 2, 2, 3

3. Практический пример

Таблица products (product_name, sales_amount):

product_name	sales_amount
Ноутбук	150000
Смартфон	95000
Планшет	95000
Наушники	42000
Мышь	18000

SQL-запрос:

SELECT
    product_name,
    sales_amount,
    RANK()       OVER (ORDER BY sales_amount DESC) AS rank_sales,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank_sales
FROM products
ORDER BY sales_amount DESC;

Результат:

product_name	sales_amount	rank_sales	dense_rank_sales	Комментарий
Ноутбук	150000	1	1	Максимальные продажи
Смартфон	95000	2	2
Планшет	95000	2	2	Оба получают 2
Наушники	42000	4	3	RANK() пропустил 3
Мышь	18000	5	4

Ключевой момент: RANK() пропустил позицию 3, а DENSE_RANK() продолжил с 3.

4. А ещё есть ROW_NUMBER()

Для полноты картины стоит упомянуть третью функцию из этого семейства — ROW_NUMBER().

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER()  OVER (ORDER BY sales_amount DESC) AS row_num,
    RANK()        OVER (ORDER BY sales_amount DESC) AS rank_sales,
    DENSE_RANK()  OVER (ORDER BY sales_amount DESC) AS dense_rank_sales
FROM products
ORDER BY sales_amount DESC;

Результат:

product_name	sales_amount	row_num	rank_sales	dense_rank_sales
Ноутбук	150000	1	1	1
Смартфон	95000	2	2	2
Планшет	95000	3	2	2
Наушники	42000	4	4	3
Мышь	18000	5	5	4

ROW_NUMBER() всегда даёт уникальный номер — даже для дубликатов. Порядок внутри группы дубликатов при этом не определён и зависит от СУБД и плана выполнения. Поэтому при использовании ROW_NUMBER() в реальных запросах лучше задавать детерминированную сортировку, например по двум полям.

5. Когда использовать каждую функцию

RANK() — выбирайте, когда:

• важно отразить реальный "пропуск" позиции;

• вы строите спортивные рейтинги или турнирные таблицы;

• пользователь должен видеть, что позиция была разделена несколькими участниками;

• нужна совместимость с классической системой рангов.

DENSE_RANK() — выбирайте, когда:

• нужны топ-N категории без дыр;

• данные используются для группировки по уровням;

• вы строите аналитические отчёты, сегменты, ценовые группы;

• важно, чтобы количество уникальных рангов совпадало с количеством уникальных значений.

ROW_NUMBER() — выбирайте, когда:

• нужна пагинация;

• требуется дедупликация;

• нужно выбрать ровно одну запись из группы;

• каждой строке нужен гарантированно уникальный номер.

6. Типичная ловушка: топ-N фильтрация

Представьте: вам нужны топ-3 продукта. Вы пишете:

SELECT *
FROM (
    SELECT
        product_name,
        sales_amount,
        RANK() OVER (ORDER BY sales_amount DESC) AS rnk
    FROM products
) ranked
WHERE rnk <= 3;

При наличии дубликатов на 3-й позиции этот запрос может вернуть больше 3 строк.

Это не баг — это корректное поведение RANK().

Именно здесь часто ломается прод. На тестовых данных всё может выглядеть нормально, потому что распределение значений маленькое и без дублей. Но в боевых таблицах, особенно в продажах, KPI, финансовых витринах, скорингах и аналитических отчётах, дубликаты — обычное дело.

Типичный кейс:

• в backend нужен строгий top-5 для выдачи на UI;

• разработчик использует RANK();

• в проде два или три товара делят одну и ту же позицию;

• фронт получает 7 элементов вместо 5;

• дальше ломаются сетка, пагинация или бизнес-ограничения.

Если вам нужно:

• ровно 3 строки → используйте ROW_NUMBER();

• все строки, входящие в топ-3 уровня → используйте DENSE_RANK();

• честное спортивное ранжирование → используйте RANK().

7. Использование с PARTITION BY

Все три функции поддерживают PARTITION BY — это позволяет ранжировать данные независимо внутри каждой группы.

SELECT
    category,
    product_name,
    sales_amount,
    DENSE_RANK() OVER (
        PARTITION BY category
        ORDER BY sales_amount DESC
    ) AS rank_within_category
FROM products;

Это особенно полезно для отчётов типа "топ-3 продукта в каждой категории".

Пример логики:

category	product_name	sales_amount	rank_within_category
Ноутбуки	Ноутбук Pro	150000	1
Ноутбуки	Ноутбук Air	120000	2
Смартфоны	Смартфон X	95000	1
Смартфоны	Смартфон Y	95000	1
Смартфоны	Смартфон Lite	50000	2

Но именно с PARTITION BY начинаются дополнительные нюансы производительности:

• если partitions слишком много — растёт overhead;

• если partitions мало, но они огромные — сортировка становится дорогой;

• если поле для PARTITION BY высококардинальное, можно получить очень тяжёлый план выполнения.

8. Анти-паттерны, которые часто встречаются в реальных запросах

Ниже — набор типичных ошибок, которые на small data почти незаметны, но на production объёмах быстро становятся проблемой.

1. Использовать RANK() там, где нужен strict top-N

Если бизнес хочет ровно 10 строк, RANK() — плохой выбор. Он может вернуть больше записей при дубликатах.

2. Использовать ROW_NUMBER() без детерминированного ORDER BY

Такой запрос формально работает, но может давать разный результат между запусками. Особенно неприятно это проявляется при дедупликации.

Плохо:

ROW_NUMBER() OVER (ORDER BY sales_amount DESC)

Лучше:

ROW_NUMBER() OVER (
    ORDER BY sales_amount DESC, product_name ASC
)

3. Делать PARTITION BY по высококардинальному полю без необходимости

Например, по user_id, если пользователей миллионы. Это не всегда ошибка логически, но очень часто — ошибка архитектурно и с точки зрения стоимости запроса.

4. Применять оконную функцию ко всему датасету, когда можно сначала сузить выборку

Если можно отфильтровать данные до последних 30 дней, это лучше сделать до оконной функции, а не после неё.

9. Что важно понимать (под капотом)

На уровне продакшена и больших данных поведение оконных функций — это не только логика, но и стоимость выполнения (cost).

Сортировка — ключевая операция

Все функции RANK(), DENSE_RANK(), ROW_NUMBER() требуют сортировки:

ORDER BY sales_amount DESC

Это означает:

• O(N log N) по времени

• потенциально большое потребление памяти

Если данных много → сортировка может:

• не поместиться в memory

• уйти в disk (spill)

В PostgreSQL это контролируется через work_mem.

На практике это значит простую вещь: запрос с оконной функцией — это уже не просто "ещё один SELECT". Это потенциально тяжёлая операция, особенно если она выполняется поверх миллионов строк, широких таблиц и сложных джоинов.

Spill to disk

Если объём данных превышает доступную память:

• происходит external sort

• данные пишутся во временные файлы на диск

Это резко увеличивает latency запроса.

Признаки:

• увеличение execution time

• появление Disk в EXPLAIN ANALYZE

Типичный сигнал проблемы в PostgreSQL:

Sort Method: external merge  Disk: 512MB
Execution Time: 12.4s

То есть логика запроса может быть корректной, но стоимость — уже неприемлемой для продакшена.

PARTITION BY и большие данные

PARTITION BY логически разбивает данные, но физически это:

• сортировка внутри каждой группы

• либо перераспределение данных

На больших объёмах:

• много partitions → overhead на сортировку

• мало partitions → большие группы → дорого

Именно поэтому senior-подход — это не только "знаю синтаксис", но и вопрос: на каком объёме это будет работать, а на каком начнёт деградировать?

Распределённые системы (Greenplum, ClickHouse)

В MPP-системах ситуация усложняется.

Пример:

DENSE_RANK() OVER (
    PARTITION BY category
    ORDER BY sales_amount DESC
)

Что происходит:

1. Данные могут быть разбросаны по нодам

2. Нужно сгруппировать по category

3. Это вызывает Shuffle / Redistribute Motion

Последствия:

• network IO

• serialization cost

• возможный bottleneck

То есть оконная функция в распределённой системе — это уже не только CPU и memory, но ещё и сеть.

ClickHouse особенности

В ClickHouse:

• оконные функции выполняются после aggregation stage

• могут требовать полной сортировки набора

Если нет подходящего ORDER BY в таблице: → full scan + sort

На маленьких выборках это может быть незаметно. На аналитической витрине с большим количеством строк — уже очень заметно.

Как оптимизировать

1. Использовать правильные индексы (PostgreSQL):

CREATE INDEX idx_sales ON products (sales_amount DESC);

2. Минимизировать объём данных:

WHERE created_at >= now() - interval '30 days'

3. Уменьшать размер partition:

• не делать PARTITION BY по высококардинальным полям без необходимости

4. Использовать pre-aggregation:

• материализованные представления

• витрины

5. Проверять EXPLAIN ANALYZE:

• Sort Method

• Memory vs Disk

• Execution time

Итог: шпаргалка

Функция	Что делает с дубликатами	Пропуск рангов	Уникальность
ROW_NUMBER()	Даёт разные номера	Нет	Всегда
RANK()	Даёт одинаковый ранг	Да	Только у уникальных значений
DENSE_RANK()	Даёт одинаковый ранг	Нет	Только у уникальных значений

Правило выбора:

• спорт, соревнования, "честный" рейтинг → RANK();

• топ-N отчёты, уровни, категории → DENSE_RANK();

• пагинация, дедупликация, нумерация → ROW_NUMBER().

Вывод

На малых данных: → всё работает "из коробки"

На больших: → это тяжёлая операция: sort + memory + возможный spill + shuffle в MPP