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

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

История и происхождение

Концепция оконных функций была впервые предложена в стандарте SQL:1999 и получила дальнейшее развитие в SQL:2003, SQL:2008 и SQL:2016. Идея возникла из необходимости выполнения аналитических расчётов (например, скользящих средних, ранжирования, накопительных итогов) без написания сложных подзапросов или использования курсоров. Коммерческие СУБД начали внедрять оконные функции постепенно: Oracle Database реализовала их в версии 8i (1999 год), Microsoft SQL Server — в 2005 году (версия 9.0), PostgreSQL — в версии 8.4 (2009 год). MySQL долгое время не поддерживал оконные функции, включив их только в версии 8.0 (2018 год). Современные системы управления базами данных, такие как ClickHouse, Snowflake, BigQuery, также поддерживают оконные функции.

Ключевые компоненты оконных функций

Оконная спецификация (OVER)

Ключевым синтаксическим элементом является предложение OVER, которое определяет окно. Оно может включать три необязательных компонента:

• PARTITION BY — разбиение набора строк на разделы (аналогично GROUP BY, но без схлопывания). Функция вычисляется отдельно для каждого раздела.
• ORDER BY — упорядочивание строк внутри каждого раздела. Порядок важен для функций ранжирования и накопительных операций.
• ROWS / RANGE / GROUPS — определение границ окна (фрейма) относительно текущей строки. Например:
• ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING — окно из двух строк до, двух после и текущей строки.
• ROWS UNBOUNDED PRECEDING — все строки от начала раздела до текущей.
• RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW — все строки в пределах одного дня до текущей (для временных данных).

Если фрейм не указан явно, используется значение по умолчанию: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

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

Агрегатные функции (SUM, AVG, COUNT, MIN, MAX, STDDEV и другие) могут применяться как оконные. Они вычисляют агрегат по определённому окну. Пример: ``sql SELECT id, date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY date) AS running_total FROM orders; `` Этот запрос вычисляет накопительный итог суммы заказов для каждого клиента, упорядоченных по дате.

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

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

Пример: ``sql SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept FROM employees; ``

Аналитические функции смещения

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

Пример использования LAG для расчёта разницы: ``sql SELECT date, price, price - LAG(price, 1) OVER (ORDER BY date) AS price_change FROM stock_prices; ``

Функции статистического распределения

• PERCENT_RANK() — относительная позиция строки в разбиении (от 0 до 1).
• CUME_DIST() — куммулятивное распределение (доля строк, меньших или равных текущей).
• PERCENTILE_CONT(n) — непрерывный перцентиль (интерполяция).
• PERCENTILE_DISC(n) — дискретный перцентиль (ближайшее значение).

Поведение оконных функций

• Все оконные функции вычисляются после предложений WHERE, GROUP BY и HAVING, но перед ORDER BY (в большинстве СУБД). Это означает, что к оконным функциям нельзя обращаться в этих предложениях.
• Оконные функции могут быть использованы только в SELECT или ORDER BY (зависит от реализации).
• Если в запросе отсутствует PARTITION BY, окном считается весь результирующий набор строк.
• NULL-значения в ORDER BY обрабатываются по стандарту SQL: либо как наибольшие (по умолчанию), либо с помощью NULLS FIRST / NULLS LAST.

Примеры применения

Скользящее среднее (moving average)

``sql SELECT date, value, AVG(value) OVER (ORDER BY date ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS moving_avg_6 FROM sensor_data; ``

Топ-N в каждой группе

Найти двух самых высокооплачиваемых сотрудников в каждом отделе: ``sql SELECT employee_id, department, salary FROM ( SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r FROM employees ) sub WHERE r <= 2; ``

Расчёт времени между событиями

``sql SELECT user_id, event_time, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event, EXTRACT(EPOCH FROM (event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time))) AS seconds_since_prev FROM events; ``

Накопительный итог с условием (проверка лимита)

Вычисление суммы заказов с остановкой при достижении лимита в 1000: ``sql SELECT id, amount, SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum, CASE WHEN SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) <= 1000 THEN 'inside' ELSE 'exceeded' END AS status FROM orders; ``

Особенности реализации в различных СУБД

• PostgreSQL — практически полная поддержка стандарта SQL, включая фреймы RANGE и GROUPS, а также фильтры для оконных функций (FILTER (WHERE ...)).
• Oracle Database — ввела оконные функции раньше других, поддерживает такие уникальные функции, как RATIO_TO_REPORT() (отношение к сумме раздела).
• Microsoft SQL Server — поддерживает все основные оконные функции, фреймы ROWS и RANGE, начиная с версии 2012.
• MySQL / MariaDB — оконные функции появились в MySQL 8.0; MariaDB поддерживает их с версии 10.2.
• ClickHouse — поддержка оконных функций (называется window functions) с версии 21.2, но реализация может отличаться — например, некоторые функции могут быть ограничены (LEAD/LAG без ORDER BY).

Производительность и ограничения

• Оконные функции могут быть дорогостоящими при больших объёмах данных, поскольку требуют однократной или многократной сортировки данных. Использование PARTITION BY и ORDER BY на проиндексированных колонках улучшает производительность.
• Для каждой оконной функции в запросе может потребоваться своя сортировка, если их оконные спецификации различаются.
• Вложенные оконные функции не поддерживаются (например, SUM(SUM(...))), хотя можно использовать подзапросы.

Интересные факты

• Термин «оконные функции» происходит от концепции «окна» — ограниченного набора строк вокруг текущей строки, аналогично скользящему окну в обработке сигналов.
• В стандарте SQL:2016 были введены функции ROW и RANGE с поддержкой интервалов для временных данных, что упростило анализ временных рядов.
• Некоторые базы данных, такие как SQLite, начали поддерживать оконные функции относительно недавно (SQLite 3.25, 2018 г.).

Источники

• ISO/IEC 9075-2:2016, Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).
• PostgreSQL Documentation: «Window Functions», Chapter 4.2.8.
• MySQL 8.0 Reference Manual: «Window Functions», Section 12.21.
• Microsoft SQL Server Documentation: «OVER Clause (Transact-SQL)».
• Oracle Database SQL Language Reference: «Analytic Functions».
• C. J. Date, «An Introduction to Database Systems», 8th Edition, 2004.
• Joe Celko, «SQL for Smarties: Advanced SQL Programming», 5th Edition, 2015.