Tecnica
Totale progressivo
Somma una misura dalla prima riga fino a quella corrente.
SELECT month, ca, SUM(ca) OVER (ORDER BY month) AS running_total
FROM monthly_sales;
Aggiungi PARTITION BY per far ripartire il totale a ogni gruppo (ogni anno, ogni cliente).
WITH monthly AS (
SELECT CAST(date_trunc('month', o.ordered_at) AS DATE) AS month,
SUM(l.quantity * l.unit_price) AS revenue
FROM orders o JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status <> 'cancelled' AND o.ordered_at < DATE '2024-05-01'
GROUP BY 1
)
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly
ORDER BY month;
| month | revenue | running_total |
|---|---|---|
| 2024-01-01 | 5874.70 | 5874.70 |
| 2024-02-01 | 5611.30 | 11486.00 |
| 2024-03-01 | 7261.70 | 18747.70 |
| 2024-04-01 | 5768.50 | 24516.20 |