Что такое оконные функции

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

Оконная функция — это функция, которая для каждой строки набора данных рассчитывает значение, опираясь не только на текущую строку, но и на другие строки в логическом «окне». Окно задаётся после функции через ключевое слово OVER, внутри которого описывается, какие строки участвуют в вычислении для каждой текущей строки.

Обычно окно в OVER (...) задаётся тремя элементами: PARTITION BY разбивает строки на независимые группы (разделы), ORDER BY определяет порядок строк внутри раздела, а рамка окна (ROWS или RANGE) уточняет, какие именно строки относительно текущей включаются в расчёт (например, от начала раздела до текущей строки или только несколько соседних строк).

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

Основные виды оконных функций, которые поддерживаются большинством СУБД:

• Агрегатные оконные функции: SUM(...) (сумма по окну), AVG(...) (среднее по окну), MIN(...) и MAX(...) (минимум и максимум в окне), COUNT(...) (количество строк в окне).
• Ранжирующие функции: ROW_NUMBER() (уникальный порядковый номер строки в разделе), RANK() (ранг с пропусками при равенствах), DENSE_RANK() (ранг без пропусков), NTILE(N) (разбиение строк раздела на N примерно равных групп, например для квантилей).
• Навигационные функции: LAG(expr [, offset, default]) (значение выражения из предыдущей строки или на заданное число строк назад), LEAD(expr [, offset, default]) (значение из следующей строки или на заданное число строк вперёд), FIRST_VALUE(expr) (первое значение в текущей рамке окна), LAST_VALUE(expr) (последнее значение в текущей рамке), NTH_VALUE(expr, n) (n‑е значение в текущей рамке окна).
• Статистические и процентильные оконные функции (зависят от СУБД): CUME_DIST() (кумулятивное распределение — доля строк с меньшим или равным значением), PERCENT_RANK() (относительный ранг строки от 0 до 1), PERCENTILE_CONT(p) (непрерывный процентиль p в интервале 0–1), PERCENTILE_DISC(p) (дискретный процентиль p).

Типичные задачи, решаемые оконными функциями: расчёт накопительных и скользящих итогов (например, сумма продаж по датам для каждого клиента или региона), ранжирование и выбор топ-N элементов в пределах группы (например, топ-3 товара по выручке в каждом регионе), сравнение текущей строки с предыдущей или следующей (анализ изменения показателей во времени), а также вычисление долей и процентилей (доля строки в общей сумме по группе, позиция значения в распределении).

Ниже приведён пример использования оконной функции: посчитаем для каждой продажи общую сумму продаж по региону и накопительный итог продаж по датам внутри региона.

В этом запросе SUM(amount) OVER (PARTITION BY region) возвращает для каждой строки сумму всех продаж в соответствующем регионе, не объединяя строки, а SUM(amount) OVER (PARTITION BY region ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) считает накопительный итог: для каждой строки это сумма всех предыдущих и текущей продаж в рамках того же региона, что удобно для построения аналитических отчётов и графиков динамики.