Оконные функции в SQL: определение, синтаксис и примеры применения

Что такое оконная функция SQL

Оконная функция работает с набором строк — окном — и добавляет вычисленное значение к каждой строке, не сворачивая их в одну. Ключевое отличие от агрегации: SUM(salary) GROUP BY department вернёт одну строку на отдел, а SUM(salary) OVER (PARTITION BY department) — все строки таблицы плюс итог по отделу в каждой.

Как формируется окно

Окно может охватывать всю таблицу, партицию строк или динамический диапазон. Без PARTITION BY пустой OVER() обрабатывает всю таблицу как одну партицию. Добавьте PARTITION BY — строки разобьются на непересекающиеся подмножества по значению столбца. ROWS/RANGE BETWEEN задаёт скользящий диапазон внутри партиции. Оконные функции выполняются на шаге 5 из 6: после WHERE, GROUP BY и HAVING, но до ORDER BY.

Ключевое отличие от GROUP BY

GROUP BY сворачивает таблицу: одна строка на группу, детали теряются. Оконная функция сохраняет все исходные строки и добавляет столбец с агрегатом. SELECT department, AVG(salary) FROM employees GROUP BY department вернёт 3 строки; SELECT department, salary, AVG(salary) OVER (PARTITION BY department) FROM employees — все строки сотрудников со средней зарплатой по отделу рядом с каждой.

Синтаксис оконных функций

Общий шаблон:

функция(поле) OVER ([PARTITION BY …] [ORDER BY …] [ROWS|RANGE …])

Все три параметра внутри OVER необязательны и задаются по потребности. Пустой OVER() допустим: функция применяется ко всей таблице как к одному окну.

Кто хочет освоить работу с SQL системно — в рамках нацпроекта «Кадры» доступна программа «Специалист по аналитике и базам данных в информационных системах» (72 часа, бесплатно). Актуальный список программ — в каталоге ТГУ ДПО.

Конструкция OVER и её параметры

• PARTITION BY col — делит результат на партиции; без него вся таблица — одна партиция.
• ORDER BY col — задаёт порядок строк внутри партиции; обязателен для ранжирующих функций.
• ROWS BETWEEN start AND end — скользящее окно; ROWS BETWEEN 2 PRECEDING AND CURRENT ROW берёт текущую строку и две предыдущих.
• Значения фрейма оконной функции: UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING.
• WINDOW — псевдоним окна для повторного использования в одном запросе.

Классы оконных функций SQL

Стандарт SQL:2003 (ISO/IEC 9075) закрепляет четыре класса оконных функций: агрегатные, ранжирующие, функции смещения и аналитические. Каждый класс решает свой тип задач — от подсчёта долей и скользящих средних до ранжирования строк и сравнения с соседними значениями.

Агрегатные: SUM, AVG, COUNT, MIN, MAX

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

• SUM() — накопительная сумма или доля от итога по партиции;
• AVG() — скользящее среднее при сочетании с ROWS BETWEEN N PRECEDING AND CURRENT ROW;
• COUNT() — количество строк в каждой партиции рядом с каждой строкой;
• MIN() / MAX() — минимум или максимум по строкам окна.

Ранжирующие: ROW_NUMBER, RANK, DENSE_RANK, NTILE

ORDER BY в OVER обязателен для всего класса. Поведение при равных значениях:

• ROW_NUMBER() — уникальный порядковый номер, дубли не влияют на нумерацию;
• RANK() — одинаковый ранг при равных значениях, следующая позиция пропускается (gap);
• DENSE_RANK() — одинаковый ранг при равных, без пропуска (dense — «плотный»);
• NTILE(n) — делит партицию на n равных групп, возвращает номер группы.

NULL-значения получают одинаковый ранг.

Функции смещения: LAG, LEAD, FIRST_VALUE, LAST_VALUE

• LAG(col, n, default) — значение на n строк назад; по умолчанию n=1, default=NULL; используется для расчёта изменения цен.
• LEAD(col, n, default) — значение на n строк вперёд, зеркало LAG.
• FIRST_VALUE() — первое значение фрейма; требует ORDER BY.
• LAST_VALUE() — ⚠️ без явного фрейма возвращает значение текущей строки, так как дефолтный диапазон строк — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Решение: явно задать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

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

Рассмотрим два практических запроса к таблице employee_sales(employee_id, month, sales_amount). Каждый пример разберём по параметрам — чтобы видеть, как изменение каждого аргумента OVER влияет на результат.

