ИТ.03 - 40 - Оконные функции в MySQL: продвинутый уровень

Введение

В лекции 39 мы познакомились с оконными функциями: разобрали синтаксис OVER(), секции PARTITION BY и ORDER BY, ранжирующие функции (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()), агрегатные функции в роли оконных, функции смещения LAG(), LEAD(), FIRST_VALUE() и именованные окна WINDOW. Тогда мы вскользь упомянули рамку окна (frame) — механизм, который управляет тем, какие именно строки попадают в вычисление для текущей строки.

Именно рамки, а также продвинутые статистические функции и типовые аналитические задачи — тема этой лекции. Без понимания рамок легко получить «неожиданные» результаты: например, LAST_VALUE() возвращает значение текущей строки, а бегущая сумма внезапно становится накопительной.

В этой лекции мы рассмотрим:

• рамку окна: границы ROWS и RANGE, рамку по умолчанию;
• скользящие (rolling) вычисления: суммы и средние по нескольким строкам;
• функции распределения: CUME_DIST(), PERCENT_RANK(), NTH_VALUE();
• практические задачи: топ-N по группам, дедупликация строк, медиана, динамика показателей;
• сочетание оконных функций с JOIN, CASE и CTE;
• логический порядок выполнения запроса с оконными функциями;
• вопросы производительности и использования индексов.

Все примеры используют ту же учебную базу данных, что и лекция 39:

SQL-дамп таблиц и тестовых данных можно взять из лекции 39.

Рамка окна: управляем набором строк

Рамка окна (window frame) — это подмножество строк партиции, относительно которого вычисляется оконная функция для каждой текущей строки. Пока рамка не задана явно, действует её значение по умолчанию, и именно оно объясняет «странное» поведение агрегатов с ORDER BY.

Рамка по умолчанию

Если в окне есть ORDER BY, то по умолчанию используется рамка

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

то есть «от начала партиции до текущей строки включительно». Именно поэтому SUM(salary) OVER (ORDER BY hire_date) даёт накопительную сумму, а не итог по всей партиции. Если же ORDER BY в окне нет, рамкой по умолчанию является вся партиция.

ROWS BETWEEN ... AND ...

Секция ROWS BETWEEN ... AND ... задаёт рамку по количеству строк: границы отсчитываются физически — столько-то строк до и после текущей. Типичная задача — скользящее (rolling) окно фиксированной ширины.

Пример 1: Скользящая сумма и среднее за три строки

Вычислим для каждого сотрудника сумму и среднюю зарплату по трём строкам: двум предыдущим (по дате приёма) и текущей.

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

Результат:

Обратите внимание на первые две строки: рамка «упирается» в начало партиции, поэтому у Павла в окне всего одна строка (сумма 99000.00, среднее 99000.00), у Ольги — две (194000.00 и 97000.00). Начиная с третьей строки окно полностью заполнено тремя строками. Это нормальное поведение скользящих окон, и его нужно учитывать при интерпретации результатов.

Скользящие средние — стандартный инструмент сглаживания временных рядов: продажи по месяцам, нагрузка на серверы, посещаемость сайта. Вместо скачков «месяц к месяцу» они показывают устойчивый тренд.

RANGE BETWEEN ... AND ...

Секция RANGE задаёт рамку по значениям ключа сортировки. Границы отсчитываются не по номерам строк, а по значениям столбца из ORDER BY: в рамку попадают все строки, значение которых укладывается в диапазон. Главное следствие — при одинаковых значениях ключа сортировки рамка RANGE захватывает сразу все такие строки, даже если их больше, чем позволяет «ширина» окна.

Пример 2: Сравнение ROWS и RANGE при одинаковых зарплатах

В нашей таблице есть пары сотрудников с одинаковой зарплатой (92000.00 и 85000.00). Сравним бегущую сумму при сортировке по убыванию зарплаты.

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): ROWS учитывает только одну строку с 92000.00 → 286000.00, а RANGE включает обе строки с этим значением → 378000.00.
• Сергей Козлов (вторая 92000.00): в ROWS к предыдущей сумме добавляется ещё одна 92000.00 → 378000.00, а RANGE даёт тот же результат, что и у Марии, — 378000.00.
• Аналогично ведут себя строки с зарплатой 85000.00: у Ивана Петрова rows_running = 551000.00, а range_running сразу включает обе строки → 636000.00.

То есть RANGE гарантирует, что строки с одинаковым значением ключа сортировки всегда получают одинаковое значение оконной функции.

Сводка границ рамки

Полный перечень границ, которые можно указывать внутри ROWS BETWEEN ... AND ... / RANGE BETWEEN ... AND ...:

