Что такое оконная функция?

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

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

Ключевые характеристики оконных функций

Основные особенности, отличающие оконные функции от обычных агрегатных:

• Сохранение детализации — каждая строка исходного запроса сохраняется в результате
• Отсутствие группировки — не требуется использовать GROUP BY
• Использование предложения OVER() — обязательный синтаксический элемент
• Работа с контекстом строки — вычисления учитывают положение текущей записи

Базовый синтаксис

SELECT 
    столбцы,
    ФУНКЦИЯ() OVER (
        [PARTITION BY столбец1, столбец2...]
        [ORDER BY столбец [ASC|DESC]]
        [ROWS/RANGE рамка_окна]
    ) AS имя_столбца
FROM таблица;

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

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

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

SELECT 
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) as avg_department_salary,
    SUM(salary) OVER (PARTITION BY department_id) as total_department_salary
FROM employees;

2. Ранжирующие функции

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

SELECT 
    product_name,
    category,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) as price_rank,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;

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

• LAG() — значение из предыдущей строки
• LEAD() — значение из следующей строки
• FIRST_VALUE() — первое значение в окне
• LAST_VALUE() — последнее значение в окне

SELECT 
    order_date,
    daily_revenue,
    LAG(daily_revenue, 1) OVER (ORDER BY order_date) as prev_day_revenue,
    LEAD(daily_revenue, 1) OVER (ORDER BY order_date) as next_day_revenue
FROM daily_sales;

4. Аналитические функции

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

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

Пример 1: Скользящее среднее

SELECT 
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3days
FROM daily_sales;

Пример 2: Вычисление накопленных итогов

SELECT 
    month,
    revenue,
    SUM(revenue) OVER (
        ORDER BY month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_revenue
FROM monthly_financials;

Пример 3: Сравнение со средним по группе

SELECT 
    employee_name,
    department,
    salary,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

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

• Производительность — часто эффективнее коррелированных подзапросов
• Читаемость — более понятный и компактный код
• Гибкость — широкие возможности анализа данных
• Экспрессивность — сложная логика в одном запросе

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

• SQL Server — поддерживает с версии 2005
• PostgreSQL — полная поддержка с версии 8.4
• MySQL — поддержка с версии 8.0
• Oracle — исторически наиболее развитая поддержка

Ограничения и нюансы

1. Нельзя использовать в WHERE, GROUP BY, HAVING (только в SELECT и ORDER BY)
2. Порядок выполнения — после WHERE, GROUP BY, HAVING, но до ORDER BY
3. Производительность может снижаться на больших объемах данных без proper indexing

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