Как получить из таблицы отдел с самой высокой заработной платой?

SQL-запрос для отдела с максимальной суммарной зарплатой

Предположим, у нас есть таблица Employees со следующими полями:

• id — идентификатор сотрудника
• name — имя сотрудника
• department — название отдела
• salary — зарплата сотрудника

1. Базовый подход с группировкой и сортировкой

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

SELECT 
    department,
    SUM(salary) as total_salary
FROM Employees
GROUP BY department
ORDER BY total_salary DESC
LIMIT 1;

2. Использование подзапроса с MAX()

Более надежный способ — найти максимальное значение суммарной зарплаты, а затем получить отдел с этой суммой:

SELECT 
    department,
    SUM(salary) as total_salary
FROM Employees
GROUP BY department
HAVING SUM(salary) = (
    SELECT MAX(sum_salary)
    FROM (
        SELECT SUM(salary) as sum_salary
        FROM Employees
        GROUP BY department
    ) as department_salaries
);

3. Использование оконных функций (более современный подход)

В современных СУБД (PostgreSQL, SQL Server, Oracle) можно использовать оконные функции:

WITH DepartmentSalaries AS (
    SELECT 
        department,
        SUM(salary) as total_salary,
        RANK() OVER (ORDER BY SUM(salary) DESC) as salary_rank
    FROM Employees
    GROUP BY department
)
SELECT 
    department,
    total_salary
FROM DepartmentSalaries
WHERE salary_rank = 1;

4. Решение с Common Table Expression (CTE)

WITH SalarySums AS (
    SELECT 
        department,
        SUM(salary) as total_salary
    FROM Employees
    GROUP BY department
)
SELECT 
    department,
    total_salary
FROM SalarySums
WHERE total_salary = (SELECT MAX(total_salary) FROM SalarySums);

Важные нюансы и оптимизация

Обработка нескольких отделов с одинаковой максимальной зарплатой

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

• LIMIT 1 вернет только один отдел (случайный или первый в порядке сортировки)
• HAVING с MAX() вернет все отделы с максимальной зарплатой
• Оконные функции с RANK() также вернут все отделы-победители

Оптимизация производительности

Для больших таблиц важно:

1. Создать индекс на поле department и, возможно, составной индекс на (department, salary):

   CREATE INDEX idx_dept_salary ON Employees(department, salary);
   

2. Использовать материализованные представления для часто выполняемых агрегаций:

   CREATE MATERIALIZED VIEW department_totals AS
   SELECT department, SUM(salary) as total_salary
   FROM Employees
   GROUP BY department;
   

Потенциальные проблемы и их решения

Проблема 1: NULL-значения в зарплатах

SELECT 
    department,
    SUM(COALESCE(salary, 0)) as total_salary
FROM Employees
GROUP BY department
ORDER BY total_salary DESC
LIMIT 1;

Проблема 2: Не учитываем уволенных сотрудников Если в таблице есть поле is_active:

SELECT 
    department,
    SUM(salary) as total_salary
FROM Employees
WHERE is_active = true
GROUP BY department
ORDER BY total_salary DESC
LIMIT 1;

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

-- Создание тестовой таблицы
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Вставка тестовых данных
INSERT INTO Employees VALUES 
(1, 'Иван', 'IT', 100000),
(2, 'Мария', 'IT', 120000),
(3, 'Петр', 'Бухгалтерия', 80000),
(4, 'Анна', 'Бухгалтерия', 85000),
(5, 'Сергей', 'Продажи', 90000);

-- Выполнение запроса
SELECT 
    department,
    SUM(salary) as total_salary,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM Employees
GROUP BY department
ORDER BY total_salary DESC
LIMIT 1;

Результат:

| department | total_salary | employee_count | avg_salary |
|------------|--------------|----------------|------------|
| IT         | 220000       | 2              | 110000     |

Выводы

Выбор конкретного подхода зависит от:

1. Размера данных — для маленьких таблиц подойдет простой GROUP BY с LIMIT, для больших — оптимизированные запросы с индексами
2. Требований к результату — нужен один отдел или все отделы с максимальной зарплатой
3. Версии СУБД — поддержка оконных функций и CTE
4. Частоты выполнения — для часто выполняемых запросов стоит использовать материализованные представления

Наиболее универсальным и надежным является подход с CTE или оконными функциями, так как они четко обрабатывают случаи с несколькими отделами-лидерами и обычно хорошо оптимизируются современными СУБД.