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

Коротко

Все три функции нумеруют строки внутри окна в заданном порядке. Разница только в том, что они делают с равными значениями, и именно она определяет, сколько строк вернёт «топ-N».

• ROW_NUMBER даёт каждой строке свой номер: 1, 2, 3, 4. Кого из равных поставить выше, решает случай, если порядок не уточнить.
• RANK даёт равным одно место и пропускает следующие: 1, 2, 2, 4.
• DENSE_RANK даёт равным одно место и не пропускает: 1, 2, 2, 3.
• Фильтр <= 3 по этим трём функциям на одних данных вернул 3, 4 и 4 дня в июле и 3, 3 и 4 дня в августе.
• Ранг нельзя фильтровать в том же WHERE: окна считаются после него. Нужен подзапрос, CTE или QUALIFY, где он есть.
• В PostgreSQL при ORDER BY … DESC NULL встают первыми, в DuckDB — последними. «Топ-3 по дате последней оплаты» в PostgreSQL состоит из тех, кто не платил вовсе.

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

LIMIT обрезает итоговый результат и ничего не знает о группах. Если отсортировать все 90 дней по числу регистраций и взять три первых, получатся 16 июля (100), 15 июля (99) и 28 августа (88). Июнь в ответ не попадёт: его лучший день — 30 июня с 51 регистрацией. Трафик в июне был меньше, и в общем рейтинге его дни проигрывают любому дню августа.

GROUP BY month тоже не помогает: он схлопнет месяц в одну строку, а нужно несколько строк внутри каждого месяца. Оконная функция делает именно это. Она считает значение по группе строк, PARTITION BY month, но строки не склеивает. Каждый день остаётся отдельной строкой и получает номер внутри своего месяца.

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

Вот июль, отсортированный по числу регистраций. На первых двух местах 16 и 15 июля со 100 и 99 регистрациями. Потом идут два дня по 70: 30 и 31 июля. Потом 29 июля с 69.

ROW_NUMBER раздал номера 1–5 и поставил 31 июля третьим, а 30-е четвёртым. Почему именно так — не скажет никто: порядок равных строк не определён, и в другой СУБД, в другой версии или после перезагрузки данных он может быть обратным. RANK дал обоим дням третье место, а следующий день поставил пятым: четвёртое место «занято» ничьёй. DENSE_RANK тоже дал обоим третье место, но следующий день поставил четвёртым.

Ни одна функция не правильнее другой. Они отвечают на разные вопросы. ROW_NUMBER — «какая по счёту строка», RANK — «сколько строк выше этой, плюс один», DENSE_RANK — «какое по счёту значение». Выбор зависит от того, что должен увидеть потребитель рейтинга.

Топ-N в каждой группе: запрос

Запрос складывается из трёх слоёв. Первый сворачивает регистрации до одной строки на день. Второй ранжирует дни внутри месяца. Третий оставляет нужные места. Ранжировать нужно именно второй слой — уже агрегированные дни, а не строки пользователей: иначе ранг получат люди, а не дни.

В ORDER BY окна стоит только число регистраций, и это намеренно. RANK даёт одно место тем, кто равен по всем колонкам этого ORDER BY. Добавьте туда дату — и два дня по 70 перестанут быть равными: 30 июля получит третье место, 31-е четвёртое, и RANK станет вести себя как ROW_NUMBER. Дата нужна во внешнем ORDER BY, чтобы равные дни в выдаче стояли в понятном порядке.

WITH daily AS (
  -- одна строка — день
  SELECT date_trunc('month', signup_date) AS month,
         signup_date AS day,
         count(*) AS signups
  FROM users
  GROUP BY 1, 2
),
ranked AS (
  SELECT *,
         rank() OVER (PARTITION BY month ORDER BY signups DESC) AS place
  FROM daily
)
SELECT month, day, signups, place
FROM ranked
WHERE place <= 3
ORDER BY month, place, day;

-- 10 строк: июнь 3, июль 4 (ничья на третьем месте), август 3

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

