Поиск сотрудника с максимальной зарплатой в отделе

Решение

Задача требует нахождения сотрудника (или сотрудников) с максимальной зарплатой в каждом отделе. Ключевой момент — обработка случаев, когда несколько сотрудников имеют одинаковую максимальную зарплату.

Подход к решению

Оконные функции RANK() или DENSE_RANK() позволяют ранжировать зарплаты внутри каждого отдела и выбрать те, которые имеют ранг 1 (максимальные).

SQL-запрос (рекомендуемый вариант)

WITH ranked_salaries AS (
  SELECT 
    depname,
    empno,
    salary,
    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank
  FROM salaries
)
SELECT 
  depname,
  empno,
  salary
FROM ranked_salaries
WHERE rank = 1
ORDER BY depname, empno;

Пошаговое объяснение

1. CTE ranked_salaries

• PARTITION BY depname — окно считается отдельно для каждого отдела
• ORDER BY salary DESC — сортируем по зарплате в убывающем порядке
• RANK() — присваивает ранг (при одинаковых значениях ранг одинаковый, следующий скачет)

2. WHERE rank = 1

• Выбираем только сотрудников с максимальной зарплатой в отделе
• Если несколько имеют одинаковую максимум, все получат ранг 1

3. Сортировка

• ORDER BY depname, empno — для удобства чтения результата

Различие RANK vs DENSE_RANK

RANK() — имеет пробелы при связях:

Salary: 6000, 6000, 5000
Rank:   1,    1,    3    (пропускает 2)

DENSE_RANK() — без пробелов:

Salary: 6000, 6000, 5000
Rank:   1,    1,    2    (нет пробелов)

В данной задаче для нахождения максимума оба одинаково работают, но RANK() более общепринят.

Альтернативный вариант: С MAX и самообъединением

SELECT 
  s.depname,
  s.empno,
  s.salary
FROM salaries s
INNER JOIN (
  SELECT depname, MAX(salary) AS max_salary
  FROM salaries
  GROUP BY depname
) max_sal ON s.depname = max_sal.depname AND s.salary = max_sal.max_salary
ORDER BY s.depname, s.empno;

Как работает:

• Подзапрос находит максимальную зарплату в каждом отделе
• INNER JOIN соединяет со строками, у которых зарплата равна максимальной
• Автоматически обрабатывает случаи с одинаковыми максимумами

Этот вариант может быть медленнее на больших объёмах из-за двойного сканирования таблицы.

Вариант с ROW_NUMBER (если нужен ровно один сотрудник)

Если требуется выбрать только одного сотрудника при одинаковых зарплатах:

WITH ranked_salaries AS (
  SELECT 
    depname,
    empno,
    salary,
    ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC, empno ASC) AS row_num
  FROM salaries
)
SELECT 
  depname,
  empno,
  salary
FROM ranked_salaries
WHERE row_num = 1
ORDER BY depname;

ROW_NUMBER() — присваивает уникальный номер каждой строке, даже при одинаковых значениях. ORDER BY salary DESC, empno ASC — если зарплаты равны, выбирается сотрудник с меньшим номером.

Пример выполнения

Исходные данные:

depname | empno | salary
--------|-------|-------
IT      | 1     | 5000
IT      | 2     | 6000
IT      | 3     | 6000
Sales   | 4     | 4500
Sales   | 5     | 5500

После RANK():

depname | empno | salary | rank
--------|-------|--------|-----
IT      | 2     | 6000   | 1
IT      | 3     | 6000   | 1
IT      | 1     | 5000   | 3
Sales   | 5     | 5500   | 1
Sales   | 4     | 4500   | 2

Финальный результат (rank = 1):

depname | empno | salary
--------|-------|-------
IT      | 2     | 6000
IT      | 3     | 6000
Sales   | 5     | 5500

Расширенный вариант с дополнительной информацией

WITH ranked_salaries AS (
  SELECT 
    depname,
    empno,
    salary,
    COUNT(*) OVER (PARTITION BY depname) AS dept_size,
    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank,
    MAX(salary) OVER (PARTITION BY depname) AS max_salary,
    AVG(salary) OVER (PARTITION BY depname) AS avg_salary
  FROM salaries
)
SELECT 
  depname,
  empno,
  salary,
  dept_size,
  max_salary,
  ROUND(avg_salary, 2) AS avg_salary,
  ROUND((salary - avg_salary) / avg_salary * 100, 2) AS pct_above_avg
FROM ranked_salaries
WHERE rank = 1
ORDER BY depname, empno;

Это показывает контекст зарплаты в отделе.

Важные моменты

✓ PARTITION BY depname — критично для разделения отделов

✓ ORDER BY salary DESC — сортируем в убывающем порядке для нахождения максимума

✓ RANK vs ROW_NUMBER — RANK сохраняет связи, ROW_NUMBER — выбирает одного по доп. критерию

✓ Производительность — оконные функции эффективнее GROUP BY + MAX для таких задач

✓ Индексы — для больших таблиц рекомендуется индекс на (depname, salary DESC)