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

Оконные функции SQL — это разновидность функций в языке структурированных запросов SQL, которые выполняют вычисления над набором строк, связанных с текущей строкой, при этом не группируя строки в одну выходную запись. В отличие от агрегатных функций (SUM, AVG, COUNT), которые сворачивают множество строк в одну, оконные функции сохраняют все строки исходного набора данных, добавляя к каждой из них результат вычисления. Ключевой особенностью является наличие предложения OVER(), которое определяет «окно» — набор строк, относительно которого производится вычисление. Оконные функции широко применяются для ранжирования, вычисления скользящих средних, накопительных итогов, сравнения значений со смежными строками и других аналитических задач.

Синтаксис и основные компоненты

Общий синтаксис оконной функции выглядит следующим образом:

``sql <имя_функции>(<аргументы>) OVER ( [PARTITION BY <столбец_или_выражение>] [ORDER BY <столбец_или_выражение> [ASC | DESC]] [ROWS | RANGE | GROUPS BETWEEN <начало_рамки> AND <конец_рамки>] ) ``

Предложение PARTITION BY

Предложение PARTITION BY делит результирующий набор данных на логические разделы (партиции). Оконная функция вычисляется независимо для каждой партиции. Если PARTITION BY опущено, всё множество строк считается одной партицией. Например, при вычислении средней зарплаты по отделам PARTITION BY department_id позволит получить среднее значение для каждого отдела отдельно, но при этом каждая строка сотрудника останется в выводе.

Предложение ORDER BY

Предложение ORDER BY внутри OVER() задаёт порядок строк внутри каждой партиции. Этот порядок важен для функций ранжирования (ROW_NUMBER, RANK, DENSE_RANK) и для функций, работающих с последовательностью строк (LAG, LEAD, FIRST_VALUE, LAST_VALUE). Порядок может быть задан по возрастанию (ASC) или убыванию (DESC).

Рамка окна (Frame)

Рамка окна определяет, какие строки внутри партиции включаются в вычисление для каждой текущей строки. Рамка задаётся с помощью ключевых слов ROWS, RANGE или GROUPS и указанием границ. По умолчанию, если указан ORDER BY, рамка включает все строки от начала партиции до текущей строки (и строки, равные текущей по значению ORDER BY, если используется RANGE). Если ORDER BY не указан, рамка по умолчанию включает все строки партиции.

• ROWS: задаёт рамку на основе физического количества строк до и после текущей. Например, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING включает текущую строку, одну строку до и одну после.
• RANGE: задаёт рамку на основе значений столбца, указанного в ORDER BY. Все строки, значения которых находятся в заданном диапазоне относительно текущей строки, включаются в рамку.
• GROUPS: задаёт рамку на основе групп строк с одинаковыми значениями в ORDER BY. Обрабатывает целые группы, а не отдельные строки.

Границы рамки могут быть заданы как:

• UNBOUNDED PRECEDING — от начала партиции.
• n PRECEDING — n строк (или значений) до текущей.
• CURRENT ROW — текущая строка.
• n FOLLOWING — n строк (или значений) после текущей.
• UNBOUNDED FOLLOWING — до конца партиции.

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

Функции ранжирования

Эти функции присваивают каждой строке порядковый номер в рамках партиции.

• ROW_NUMBER() — присваивает уникальный последовательный номер каждой строке, начиная с 1. Номера не повторяются, даже если значения в ORDER BY одинаковы.
• RANK() — присваивает номер, при этом строки с одинаковыми значениями в ORDER BY получают одинаковый ранг. После таких строк образуется пропуск в нумерации. Например, если две строки делят первое место, следующая строка получит ранг 3.
• DENSE_RANK() — аналогичен RANK(), но без пропусков. После двух строк с рангом 1 следующая строка получит ранг 2.
• NTILE(n) — распределяет строки в партиции на n примерно равных групп (корзин) и присваивает каждой строке номер группы (от 1 до n).

Агрегатные оконные функции

Это обычные агрегатные функции (SUM, AVG, COUNT, MIN, MAX), применяемые в оконном контексте. Они вычисляют агрегатное значение по рамке окна для каждой строки. Например, SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) вычислит накопительную сумму зарплат по каждому отделу, упорядоченную по дате найма.

Функции смещения

Эти функции позволяют обращаться к значениям из других строк, не входящих в текущую рамку.

• LAG(column, offset, default) — возвращает значение из строки, расположенной на offset строк выше (раньше) текущей. Если такой строки нет, возвращается default.
• LEAD(column, offset, default) — возвращает значение из строки, расположенной на offset строк ниже (позже) текущей.
• FIRST_VALUE(column) — возвращает значение из первой строки рамки окна.
• LAST_VALUE(column) — возвращает значение из последней строки рамки окна.
• NTH_VALUE(column, n) — возвращает значение из n-й строки рамки окна.

Примеры использования

Пример 1: Ранжирование сотрудников по зарплате в каждом отделе

``sql SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept FROM employees; ``

Этот запрос присвоит каждому сотруднику номер в порядке убывания зарплаты внутри его отдела.

Пример 2: Накопительная сумма продаж по месяцам

``sql SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM sales; ``

Запрос вычисляет накопительную сумму всех продаж, упорядоченных по дате.

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

``sql SELECT employee_id, department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg FROM employees; ``

Пример 4: Разница между текущей и предыдущей продажей

``sql SELECT order_date, amount, LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount, amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS difference FROM sales; ``

Отличия от GROUP BY и подзапросов

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

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

Поддержка в СУБД

Оконные функции впервые были стандартизированы в SQL:2003. На сегодняшний день они поддерживаются большинством современных реляционных СУБД, включая PostgreSQL, Oracle Database, Microsoft SQL Server, MySQL (начиная с версии 8.0), MariaDB (начиная с версии 10.2), SQLite (начиная с версии 3.25.0) и другие. Синтаксис и доступные функции могут незначительно различаться в зависимости от реализации.

Ограничения и особенности

• Оконные функции не могут использоваться в предложении WHERE, так как они вычисляются после фильтрации строк. Для фильтрации по результатам оконной функции необходимо использовать подзапрос или общее табличное выражение (CTE).
• Оконные функции могут применяться только в SELECT и ORDER BY (в некоторых СУБД).
• Использование DISTINCT вместе с оконными функциями может привести к неожиданным результатам, так как DISTINCT применяется после вычисления оконных функций.
• Производительность оконных функций может быть ниже, чем у простых агрегатов, особенно при работе с очень большими наборами данных и сложными рамками окна. Однако правильное использование индексов и оптимизация запросов могут смягчить этот эффект.

Источники

• ISO/IEC 9075-2:2023, Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).
• Документация PostgreSQL 16: «Window Functions».
• Документация Microsoft SQL Server 2022: «OVER Clause (Transact-SQL)».
• Документация MySQL 8.0: «Window Functions».
• Джо Селко, «SQL для профессионалов», 3-е издание, 2005.