Также допустимы сокращённые формы: ROWS UNBOUNDED PRECEDING (эквивалентно ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) и ROWS N PRECEDING.

SUM(salary) OVER (ORDER BY salary DESC
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Функции распределения

Помимо ранжирующих функций в MySQL 8 доступны функции, возвращающие относительную позицию строки в окне.

CUME_DIST() — кумулятивное распределение

CUME_DIST() возвращает долю строк, значение которых меньше или равно значению текущей строки (в порядке сортировки окна):

CUME_DIST() = (число строк со значением <= текущего) / (всего строк в партиции)

Значение всегда находится в диапазоне от 0 до 1 включительно. Для строки с максимальным значением CUME_DIST() равна 1.

Пример 3: Кумулятивное распределение зарплат

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

Результат:

У двух сотрудников с зарплатой 85000.00 доля одинаковая — 0.500, потому что 5 строк из 10 имеют зарплату не выше 85000.00. Максимальной зарплате соответствует cume_dist = 1.000.

PERCENT_RANK() — процентный ранг

PERCENT_RANK() показывает относительную позицию строки между первой и последней строкой окна:

PERCENT_RANK() = (RANK() - 1) / (всего строк - 1)

Первая строка всегда получает 0, последняя — 1. В отличие от CUME_DIST(), этот показатель не учитывает «хвост» одинаковых значений.

Пример 4: Сравнение CUME_DIST() и PERCENT_RANK()

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;

Результат:

Разница между функциями видна на примере зарплаты 88000.00: cume_dist = 0.600 («60% сотрудников получают не больше»), а percent_rank = 0.556 («строка прошла 55.6% пути от минимальной зарплаты до максимальной»).

NTH_VALUE() — значение n-й строки окна

NTH_VALUE(столбец, n) возвращает значение столбца из n-й строки рамки. Здесь рамка играет решающую роль: по умолчанию она заканчивается на текущей строке, поэтому для «настоящего» n-го значения всей партиции нужно расширять рамку до UNBOUNDED FOLLOWING.

Пример 5: Вторая по величине зарплата в каждом отделе

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

Результат:

Для каждой строки вернулась вторая по величине зарплата её отдела (для отдела 2 — 78000.00, для отдела 3 — 95000.00), причём одинаково для всех строк партиции благодаря полной рамке UNBOUNDED PRECEDING ... UNBOUNDED FOLLOWING.

Практические аналитические задачи

Объединим изученные приёмы для решения задач, которые регулярно встречаются в реальных отчётах.

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

Классическая задача «выдать трёх лучших по каждой группе». Решение опирается на правило из лекции 39: оконную функцию нельзя использовать в WHERE, поэтому сначала нумеруем строки в CTE, а затем фильтруем по номеру.

Пример 6: Два самых высокооплачиваемых сотрудника каждого отдела

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

Результат:

Если вместо «ровно двух» нужны «все, кто входит в двойку лидеров» (с учётом одинаковых зарплат), замените ROW_NUMBER() на RANK() или DENSE_RANK() — тогда при равенстве значений в топ попадут все связанные строки.

Дедупликация строк

ROW_NUMBER() идеально подходит для поиска дубликатов: пронумеруем строки внутри групп, определяемых ключом уникальности, и оставим только первую.

Пример 7: Поиск дубликатов по имени и отделу

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;

В нашей базе дубликатов нет, поэтому запрос вернёт пустой результат. Однако тот же паттерн легко превращается в удаление лишних строк (MySQL 8.0 поддерживает DELETE с CTE):

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;

Медианная зарплата

Медиана — значение, которое делит упорядоченную выборку пополам. В отличие от среднего арифметического, она устойчива к выбросам. Алгоритм на оконных функциях:

1. нумеруем строки по возрастанию зарплаты (ROW_NUMBER());
2. узнаём общее количество строк (COUNT(*) OVER ());
3. берём строки, номера которых приходятся на середину: FLOOR((n + 1) / 2) и CEIL((n + 1) / 2);
4. усредняем их значения.

Для чётного количества строк (у нас 10) медиана — среднее пятой и шестой строк.

Пример 8: Медианная зарплата сотрудников

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));

Результат:

Проверим вручную: упорядоченный ряд зарплат — 76000, 78000, 81000, 85000, 85000, 88000, 92000, 92000, 95000, 99000. Пятая строка — 85000.00, шестая — 88000.00, их среднее равно 86500.00. Среднее арифметическое всех зарплат (87100.00) отличается — медиана менее чувствительна к «дорогому» Павлу Лебедеву с зарплатой 99000.00.

Динамика: прирост относительно предыдущей строки

