ИТ.03 - 41 - Практикум: оконные функции

Введение

В лекциях 39 и 40 мы разобрали оконные функции MySQL 8.0: синтаксис OVER(), секции PARTITION BY и ORDER BY, ранжирующие функции (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()), агрегатные функции в роли оконных (бегущие суммы и средние), функции смещения (LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()), рамки окна (ROWS/RANGE), функции распределения (CUME_DIST(), PERCENT_RANK(), NTH_VALUE()) и именованные окна WINDOW.

Этот практикум закрепит полученные знания. Вы не просто повторите примеры из лекций, а решите серию аналитических задач на расширенной версии учебной базы данных: добавите новый отдел и сотрудников, построите рейтинги, накопительные итоги, скользящие средние, топ-N по отделам и выполните дедупликацию данных.

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

SELECT VERSION();

Цель и формат

• Работаем локально в MySQL (клиент mysql, MySQL Workbench или любой другой инструмент из лекции 17).
• Все команды фиксируем в файле practice_log.sql, чтобы преподаватель мог воспроизвести ход работы (подробнее — в разделе «Что сдавать»).
• На выполнение отводится 90 минут. Задания выполняются последовательно: результат каждого следующего опирается на предыдущее.
• Если задание не получается — используйте подсказку, а затем сверьте своё решение с образцом. Не переходите к следующему заданию, пока текущий запрос не вернул ожидаемый результат.

Подготовка

1. Создайте базу данных company и выполните скрипт создания таблиц departments и employees из лекции 39 (для удобства он приведён ниже полностью).

2. Включите вывод в удобном формате. Для клиента mysql:

mysql -u root -p company

-- В консоли клиента
SELECT * FROM employees\G

-- Создание базы
CREATE DATABASE IF NOT EXISTS company;
USE company;

-- Создание таблицы departments
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

-- Создание таблицы employees
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) DEFAULT 0.00,
    hire_date DATE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Вставка тестовых данных (как в лекции 39)
INSERT INTO departments (name) VALUES
('Разработка'),
('Маркетинг'),
('Финансы');

INSERT INTO employees (name, salary, hire_date, department_id) VALUES
('Иван Петров',     85000.00, '2019-03-15', 1),
('Мария Сидорова',  92000.00, '2018-11-01', 1),
('Алексей Иванов',  78000.00, '2020-06-20', 2),
('Ольга Кузнецова', 95000.00, '2017-02-10', 3),
('Дмитрий Смирнов', 88000.00, '2021-09-05', 1),
('Елена Волкова',   76000.00, '2022-01-17', 2),
('Сергей Козлов',   92000.00, '2019-08-30', 3),
('Анна Морозова',   85000.00, '2023-04-12', 1),
('Павел Лебедев',   99000.00, '2016-05-23', 3),
('Наталья Орлова',  81000.00, '2021-12-01', 2);

После выполнения скрипта в базе company 3 отдела и 10 сотрудников — стартовое состояние перед практикумом.

Задание 1. Расширяем базу: отдел продаж

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

Условие:

1. Добавьте отдел с названием 'Продажи' (он получит id = 4).
2. Добавьте шесть сотрудников по данным из таблицы:

Решение:

INSERT INTO departments (name) VALUES ('Продажи');

INSERT INTO employees (name, salary, hire_date, department_id) VALUES
('Виктор Ерёмин',   83000.00, '2020-09-14', 4),
('Татьяна Белова',  87000.00, '2018-04-02', 4),
('Григорий Фомин',  79000.00, '2022-07-25', 4),
('Инна Соколова',   94000.00, '2017-11-30', 4),
('Кирилл Данилов',  86000.00, '2023-01-09', 4),
('Вера Гусева',     82000.00, '2021-05-18', 4);

Контроль: запрос SELECT COUNT(*) FROM employees; должен вернуть 16, а SELECT COUNT(*) FROM departments; — 4. Итоговые данные:

Все последующие задания выполняются на этой расширенной базе.

Задание 2. Разминка: OVER () без партиций

Условие:

Выведите список сотрудников, добавив к каждой строке:

• среднюю зарплату по всей компании (столбец company_avg);
• долю зарплаты сотрудника в общем фонде оплаты труда в процентах (столбец percent_of_total), округлённую до двух знаков.

