Ранжирующие функции и top-N на группу

Три ранжирующих функции, одно различие: ничьи

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

• ROW_NUMBER() — строгие 1, 2, 3, 4… Ничьих нет; если две строки равны, он всё равно даёт им разные номера (порядок между ними произволен, если не добавить колонку-тайбрейкер). Используй, когда нужна уникальная последовательность: top-N, дедупликация, пагинация.
• RANK() — ничьи получают одинаковый ранг, затем следующий ранг пропускается. Две строки в ничьей за 1-е место обе 1, а следующая — 3 (2 исчезает). Это «олимпийское» ранжирование: два золота, без серебра.
• DENSE_RANK() — ничьи получают одинаковый ранг, но следующий ранг не пропускается. Две 1, затем 2. Используй для «сколько различных уровней есть», например различных ценовых тиров.

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

SELECT region, salesperson, total,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) AS rn,
       RANK()       OVER (PARTITION BY region ORDER BY total DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY total DESC) AS drnk
FROM region_sales;

Top-N на группу: канонический паттерн

Нельзя фильтровать по оконной функции в WHERE того же SELECT — оконные функции вычисляются после WHERE, в более поздней логической фазе. Поэтому паттерн всегда двухуровневый: посчитай ранг в подзапросе (или CTE), затем отфильтруй ранг во внешнем запросе.

SELECT region, salesperson, total
FROM (
  SELECT region, salesperson, total,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) AS rn
  FROM region_sales
) ranked
WHERE rn <= 3
ORDER BY region, rn;

Это ответ на «топ-3 на регион», «5 последних заказов на клиента», «самый высокооплачиваемый сотрудник в каждом отделе». Выбор ранжирующей функции тут важен: ROW_NUMBER даёт ровно N строк на группу (ничьи разрешены произвольно); RANK может вернуть больше N, если есть ничья на границе (трое в ничьей за 3-е место все имеют ранг 3 и все проходят rnk <= 3). Для «ровно топ-3» используй ROW_NUMBER с детерминированным тайбрейкером; для «все, кто достиг топ-3-результата» используй RANK.

Дедупликация: оставить одну строку на ключ

Та же механика дедуплицирует. Чтобы схлопнуть дублирующиеся строки до самой новой на ключ, ранжируй по свежести и оставь rn = 1:

-- оставить самое свежее событие на пользователя, отбросить более старые дубли
SELECT user_id, ts, payload
FROM (
  SELECT user_id, ts, payload,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS rn
  FROM events
) e
WHERE rn = 1;

PARTITION BY user_id делает каждого пользователя группой; ORDER BY ts DESC ставит самое новое первым; rn = 1 — это самая новая строка. Это чище и куда быстрее, чем гимнастика с DISTINCT ON или self-join, и обобщается: rn <= k оставляет k самых новых.

NTILE(n) — двоюродный брат для разбиения на корзины: он делит упорядоченную партицию на n примерно равных корзин и метит каждую строку 1…n — основа квартилей и перцентильных полос. PERCENT_RANK() и CUME_DIST() дают относительное положение строки дробью в [0, 1] — «эта продажа в 92-м перцентиле своего региона».