RANK, DENSE_RANK и ROW_NUMBER: топ-N внутри каждой группы

Почему LIMIT не решает задачу

LIMIT обрезает финальный результат, ничего не зная про группы. Он работает, когда нужен один общий топ: десять самых прибыльных товаров магазина. Как только появляется формулировка «в каждой категории», «по каждой стране», «для каждого продавца» — обрезка результата перестаёт подходить.

Агрегация тоже не помогает. GROUP BY category схлопнет данные до одной строки на категорию, а нам нужны несколько строк внутри каждой — то есть детализация должна остаться. Оконная функция как раз и решает эту задачу: она считает значение, глядя на группу, но не склеивает строки.

Три функции и три разных ответа на ничью

Все три функции нумеруют строки внутри окна, но по-разному ведут себя, когда значения совпадают. ROW_NUMBER всегда даёт уникальные номера подряд — при равенстве порядок определяется тем, что стоит в ORDER BY, а если различающего поля нет, порядок вообще не гарантирован. RANK присваивает равным строкам одинаковый номер и пропускает следующие: 1, 1, 3. DENSE_RANK тоже уравнивает, но не оставляет дыр: 1, 1, 2.

Разница не косметическая. Если два товара делят первое место, условие rank <= 3 вернёт четыре строки при RANK и три при DENSE_RANK. А ROW_NUMBER вернёт ровно три, но один из товаров с одинаковой выручкой попадёт в топ, а другой нет — и решение об этом примет база, а не вы.

Что выбрать под требование бизнеса

Выбор функции — это перевод бизнес-требования в код, а не вопрос вкуса. Если на витрине помещается ровно три карточки, нужен ROW_NUMBER с осмысленным тай-брейкером: при равной выручке показываем товар с большей маржой, а при равной марже — с меньшим id, чтобы результат не менялся от запуска к запуску.

Если рейтинг публичный и «второе место» должно быть честным — берите RANK или DENSE_RANK и заранее договоритесь, что строк может оказаться больше трёх. Для отчёта это нормально, для интерфейса с фиксированной сеткой — нет.

Отдельный случай — когда ничьи в данных возникают массово. Обычно это признак низкой разрядности метрики: округлённые суммы, счётчики с малыми значениями, доли в процентах без десятых. Тогда честнее поменять метрику или добавить вторую, чем героически разруливать совпадения.

• Нужно ровно N строк — ROW_NUMBER плюс явный тай-брейкер.
• Ничьи должны делить место — RANK, число строк может превысить N.
• Нужны последовательные места без пропусков — DENSE_RANK.
• Ничьих подозрительно много — проблема в метрике, а не в функции ранжирования.

Ранжировать нужно уже агрегированный слой

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

Правильный порядок такой: сначала свернуть факты до нужного зерна — одна строка на товар в категории, — и только потом ранжировать этот слой. Зерно окна должно совпадать с зерном вопроса, который задал бизнес.

Проверяется это в одну строку. Если после агрегации число строк равно числу уникальных пар «категория + товар», слой собран верно. Если строк больше, где-то остался лишний ключ — например, забытый order_id в GROUP BY, из-за которого каждый заказ поедет в рейтинг отдельной строкой.

Вторая частая проблема того же рода — товар, который живёт в нескольких категориях. Тогда его выручка попадёт в рейтинг дважды, и сумма мест по категориям перестанет сходиться с общей выручкой. Решать это нужно на уровне справочника: либо у товара одна основная категория, либо в отчёте явно написано, что выручка распределяется между категориями.

Почему фильтр по рангу не работает в том же WHERE

Оконные функции вычисляются после того, как отработали WHERE, GROUP BY и HAVING. Это значит, что на момент фильтрации строк ранга ещё не существует, и обратиться к нему в том же WHERE невозможно — база вернёт ошибку.

Стандартное решение: посчитать ранг в CTE или подзапросе, а фильтровать во внешнем запросе. Получается лишний уровень, зато запрос честно показывает последовательность шагов.

