Первые N строк в каждой группе с ROW_NUMBER

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

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

Предположим, Вы написали запрос ниже. Он возвращает всего 3 строки по всей таблице, а не по 3 строки для каждого отдела.

LIMIT (или TOP, или FETCH FIRST) применяется к итоговому набору результатов. В стандартном SQL нет LIMIT для каждой группы. Когда интервьюер слышит от Вас предложение использовать LIMIT 3 для задачи с группами, это показывает, что Вы ещё не до конца поняли разбиение на группы.

ROW_NUMBER() — оконная функция, которая присваивает каждой строке уникальное целое число без пропусков в соответствии с порядком сортировки. Без дополнительных указаний она нумерует весь результат целиком.

Ключевой элемент — PARTITION BY: он начинает нумерацию заново с 1 для каждой группы. Объедините PARTITION BY department с ORDER BY salary DESC, и каждый отдел получит собственную последовательность 1, 2, 3, ... в порядке убывания зарплаты.

После выполнения предыдущего запроса каждая строка содержит значение rn. Внутри каждого отдела самая высокая зарплата получает rn = 1, следующая — 2 и так далее. В новом отделе нумерация снова начинается с 1.

• Продажи: Ana (1), Bo (2), Cal (3), Dee (4)
• Инженерный отдел: Eve (1), Fin (2), Gus (3)

Теперь «топ-3 для каждого отдела» просто означает «оставить строки, где rn <= 3».

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

Интервьюеры любят эту ловушку. Решение — вычислить оконную функцию во вложенном запросе или CTE, а затем отфильтровать результат внутреннего запроса во внешнем.

Оберните нумерацию в CTE с именем ranked, а затем выберите из него данные, поместив фильтр во внешний WHERE. Именно такой ответ интервьюеры хотят увидеть, и он хорошо читается.

Запомните этот каркас: разбить данные по группам, отсортировать по показателю, отфильтровать rn ≤ N во внешнем запросе. Он подходит для топ-1, топ-5 и любого N — достаточно изменить одно число.

Если диалект интервьюера старый или он предпочитает вложенные запросы, ту же логику можно разместить в производной таблице внутри FROM. Помните: у производной таблицы обязательно должен быть псевдоним (здесь это r), иначе возникнет синтаксическая ошибка.

Варианты с CTE и производной таблицей взаимозаменяемы для этой задачи. Выберите тот, который интервьюеру кажется более понятным: оба варианта одинаково корректны.

«Найти сотрудника с самой высокой зарплатой в каждом отделе» — это просто случай N = 1. Установите фильтр rn = 1.

Почему бы не использовать MAX(salary) с GROUP BY department? Потому что MAX возвращает значение зарплаты, но не остальные данные этого сотрудника: его имя, дату найма и так далее. ROW_NUMBER сохраняет всю строку победителя, чего обычно и требует задача.

ROW_NUMBER всегда возвращает ровно N строк, даже если зарплаты совпадают. Но определить, какая строка с одинаковым значением получит rn = 1, невозможно однозначно, если не разрешить ничью. Если два человека получают 90000 и Вы оставляете только rn = 1, выбранный сотрудник может меняться между запусками.

Добавьте вторичный уникальный ключ сортировки, например employee_id, чтобы результат был стабильным и воспроизводимым. Интервьюеры высоко оценивают кандидатов, которые сами упоминают детерминированность результата.

Пусть есть таблица sales со столбцами region, product и revenue. Верните 2 лучших продукта по выручке в каждом регионе. Рецепт тот же: разбить данные по region, отсортировать по revenue DESC и оставить строки, где rn <= 2.

Обратите внимание: меняются только столбец разбиения и столбец с показателем. Структура остаётся одинаковой независимо от предметной области.

Чтобы показать больше, чем просто правильность решения, упомяните следующее:

• Индекс по (department, salary DESC) помогает механизму базы данных эффективно формировать отсортированные строки для каждой группы.
• Оконечный подход просматривает таблицу один раз, что значительно эффективнее коррелированного вложенного запроса, выполняемого для каждой строки.
• Для задач top-1 на очень больших объёмах данных некоторые механизмы поддерживают DISTINCT ON (Postgres) как сокращённый вариант, но ROW_NUMBER является переносимым стандартным решением.

Всегда называйте критерий разрешения ничьей и уточняйте требуемое значение N.

Проверьте, насколько хорошо Вы освоили шаблон top-N для каждой группы.

Весь шаблон в одной фразе: разбить данные по группам, отсортировать по показателю, присвоить ROW_NUMBER, а затем оставить rn ≤ N во внешнем запросе.

• LIMIT ограничивает весь набор, но никогда не применяется отдельно к каждой группе.
• Нельзя фильтровать псевдоним оконной функции в WHERE; оберните его в CTE или вложенный запрос.
• Добавляйте уникальный критерий разрешения ничьей для получения детерминированного результата.
• Топ-1 сохраняет всю строку победителя, в отличие от сочетания MAX + GROUP BY.

Измените одно число — и тот же запрос решит задачу для топ-1, топ-5 или любого N.