SQL: Cumulative sum (нарастающий итог)

Решение: Cumulative Sum (нарастающий итог)

Объяснение задачи

Нужно для каждого пользователя посчитать running total — сумму всех транзакций с начала периода до текущей даты. Это классическая задача аналитиков для анализа накопления денег, расходов и т.д.

Синтаксис: Window Function

SQL предоставляет window function SUM() OVER() — идеальный инструмент для нарастающих итогов:

SELECT 
    user_id,
    transaction_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id 
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum
FROM transactions
ORDER BY user_id, transaction_date;

Разбор синтаксиса

PARTITION BY user_id — разделяет данные по пользователям. Каждый пользователь считается отдельно.

ORDER BY transaction_date — упорядочивает строки в партиции. Без этого cumulative sum не имеет смысла.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — определяет frame (окно):

• UNBOUNDED PRECEDING — от первой строки партиции
• CURRENT ROW — до текущей строки

Это даёт нам нарастающий итог: для каждой строки суммируются все значения от начала до текущей строки.

Почему не GROUP BY?

GROUP BY агрегирует строки и выводит одну строку на группу. Window function сохраняет все строки и добавляет вычисленный столбец.

❌ Неправильно:

SELECT user_id, SUM(amount)
FROM transactions
GROUP BY user_id;
-- Результат: 1 строка на пользователя, видим только итоговую сумму

✅ Правильно:

SELECT 
    user_id, 
    transaction_date, 
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY transaction_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM transactions;
-- Результат: сохраняются все строки + добавляется cumulative_sum

Альтернативный синтаксис (более лаконичный)

В большинстве БД можно написать:

SELECT 
    user_id,
    transaction_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id 
        ORDER BY transaction_date
    ) AS cumulative_sum
FROM transactions
ORDER BY user_id, transaction_date;

Примечание: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — это default frame для ORDER BY, поэтому можно его опустить.

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

Данные:

user_id | transaction_date | amount
--------|------------------|-------
1       | 2024-01-01       | 100
1       | 2024-01-05       | 200
1       | 2024-01-10       | 150
2       | 2024-01-02       | 300
2       | 2024-01-08       | 50

Результат:

user_id | transaction_date | amount | cumulative_sum
--------|------------------|--------|---------------
1       | 2024-01-01       | 100    | 100
1       | 2024-01-05       | 200    | 300
1       | 2024-01-10       | 150    | 450
2       | 2024-01-02       | 300    | 300
2       | 2024-01-08       | 50     | 350

Видно, что у user_id=1 сумма растёт: 100 → 300 → 450. У user_id=2: 300 → 350.

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

Cumulative sum используется для:

• Анализа тренда накопления (выручка, расходы)
• Отслеживания роста метрик
• Создания когортных анализов
• Выявления аномалий (резкие скачки в нарастающем итоге)