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

Задача

Финансовый аналитик строит график «как копилась выручка»: «Дай по каждому дню две цифры: сколько заработали за этот день и сколько накопилось с самого начала. Нужен нарастающий итог, чтобы видеть кривую роста».

Верни день, дневную выручку и накопительную выручку на конец этого дня.

Схема данных

Учебная база проката dvdrental:

• payment(payment_id, amount, payment_date, ...) — платежи; payment_date — момент оплаты (timestamp), amount — сумма.

Решение

SELECT to_char(payment_date::date, 'YYYY-MM-DD') AS day,
       sum(amount) AS daily_revenue,
       sum(sum(amount)) OVER (ORDER BY payment_date::date) AS cumulative_revenue
FROM payment
GROUP BY payment_date::date
ORDER BY payment_date::date;

Разбор

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

Самое хитрое место — двойной sum: sum(sum(amount)). Выглядит как опечатка, но это не она. Внутренний sum(amount) — обычный агрегат: он считает дневную выручку внутри GROUP BY payment_date::date. А внешний sum(...) OVER (...) — уже оконный: он берёт эти дневные суммы и катит по ним нарастающий итог. Порядок исполнения такой: сначала группировка сворачивает платежи в дни, потом окно проходит по дням и накапливает. Что такое окно и почему оно работает после GROUP BY — в вопросе что такое оконные функции.

payment_date::date отрезает время и оставляет только дату — иначе каждый платёж был бы в своей «секунде» и группировки по дням не вышло бы. Каст стоит и в GROUP BY, и внутри окна — это одно и то же выражение, движок сгруппирует по нему корректно. В SELECT дату дополнительно оборачиваем в to_char(..., 'YYYY-MM-DD') — только для читаемого вывода, на саму группировку и порядок окна это не влияет.

Про детерминизм: у окна ORDER BY payment_date::date определяет порядок накопления, а внешний ORDER BY day — порядок вывода. День уникален после свёртки, так что дополнительный tie-break тут не нужен — каждая строка это отдельная дата.

Если вместо нарастающего итога нужна доля одного платежа в общей сумме клиента — это тоже окно, но с PARTITION BY, смотри задачу доля платежа от выручки клиента.

Ожидаемый результат

day | daily_revenue | cumulative_revenue
--- | --- | ---
2007-02-14 | 116.73 | 116.73
2007-02-15 | 1188.92 | 1305.65
2007-02-16 | 1154.18 | 2459.83
2007-02-17 | 1188.17 | 3648.00
2007-02-18 | 1275.98 | 4923.98
2007-02-19 | 1290.90 | 6214.88
2007-02-20 | 1219.09 | 7433.97
2007-02-21 | 917.87 | 8351.84
2007-03-01 | 2808.24 | 11160.08
2007-03-02 | 2550.05 | 13710.13
… всего строк: 32 (показаны первые 10)