Топ-N по группам

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

Как спрашивают

Выведи двух самых высокооплачиваемых сотрудников в каждом отделе.

Следом почти всегда: «А если у двоих одинаковая зарплата?»

Короткий ответ

Пронумеровать строки внутри каждой группы оконной функцией ROW_NUMBER() OVER (PARTITION BY отдел ORDER BY зарплата DESC) и оставить строки с номером не больше N. Фильтровать по номеру приходится во внешнем запросе, потому что окна вычисляются после WHERE. Второй способ в PostgreSQL: LATERAL-подзапрос с ORDER BY ... LIMIT N для каждого отдела. При равных значениях выбор функции решает, сколько строк попадёт в ответ: ROW_NUMBER режет строго по N, RANK и DENSE_RANK пускают всех равных.

Разбор: пьедесталы на соревнованиях

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

Раскладка по видам это PARTITION BY, расстановка по местам внутри вида это ORDER BY в окне, а номер места даёт ROW_NUMBER().

На учебной базе блока:

SELECT department_id, name, salary
FROM (
  SELECT department_id, name, salary,
         row_number() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id
         ) AS rn
  FROM employees
) t
WHERE rn <= 2
ORDER BY department_id, rn;

department_id | name  | salary
--------------+-------+-------
            1 | Анна  | 300000
            1 | Борис | 250000
            2 | Глеб  | 200000
            2 | Дина  | 150000
            3 | Егор  | 120000
         NULL | Жанна | 180000

Посмотри на три детали:

1. Фильтр снаружи. WHERE rn <= 2 стоит во внешнем запросе. Написать его рядом с row_number() нельзя: окна считаются на шаге SELECT, после WHERE, это мы разбирали в уроке про порядок выполнения.
2. Жанна без отдела. PARTITION BY собирает все NULL в одну группу, поэтому Жанна стала «лучшей» в группе без отдела. Если такая группа не нужна, добавь WHERE department_id IS NOT NULL во внутренний запрос.
3. id в окне. У Бориса и Веры одинаковая зарплата. Без второй колонки сортировки СУБД вправе отдать второе место любому из них, и ответ будет меняться от запуска к запуску. id делает выбор детерминированным.

Что делать с равными

Это главный уточняющий вопрос. Сравним три функции из урока про ранжирование в отделе разработки. Все три сортируют по salary DESC, а row_number для детерминизма ещё и по id:

name  | salary | row_number | rank | dense_rank
------+--------+------------+------+-----------
Анна  | 300000 |          1 |    1 |          1
Борис | 250000 |          2 |    2 |          2
Вера  | 250000 |          3 |    2 |          2

• С ROW_NUMBER() <= 2 в ответе ровно два человека: Анна и Борис. Вера отброшена, хотя получает столько же.
• С RANK() <= 2 или DENSE_RANK() <= 2 в ответе трое: равные делят место.

Какой вариант верный, решает бизнес. «Покажи на витрине ровно два товара» это ROW_NUMBER. «Выдай премию двум лучшим зарплатам отдела» это DENSE_RANK: обидно лишить Веру премии из-за алфавита. На собеседовании назови оба варианта и спроси, какой нужен.