Отсортируйте результат по убыванию зарплаты.

Подсказка:

Используйте пустое окно OVER () — оно охватывает всю выборку (лекция 39, пример 3 и 10). Сумму всех зарплат даёт SUM(salary) OVER ().

Решение:

SELECT
    name,
    salary,
    department_id,
    ROUND(AVG(salary) OVER (), 2) AS company_avg,
    ROUND(salary / SUM(salary) OVER () * 100, 2) AS percent_of_total
FROM employees
ORDER BY salary DESC;

Результат:

Контроль: общий фонд оплаты труда — 1 382 000.00 (сумма всех зарплат), средняя — 86 375.00. Сумма столбца percent_of_total должна быть равна 100.00.

Задание 3. PARTITION BY: показатели по отделам

Условие:

Для каждого сотрудника выведите:

• максимальную зарплату его отдела (dept_max);
• среднюю зарплату его отдела (dept_avg), округлённую до двух знаков;
• отклонение его зарплаты от средней по отделу (diff), округлённое до двух знаков.

Отсортируйте результат по номеру отдела и убыванию зарплаты.

Подсказка:

Разбейте окно на партиции секцией PARTITION BY department_id (лекция 39, примеры 4 и 9).

Решение:

SELECT
    name,
    salary,
    department_id,
    MAX(salary) OVER (PARTITION BY department_id) AS dept_max,
    ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS dept_avg,
    ROUND(salary - AVG(salary) OVER (PARTITION BY department_id), 2) AS diff
FROM employees
ORDER BY department_id, salary DESC;

Результат:

Контроль: средние по отделам 1–3 совпадают с лекцией 39 (87500.00, 78333.33, 95333.33) — расширение базы их не изменило. Средняя по отделу продаж — 85166.67.

Задание 4. Ранжирующие функции

Условие:

1. Присвойте каждому сотруднику порядковый номер внутри его отдела при сортировке по убыванию зарплаты (ROW_NUMBER()).
2. Для той же сортировки добавьте столбцы RANK() и DENSE_RANK() по всей компании (без партиций) и сравните поведение трёх функций на строках с одинаковой зарплатой.
3. Разбейте всех сотрудников на 4 квартиля по зарплате функцией NTILE(4).

Подсказка:

ROW_NUMBER() — уникальные номера; RANK() пропускает номера после дублей (1, 2, 3, 3, 5); DENSE_RANK() не пропускает (1, 2, 3, 3, 4) — лекция 39, примеры 5–7.

Решение (пункты 1–2):

SELECT
    name,
    salary,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY department_id, row_num;

Результат:

Обратите внимание на пару «Иван Петров / Анна Морозова» (зарплата 85000.00 в отделе 1): row_num различает их (3 и 4), а RANK() и DENSE_RANK() присваивают одинаковый ранг 9 и 8 соответственно.

Решение (пункт 3):

SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees
ORDER BY salary DESC;

Результат:

16 строк разделены на 4 квартиля ровно по 4 строки в каждом.

Задание 5. Накопительные итоги

Условие:

1. Постройте накопительный (бегущий) итог фонда оплаты труда в порядке даты приёма на работу (running_total).
2. Для каждой строки покажите также накопительную сумму внутри своего отдела (dept_running_total), отсортировав строки по возрастанию стажа (по hire_date).

Подсказка:

SUM(...) OVER (ORDER BY hire_date) даёт накопительный итог благодаря рамке по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (лекция 39, пример 8; лекция 40, раздел «Рамка по умолчанию»). Для накопительной суммы по отделу добавьте PARTITION BY department_id.

Решение:

SELECT
    name,
    salary,
    department_id,
    hire_date,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS dept_running_total
FROM employees
ORDER BY hire_date;

Результат:

Контроль: последняя строка running_total равна полному фонду оплаты труда — 1 382 000.00. Последние значения dept_running_total для каждого отдела равны его суммарному фонду: отдел 1 — 350000.00, отдел 2 — 235000.00, отдел 3 — 286000.00, отдел 4 — 511000.00.

Задание 6. LAG() и LEAD(): сравнение с соседними строками

Условие:

1. Выведите сотрудников в порядке даты приёма. Для каждого покажите зарплату предыдущего принятого сотрудника (prev_salary) и процент изменения зарплаты относительно него (growth_pct), округлённый до двух знаков.
2. Тем же запросом добавьте зарплату следующего принятого сотрудника (next_salary) с помощью LEAD().

Подсказка:

LAG(столбец) — значение из предыдущей строки окна, LEAD(столбец) — из следующей. Для первой строки LAG() вернёт NULL (лекция 39, пример 11; лекция 40, пример 9).

Решение:

SELECT
    name,
    salary,
    hire_date,
    LAG(salary) OVER (ORDER BY hire_date) AS prev_salary,
    ROUND(
        (salary - LAG(salary) OVER (ORDER BY hire_date))
        / LAG(salary) OVER (ORDER BY hire_date) * 100, 2
    ) AS growth_pct,
    LEAD(salary) OVER (ORDER BY hire_date) AS next_salary
FROM employees
ORDER BY hire_date;

Результат:

Контроль: prev_salary у первого сотрудника и next_salary у последнего равны NULL. Чтобы убрать NULL из отчёта, оберните вычисление в COALESCE() или отфильтруйте строки через CTE.

Задание 7. Рамки окна: ROWS и RANGE

Условие:

1. Для каждого сотрудника (в порядке даты приёма) вычислите скользящее среднее зарплаты по трём строкам: две предыдущие плюс текущая (rolling_avg_3), округлённое до двух знаков.
2. Выведите также полную сумму зарплат отдела (dept_total), несмотря на наличие ORDER BY в окне, — используйте рамку ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
3. Самостоятельно сравните ROWS и RANGE на бегущей сумме при сортировке по убыванию зарплаты и объясните разницу на строках с одинаковой зарплатой (для проверки возьмите пример 2 из лекции 40).

Подсказка:

Скользящее окно — ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (лекция 40, пример 1). Полная партиция при наличии ORDER BY — рамка UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING (лекция 40, раздел «Сводка границ рамки»).

Решение (пункты 1–2):