Хочется написать короче: WHERE rank() OVER (…) <= 3. PostgreSQL ответит window functions are not allowed in WHERE, DuckDB — WHERE clause cannot contain window functions. Дело не в капризе синтаксиса, а в порядке выполнения. Сначала FROM и WHERE отбирают строки, потом GROUP BY и HAVING собирают группы, и только потом вычисляются оконные функции. К моменту WHERE ранга ещё не существует.

По той же причине ранг нельзя использовать в HAVING, и псевдоним place из SELECT недоступен в WHERE того же запроса. Отсюда стандартное решение: посчитать ранг в подзапросе или CTE и фильтровать снаружи, как в запросе выше.

В DuckDB, Snowflake и BigQuery есть QUALIFY — фильтр, который выполняется после окон: SELECT … QUALIFY rank() OVER (…) <= 3. Это удобно, но в PostgreSQL и MySQL его нет. Запрос с QUALIFY, скопированный из ноутбука в отчёт на PostgreSQL, упадёт с синтаксической ошибкой.

Длину топ-N задают данные, а не число в условии

У запроса выше есть свойство, которое замечают уже после того, как отчёт ушёл заказчику: place <= 3 не обещает трёх строк на группу. Сколько их вернётся, зависит от того, где в группе оказалась ничья и какой функцией считали место.

Три месяца учебной базы показывают все случаи. В июне ничьих в первой тройке нет: 51, 50, 48. Все три функции вернули по три дня. В июле ничья на границе, на третьем месте. ROW_NUMBER отрезал один из двух равных дней по случайному признаку, а RANK и DENSE_RANK вернули оба, то есть четыре строки. В августе ничья выше границы: второе место делят 27 и 26 августа по 87. RANK дал следующему дню, 25 августа с 86, четвёртое место и не пустил его в тройку. DENSE_RANK дал ему третье и пустил: снова четыре строки.

Здесь две функции меняются ролями. При ничьей на границе RANK и DENSE_RANK расширяют топ одинаково. При ничьей выше границы RANK держит длину, а DENSE_RANK расширяет. Предсказать длину, не глядя в данные, нельзя.

Крайний случай — топ по значению с малым числом различных значений. У 912 плательщиков учебной базы всего девять различных сумм выручки: от $19 до $117. «Топ-10 плательщиков» через ROW_NUMBER — это 10 человек. Через RANK — 22: семь человек делят первое место со $117, пятнадцать делят восьмое с $87. Через DENSE_RANK — все 912: десятого различного значения просто нет.

Поэтому длину топа решают заранее, по потребителю. Интерфейсу с фиксированной сеткой нужен ROW_NUMBER с записанным правилом ничьей: ровно N строк, а выбор между равными виден в коде. Отчёту, где важно никого не обидеть, нужен RANK: все равные попадут, и длину стоит показать рядом с таблицей. DENSE_RANK нужен, когда вопрос про значения, а не про объекты: «три лучших уровня выручки», а не «три лучших клиента».

Зеркальный случай — группа, в которой строк меньше N. Ошибки не будет: фильтр вернёт две строки или одну, и в сводной таблице группа окажется короче остальных. Если отчёт ждёт ровно N строк на группу и дорисовывает пустые места, это нужно делать явно, календарём или справочником групп через LEFT JOIN, а не надеяться, что данных хватит.

Ничью решает правило, записанное в ORDER BY

Для ROW_NUMBER ничья — не особый случай, а постоянный источник нестабильности. Если в ORDER BY окна стоит только метрика, порядок равных строк не определён, и отчёт, построенный вчера, сегодня может показать другого «третьего». Никто ничего не менял, просто база прочитала страницы в другом порядке.

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

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

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

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

На агрегированном слое снова всплывают ничьи, и здесь они массовые. У активности мало различных значений: в referral лучший результат 22 события, дальше трое по 19 и шестеро по 18. ROW_NUMBER вернёт трёх человек, RANK — четырёх (1, 2, 2, 2), DENSE_RANK — десятерых: одного с 22, трёх с 19 и шестерых с 18. Для «трёх самых активных» ни один из этих ответов не очевиден. Это повод ещё раз спросить заказчика, что он хочет увидеть.