В некоторых СУБД есть сокращение — QUALIFY, который фильтрует результат окна прямо в запросе. Он поддерживается в DuckDB, Snowflake и BigQuery, но не в PostgreSQL, поэтому переносимый вариант — всё-таки CTE.

WITH product_revenue AS (        -- зерно: товар в категории
  SELECT category, product_id,
         sum(amount)  AS revenue,
         sum(margin)  AS margin
  FROM orders
  WHERE status = 'paid'
    AND created_at >= date '2026-07-01'
    AND created_at <  date '2026-08-01'
  GROUP BY category, product_id
),
ranked AS (
  SELECT *,
         row_number() OVER (
           PARTITION BY category
           ORDER BY revenue DESC, margin DESC, product_id
         ) AS place,
         max(revenue) OVER (PARTITION BY category) AS category_leader
  FROM product_revenue
)
SELECT category, place, product_id, revenue,
       round(100.0 * revenue / category_leader, 1) AS pct_of_leader
FROM ranked
WHERE place <= 3
ORDER BY category, place;

Рейтинг без контекста вводит в заблуждение

Место в топе само по себе мало что говорит. Первый товар может опережать второй в пять раз, а может отличаться на полпроцента — решения в этих двух случаях разные, но в колонке «1, 2, 3» они выглядят одинаково.

Поэтому в примере выше рядом с местом считается доля от лидера категории через ещё одно окно. Это дешёвый способ показать, насколько разрыв реальный. Другой полезный контекст — доля товара в выручке всей категории: она сразу отвечает на вопрос, стоит ли вообще заниматься этим рейтингом или в категории всё решает один товар.

Когда рейтинг отвечает не на тот вопрос

Топ-N хорошо показывает лидеров и плохо — структуру. Если в категории 400 товаров, а первые три дают 8% выручки, рейтинг создаёт ложное ощущение, что мы посмотрели на главное. Обратная ситуация не менее опасна: когда один продавец приносит половину оборота, «топ-3» выглядит как нормальное распределение, хотя на деле это риск концентрации.

Поэтому рядом с рейтингом полезно считать накопленную долю: сколько выручки набирают первые N позиций от общего итога. Это то же окно, только с sum(...) OVER (PARTITION BY category ORDER BY revenue DESC), и оно превращает список лидеров в разговор о структуре бизнеса.

Практическое следствие: прежде чем строить топ, спросите, какое решение будет принято по результату. «Что продвигать на главной» — это про топ. «Насколько мы зависим от нескольких позиций» — это про концентрацию, и рейтинг здесь только первый шаг.

Соседние задачи: сегменты и распределение

Если вопрос звучит не «кто в топе», а «как распределена база», ранги заменяются другими оконными функциями. NTILE(4) разложит строки по квартилям и удобен для сегментации клиентов по выручке. PERCENT_RANK и CUME_DIST дают положение строки в распределении в долях, что полезнее ранга, когда групп много и абсолютные места несопоставимы.

А для частой задачи «одна последняя строка на каждого пользователя» в PostgreSQL есть более короткий путь — DISTINCT ON. Он читается компактнее, чем окно с фильтром по первому месту, но работает только в PostgreSQL, о чём стоит помнить, если запрос переедет в другую СУБД.

-- переносимый вариант: окно + фильтр по первому месту
WITH ranked AS (
  SELECT o.*,
         row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC, order_id DESC) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn = 1;

-- короткий вариант для PostgreSQL
SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC, order_id DESC;

Чеклист для рейтинга

Рейтинг почти всегда попадает в презентацию, поэтому ошибку в нём замечают позже всего и обсуждают дольше всего.

• Окно навешено на агрегированный слой, а не на сырые строки?
• В PARTITION BY перечислены именно те поля, которые бизнес называет группой?
• В ORDER BY окна есть тай-брейкер, чтобы результат не менялся между запусками?
• Выбор между RANK, DENSE_RANK и ROW_NUMBER объясняется требованием, а не привычкой?
• Проверено поведение на группе, где ничьи есть, и на группе, где строк меньше N?
• Рядом с местом показан контекст — отрыв от лидера или доля в группе?