SQL: Найти второго по зарплате сотрудника

SQL: Найти второго по зарплате сотрудника

Условие

Дана таблица employees:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT
);

Нужно найти второго по зарплате сотрудника в каждом департаменте.

Решение 1: Оконная функция ROW_NUMBER()

-- Самое простое и рекомендуемое решение

WITH ranked_employees AS (
    SELECT 
        id,
        name,
        salary,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT 
    id,
    name,
    salary,
    department_id
FROM ranked_employees
WHERE rank = 2;

Объяснение:

• ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) присваивает номер каждому сотруднику внутри его департамента
• PARTITION BY department_id — группирует по департаменту
• ORDER BY salary DESC — сортирует по зарплате (от высокой к низкой)
• rank = 2 — берём только вторых

Сложность:

• Время: O(n log n) из-за сортировки
• Память: O(n)

Решение 2: RANK() вместо ROW_NUMBER()

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

WITH ranked_employees AS (
    SELECT 
        id,
        name,
        salary,
        department_id,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT 
    id,
    name,
    salary,
    department_id
FROM ranked_employees
WHERE rank = 2;

Разница между ROW_NUMBER() и RANK():

Зарплаты: 5000, 4500, 4500, 3000

ROW_NUMBER():  1,    2,    3,    4
RANK():        1,    2,    2,    4
DENSE_RANK():  1,    2,    2,    3

• ROW_NUMBER() — уникальный номер для каждого (не пропускает)
• RANK() — может быть несколько с одинаковым номером (пропускает следующие)
• DENSE_RANK() — пропускает меньше

Выбор:

• ROW_NUMBER() — если нужен ровно k-й сотрудник
• RANK() — если нужен k-й по уровню зарплаты (может быть несколько человек)

Решение 3: OFFSET/LIMIT

-- Находит всех людей со второй по размеру зарплатой в каждом отделе

SELECT 
    e.id,
    e.name,
    e.salary,
    e.department_id
FROM employees e
WHERE (
    SELECT COUNT(DISTINCT salary) 
    FROM employees e2 
    WHERE e2.department_id = e.department_id 
    AND e2.salary > e.salary
) = 1;

Идея: Считаем сколько РАЗЛИЧНЫХ зарплат выше текущей. Если ровно 1 — это второй по величине.

Сложность: O(n^2) — плохо для больших таблиц

Решение 4: LAG() функция

-- Берём следующий уникальный уровень зарплаты

WITH unique_salaries AS (
    SELECT DISTINCT 
        salary,
        department_id
    FROM employees
    ORDER BY department_id, salary DESC
),
ranked_salaries AS (
    SELECT 
        salary,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM unique_salaries
)
SELECT 
    e.id,
    e.name,
    e.salary,
    e.department_id
FROM employees e
INNER JOIN ranked_salaries rs 
    ON e.salary = rs.salary 
    AND e.department_id = rs.department_id 
WHERE rs.rank = 2;

Решение 5: Без оконных функций (для старых БД)

-- Для PostgreSQL/MySQL без поддержки оконных функций

SELECT 
    e.id,
    e.name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department_id = e.department_id 
    AND e2.salary < (
        SELECT MAX(salary) 
        FROM employees e3 
        WHERE e3.department_id = e.department_id
    )
);

Логика:

1. Внутренний SELECT MAX — максимальная зарплата в отделе
2. Где salary < этого максимума — находим зарплаты ниже максимума
3. FROM этих значений берём MAX — вторая по величине зарплата

Полное решение с тестовыми данными

-- Создание таблицы
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT
);

-- Вставка тестовых данных
INSERT INTO employees VALUES
(1, 'Alice', 5000.00, 1),
(2, 'Bob', 4500.00, 1),
(3, 'Charlie', 4500.00, 1),
(4, 'David', 3000.00, 1),
(5, 'Eve', 6000.00, 2),
(6, 'Frank', 5500.00, 2),
(7, 'Grace', 5000.00, 2),
(8, 'Henry', 4000.00, 2);

-- Решение с ROW_NUMBER()
WITH ranked_employees AS (
    SELECT 
        id,
        name,
        salary,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT 
    id,
    name,
    salary,
    department_id
FROM ranked_employees
WHERE rank = 2
ORDER BY department_id;

-- Результат:
-- Departament 1: Bob (4500) - первый из двух с одинаковой зарплатой
-- Departament 2: Frank (5500)

Расширение: Найти k-го сотрудника

-- Общее решение для любого k

CREATE FUNCTION get_kth_salary(p_department_id INT, p_k INT)
RETURNS TABLE(id INT, name VARCHAR, salary DECIMAL, department_id INT) AS $$
    WITH ranked AS (
        SELECT 
            id,
            name,
            salary,
            department_id,
            ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
        FROM employees
    )
    SELECT id, name, salary, department_id
    FROM ranked
    WHERE department_id = p_department_id AND rank = p_k;
$$ LANGUAGE SQL;

-- Использование
SELECT * FROM get_kth_salary(1, 2);

Сравнение производительности

Оптимизация для больших таблиц

-- Добавляем индекс для быстроты
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary DESC);

-- Тогда запрос будет использовать этот индекс
EXPLAIN ANALYZE
SELECT 
    id,
    name,
    salary,
    department_id
FROM (
    SELECT 
        id,
        name,
        salary,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank = 2;

Обработка граничных случаев

-- Что если в departamente менее 2 сотрудников?

WITH ranked_employees AS (
    SELECT 
        id,
        name,
        salary,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
        COUNT(*) OVER (PARTITION BY department_id) AS dept_count
    FROM employees
)
SELECT 
    id,
    name,
    salary,
    department_id,
    CASE 
        WHEN dept_count < 2 THEN 'Недостаточно сотрудников'
        ELSE 'OK'
    END AS status
FROM ranked_employees
WHERE rank = 2 OR (rank = 1 AND dept_count < 2);

Альтернатива: Найти с условием на зарплату

-- Найти сотрудника, зарплата которого вторая по величине

SELECT 
    e.id,
    e.name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary = (
    SELECT DISTINCT salary 
    FROM employees e2 
    WHERE e2.department_id = e.department_id 
    ORDER BY salary DESC 
    LIMIT 1 OFFSET 1
);

Итоги

Рекомендуемое решение:

SELECT id, name, salary, department_id
FROM (
    SELECT 
        id, name, salary, department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank = 2;

Почему это лучшее:

• Читаемо и понятно
• Эффективно (O(n log n))
• Стандартный SQL
• Легко расширить (просто измените rank = 2 на rank = k)
• Работает с дубликатами зарплат корректно