Урок 12.7 · Время чтения: ~6 мин

Нарастающий итог (накопительная сумма) прибавляет каждое значение к сумме всех предыдущих: выручка с начала периода, остаток на счёте после каждой операции, накопленное число регистраций. Это основа дашбордов и популярная задача на собеседовании по SQL. В этом уроке вы посчитаете нарастающий итог через SUM() OVER (ORDER BY ...), научитесь начинать его заново для каждой группы, обойдёте ловушку с одинаковыми значениями сортировки и увидите, как это делали до появления оконных функций.

Нарастающий итог в SQL

Обычный SUM с GROUP BY сворачивает строки в одну сумму. Нарастающий итог сохраняет каждую строку и добавляет столбец с суммой на текущий момент. Именно это делает оконная функция: SUM(amount) OVER (ORDER BY day) суммирует все строки от первой до текущей в порядке day. Оконные функции и рамки разобраны в уроках 7.1 и 7.3; здесь они решают практическую задачу.

Примеры работают на учебной базе Sakila (MySQL 8.0).

Накопленная выручка по дням

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

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT pay_day,
       daily_amount,
       SUM(daily_amount) OVER (ORDER BY pay_day) AS running_total
FROM daily
ORDER BY pay_day;

Результат: 10 дней с платежами в августе 2005 года; нарастающий итог растёт с 2817.29 до 24072.13 — выручки за весь месяц.

Сначала агрегировать важно: SUM(...) OVER по отдельным платежам вернул бы строку на каждый платёж, а не на день. Дни без платежей просто отсутствуют; чтобы показать их с нулём, присоедините данные к календарю (урок 6.6).

Начинаем итог заново для каждой группы

PARTITION BY запускает отдельный нарастающий итог для каждой группы. Здесь у месячной выручки два накопительных столбца: за всё время и с обнулением каждый год:

WITH monthly AS (
    SELECT YEAR(payment_date) AS pay_year,
           MONTH(payment_date) AS pay_month,
           SUM(amount) AS monthly_amount
    FROM payment
    GROUP BY YEAR(payment_date), MONTH(payment_date)
)
SELECT pay_year, pay_month, monthly_amount,
       SUM(monthly_amount) OVER (ORDER BY pay_year, pay_month) AS running_total,
       SUM(monthly_amount) OVER (PARTITION BY pay_year ORDER BY pay_month) AS year_to_date
FROM monthly
ORDER BY pay_year, pay_month;

Результат: 5 месяцев; общий нарастающий итог доходит до 67416.51, а итог с начала года в 2006 году начинается заново с 514.18.

Тот же шаблон даёт нарастающий итог по каждому клиенту (PARTITION BY customer_id ORDER BY payment_date) или по магазину.

Ловушка: одинаковые значения сортировки

Когда в ORDER BY окна есть повторы, результат многих удивляет. В Sakila есть два платежа с точно одинаковым временем:

SELECT payment_id, payment_date, amount,
       SUM(amount) OVER (ORDER BY payment_date) AS default_frame,
       SUM(amount) OVER (ORDER BY payment_date, payment_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM payment
WHERE payment_date >= '2005-05-30 14:40:00'
  AND payment_date < '2005-05-30 15:00:00'
ORDER BY payment_date, payment_id;

Результат: у платежей 1258 и 8245 default_frame = 5.98, а rows_frame показывает 2.99 и затем 5.98.

Если в окне есть ORDER BY, а рамка не указана явно, по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: в рамку попадают все строки с тем же значением сортировки, что и у текущей. Строки с равными значениями получают одинаковый итог. Чтобы прибавлять строки строго по одной, используйте рамку ROWS и уникальный дополнительный критерий в ORDER BY, например первичный ключ.

Без оконных функций

До оконных функций (MySQL до 8.0) нарастающий итог считали коррелированным подзапросом: для каждой строки суммировали все строки до неё.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day,
           SUM(amount) AS daily_amount
    FROM payment
    WHERE payment_date >= '2005-08-01'
      AND payment_date < '2005-09-01'
    GROUP BY DATE(payment_date)
)
SELECT d1.pay_day,
       d1.daily_amount,
       (SELECT SUM(d2.daily_amount)
        FROM daily d2
        WHERE d2.pay_day <= d1.pay_day) AS running_total
FROM daily d1
ORDER BY d1.pay_day;

Результат: те же 10 строк и итоги, что и в варианте с оконной функцией.

Для каждой строки подзапрос просматривает все предыдущие, поэтому объём работы растёт квадратично. Упомяните этот способ как запасной, а на практике используйте SUM() OVER.

Практика

• Месячные и накопительные платежи
• Анализ накопительных платежей
• Суммы платежей за август 2005 года

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

• SUM(x) OVER (ORDER BY key) возвращает накопительную сумму до каждой строки и сохраняет все строки.
• Сначала агрегируйте до нужного уровня (день, месяц), затем добавляйте нарастающий итог.
• PARTITION BY начинает итог заново для каждой группы: клиента, магазина, года.
• Рамка по умолчанию — RANGE, поэтому строки с равными значениями сортировки делят один итог; используйте ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW и уникальный дополнительный критерий.
• Без оконных функций используйте коррелированный подзапрос, но он гораздо медленнее.

Часто задаваемые вопросы

Как посчитать нарастающий итог в SQL?

С помощью оконной функции: SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM daily;. Это работает в MySQL 8.0+, MariaDB 10.2+, PostgreSQL, SQL Server, Oracle и SQLite 3.25+.

Как посчитать нарастающий итог по группам?

Добавьте PARTITION BY: SUM(amount) OVER (PARTITION BY customer_id ORDER BY payment_date). Сумма начинается заново с первой строки каждого клиента.

Почему у некоторых строк одинаковый нарастающий итог?

Потому что у них одинаковое значение в столбце ORDER BY окна, а рамка RANGE по умолчанию включает все такие строки. Укажите ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW и добавьте в ORDER BY уникальный столбец, например id.

Как посчитать скользящее среднее вместо нарастающего итога?

Ограничьте рамку фиксированным числом строк: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) усредняет текущий день и шесть предыдущих. Рамки окна разобраны в уроке 7.3.