Пример 1 — скользящее среднее продаж за 3 месяца

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

SELECT employee_id, month,

AVG(sales_amount) OVER (

PARTITION BY employee_id

ORDER BY month

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

) AS moving_avg

FROM employee_sales;

PARTITION BY employee_id — окно сбрасывается для каждого сотрудника; ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — берёт последние три строки для каждого значения.

Пример 2 — накопительная сумма нарастающим итогом

Задача: нарастающий итог продаж по хронологии для всего набора данных.

SELECT month, sales_amount,

SUM(sales_amount) OVER (ORDER BY month) AS running_total

FROM employee_sales;

С добавлением PARTITION BY employee_id сумма обнуляется в каждой группе — итог считается независимо для каждого сотрудника. В отличие от SUM + GROUP BY, строки не сворачиваются и детализация сохраняется.

Поддержка оконных функций в разных СУБД

Оконные функции SQL включены в стандарт SQL:2003 и поддерживаются всеми основными системами управления базами данных. Разница — в версии, с которой появилась поддержка, и в отдельных особенностях синтаксиса.

Часто задаваемые вопросы

Что такое оконная функция в SQL?

Оконная функция SQL вычисляет результат для каждой строки на основе набора связанных строк (окна), не уменьшая количество строк в результате. Пример: SUM(salary) OVER (PARTITION BY department) добавляет суммарную зарплату по отделу к каждой строке, не сворачивая данные. Обязательный элемент — конструкция OVER.

Чем оконные функции отличаются от GROUP BY?

GROUP BY сжимает результат до одной строки на группу и теряет детализацию. Оконная функция сохраняет все строки и добавляет столбец с вычисленным значением. Оба подхода выполняют агрегацию, но оконные функции не нарушают структуру результирующей таблицы.

В чём разница между ROW_NUMBER, RANK и DENSE_RANK?

Разница проявляется при равных значениях. ROW_NUMBER присваивает уникальные номера: 1, 2, 3. RANK даёт одинаковый ранг дублям и пропускает позицию: 1, 1, 3. DENSE_RANK тоже даёт одинаковый ранг, но без пропуска: 1, 1, 2. На данных (5, 5, 3) это хорошо видно.

Как работает PARTITION BY в оконной функции?

PARTITION BY делит таблицу на непересекающиеся подмножества по значениям указанных столбцов. Оконная функция выполняется отдельно внутри каждой партиции. Без PARTITION BY вся таблица обрабатывается как одна партиция — функция применяется ко всем строкам сразу.

Почему LAST_VALUE возвращает неожиданный результат?

Дефолтный фрейм — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, поэтому «последнее» значение всегда равно текущей строке. Чтобы получить последнее значение партиции, нужно явно добавить фрейм: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Как рассчитать скользящее среднее?

AVG(col) OVER (

PARTITION BY id

ORDER BY date

ROWS BETWEEN N PRECEDING AND CURRENT ROW

)

Параметр N PRECEDING задаёт ширину окна: 2 PRECEDING берёт текущую и две предыдущих строки. Чем меньше N — тем быстрее среднее реагирует на изменения данных.

Где тренироваться решать задачи на оконные функции SQL?

Онлайн-тренажёры с интерактивными задачами — хорошая точка входа. Для системного изучения подойдёт программа «Специалист по аналитике и базам данных в информационных системах» (72 часа, бесплатно по нацпроекту «Кадры»): разбираются реальные аналитические кейсы с SQL, включая оконные функции.

Поддерживает ли MySQL оконные функции?

Да, начиная с версии 8.0 (2018). Синтаксис стандартный: OVER, PARTITION BY, ORDER BY, ROWS/RANGE. В версиях 5.x и 7.x оконные функции недоступны — запросы с OVER вернут ошибку синтаксиса.

Что такое фрейм оконной функции?

Фрейм — поднабор строк внутри партиции, который динамически меняется от строки к строке. Задаётся через ROWS BETWEEN start AND end. Используется для скользящих вычислений и для корректной работы LAST_VALUE, которая без явного фрейма возвращает только текущую строку.

Что такое накопительная сумма и как её вычислить?

Накопительная сумма — нарастающий итог значений в заданном порядке сортировки. Реализуется через SUM(col) OVER (ORDER BY col2). С добавлением PARTITION BY сумма обнуляется в каждой группе — итог считается независимо внутри каждой партиции.