Что такое оконные функции в БД?

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

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

Основные характеристики оконных функций

• Сохраняют детализацию строк: Каждая строка исходного запроса остается в выходном результате.
• Работают над окном: Окно определяется относительно текущей строки с помощью секций (PARTITION BY) и порядка (ORDER BY).
• Используют специальный синтаксис: Оператор OVER() определяет, как будет построено окно для каждой функции.

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

SELECT 
    column1,
    column2,
    WINDOW_FUNCTION(column3) OVER (
        PARTITION BY partition_column
        ORDER BY order_column
        ROWS/RANGE BETWEEN frame_start AND frame_end
    ) AS window_result
FROM table_name;

PARTITION BY — разделяет данные на независимые группы (секции). Функция вычисляется только внутри каждой секции. Если PARTITION BY не указан, окном считается весь набор результатов.

ORDER BY — определяет логический порядок строк внутри каждой секции. Это важно для функций, зависящих от порядка (например, RANK, LEAD).

Фрейм окна (Frame) — точное подмножество строк внутри секции относительно текущей строки. Определяется через ROWS или RANGE.

Типы оконных функций

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

Те же SUM, AVG, COUNT, MIN, MAX, но применяемые с OVER().

SELECT 
    EmployeeId,
    DepartmentId,
    Salary,
    AVG(Salary) OVER (PARTITION BY DepartmentId) AS AvgDeptSalary,
    SUM(Salary) OVER (PARTITION BY DepartmentId ORDER BY Salary) AS CumulativeSum
FROM Employees;

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

• ROW_NUMBER() — уникальный последовательный номер строки внутри секции.
• RANK() — ранжирование с учетом одинаковых значений (возможны "пропуски").
• DENSE_RANK() — ранжирование без пропусков при одинаковых значениях.
• NTILE(n) — разделение строк секции на n примерно равных групп.

SELECT 
    ProductName,
    CategoryId,
    Price,
    RANK() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS PriceRankInCategory
FROM Products;

3. Функции доступа к смежным строкам

• LAG(column, offset) — значение из предыдущей строки.
• LEAD(column, offset) — значение из следующей строки.
• FIRST_VALUE(column) — первое значение в окне.
• LAST_VALUE(column) — последнее значение в окне (часто требует указания фрейма).

SELECT 
    OrderDate,
    DailyRevenue,
    LAG(DailyRevenue, 1) OVER (ORDER BY OrderDate) AS PrevDayRevenue,
    LEAD(DailyRevenue, 1) OVER (ORDER BY OrderDate) AS NextDayRevenue
FROM DailySales;

4. Функции распределения

• PERCENT_RANK() — относительный ранг (от 0 до 1).
• CUME_DIST() — кумулятивное распределение.

Примеры практического применения

Анализ скользящих средних и трендов

SELECT 
    Date,
    Sales,
    AVG(Sales) OVER (
        ORDER BY Date 
        ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
    ) AS MovingAverage5Days
FROM SalesData;

Вычисление процента от общего в секции

SELECT 
    Department,
    Employee,
    Salary,
    Salary / SUM(Salary) OVER (PARTITION BY Department) * 100 AS SalaryPercentOfDept
FROM Employees;

Поиск разницы с предыдущим значением

SELECT 
    Month,
    Revenue,
    Revenue - LAG(Revenue, 1) OVER (ORDER BY Month) AS MonthlyGrowth
FROM MonthlyReports;

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

• Упрощение сложных запросов: Заменяют множество подзапросов и JOIN-операций.
• Высокая производительность: Часто оптимизируются лучше, чем коррелирующие подзапросы.
• Улучшенная читаемость: Логика анализа "в одном месте" — внутри OVER().
• Расширенный аналитический потенциал: Включение в стандарт SQL (с 2003 года) открыло возможности для сложного анализа без извлечения данных в инструменты BI.

Ограничения и важные замечания

• Оконные функции нельзя использовать в WHERE, GROUP BY или HAVING — они вычисляются после этих операций. Их можно применять только в SELECT или ORDER BY.
• Порядок выполнения: Оконные функции вычисляются после агрегаций (GROUP BY) и до окончательного ORDER BY.
• Фреймы по умолчанию: При наличии ORDER BY и отсутствии явного фрейма используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, что может привести к неожиданным результатам.

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