Агрегатные оконные функции: SUM, AVG, COUNT

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

Агрегатные функции в оконном контексте

Когда SUM(), AVG(), COUNT(), MIN() или MAX() используются с конструкцией OVER(), они вычисляют агрегат не для всей группы сразу, а для каждой строки с учетом определенного "окна" строк. Это позволяет добавить к детальным данным сводные показатели без потери информации.

Базовый пример: Добавим к каждому заказу общую сумму всех заказов клиента:

SELECT 
    customer_id,
    order_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Каждая строка сохранена, но теперь можно сразу увидеть, какую долю составляет конкретный заказ от общей суммы клиента: amount / customer_total.

Накопительные суммы (Running Total)

Накопительная сумма — это итог, который увеличивается от строки к строке. Для её расчета используется ORDER BY внутри окна: функция суммирует все строки от начала партиции до текущей строки включительно.

Пример: Рассчитаем накопительную выручку по дням:

SELECT 
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY sale_date) AS cumulative_revenue
FROM daily_sales
ORDER BY sale_date;

Накопительная сумма позволяет отслеживать прогресс к целевому показателю и строить графики роста метрик.

Накопительная сумма внутри групп:

SELECT 
    category,
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY category 
        ORDER BY sale_date
    ) AS category_cumulative
FROM sales
ORDER BY category, sale_date;

Здесь накопительная сумма рассчитывается отдельно для каждой категории товаров, начинаясь заново при смене категории.

Скользящие окна (Moving Windows)

Скользящее окно — это фиксированный диапазон строк относительно текущей, используемый для расчета метрик "в динамике". Конструкция ROWS BETWEEN явно задает границы окна.

Синтаксис:

FUNCTION() OVER (
    ORDER BY column
    ROWS BETWEEN N PRECEDING AND M FOLLOWING
)

• N PRECEDING — N строк перед текущей
• CURRENT ROW — текущая строка
• M FOLLOWING — M строк после текущей
• UNBOUNDED PRECEDING — все строки от начала до текущей (для накопительных итогов)

Пример: Скользящее среднее за 3 дня (текущий день + 2 предыдущих):

SELECT 
    sale_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM daily_sales
ORDER BY sale_date;

Для первой строки окно содержит только её саму, для второй — две строки, начиная с третьей — полное окно из трёх строк. Скользящее среднее сглаживает колебания и помогает выявлять тренды.

Скользящая сумма за последние 7 дней:

SELECT 
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS sum_last_7_days
FROM daily_sales;

Это полезно для расчета метрик типа "продажи за последнюю неделю" для каждого дня.

Сравнение с агрегатом группы

Частая задача — показать, как каждая строка соотносится со средним или суммой по группе:

SELECT 
    category,
    product_name,
    sales,
    AVG(sales) OVER (PARTITION BY category) AS category_avg,
    sales - AVG(sales) OVER (PARTITION BY category) AS diff_from_avg
FROM products;

Этот запрос добавляет к каждому товару среднее значение продаж по его категории и отклонение от этого среднего, позволяя сразу увидеть лидеров и аутсайдеров.

Ключевые выводы

• Агрегатные оконные функции сохраняют детализацию данных, добавляя к каждой строке сводные показатели.
• Накопительные суммы рассчитываются с помощью ORDER BY без явного указания границ окна (по умолчанию UNBOUNDED PRECEDING AND CURRENT ROW).
• ROWS BETWEEN явно определяет границы скользящего окна для расчета скользящих средних, сумм и других метрик в динамике.
• Комбинация PARTITION BY и ORDER BY позволяет строить сложные аналитические расчеты внутри групп с учетом временной последовательности.