SELECT
    name,
    salary,
    department_id,
    hire_date,
    ROUND(AVG(salary) OVER (ORDER BY hire_date
                            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS rolling_avg_3,
    SUM(salary) OVER (PARTITION BY department_id
                      ORDER BY hire_date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS dept_total
FROM employees
ORDER BY hire_date;

Результат:

Контроль: rolling_avg_3 первых двух строк считается по одной и двум строкам (окно «упирается» в начало партиции): 99000.00 и 97000.00. Значения dept_total одинаковы для всех строк отдела и равны суммам из задания 5: 350000.00, 235000.00, 286000.00, 511000.00.

Решение (пункт 3) — сравнение ROWS и RANGE:

SELECT
    name,
    salary,
    SUM(salary) OVER (ORDER BY salary DESC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_running,
    SUM(salary) OVER (ORDER BY salary DESC
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_running
FROM employees
ORDER BY salary DESC;

Фрагмент результата на строках с одинаковыми зарплатами (92000.00 и 85000.00):

(Полная таблица — 16 строк; суммы считаются накопительно по убыванию зарплаты.) Разница: ROWS обрабатывает строки с одинаковой зарплатой по очереди, поэтому у первой из пары значение меньше; RANGE включает в рамку все строки с тем же значением ключа сортировки, и обе строки пары получают одинаковое значение.

Задание 8. FIRST_VALUE, LAST_VALUE, NTH_VALUE

Условие:

Для каждого отдела выведите:

• зарплату самого раннего принятого сотрудника отдела (first_salary, FIRST_VALUE() по hire_date);
• зарплату самого позднего принятого сотрудника отдела (last_salary, LAST_VALUE() с рамкой UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING);
• вторую по величине зарплату отдела (second_highest, NTH_VALUE(salary, 2)).

Отсортируйте результат по номеру отдела и возрастанию даты приёма.

Подсказка:

Без явной рамки LAST_VALUE() вернёт значение текущей строки (лекция 39, предупреждение в примере 12). Для NTH_VALUE(salary, 2) тоже нужна полная рамка, иначе для первой строки результатом будет NULL (лекция 40, пример 5).

Решение:

SELECT
    name,
    salary,
    department_id,
    hire_date,
    FIRST_VALUE(salary) OVER w AS first_salary,
    LAST_VALUE(salary) OVER w AS last_salary,
    NTH_VALUE(salary, 2) OVER w AS second_highest
FROM employees
WINDOW w AS (PARTITION BY department_id
             ORDER BY hire_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY department_id, hire_date;

Результат:

SELECT
    name,
    salary,
    department_id,
    NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY salary DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest
FROM employees
ORDER BY department_id, salary DESC;

Задание 9. Топ-N сотрудников в каждом отделе

Условие:

Выведите двух самых высокооплачиваемых сотрудников каждого отдела, включив название отдела. Используйте CTE с ROW_NUMBER().

Подсказка:

Оконную функцию нельзя использовать в WHERE — сначала пронумеруйте строки в CTE, затем отфильтруйте WHERE rn <= 2 (лекция 39, раздел «Ограничения»; лекция 40, пример 6). Название отдела получите через JOIN с таблицей departments.

Решение:

WITH ranked AS (
    SELECT
        e.name,
        e.salary,
        e.department_id,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rn
    FROM employees e
)
SELECT
    r.name,
    r.salary,
    d.name AS department
FROM ranked r
JOIN departments d ON d.id = r.department_id
WHERE r.rn <= 2
ORDER BY r.department_id, r.salary DESC;

Результат:

Дополнительно: замените ROW_NUMBER() на RANK() или DENSE_RANK() и объясните, как изменится результат, если в одном отделе появятся сотрудники с одинаковой зарплатой, претендующие на «второе место».

Задание 10. Дедупликация строк

Условие:

Представьте, что при импорте данных в базу попали дубликаты: два сотрудника с именем «Дмитрий Смирнов» в отделе 1 (записи с hire_date 2024-02-01 и 2024-03-01, зарплаты 87000.00 и 86000.00).

1. Вставьте эти две записи.
2. Найдите все дубликаты по паре (name, department_id), пронумеровав строки внутри групп через ROW_NUMBER() и оставив только rn > 1.
3. Удалите лишние (продублированные) строки из таблицы employees.
4. Проверьте, что в базе снова 16 сотрудников.

Подсказка:

Паттерн дедупликации: ROW_NUMBER() OVER (PARTITION BY name, department_id ORDER BY id) внутри CTE, затем фильтр WHERE rn > 1 (лекция 40, пример 7). Для удаления соедините таблицу с CTE.

Решение:

-- Шаг 1: имитация ошибочного импорта
INSERT INTO employees (name, salary, hire_date, department_id) VALUES
('Дмитрий Смирнов', 87000.00, '2024-02-01', 1),
('Дмитрий Смирнов', 86000.00, '2024-03-01', 1);

-- Шаг 2: поиск дубликатов
WITH numbered AS (
    SELECT
        id,
        name,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY name, department_id ORDER BY id) AS rn
    FROM employees
)
SELECT id, name, department_id, rn
FROM numbered
WHERE rn > 1;

-- Шаг 3: удаление дубликатов
WITH numbered AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY name, department_id ORDER BY id) AS rn
    FROM employees
)
DELETE e
FROM employees e
JOIN numbered n ON n.id = e.id
WHERE n.rn > 1;

-- Шаг 4: контроль
SELECT COUNT(*) AS employees_count FROM employees;

Ожидаемый результат шага 2:

(id могут отличаться в зависимости от порядка вставки; главное — rn > 1.) После шага 3 employees_count снова равен 16.

Задание 11. Функции распределения: CUME_DIST и PERCENT_RANK

Условие:

1. Для каждого сотрудника вычислите CUME_DIST() и PERCENT_RANK() по зарплате (сортировка по возрастанию), округлив до трёх знаков.
2. Выделите топ-25% самых высокооплачиваемых сотрудников: оставьте строки, у которых cume_dist >= 0.75.

Подсказка:

CUME_DIST() — доля строк со значением ≤ текущего; PERCENT_RANK() — относительная позиция строки между первой и последней (лекция 40, примеры 3–4). Для фильтрации по значению оконной функции используйте CTE.

Решение (пункт 1):

SELECT
    name,
    salary,
    ROUND(CUME_DIST() OVER (ORDER BY salary), 3) AS cume_dist,
    ROUND(PERCENT_RANK() OVER (ORDER BY salary), 3) AS percent_rank
FROM employees
ORDER BY salary;

Результат:

У сотрудников с одинаковой зарплатой (85000.00 и 92000.00) cume_dist одинаков — 0.500 и 0.813 соответственно, потому что доля считается по всем строкам с таким же значением.

Решение (пункт 2):

WITH dist AS (
    SELECT
        name,
        salary,
        CUME_DIST() OVER (ORDER BY salary) AS cume_dist
    FROM employees
)
SELECT name, salary
FROM dist
WHERE cume_dist >= 0.75
ORDER BY salary DESC;

Результат:

В топ-25% попали 5 человек: из-за одинаковых зарплат 92000.00 в группу вошли обе строки, что как раз демонстрирует отличие CUME_DIST() от жёсткого деления на корзины через NTILE().

Задание 12. Комбинированные запросы

Условие:

1. Постройте рейтинг сотрудников внутри отделов по убыванию зарплаты с названием отдела через JOIN и RANK().
2. Классифицируйте каждого сотрудника относительно средней зарплаты его отдела через CASE: 'выше среднего', 'ниже среднего' или 'равна средней'.
3. Вычислите медианную зарплату компании через ROW_NUMBER() и COUNT(*) OVER ().

Подсказка:

Оконные функции вычисляются после JOIN — соединение и оконные функции отлично сочетаются (лекция 40, примеры 10–11). Медиана: усреднить значения строк с номерами FLOOR((n+1)/2) и CEIL((n+1)/2) (лекция 40, пример 8).

Решение (пункт 1):

SELECT
    d.name AS department,
    e.name,
    e.salary,
    RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS dept_rank
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY d.id, dept_rank;

Результат:

Решение (пункт 2):

SELECT
    name,
    salary,
    department_id,
    CASE
        WHEN salary > AVG(salary) OVER (PARTITION BY department_id) THEN 'выше среднего'
        WHEN salary < AVG(salary) OVER (PARTITION BY department_id) THEN 'ниже среднего'
        ELSE 'равна средней'
    END AS vs_dept_avg
FROM employees
ORDER BY department_id, salary DESC;

Результат:

Решение (пункт 3):

WITH ranked AS (
    SELECT
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS rn,
        COUNT(*) OVER () AS total
    FROM employees
)
SELECT ROUND(AVG(salary), 2) AS median_salary
FROM ranked
WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));

Результат:

Проверка вручную: упорядоченный ряд из 16 зарплат — 76000, 78000, 79000, 81000, 82000, 83000, 85000, 85000, 86000, 87000, 88000, 92000, 92000, 94000, 95000, 99000. Восьмая строка — 85000.00, девятая — 86000.00, их среднее равно 85500.00. Средняя зарплата по компании (86375.00) выше медианы — как и в лекции 40, «дорогие» зарплаты тянут среднее вверх, а медиана устойчива к выбросам.

Что сдавать

Сдать нужно папку, названную ФИО студента, например: Иванов Иван Иванович. Внутри папки должны лежать файлы:

1. practice_log.sql — последовательность команд и комментариев, позволяющая воспроизвести весь практикум от подготовки базы до последнего задания.
2. (опционально) дамп итоговой базы: mysqldump -u root -p company > company_dump.sql.

Пример структуры папки:

Иванов Иван Иванович/
├── practice_log.sql
└── company_dump.sql

Критерии оценки

• Задания 1–4 (базовые конструкции OVER(), PARTITION BY, ранжирование).
• Задания 5–8 (накопительные итоги, LAG/LEAD, рамки окна, FIRST_VALUE/LAST_VALUE/NTH_VALUE).
• Задания 9–12 (топ-N через CTE, дедупликация, функции распределения, комбинированные запросы).

Заключение

Выполнив практикум, вы закрепили все ключевые приёмы работы с оконными функциями MySQL 8.0: построение окон над всей выборкой и отдельными партициями, ранжирование строк, накопительные и скользящие агрегаты, сравнение с соседними строками, явное управление рамкой окна, фильтрацию результатов оконных вычислений через CTE и дедупликацию данных. Эти навыки напрямую применяются в аналитических отчётах, построении дашбордов и подготовке данных — от «топ-N по группам» до расчёта медиан и процентилей.