В чём разница между агрегирующей и оконной функцией?

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

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

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

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

Характеристики:

• Уменьшает количество строк в результате
• Требует GROUP BY для работы с подмножествами данных
• Возвращает одно значение на группу
• Распространённые функции: SUM(), COUNT(), AVG(), MIN(), MAX()

-- Агрегирующая функция: подсчёт заказов по пользователю
SELECT 
    user_id,
    COUNT(*) as total_orders,
    SUM(amount) as total_spent,
    AVG(amount) as avg_order_value
FROM orders
GROUP BY user_id;

-- Результат: по одной строке на каждого пользователя

Оконные функции

Оконная функция применяет функцию к набору строк (окну) без уменьшения количества строк в результате. Каждая исходная строка остаётся в результате с добавленным вычисленным значением.

Характеристики:

• Сохраняет все исходные строки
• Работает с логическим "окном" строк
• Определяется с помощью OVER() клаузулы
• Включает: агрегирующие функции + ранжирование + смещение

-- Оконная функция: бегущая сумма на пользователя
SELECT 
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id 
        ORDER BY order_date
    ) as running_total
FROM orders
ORDER BY user_id, order_date;

-- Результат: все исходные строки + новая колонка с бегущей суммой

Прямое сравнение

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

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

SUM(...) OVER (...)
COUNT(...) OVER (...)
AVG(...) OVER (...)

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

ROW_NUMBER() OVER (ORDER BY salary DESC)
RANK() OVER (ORDER BY salary DESC)
DENSE_RANK() OVER (ORDER BY salary DESC)
NTILE(4) OVER (ORDER BY salary DESC)  -- квартили

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

LAG(amount, 1) OVER (ORDER BY date)      -- предыдущее значение
LEAD(amount, 1) OVER (ORDER BY date)     -- следующее значение
FIRST_VALUE(...) OVER (ORDER BY date)    -- первое значение в окне
LAST_VALUE(...) OVER (ORDER BY date)     -- последнее значение в окне

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

-- Задача: для каждого заказа показать:
-- 1. Детали заказа (агрегирующие НЕ нужны)
-- 2. Ранг заказа по сумме для этого пользователя
-- 3. Накопленную сумму заказов этого пользователя
-- 4. Процент от общей суммы пользователя

SELECT 
    user_id,
    order_date,
    amount,
    RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as order_rank,
    SUM(amount) OVER (
        PARTITION BY user_id 
        ORDER BY order_date
    ) as cumulative_sum,
    ROUND(
        amount * 100.0 / SUM(amount) OVER (PARTITION BY user_id),
        2
    ) as pct_of_user_total
FROM orders
ORDER BY user_id, order_date;

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

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

• Нужна сводка данных
• Требуется снизить объём результата
• Создание отчётов по группам

Оконные функции:

• Нужны детали + вычисленные значения
• Ранжирование внутри групп
• Временные ряды (бегущие суммы, скользящие среднее)
• Сравнение текущей строки с соседними
• Анализ тренды по порядку