SQL — сумма транзакций по дням накопительным итогом

Условие

В таблице task_3(date, amount) — транзакции. Постройте SQL-запрос, возвращающий таблицу с суммой операций по дням, причём в значении дня n должна учитываться сумма всех предыдущих дней (накопительный итог, running total).

Решение

Подход

Это каноническая задача на window-функцию SUM(...) OVER (ORDER BY date). План:

1. CTE считает дневную сумму (day_sum) — как в задаче 3.1.
2. Window-функция SUM(day_sum) OVER (ORDER BY date) накапливает сумму по растущему окну от первого дня до текущего.

Реализация

Эталон из ответов задачи:

WITH daily_sum AS (
  SELECT
    DATE_TRUNC('day', date::date) AS date,
    SUM(amount)                   AS day_sum
  FROM task_3
  GROUP BY DATE_TRUNC('day', date::date)
)
SELECT
  date,
  day_sum,
  SUM(day_sum) OVER (ORDER BY date) AS cumulative_amount
FROM daily_sum
ORDER BY date;

Поведение SUM ... OVER (ORDER BY ...) без ROWS BETWEEN

По умолчанию диапазон окна — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это значит, что для текущей строки берутся все строки с тем же значением ORDER BY и все предыдущие. Для уникальных date это эквивалентно ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Если есть дубли по дате (несколько записей с тем же днём в daily_sum), стоит явно указать:

SUM(day_sum) OVER (
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Сегментированный накопительный итог

Если бы потребовался running total по пользователю (а не глобальный), достаточно добавить PARTITION BY user_id:

SUM(amount) OVER (PARTITION BY user_id ORDER BY date)

Подводные камни

1. ORDER BY обязателен в window для running total. Без него SUM(...) OVER () возвращает грубо «сумму всего», а не накопительный итог.
2. Дубли в ключе сортировки + RANGE. Если у нескольких строк один date, все они получают один и тот же running total — частая причина багов с «плато» в графиках. Добавляйте ROWS BETWEEN ... и тай-брейкер.
3. Пропуски дней. Если за день n транзакций не было, в выдаче этот день отсутствует. Для непрерывной серии — LEFT JOIN к generate_series.
4. SUM по сырой таблице vs CTE. Можно записать в один проход: SUM(SUM(amount)) OVER (ORDER BY ...) после GROUP BY. Многим читателям такая запись непонятна — CTE прозрачнее.
5. Перфоманс. Window-функция требует сортировки. На очень больших таблицах подумайте о материализации дневной агрегации в отдельной таблице.

Альтернатива: одним запросом без CTE

SELECT
  DATE_TRUNC('day', date::date) AS date,
  SUM(amount)                   AS day_sum,
  SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('day', date::date)) AS cumulative_amount
FROM task_3
GROUP BY DATE_TRUNC('day', date::date)
ORDER BY 1;

SUM(SUM(...)) — это window поверх агрегата (легально в стандарте SQL).

Эталонный ответ

CTE с дневной агрегацией → SUM(day_sum) OVER (ORDER BY date). Это даёт колонку cumulative_amount — нарастающий итог.