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

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

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

Основная концепция: "окно" над данными

Можно представить, что для каждой строки результата запроса определяется "окно" (window) — это подмножество строк относительно текущей, над которым и производятся вычисления. Окно определяется с помощью предложения OVER().

-- Базовая структура
SELECT
    column1,
    column2,
    WINDOW_FUNCTION(column3) OVER (
        [PARTITION BY partition_column]
        [ORDER BY order_column]
        [ROWS/RANGE frame_definition]
    ) AS new_column
FROM table_name;

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

1. Сами функции:

    *   **Агрегатные**: `SUM()`, `AVG()`, `COUNT()`, `MIN()`, `MAX()`. Работают как обычные, но не сворачивают строки.
    *   **Ранжирующие**: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE(n)`. Присваивают порядковые номера.
    *   **Функции смещения**: `LAG()` (значение из предыдущей строки), `LEAD()` (значение из следующей строки), `FIRST_VALUE()`, `LAST_VALUE()`.
    *   **Статистические**: `PERCENT_RANK()`, `CUME_DIST()` (распределение).

1. PARTITION BY (секционирование):

    Разбивает набор данных на логические группы (окна/партиции), внутри которых функция вычисляется независимо. Аналог `GROUP BY`, но без свертки.

```sql
-- Средняя зарплата по отделам для каждого сотрудника
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary_in_dept
FROM employees;
```

3. ORDER BY внутри OVER():

    Определяет порядок строк внутри окна. Критично для ранжирующих функций и функций смещения, а также для определения "рамки" (frame).

```sql
-- Нумерация сотрудников внутри отдела по убыванию зарплаты
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank_in_dept
FROM employees;
```

4. Фрейм (рамка вычислений):

    Уточняет, какие именно строки внутри окна (партиции) участвуют в расчете для текущей строки. Задается через `ROWS` или `RANGE`.
    *   `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` — нарастающий итог от начала партиции до текущей строки (по умолчанию при наличии `ORDER BY`).
    *   `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` — среднее между предыдущей, текущей и следующей строкой.

```sql
-- Накопительная сумма зарплаты в отделе
SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_salary
FROM employees;
```

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

Преимущества:

• Сохраняют детализацию — не нужно делать сложные JOIN с агрегированными подзапросами.
• Более читаемый и производительный код — часто эффективнее множественных подзапросов.
• Решают специфичные задачи:

    *   Расчет скользящих средних и накопительных итогов.
    *   Поиск разрывов и островов в последовательностях.
    *   Присвоение рангов (топ-N запросы).
    *   Сравнение строк со смещением (месяц к месяцу, год к году).

Пример: сравнение с предыдущей записью

-- Ежемесячная выручка и ее изменение относительно предыдущего месяца
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS revenue_change
FROM monthly_sales;

Сравнение с GROUP BY

Заключение

Оконные функции — это мощный инструмент аналитической обработки данных прямо на уровне СУБД. Они элегантно решают целый класс задач, которые ранее требовали громоздких самообъединений или курсоров, значительно повышая читаемость и часто — производительность запросов. Освоение оконных функций — обязательный шаг для разработчика, работающего со сложной аналитикой и отчетностью в SQL. Их поддержка есть во всех современных БД (PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite).