Создание и вычисление кумулятивной суммы в MySQL

Быстрый ответ

Чтобы создать колонку с накопительной суммой в MySQL версии 8.0 и более поздних, используйте функции SUM() и OVER(), которые вычисляют накопительную сумму:

SELECT
    id, 
    SUM(value) OVER (ORDER BY id) AS cumulative_sum
FROM
    sales;

В старых версиях MySQL (ранее версии 8.0), используйте пользовательские переменные для последовательного вычисления накопительной суммы:

SET @cumulative := 0;
SELECT
    id, 
    (@cumulative := @cumulative + value) AS cumulative_sum
FROM
    sales
ORDER BY
    id;

В запросе замените id, value и sales на свои поля и таблицы.

Вычисление с использованием коррелированного подзапроса

Накопительную сумму можно расчитать с использованием коррелированного подзапроса, хотя это решение может быть менее эффективным с точки зрения производительности:

SELECT
    sales_primary.id,
    (SELECT SUM(value) FROM sales AS sales_secondary WHERE sales_secondary.id <= sales_primary.id) AS cumulative_sum
FROM
    sales AS sales_primary
ORDER BY
    sales_primary.id;

Как пользовательские переменные могут помочь (до MySQL 8.0)

До внедрения оконных функций в MySQL 8.0 для расчета накопительной суммы активно применялись пользовательские переменные:

SET @total := 0;
SELECT
    id,
    (@total := @total + value) AS cumulative_sum
FROM
    sales
ORDER BY
    id;

Необходимо инициализировать переменную с помощью SET и следить за порядком строк, используя ORDER BY.

Введение MySQL 8.0 и более поздних версий

С выходом MySQL 8.0 появились новые оконные функции, которые предоставляют удобный и эффективный способ расчета накопительной суммы:

SELECT
    id,
    SUM(value) OVER (ORDER BY id) as cumulative_sum
FROM
    sales;

Обновление до новых версий MySQL позволяет воспользоваться этими улучшениями.

Расчет накопительной суммы на лету

Не требуется изменять структуру базы данных для расчета накопительной суммы – делайте это прямо в запросе SELECT:

SELECT
    id,
    value,
    (@cumulative := @cumulative + value) AS cumulative_sum
FROM
    (SELECT @cumulative := 0) AS variable_init,
    sales
ORDER BY
    id;

Важность ORDER BY при расчете накопительной суммы

Правильная сортировка данных с использованием ORDER BY имеет решающее значение при расчете накопительной суммы. Не забудьте это проверить.

Обновление с использованием подзапросов

Если требуется сохранить накопительную сумму в таблице, используйте подзапрос в команде UPDATE:

UPDATE sales s1
JOIN (
    SELECT id, (@cumulative := @cumulative + value) AS cumulative_sum
    FROM (SELECT @cumulative := 0) AS var_init, sales
    ORDER BY id
) AS s2
ON s1.id = s2.id
SET s1.cumulative_sum = s2.cumulative_sum;

Визуализация

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

День 1: Вклад 🪙 | Общая сумма  = 🪙
День 2: Вклад 🪙🪙 | Общая сумма = 🪙🪙🪙
День 3: Вклад 🪙 | Общая сумма = 🪙🪙🪙🪙

Каждый новый вклад увеличивает общую сумму монет.

В SQL это может выглядеть так:

SELECT date, 
       deposit, 
       SUM(deposit) OVER (ORDER BY date) as cumulative_sum
FROM savings_table;

Так же, как и монета в копилке, каждый следующий вклад добавляется к предыдущей накопленной сумме.

Тестирование: незаменимый этап

Важно проверить корректность работы запроса на накопление сумм, проведя тестирование на примере тестовых данных:

SET @total = 0;
INSERT INTO sales VALUES (1, 10), (2, 20), (3, 30), (4, 40);
SELECT id, (@total := @total + value) AS cumulative_sum FROM sales ORDER BY id;

Настройка запроса

Подгоните запрос под свои потребности: измените начальные значения переменных и порядок сортировки данных:

SET @cumulative := 100;
SELECT id, (@cumulative := @cumulative + value) AS cumulative_sum FROM sales ORDER BY id DESC;

Полезные материалы

1. Оконные функции в MySQL 8.0 – официальная документация по оконным функциям MySQL.
2. Продвинутые оконные функции SQL – подробное руководство с примерами применения оконных функций SQL.
3. Использование агрегации данных с использованием функции SUM() в сочетании с GROUP BY в MySQL.