Muster
Laufende Summe
Eine Kennzahl von der ersten bis zur aktuellen Zeile aufsummieren.
SELECT month, ca, SUM(ca) OVER (ORDER BY month) AS running_total
FROM monthly_sales;
Fügen Sie PARTITION BY hinzu, um die Summe für jede Gruppe neu zu beginnen (jedes Jahr, jeden Kunden).
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 |