ROW_NUMBER, RANK и DENSE_RANK: разница и когда что

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

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

Я эти три функции использую чуть ли не каждый день: вытащить последний заказ пользователя, построить лидерборд менеджеров, разбить клиентов на квартили по выручке. И почти всегда вопрос сводится к одному: что делать с ничьими. Ответ на него и определяет выбор функции.

Как разница видна на одном примере?

Возьмём маленькую табличку sales с выручкой трёх менеджеров. Двое сделали одинаково — по 500, третий 300. Прогоним все три функции разом:

SELECT
  manager,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rn,
  RANK()       OVER (ORDER BY revenue DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dns
FROM sales;

Результат:

Смотрите на две строки по 500. ROW_NUMBER дал им 1 и 2 — просто потому что кому-то надо быть первым, и движок выбрал сам. RANK дал обоим 1, а потом отдал Вере не 2, а 3: два места «съедены» ничьёй, и второе место как будто пропущено. DENSE_RANK тоже дал обоим 1, но Вере досталось 2 — ранг идёт вплотную, дыр нет.

Отсюда и запоминающийся паттерн: на паре одинаковых значений RANK выдаёт 1, 1, 3, DENSE_RANK — 1, 1, 2, а ROW_NUMBER — 1, 2, 3. Если проговорить это вслух пару раз, на собеседовании уже не собьётесь. Хотите пощупать руками — забейте эти три строки в SQL-тренажёр и поменяйте значения: сделайте три одинаковых, четыре, добавьте ещё уровень. Разница въедается в память быстрее, чем от чтения.

Почему без правильного ORDER BY ранг превращается в лотерею?

Тонкий момент вот в чём. ROW_NUMBER обязан выдать уникальные номера, но если по ключу сортировки есть ничьи, порядок внутри ничьей движок выбирает на своё усмотрение. Сегодня Аня получит 1, а Борис 2; завтра после VACUUM, смены плана запроса или просто другого железа — наоборот. Запрос «дай последний заказ каждого пользователя» с сортировкой только по дате — классическая мина: если у человека два заказа в одну секунду, вы будете стабильно-нестабильно вытаскивать то один, то другой.

-- Ненадёжно: при равной дате порядок не определён
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)

Как сделать tie-break детерминированным?

Лечится добавлением второго (а если надо — третьего) ключа сортировки, который однозначно разводит ничьи. Обычно это уникальный или почти уникальный столбец — id, точный timestamp, суррогатный ключ.

-- Надёжно: при равной дате добит по order_id
SELECT *
FROM (
  SELECT
    order_id,
    user_id,
    amount,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, order_id DESC
    ) AS rn
  FROM orders
) t
WHERE rn = 1;

Теперь при совпадении даты выигрывает больший order_id — правило явное, воспроизводимое, объяснимое. Я держу простое правило: если по функции ранжирования потом фильтрую и жду ровно одну строку на группу, ключ сортировки обязан быть уникальным. Не уверен, что уникален, — дописываю id в хвост ORDER BY. Стоит это ноль усилий, а от плавающих багов спасает намертво.

Для RANK и DENSE_RANK второй ключ работает иначе: он не разбивает ничью (равные значения по-прежнему получат равный ранг по первому ключу), а лишь упорядочивает строки внутри неё. Если вам нужны именно места с корректным дележом — это правильное поведение, ничего дописывать не надо.

Когда брать ROW_NUMBER для топ-N в группе?

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

Ключевое слово — PARTITION BY: он превращает единый рейтинг в отдельный рейтинг внутри каждой группы, счётчик сбрасывается на 1 для каждого user_id. Если возьмёте здесь RANK вместо ROW_NUMBER, при двух заказах с одинаковой суммой можете получить не три строки на клиента, а четыре или пять — потому что ничья раздувает выборку. Когда вам нужно строго «не больше трёх», это ошибка. Задачи ровно такого вида часто попадаются на реальных собеседованиях — их полно в разделе вопросов и разборов и в наборе практических заданий.

Когда RANK, а когда DENSE_RANK?

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

SELECT product, revenue, rnk
FROM (
  SELECT
    product,
    revenue,
    RANK() OVER (ORDER BY revenue DESC) AS rnk
  FROM product_sales
) t
WHERE rnk <= 3;   -- при ничьих может вернуться больше 3 строк — и это ожидаемо

SELECT
  employee,
  salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS grade
FROM employees;

Практическое правило, которым делюсь с джунами: спроси себя, что ты меришь — «сколько людей впереди» или «на каком по счёту уровне». Первое — RANK, второе — DENSE_RANK. А если ничьи вообще нужно проигнорировать и получить уникальную нумерацию — ROW_NUMBER.

Причём здесь NTILE и PERCENT_RANK?

Это соседи по семейству ранжирующих функций, просто отвечают на другой вопрос — не «какое место», а «в какой части распределения».

SELECT
  customer_id,
  revenue,
  NTILE(4) OVER (ORDER BY revenue DESC) AS quartile   -- 1 = топ-25%, 4 = нижние 25%
FROM customer_revenue;

Важный нюанс: NTILE делит по количеству строк, а не по значению. Если строк не поровну на n, первые корзины получат на одну строку больше. И одинаковые значения NTILE может раскидать в разные корзины — на границе бакетов такое бывает. Для честного распределения это нормально, но если пугает, что два клиента с равной выручкой попали в разные квартили, вы теперь знаете почему.

Общая логика всего семейства: ROW_NUMBER/RANK/DENSE_RANK отвечают на «какое место», NTILE — «в какую группу из n», PERCENT_RANK/CUME_DIST — «в каком проценте распределения». Когда держишь это разделение в голове, выбор функции перестаёт быть гаданием. Разложенные определения и синтаксис удобно держать под рукой в SQL-справочнике.

Какие ошибки с ранжированием всплывают на собеседовании?

Самая частая — фильтровать по оконной функции прямо в WHERE. Так нельзя: оконные функции считаются после WHERE, поэтому WHERE rn <= 3 в том же запросе, где объявлен rn, упадёт с ошибкой. Оборачивайте в подзапрос или CTE и фильтруйте снаружи — как во всех примерах выше.

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

Третья — забыть PARTITION BY, когда рейтинг нужен внутри групп. Без него счётчик не сбрасывается, и вы ранжируете всю таблицу как одну кучу вместо «по каждому пользователю отдельно».

Четвёртая — недетерминированный ROW_NUMBER из-за неуникального ORDER BY. Про добивание вторым ключом я уже говорил, но на собеседовании этот момент любят проверять отдельным вопросом «а что если даты совпадают?». Готовый ответ про order_id в хвосте сортировки экономит нервы.

И пятая, чисто из практики: NTILE на маленьких выборках ведёт себя неинтуитивно — на 7 строках и NTILE(4) корзины будут неравными, и это не баг. Ранжирующие функции почти всегда живут внутри более крупных задач — воронки, когорты, ретеншен, — так что тренировать их лучше на живых сценариях. Хорошая пачка таких сценариев есть в разборах кейсов, а базовые метрики вроде DAU как раз строятся на оконных подсчётах поверх событий.

Что забрать с собой

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

Всё это встаёт в голову только руками. Возьмите свой рабочий датасет с заказами или событиями, напишите все три функции в одном SELECT и посмотрите, где расходятся цифры — пройти путь от JOIN до оконных функций можно по шагам, а разбор паттерна «топ-N в группе» и десятки смежных задач с проверкой открываются в Pro, если захочется закрепить не на одном примере, а на потоке.