Ранжирование и top-N на группу

Маркетолог Brew пришёл с двумя просьбами, и обе звучали безобидно. Первая: «дай мне топ-1 напиток в каждой категории — что именно лучше всего продаётся в кофе, в холодных, в чае». Вторая: «разложи всё меню по квартилям продаж — кто лидеры, а кого пора снимать». Аналитик сети сел писать запрос, набрал привычное SELECT category, max(units) FROM drink_sales_lab GROUP BY category — и тут же завис.

max(units) вернул число — 150 для кофе. А маркетологу нужно было название напитка, который эти 150 сделал. Дописать в SELECT ещё и drink нельзя: он не под агрегатом и не в GROUP BY, Postgres такое не пропускает. Можно вывернуться через коррелированный подзапрос или JOIN обратно по max, но тогда на ничьих — а внутри кофе два напитка продались по 120 — вылезут оба, и непонятно, кого считать «топ-1». А квартили GROUP BY не умеет в принципе: чтобы разложить восемь строк на четыре корзины, нужно сравнивать строку с её соседями по порядку, а не схлопывать их в одно число.

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

Три ранга и их поведение на ничьих

Postgres даёт три функции, которые нумеруют строки внутри окна по ORDER BY. Снаружи они выглядят одинаково — все три выдают 1, 2, 3, …. Различие проявляется ровно там, где две строки равны по порядку сортировки, то есть на ничьей.

row_number() присваивает строго уникальный номер: 1, 2, 3, 4. Две строки с одинаковыми продажами всё равно получат разные номера — какой именно, решит порядок, в котором они подвернутся (а если в ORDER BY нет уникального tie-break, то решит случай). rank() даёт ничьим один номер и затем пропускает следующие: после двух вторых мест идёт сразу четвёртое — 1, 2, 2, 4. Это «спортивный» ранг: два серебра — и бронзы нет, сразу четвёртое место. dense_rank() тоже даёт ничьим один номер, но не пропускает: 1, 2, 2, 3. Это «плотный» ранг — после двух вторых идёт третье, дырки в нумерации нет.

Важная тонкость: что вообще считается ничьей? Для rank() и dense_rank() две строки — пиры (равны по рангу) тогда и только тогда, когда они равны по всем колонкам ORDER BY окна. Стоит добавить в ORDER BY ещё одну колонку, по которой строки различаются — и ничья распадётся, а rank/dense_rank выродятся в row_number. Это не баг, это определение: ранг считается по тому, что записано в ORDER BY, ни больше ни меньше.

Три функции в одной таблице — расходятся они только на ничьей:

Функция	На ничьей	Дальше после ничьей	Когда брать
`row_number()`	разные номера	— (нумерация и так сплошная)	стабильный top-N, пагинация по рангу
`rank()`	один номер на всех	пропуск: `1, 2, 2, 4`	призовые места («два серебра — и сразу четвёртое»)
`dense_rank()`	один номер на всех	без пропуска: `1, 2, 2, 3`	уровни и грейды, где дырка в нумерации сбивает

Что показывает наш код

Центр урока — query.sql. Первый запрос ставит три ранга бок о бок внутри одной категории coffee, и здесь видна та самая тонкость с ничьими — поэтому в нём два разных окна:

sql

SELECT
    drink,
    units,
    row_number() OVER wu AS rn,
    rank()       OVER wt AS rnk,
    dense_rank() OVER wt AS dns
FROM drink_sales_lab
WHERE category = 'coffee'
WINDOW wu AS (ORDER BY units DESC, drink),
       wt AS (ORDER BY units DESC)
ORDER BY units DESC, drink;

Окно wu сортирует по units DESC, drink — добавочный drink даёт уникальный tie-break, поэтому row_number() получает строгую нумерацию 1, 2, 3, 4 без всякой случайности. Окно wt сортирует только по units DESC — без tie-break, и тогда Капучино и Эспрессо по 120 остаются настоящими пирами, на которых rank() и dense_rank() покажут своё фирменное поведение. Считать все три по одному окну было бы ошибкой: общий tie-break по drink сделал бы 120 и 120 различимыми, и тогда rank/dense_rank выдали бы 2, 3 вместо 2, 2 — то самое вырождение в row_number.

Второй запрос решает задачу маркетолога про топ-1. Приём классический: пронумеровать строки внутри каждой категории и оставить только первые.

sql

WITH ranked AS (
    SELECT category, drink, units,
           row_number() OVER (PARTITION BY category ORDER BY units DESC, drink) AS rn
    FROM drink_sales_lab
)
SELECT category, drink, units
FROM ranked
WHERE rn = 1
ORDER BY category;

PARTITION BY category перезапускает нумерацию в каждой категории, так что rn = 1 — это лидер своей группы, а не всей таблицы. Почему нумерация прячется в CTE, а не фильтруется прямо в WHERE rn = 1 снаружи? Потому что оконные функции считаются после WHERE — на этапе фильтрации rn ещё не существует. Поэтому сначала считаем ранг в подзапросе, а уже к его результату применяем условие.

Третий запрос отвечает на просьбу про квартили — ntile(4) раскладывает все восемь напитков на четыре равные корзины по продажам, по две в каждой. Корзина 1 — лидеры, 4 — аутсайдеры; конкретное место внутри корзины не важно, важна сама группа.

