Расчет накопительных сумм с использованием оконных функций: SUM(), AVG()

Давайте представим: вы следите за доходами вашей компании, продажами в интернет-магазине или даже просто анализируете свои траты за год. Вам нужно не только видеть доходы или расходы за каждый месяц, но и понимать, как они накапливаются от месяца к месяцу.

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

Основы использования оконных функций для накопительных сумм

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

Синтаксис SUM() с оконной функцией

Вот базовый шаблон для использования накопительной суммы:

SELECT
    column_name,
    SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS cumulative_sum
FROM 
    table_name;

Здесь:

• SUM(column_name) — суммирует значения.
• OVER() — задает окно для расчета.
• PARTITION BY — делит данные на группы (опционально).
• ORDER BY — задает порядок строк внутри окна.

Пример: накопительный доход по месяцам

Представим таблицу ваших доходов:

Мы хотим увидеть доход за каждый месяц и накопительный итог. Попробуем написать SQL-запрос:

SELECT
    месяц,
    доход,
    SUM(доход) OVER (ORDER BY месяц) AS накопительный_доход
FROM 
    доходы;

Результат:

Что происходит:

1. ORDER BY месяц внутри OVER() говорит PostgreSQL, что строки нужно учитывать в хронологическом порядке.
2. Для каждой строки сумма рассчитывается с учетом всех предыдущих строк (и текущей).

Подумайте внимательно над тем что тут происходит. Для первой строки SUM() считает сумму только 1-й строки, для второй - сумму двух строк, для третей - сумму трех строк. Именно поэтому важен порядок сортировки месяцев!

Пример: накопительный доход по регионам

Если у вас была бы таблица продаж по регионам, частями она могла бы выглядеть так:

Теперь мы хотим считать накопительный доход отдельно для каждого региона:

SELECT
    регион,
    месяц,
    доход,
    SUM(доход) OVER (PARTITION BY регион ORDER BY месяц) AS накопительный_доход
FROM 
    продажи;

Результат будет таким:

Теперь каждый регион анализируется отдельно (PARTITION BY регион), но внутри региона строки упорядочиваются по времени (ORDER BY месяц).

Скользящее среднее (AVG())

Окей, накопительные суммы — это здорово, но что, если вы хотите анализировать тренды, скажем, за последние 3 месяца? Для этого подойдет скользящее среднее.

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

Мы снова работаем с таблицей доходы, и вот её данные:

Запрос для расчета 3-месячного скользящего среднего:

SELECT
    месяц,
    доход,
    AVG(доход) OVER (
        ORDER BY месяц 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS скользящее_среднее
FROM 
    доходы;

Результат:

Объяснение:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW говорит PostgreSQL смотреть на текущую строку и две предыдущие строки для расчета среднего.
2. В итоге для каждого месяца мы видим средний доход за последние 3 месяца.

Т.е. для каждой строки мы задаем окно в 3 строки: текущая и две предыдущие. А потом по ним считаем среднее. Очень удобно.

Работа ORDER BY и его влияние

Оконные функции зависят от правильного порядка строк. Если порядок указан неправильно (или вообще не указан), результаты могут быть неожиданными.

Пример: ошибки из-за отсутствия ORDER BY

Если мы уберем ORDER BY из OVER(), то вместо накопительной суммы мы получим сумму всех доходов для каждой строки:

SELECT
    месяц,
    доход,
    SUM(доход) OVER () AS неправильная_накопительная_сумма
FROM 
    доходы;

Результат:

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

Реальные кейсы использования

Анализ доходов:

• Накопительные суммы позволяют отслеживать, как растут продажи или доходы компании.
• Скользящее среднее помогает увидеть "чистый" тренд без шумов.

Финансовое моделирование:

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

Построение временных рядов:

Временные данные, такие как количество пользователей онлайн, просмотры страниц, выручка и т.п., идеально анализируются с SUM() и AVG().

2

Задача

SQL SELF, 29 уровень, 4 лекция

Недоступна

Скользящее среднее для последних 3 месяцев

Скользящее среднее для последних 3 месяцев

Открыть

1

Опрос

Оконные функции, 29 уровень, 4 лекция

Недоступен

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

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

Открыть