0Pricing
Coding Interview Prep · Урок

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

Классический шаблон разбиения и ранжирования для задачи «три лучших записи в каждой категории».

«Первые N строк в каждой группе с ROW_NUMBER» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Задача о топ-N в каждой группе

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

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

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

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

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

-- WRONG: only 3 rows total, not 3 per department
SELECT department, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Знакомство с ROW_NUMBER

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

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

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rn
FROM employees;

Чтение пронумерованного результата

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

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

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

Нельзя фильтровать rn в WHERE

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

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

-- ERROR: rn does not exist in WHERE
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

Каноническое решение с CTE

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

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

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

Вариант с вложенным запросом

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

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

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
) AS r
WHERE rn <= 3;

Топ-1: лучший сотрудник в каждой группе

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

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

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT name, department, salary, hire_date
FROM ranked
WHERE rn = 1;

Добавление детерминированного критерия разрешения ничьей

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

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

ROW_NUMBER() OVER (
  PARTITION BY department
  ORDER BY salary DESC, employee_id ASC
) AS rn

Конкретный пример с решением

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

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

WITH ranked AS (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY region ORDER BY revenue DESC, product
         ) AS rn
  FROM sales
)
SELECT region, product, revenue
FROM ranked
WHERE rn <= 2
ORDER BY region, rn;

Производительность и тезисы для обсуждения

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

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

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

Быстрая проверка

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

Итоги: топ-N в каждой группе

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

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

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

Часто задаваемые вопросы

Урок «Первые N строк в каждой группе с ROW_NUMBER» бесплатный?

Да — полный текст урока «Первые N строк в каждой группе с ROW_NUMBER» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Первые N строк в каждой группе с ROW_NUMBER»?

Классический шаблон разбиения и ранжирования для задачи «три лучших записи в каждой категории». Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Первые N строк в каждой группе с ROW_NUMBER»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Первые N строк в каждой группе с ROW_NUMBER
  2. Обработка совпадений среди первых N строк
  3. Безопасное удаление дубликатов строк
  4. Сохранение последней строки для каждого ключа
← Назад к Coding Interview Prep