NULL в сортировке: PostgreSQL и DuckDB расставляют их по-разному

Задача: пять пользователей с самой свежей оплатой. Последняя оплата считается через max(paid_at) после LEFT JOIN пользователей с платежами. У 3 701 из 4 613 человек платежей нет, и дата у них NULL.

Дальше всё зависит от базы. PostgreSQL считает NULL больше любого значения, поэтому при ORDER BY last_paid_at DESC они встают первыми. Все пять мест в «самых свежих» займут люди, которые не платили ни разу. DuckDB по умолчанию ставит NULL в конец в любом направлении, и тот же запрос вернёт тех, кто платил 30 августа. Запрос, проверенный в ноутбуке на DuckDB, в отчёте на PostgreSQL покажет пустые даты — и никакой ошибки.

Лечится это явно: ORDER BY last_paid_at DESC NULLS LAST работает в обеих базах и в окне, и в итоговой сортировке. Второй вариант — отфильтровать NULL до ранжирования, если не плативших в рейтинге быть не должно вовсе. Второе честнее: рейтинг «самой свежей оплаты» среди тех, кто не платил, бессмыслен.

WITH last_payment AS (
  SELECT u.user_id, max(p.paid_at) AS last_paid_at
  FROM users u
  LEFT JOIN payments p ON p.user_id = u.user_id
  GROUP BY u.user_id
)
SELECT user_id, last_paid_at,
       row_number() OVER (ORDER BY last_paid_at DESC NULLS LAST, user_id) AS place
FROM last_payment
ORDER BY place
LIMIT 5;

LATERAL: топ-N без рейтинга всей группы

Окно ранжирует все строки группы и только потом выбрасывает лишние. Есть другой путь: идти от списка групп и для каждой брать N строк подзапросом с ORDER BY … LIMIT. Такой подзапрос должен видеть текущую группу, и для этого есть LATERAL. Результат совпадает с ROW_NUMBER() <= N с тем же правилом ничьей: ровно N строк на группу.

Разница — в цене. Когда групп немного, они берутся из справочника, а подчинённая таблица большая и у неё есть индекс по «группа + метрика», LATERAL читает по N строк на группу и не трогает остальные. Когда групп тысячи, а строк в каждой по несколько, окно дешевле: один проход вместо тысячи маленьких запросов. Выбирают по плану выполнения, а не по вкусу.

У LATERAL есть и смысловое преимущество. Если идти от справочника групп через LEFT JOIN LATERAL (…) ON true, группа без строк останется в отчёте пустой, а не пропадёт.

WITH months AS (
  SELECT DISTINCT date_trunc('month', signup_date) AS month
  FROM users
)
SELECT m.month, t.day, t.signups
FROM months m
CROSS JOIN LATERAL (
  SELECT signup_date AS day, count(*) AS signups
  FROM users
  WHERE date_trunc('month', signup_date) = m.month
  GROUP BY signup_date
  ORDER BY signups DESC, day
  LIMIT 3
) AS t
ORDER BY m.month, t.signups DESC, t.day;

-- 9 строк: ровно по три дня на месяц, ничьи решены более ранней датой

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

Рейтинг всегда отвечает на вопрос «по какой метрике». Каналы учебной базы по числу плательщиков выстраиваются так: organic (410), referral (278), partner (138), paid_search (86). По доле плательщиков — иначе: referral первый (26,8%), organic второй (23,5%). Оба рейтинга верны. Первый говорит, откуда приходят деньги, второй — какой канал приводит людей, готовых платить.

Вывод «organic — лучший канал» из первого рейтинга, скорее всего, будет прочитан как вывод о качестве, хотя это вывод об объёме. Рядом с рейтингом полезно показывать саму метрику и её знаменатель. Место без значения скрывает, насколько близки соседи: 410 против 278 — большой отрыв, 26,8% против 23,5% — не очень.