LAG() позволяет сравнивать строку не только с предыдущей по значению, но и выражать разницу в процентах — стандартный приём для отчётов о росте продаж, зарплат, посещаемости.

Пример 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
FROM employees
ORDER BY hire_date;

Результат:

Для первой строки предыдущей зарплаты нет — growth_pct равен NULL. Чтобы не портить отчёт, замените NULL через COALESCE() или отфильтруйте такие строки в CTE.

Комбинируем с другими конструкциями

Оконные функции и JOIN

Оконные функции отлично сочетаются с соединениями: сначала строится результат JOIN, затем над ним выполняются оконные вычисления. Удобно использовать алиасы таблиц, чтобы не было неоднозначности столбцов.

Пример 10: Рейтинг сотрудников по зарплате с названием отдела

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;

Результат:

CASE и условная логика

Оконные функции можно использовать внутри CASE, чтобы классифицировать строки относительно агрегатов.

Пример 11: Сравнение зарплаты сотрудника со средней по отделу

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;

Результат:

Ольга Кузнецова получает 95000.00 при средней по финансовому отделу 95333.33 — формально это чуть ниже среднего, что и отражает классификация.

CTE как мост к фильтрации

Как мы уже видели в примерах 6–8, CTE — основной способ «отфильтровать» по значению оконной функции: внутри CTE оконные функции вычисляются, а во внешнем запросе к их результатам можно обращаться в WHERE наравне с обычными столбцами. Это работает и для агрегирования по результатам оконных функций:

WITH avg_by_dept AS (
    SELECT
        department_id,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
)
SELECT department_id, MAX(dept_avg) AS max_avg
FROM avg_by_dept
GROUP BY department_id;

Порядок выполнения запроса с оконными функциями

Логический порядок обработки запроса (не путать с физическим планом!) выглядит так:

Из этой таблицы следуют два важных вывода:

1. Оконные функции вычисляются после WHERE, GROUP BY и HAVING — поэтому ссылаться на их результат в этих секциях нельзя.
2. Оконные функции вычисляются до ORDER BY, но сам ORDER BY может использовать их результат — в конце концов, столбец уже вычислен.

Производительность и индексы

Оконные функции требуют от сервера сортировки и временного хранения данных, поэтому на больших таблицах стоит помнить о следующих правилах:

1. Индексы под секции окна. Столбцы из PARTITION BY и ORDER BY желательно покрывать индексом. Например, для OVER (PARTITION BY department_id ORDER BY salary DESC) полезен составной индекс (department_id, salary) — сервер сможет читать строки уже отсортированными по партициям и зарплате.

2. Фильтруйте как можно раньше. Оконные функции считаются по всем строкам, прошедшим WHERE. Если отчёт требует данные только за один год — добавьте условие в WHERE до оконных вычислений, а не обрезайте результат после.

3. Минимизируйте объём промежуточных данных. Тяжёлый JOIN увеличивает количество строк, по которым считается окно. Если возможно, агрегируйте или фильтруйте стороны соединения заранее.

4. Избегайте лишних оконных функций. Каждая функция с собственным окном — это отдельная сортировка/временная структура. Если несколько функций используют одинаковое окно — применяйте WINDOW, как в лекции 39.

5. Следите за сортировкой. Если внешний ORDER BY совпадает с порядком окна, сервер может переиспользовать уже отсортированные данные и не выполнять лишнюю сортировку.

Практические рекомендации

1. Всегда задавайте рамку явно, когда результат зависит от того, какие строки попадают в вычисление: полная партиция (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), скользящее окно (ROWS BETWEEN N PRECEDING AND CURRENT ROW) или накопительный итог (рамка по умолчанию).

2. Выбирайте ROWS или RANGE осознанно. ROWS — точное количество строк, RANGE — все строки с равным значением ключа сортировки. Для финансовых расчётов и статистики чаще нужен предсказуемый ROWS.

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

4. Топ-N и дедупликацию стройте через CTE. Номер строки внутри CTE, фильтр WHERE rn <= N снаружи — универсальный и читаемый шаблон.

5. Проверяйте крайние случаи. Первая строка окна для LAG(), вторая строка для NTH_VALUE(expr, 2) с рамкой по умолчанию, пустые рамки в начале скользящих окон — везде ожидайте NULL и обрабатывайте его через COALESCE() или фильтрацию.

6. Используйте CUME_DIST() и PERCENT_RANK() для процентилей, когда нужно делить данные на доли, а не на корзины фиксированного размера.

7. Следите за объёмом данных. Чем меньше строк дошло до этапа оконных функций, тем быстрее запрос: фильтруйте в WHERE, проектируйте индексы под PARTITION BY/ORDER BY.