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

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

Ключевые особенности и преимущества:

• Сохранение детализации: В отличие от GROUP BY, оконные функции возвращают столько же строк, сколько было на входе, добавляя к каждой строке вычисленное значение. Это позволяет видеть агрегированные данные вместе с детализированными.
• Гибкость определения окна: Окно данных определяется с помощью предложения OVER(), которое может включать:
  • PARTITION BY: Делит строки на группы (партиции), по которым будут производиться вычисления.
  • ORDER BY: Определяет порядок строк внутри каждой партиции.
  • ROWS/RANGE: Указывает конкретный диапазон строк относительно текущей строки в окне (например, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).
• Мощный инструмент для аналитики: Идеально подходят для ранжирования, расчета скользящих средних, кумулятивных сумм, сравнения значений с соседними строками или с агрегатами по группе.

Категории оконных функций:

1. Ранжирующие: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().
2. Агрегатные: SUM(), AVG(), COUNT(), MIN(), MAX() (используемые с OVER()).
3. Функции смещения: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

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

-- 1. Ранжирование сотрудников по зарплате в каждом отделе
SELECT
    employee_id,
    department,
    salary,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank_in_department
FROM
    employees;

-- 2. Расчет средней зарплаты по отделу для каждого сотрудника
SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER(PARTITION BY department) as avg_dept_salary
FROM
    employees;

-- 3. Расчет кумулятивной суммы продаж по датам
SELECT
    sale_date,
    amount,
    SUM(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sum
FROM
    sales;

-- 4. Получение предыдущего значения (LAG)
SELECT
    order_id,
    order_date,
    total_amount,
    LAG(total_amount, 1, 0) OVER(ORDER BY order_date) as previous_order_amount
FROM
    orders;

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