Соседние функции: PERCENT_RANK, CUME_DIST и NTILE

Иногда нужен не номер, а положение в распределении. PERCENT_RANK переводит RANK в долю от 0 до 1: «этот день лучше 90% дней месяца». CUME_DIST показывает долю строк, которые не выше текущей. NTILE(4) делит строки группы на четыре равные по числу строк корзины.

У всех троих те же проблемы с ничьими, что и у RANK. У NTILE есть своя: он делит строки, а не диапазон метрики. Если у половины плательщиков выручка $19, «нижняя четверть» и «вторая четверть» будут состоять из людей с одинаковой суммой, а граница между ними пройдёт по случайному признаку. Подробнее — в статье про оконные функции.

Частые ошибки в рейтингах

Большинство ошибок в рейтингах не дают сообщения об ошибке. Отчёт показывает правдоподобный топ, просто не тот.

• LIMIT вместо окна: общий топ вместо топа в каждой группе, и маленькие группы пропадают.
• ROW_NUMBER без правила ничьей: «третий» меняется от запуска к запуску.
• Правило ничьей в окне RANK: равные значения получают разные места, и RANK превращается в ROW_NUMBER.
• Ожидание ровно N строк от RANK или DENSE_RANK: в июле и августе учебной базы их по четыре, а в топе плательщиков — 22 и 912.
• Ранг по сырым строкам вместо агрегированного слоя: в топ попадают события, а не люди.
• WHERE rank() OVER (…): ошибка, окна считаются после WHERE.
• QUALIFY в запросе для PostgreSQL: синтаксическая ошибка.
• ORDER BY … DESC по колонке с NULL в PostgreSQL: пустые значения занимают верх рейтинга.
• Место без значения метрики: не видно, отрыв это или ничья с точностью до округления.

Практика на учебной базе

Три задачи. База та же, что в песочнице симулятора SQL-аналитика, поэтому ответы должны совпасть.

Первая. Постройте три лучших дня по регистрациям в каждом месяце через RANK. Сколько строк получилось и в каком месяце их больше трёх? Ответ: 10 строк, в июле четыре — 30 и 31 июля делят третье место.

Вторая. Постройте «топ-10 плательщиков по выручке» тремя функциями и сравните длины. Ответ: 10, 22 и 912. Объясните последнее число, не запуская запрос ещё раз: подсказка — посчитайте count(DISTINCT revenue).

Третья. Найдите самого активного пользователя в каждом канале, включая ничьи. Сколько строк вернёт RANK() = 1? Ответ: 4, по одному на канал: лидеры organic, paid_search, partner и referral набрали 20, 14, 18 и 22 события. А DENSE_RANK() <= 3 для referral вернёт 10 человек.

Чеклист перед тем, как отдать рейтинг

Рейтинг выглядит простым отчётом, но в нём четыре решения, которые обычно принимают неявно. Перед отправкой стоит проверить каждое.

• Группа: что стоит в PARTITION BY и совпадает ли это со словами «в каждом …» из задачи.
• Слой: ранжируются агрегированные строки нужного зерна, а не сырые события.
• Функция: нужно ровно N строк (ROW_NUMBER с правилом ничьей) или все равные (RANK), или уровни значений (DENSE_RANK).
• Ничья: у ROW_NUMBER правило записано в ORDER BY окна и последним стоит уникальный ключ; у RANK и DENSE_RANK в окне только метрика.
• NULL: для сортировки по колонке, где бывают пустые значения, указано NULLS LAST или NULL отфильтрованы.
• Длина: число строк в каждой группе проверено, а не предположено.
• Контекст: рядом с местом показана метрика и её знаменатель.

Итог

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

Оконные функции разобраны в двенадцатой главе симулятора SQL-аналитика. Задания там проверяются автоматически на той же учебной базе, что и примеры выше. Первые главы курса — устройство базы, SELECT и WHERE — открыты бесплатно.