0Pricing
SQL Interview Prep · Урок

Самый высокооплачиваемый сотрудник отдела

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

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

От глобального ранжирования к ранжированию по группам

Следующее усложнение: «Найдите самого высокооплачиваемого сотрудника в каждом отделе». Это сочетает ранжирование с группировкой и является типичным вопросом для специалиста среднего уровня.

Предположим, есть таблица employee со столбцами id, name, department_id и salary. Нам нужен один (или несколько при равенстве) сотрудник с самой высокой зарплатой в каждом отделе, а не только глобальный максимум.

Главный новый инструмент — PARTITION BY, который начинает ранжирование заново внутри каждого отдела.

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY сбрасывает ранжирование

Добавление PARTITION BY department_id к оконной функции говорит базе данных вычислять ранг независимо внутри каждого отдела.

Каждый отдел начинает собственную нумерацию с ранга 1. Поэтому самый высокооплачиваемый сотрудник отдела 1 и самый высокооплачиваемый сотрудник отдела 5 оба получают ранг 1. Без разбиения ранг 1 получил бы только один сотрудник с глобально максимальной зарплатой.

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

Фильтрация по рангу 1

Чтобы оставить только сотрудников с максимальной зарплатой, оберните запрос с рангами и отфильтруйте строки по рангу 1. Как всегда, оконная функция должна быть вычислена в подзапросе или CTE, прежде чем по ней можно фильтровать.

Использование DENSE_RANK (или RANK) означает, что если два сотрудника в отделе получают одинаковую максимальную зарплату, будут возвращены оба. Обычно это правильная интерпретация понятия «самый высокооплачиваемый сотрудник».

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

ROW_NUMBER, когда нужна ровно одна строка

Иногда на собеседовании требуется ровно одна строка для каждого отдела, даже если есть одинаковые значения. Тогда используйте ROW_NUMBER и добавьте детерминированный дополнительный критерий, например наименьший идентификатор.

Без этого дополнительного критерия одинаковые значения разрешаются произвольно, и результат не является детерминированным. Добавление , id ASC делает выбор воспроизводимым.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

DENSE_RANK против ROW_NUMBER и RANK в этом случае

Выбирайте исходя из точной формулировки:

  • DENSE_RANK = 1: все сотрудники с одинаковой максимальной зарплатой в каждом отделе.
  • RANK = 1: то же, что и DENSE_RANK для первого места (пропуски имеют значение только ниже первого места).
  • ROW_NUMBER = 1: ровно один сотрудник в каждом отделе; совпадения разрешаются с помощью вашего ORDER BY.

На собеседовании оценивается то, объясните ли Вы, какой вариант выбрали и почему.

Коррелированный подход до появления оконных функций

До появления оконных функций стандартным решением был коррелированный подзапрос: оставляйте строку только в том случае, если в том же отделе никто не получает больше.

Такой подход естественным образом возвращает всех сотрудников, делящих первое место. Он переносим между системами, но может работать медленно, поскольку внутренний MAX вычисляется для каждой внешней строки, если только оптимизатор не перепишет запрос.

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

Подход с соединением через GROUP BY

Ещё один переносимый шаблон: вычислите максимальную зарплату для каждого отдела с помощью GROUP BY, а затем соедините результат обратно, чтобы получить подходящих сотрудников.

Этот подход эффективен и понятен. Соединение возвращает каждого сотрудника, чья зарплата равна максимальной зарплате в его отделе, поэтому совпадения сохраняются.

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

Первые N сотрудников в каждом отделе

Этот шаблон без новых идей расширяется до задачи «три самых высокооплачиваемых сотрудника в каждом отделе». Просто замените условие фильтра диапазоном.

С DENSE_RANK условие rnk <= 3 возвращает три высших различных уровня зарплаты (при совпадениях строк может быть больше трёх). С ROW_NUMBER условие rn <= 3 возвращает ровно три строки для каждого отдела.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

Практический пример

Отдел 1: Ana — 120, Bob — 120, Cara — 90. Отдел 2: Dan — 200, Eve — 150.

  • DENSE_RANK = 1: Ana (120) и Bob (120) из отдела 1; Dan (200) из отдела 2. Всего три строки.
  • ROW_NUMBER = 1 с идентификатором как дополнительным критерием: один из Ana и Bob (у кого идентификатор меньше) и Dan. Всего две строки.

Данные те же, но количество строк зависит от функции. Выбирайте вариант в соответствии с формулировкой задачи.

Включение отделов и соединение с их названиями

На собеседованиях часто добавляют таблицу department и просят вывести название отдела. Просто соедините её после ранжирования.

Выполняйте ранжирование по таблице employee, а таблицу-справочник соединяйте в конце, чтобы разбиение на группы по-прежнему выполнялось на правильном уровне детализации.

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

Ошибки, которых следует избегать

Распространённые ошибки при ранжировании по группам:

  • Забыть PARTITION BY и ранжировать все строки вместе, получив только самого высокооплачиваемого сотрудника во всей компании.
  • Использовать ROW_NUMBER, хотя из формулировки следует, что нужно показать всех сотрудников с одинаковым результатом, и незаметно исключить остальных лидеров.
  • Пытаться поместить оконную функцию непосредственно в WHERE, вместо того чтобы обернуть запрос.
  • Соединить таблицу отделов до ранжирования и случайно изменить уровень детализации групп.

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

Выберите подходящую функцию ранжирования для данного требования.

Итоги

Поиск самого высокооплачиваемого сотрудника в каждом отделе — это шаблон глобального ранжирования с добавлением PARTITION BY department_id:

  • DENSE_RANK = 1 возвращает всех сотрудников с одинаковой максимальной зарплатой в каждом отделе.
  • ROW_NUMBER = 1 с дополнительным критерием возвращает ровно одного сотрудника в каждом отделе.
  • Переносимые альтернативы: коррелированный MAX для каждого отдела или максимальное значение через GROUP BY с последующим соединением обратно с таблицей.

Чтобы получить первые N результатов, замените = 1 на <= N. Всегда вслух проговаривайте, как Вы обрабатываете совпадения.

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

Урок «Самый высокооплачиваемый сотрудник отдела» бесплатный?

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

Чему я научусь в уроке «Самый высокооплачиваемый сотрудник отдела»?

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

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

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

Сколько времени занимает урок «Самый высокооплачиваемый сотрудник отдела»?

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

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

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

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

  1. Вторая по величине зарплата: пять способов
  2. N-е по величине значение с DENSE_RANK
  3. Самый высокооплачиваемый сотрудник отдела
  4. Возврат NULL при отсутствии N-го значения
← Назад к SQL Interview Prep