cmd/demo/main.go тонкий: открывает пул, вызывает три типизированных метода (RankFunctions, TopPerCategory, Quartiles, сгенерированные sqlc из query.sql) и печатает результат через tabwriter. Данные drink_sales_lab зашиты фиксированным seed в schema.sql, поэтому вывод детерминирован.

Запуск

docker compose up -d
make lecture L=08-analytical-and-lateral/08-02-ranking-and-top-n-per-group T=db-reset
make lecture L=08-analytical-and-lateral/08-02-ranking-and-top-n-per-group

T=run — цель по умолчанию, её можно не указывать. Изнутри каталога юнита это просто make db-reset и make run.

plaintext

1) Три ранга в категории coffee (ORDER BY units DESC, drink):
НАПИТОК   продано  row_number  rank  dense_rank
Латте     150      1           1     1
Капучино  120      2           2     2
Эспрессо  120      3           2     2
Раф       90       4           4     3
   → row_number уникален (2,3); rank ставит ничьим 2,2 и прыгает на 4; dense_rank идёт 2,2,3.
 
2) Лидер продаж в каждой категории (row_number() = 1 в CTE):
КАТЕГОРИЯ  напиток   продано
coffee     Латте     150
cold       Колд брю  70
tea        Сенча     50
 
3) ntile(4) — квартили продаж (корзина 1 — лидеры, 4 — аутсайдеры):
НАПИТОК   продано  квартиль
Латте     150      1
Капучино  120      1
Эспрессо  120      2
Раф       90       2
Колд брю  70       3
Сенча     50       3
Фраппе    40       4
Матча     30       4

Капучино и Эспрессо в первом блоке — обе по 120. row_number развёл их по 2 и 3 (tie-break по drink), а rank и dense_rank оставили обеим 2. Дальше Раф на 90: rank прыгает на 4 (пропустил 3, потому что два места были заняты двойками), а dense_rank идёт ровно 3 — без дырки.

Заборчик

row_number() недетерминирован без полного ORDER BY. Если двум строкам нечем различиться, движок волен пронумеровать их в любом порядке, и на следующем прогоне он может смениться. Поэтому в wu стоит drink вторым ключом — 1, 2, 3, 4 каждый раз лягут одинаково. В проде аналитик добавляет такой tie-break всегда, когда ранг отбирает строки, иначе «топ-1» тихо гуляет между запусками.
С rank() и dense_rank() наоборот: лишний tie-break ломает ничью. Допишешь в их ORDER BY уникальную колонку — и 120/120 перестанут быть пирами, функции выродятся в row_number. Поэтому для них отдельное окно wt без drink: в ORDER BY кладём ровно те колонки, равенство по которым считаем ничьей, ни одной лишней.
ntile() при неделимом числе строк не падает, а раскидывает остаток по первым корзинам: восемь на четыре делятся ровно, а девять дали бы 3, 2, 2, 2. Учитывай это на децилях, где число строк не кратно числу корзин — первые корзины окажутся чуть полнее.
«Top-N на группу» через row_number() = 1 в CTE — рабочая классика, но не единственный путь. На огромных партициях быстрее LATERAL (к нему придём в 08-05) или индекс под (category, units DESC), по которому top-1 каждой группы достаётся почти даром. Твой DBA посмотрит на план: если row_number гонит сортировку всей таблицы ради одной строки на категорию, индекс или LATERAL срежут работу на порядок.
Выбор между rank и dense_rank — не про производительность, а про смысл отчёта: нужны ли в нумерации «дырки» после ничьих. Хочешь «после двух серебряных сразу четвёртое» — rank. Хочешь «уровни без пропусков» — dense_rank. Решай по тому, как отчёт будут читать люди.

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

Три ранжирующие функции нумеруют строки внутри окна по ORDER BY и расходятся только на ничьих: row_number() строго уникален (1, 2, 3, 4), rank() даёт ничьим один номер и пропускает следующие (1, 2, 2, 4), dense_rank() даёт один номер без пропуска (1, 2, 2, 3). Ничья при этом — равенство по всем колонкам ORDER BY окна, и порядок ключей в ORDER BY решает всё: для строгой нумерации добавляй уникальный tie-break, для честных пиров — не добавляй. «Top-N на группу» собирается из row_number() с PARTITION BY в CTE и WHERE rn = 1 снаружи, потому что окно считается после WHERE. А ntile(n) раскладывает строки по корзинам, когда нужен не точный ранг, а группа — квартили, децили, перцентили.

Это прямое развитие 08-01: там мы впервые поставили OVER (...) и считали агрегаты по окну, не схлопывая строки. Ранжирующие функции — те же оконные функции, только вместо «сумма по окну» они отвечают «какое место в окне». Следующий юнит 08-03 идёт дальше по той же оси: lag/lead смотрят на соседние строки окна (вчерашние продажи рядом с сегодняшними), а явные фреймы задают, какой именно кусок окна берётся в расчёт — скользящее среднее за неделю, нарастающий итог, разница с предыдущим днём. После ранга, который отвечает «где строка стоит», логично спросить «что рядом со строкой» — этим и займёмся.