Что такое оконные функции в SQL и чем они отличаются от GROUP BY?

Оконные функции в SQL и отличия от GROUP BY

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

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

Оконная функция применяет вычисление к окну (window) строк. Синтаксис:

SELECT
    employee_name,
    salary,
    department,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees
ORDER BY department, rank_in_dept;

Здесь PARTITION BY department создаёт отдельное окно для каждого отдела, а ORDER BY salary DESC сортирует строки внутри окна.

Основные отличия от GROUP BY

1. Сохранение строк

GROUP BY агрегирует данные и удаляет детали:

-- GROUP BY: 10 сотрудников становятся 3 строками (по отделам)
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

Оконная функция сохраняет все 10 строк:

-- Window Function: остаются все 10 сотрудников + средняя зарплата отдела
SELECT 
    employee_name,
    salary,
    department,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

2. Детализация

• GROUP BY — только итоговые значения
• Window Functions — исходные значения + вычисления

3. Использование с другими колонками

GROUP BY требует включить все неагрегированные столбцы в GROUP BY:

-- Так нельзя (ошибка в стандартном SQL):
SELECT employee_name, salary, department, AVG(salary)
FROM employees
GROUP BY department;  -- employee_name и salary не в GROUP BY!

Оконные функции без этого ограничения:

-- Работает идеально:
SELECT 
    employee_name,
    salary,
    department,
    AVG(salary) OVER (PARTITION BY department) as avg_salary
FROM employees;

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

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

ROW_NUMBER()   -- порядковый номер: 1, 2, 3, 4...
RANK()         -- ранг с пропусками при совпадениях: 1, 2, 2, 4...
DENSE_RANK()   -- ранг без пропусков: 1, 2, 2, 3...
NTILE(4)       -- разбиение на квартили

Агрегирующие функции:

SUM()   -- сумма по окну
AVG()   -- среднее по окну
MIN()   -- минимум по окну
MAX()   -- максимум по окну
COUNT() -- количество по окну

Функции смещения:

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

Практические примеры

Пример 1: Сравнение зарплаты с максимумом в отделе

SELECT
    employee_name,
    salary,
    department,
    MAX(salary) OVER (PARTITION BY department) as max_salary,
    MAX(salary) OVER (PARTITION BY department) - salary as diff_from_max
FROM employees
ORDER BY department, salary DESC;

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

SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7days
FROM daily_sales
ORDER BY date;

Пример 3: Накопительная сумма (cumsum)

SELECT
    transaction_date,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_date
    ) as running_total
FROM transactions
ORDER BY transaction_date;

Пример 4: Ранжирование с учётом критериев

SELECT
    product_name,
    sales,
    category,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_category
FROM products
WHERE RANK() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;
-- Примечание: используй CTE для фильтрации по рангу

Когда использовать что?

Итог

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