Написать SQL-запрос с использованием оконных функций

SQL-запросы с оконными функциями

1. Зарплата и средняя зарплата по отделу

SELECT
    e.name,
    d.name AS department,
    e.salary,
    ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS avg_salary_by_dept
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
ORDER BY e.department_id, e.salary DESC;

Объяснение:

• PARTITION BY e.department_id — разделяет данные по отделам
• AVG(e.salary) OVER (...) — считает среднюю зарплату для каждого отдела
• ROUND(..., 2) — округляет до 2 знаков

---

2. Топ-3 сотрудников по зарплате в каждом отделе

WITH ranked_employees AS (
    SELECT
        e.id,
        e.name,
        d.name AS department,
        e.salary,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank_row,
        RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank_dense
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
)
SELECT
    name,
    department,
    salary,
    rank_row
FROM ranked_employees
WHERE rank_row <= 3
ORDER BY department, rank_row;

Объяснение:

• ROW_NUMBER() — последовательный номер (1, 2, 3, 4, ...)
• PARTITION BY e.department_id — отдельно для каждого отдела
• ORDER BY e.salary DESC — сортирует по зарплате убывающая
• WHERE rank_row <= 3 — берёт только топ-3

---

3. Изменение зарплаты по сравнению с предыдущим месяцем

WITH salary_history AS (
    SELECT
        e.id,
        e.name,
        e.salary,
        e.hire_date,
        DATE_TRUNC('month', e.hire_date) AS month,
        LAG(e.salary) OVER (PARTITION BY e.id ORDER BY DATE_TRUNC('month', e.hire_date)) AS prev_salary
    FROM employees e
)
SELECT
    name,
    month,
    salary,
    prev_salary,
    CASE
        WHEN prev_salary IS NULL THEN 'N/A'
        ELSE ROUND(((salary - prev_salary) / prev_salary * 100)::NUMERIC, 2) || '%'
    END AS salary_change_percent,
    CASE
        WHEN prev_salary IS NULL THEN 'Starting salary'
        WHEN salary > prev_salary THEN 'Increase'
        WHEN salary < prev_salary THEN 'Decrease'
        ELSE 'No change'
    END AS change_type
FROM salary_history
ORDER BY name, month;

Объяснение:

• LAG(e.salary, 1) OVER (PARTITION BY e.id ORDER BY month) — берёт зарплату предыдущего месяца
• PARTITION BY e.id — отдельно для каждого сотрудника
• Рассчитывает процент изменения
• CASE — определяет тип изменения

---

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

ROW_NUMBER

• Последовательная нумерация — каждой строке присваивается уникальный номер (1, 2, 3, 4, 5)
• Если два сотрудника имеют одинаковую зарплату → они получат разные номера
• Пример:

Зарплата | ROW_NUMBER
100,000  |    1
90,000   |    2
90,000   |    3  ← разный номер, хотя одинаковая зарплата
80,000   |    4

RANK

• Ранг с пропусками — одинаковые значения получают одинаковый ранг, следующий ранг пропускает номера
• (1, 2, 2, 4) — если два человека на 2-м месте, следующий будет на 4-м
• Пример:

Зарплата | RANK
100,000  |  1
90,000   |  2
90,000   |  2  ← одинаковый ранг
80,000   |  4  ← пропуск 3

DENSE_RANK

• Ранг без пропусков — одинаковые значения получают одинаковый ранг, следующий ранг идёт без пропусков
• (1, 2, 2, 3) — непрерывная нумерация
• Пример:

Зарплата | DENSE_RANK
100,000  |     1
90,000   |     2
90,000   |     2  ← одинаковый ранг
80,000   |     3  ← без пропуска

Когда использовать

---

Бонус: Оконные функции

ROW_NUMBER() — Нумерация строк

ROW_NUMBER() OVER (PARTITION BY column ORDER BY column)

RANK() — Ранг с пропусками

RANK() OVER (ORDER BY salary DESC)

DENSE_RANK() — Ранг без пропусков

DENSE_RANK() OVER (ORDER BY salary DESC)

AVG() / SUM() / COUNT() — Агрегирующие функции

AVG(salary) OVER (PARTITION BY department_id)
SUM(salary) OVER (PARTITION BY department_id)
COUNT(*) OVER (PARTITION BY department_id)

LAG() / LEAD() — Доступ к соседним строкам

LAG(salary, 1) OVER (ORDER BY hire_date)   -- предыдущее значение
LEAD(salary, 1) OVER (ORDER BY hire_date)  -- следующее значение

FIRST_VALUE() / LAST_VALUE() — Первое и последнее значение

FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date)
LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY hire_date)

---

Практический пример: Полный анализ зарплат

WITH employee_analysis AS (
    SELECT
        e.id,
        e.name,
        d.name AS department,
        e.salary,
        e.hire_date,
        -- Средняя зарплата по отделу
        ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS avg_dept_salary,
        -- Топ сотрудников
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank_in_dept,
        -- Процент от средней
        ROUND(((e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id)) 
            / AVG(e.salary) OVER (PARTITION BY e.department_id) * 100)::NUMERIC, 2) AS pct_from_avg
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
)
SELECT
    name,
    department,
    salary,
    avg_dept_salary,
    pct_from_avg || '%' AS difference_from_avg,
    rank_in_dept,
    CASE
        WHEN rank_in_dept = 1 THEN 'Top earner'
        WHEN rank_in_dept <= 3 THEN 'High earner'
        ELSE 'Average'
    END AS salary_category
FROM employee_analysis
ORDER BY department, rank_in_dept;

Это полный набор примеров оконных функций для анализа данных о зарплатах.