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

hannadev

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

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

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

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

Например, в таблице продаж по сотрудникам PARTITION BY разобьёт данные по employee_id, ORDER BY отсортирует по месяцам, а функция AVG посчитает среднее за окно. Такие запросы выполняются после WHERE и GROUP BY, но перед финальной сортировкой. Это даёт гибкость в анализе трендов без подзапросов или JOIN.

Вот базовый шаблон:

<функция>(столбец) OVER (
    PARTITION BY столбец_группировки
    ORDER BY столбец_сортировки
    ROWS BETWEEN ... 
)

PARTITION BY: делит на группы, как GROUP BY, но без сжатия строк.
ORDER BY в OVER: определяет порядок внутри партиции, обязательно для ранжирующих функций.
ROWS или RANGE: задаёт рамку окна, например, последние 3 строки или текущую и предыдущую.

Параметр	Описание	Пример
PARTITION BY	Группировка	PARTITION BY department
ORDER BY	Сортировка в окне	ORDER BY sale_date
ROWS	Рамка строк	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Виды оконных функций и их применение

Оконные функции делятся на агрегатные, ранжирующие, смещения и другие. Агрегатные вроде SUM или AVG считают итоги по окну, ранжирующие присваивают номера, а смещения берут данные из соседних строк. Это упрощает задачи вроде расчёта процентов от общего или поиска лидеров в группах.

Возьмём таблицу sport_sales с полями sale_date, department, sales. Агрегатная функция покажет сумму продаж по отделу для каждой строки, ранжирующая — место отдела по продажам на дату. Такой подход заменяет сложные подзапросы одним SELECT.

Агрегатные: SUM(sales) OVER() — общая сумма по всем строкам.
Ранжирующие: ROW_NUMBER() — уникальный номер строки.
Смещения: LAG(sales) — значение из предыдущей строки.

Пример агрегатной:

SELECT 
    department, 
    sales, 
    SUM(sales) OVER (PARTITION BY department) AS dept_total
FROM sport_sales;

Вид	Функции	Задача
Агрегатные	SUM, AVG, COUNT	Скользящие итоги
Ранжирующие	ROW_NUMBER, RANK	Топ-N в группах
Смещения	LAG, LEAD, FIRST_VALUE	Сравнение с соседями

Практические примеры на реальных данных

Рассмотрим таблицу employee_sales: employee_id, month, sales_amount. Задача — скользящее среднее за 3 месяца и ранг лучших продаж. Оконная функция AVG с ROWS BETWEEN 2 PRECEDING AND CURRENT ROW посчитает среднее по трём строкам, RANK покажет место месяца для сотрудника.

Это полезно для отчётов: видно не только итоги, но и динамику. Без окон пришлось бы использовать подзапросы или CTE, что усложняет код. Функция LAG добавит предыдущий месяц для сравнения роста.

Примеры запросов:

Скользящее среднее:

SELECT 
    employee_id, 
    month, 
    sales_amount,
    AVG(sales_amount) OVER (
        PARTITION BY employee_id 
        ORDER BY month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM employee_sales;

Ранг продаж:

SELECT 
    employee_id, 
    month, 
    sales_amount,
    RANK() OVER (
        PARTITION BY employee_id 
        ORDER BY sales_amount DESC
    ) AS sales_rank
FROM employee_sales;

LAG/LEAD: для A/B-тестов или трендов, игнорирует NULL в дефолте.
FILTER в агрегатах: SUM(sales) FILTER (WHERE sales > 100) — только большие сделки.
NTILE(4): делит на квартили для анализа распределения.

Функция	Результат	Полезно для
ROW_NUMBER()	1,2,3…	Уникальная нумерация
DENSE_RANK()	1,1,2…	Ранг без пропусков
LAG(value,1)	Предыдущее	Дельта роста

Расширенные приёмы с FILTER и фреймами

Фреймы ROWS или RANGE уточняют, какие строки входят в расчёт: от начала окна, только текущая или с соседями. FILTER добавляет условие внутри OVER, как WHERE для агрегатов. Это решает нишевые задачи, вроде суммы только положительных продаж.

В таблице orders с order_date, category, amount LEAD(amount,1) OVER() покажет следующий заказ, FIRST_VALUE — первый в группе. Такие функции экономят на JOIN и делают запросы быстрее.

Пример с FILTER:

SELECT 
    sale_date, 
    department, 
    sales,
    SUM(sales) FILTER (WHERE sales > 100) 
    OVER (PARTITION BY department) AS big_sales
FROM sport_sales;

RANGE vs ROWS: RANGE учитывает равные значения по ORDER BY.
ROWS UNBOUNDED PRECEDING: кумулятивная сумма от начала.
Используйте в ORDER BY для сортировки по оконным результатам.

Оконные функции в сложных сценариях анализа

Оконные функции идеальны для дашбордов: кумулятивные суммы, проценты от ряда, топ-N с детализацией. Они работают после GROUP BY, добавляя ранги к сгруппированным данным. В T-SQL и PostgreSQL поддержка полная, с нюансами в фреймах.

Остаётся место для оптимизации: избегайте больших окон на миллионах строк, комбинируйте с индексами. Стоит поэкспериментировать с NTILE для сегментации или CUME_DIST для распределений — это расширит аналитику без дополнительных таблиц.