Laufende Nummern
Laufende Zahlen entstehen vereinfacht gesagt, wenn Zahlen fortlaufend miteinander addiert werden – beispielsweise, um kumulierte Jahreswerte (Year-to-Date, YTD) zu ermitteln. Das Summieren von Zahlen ist zwar ein häufiger Anwendungsfall, aber die Möglichkeiten sind nicht darauf beschränkt. Ebenso lässt sich ein gleitender Durchschnitt berechnen.
Für diesen Fall arbeiten wir mit einem YTD-Beispiel. Angenommen, wir haben eine Tabelle mit dem Umsatz für eine bestimmte Kategorie pro Monat.
ea_month id amount ea_year circle_id April 92570 1000 2014 1 April 92571 3000 2014 2 April 92572 2000 2014 3 March 92573 3000 2014 1 March 92574 2500 2014 2 March 92575 3750 2014 3 February 92576 2000 2014 1 February 92577 2500 2014 2 February 92578 1450 2014 3
Um die Gesamtsumme des Monats bis zum aktuellen Zeitpunkt zu erhalten, könnte man versucht sein, eine Unterabfrage zu verwenden – etwa nach dem Prinzip: Ermittle alle Beträge der vorherigen Monate des laufenden Jahres für das jeweilige Produkt. Das funktioniert zwar, jedoch wird für jede abgerufene Zeile eine eigene Unterabfrage ausgeführt. Dies kann leicht zu Tausenden von Abfragen führen und entsprechend sehr lange dauern, wie in diesem Beispiel:
select branch_id,
net_profit as store_profit,
(select sum(net_profit) from store_sales as s2 where s2.city = s1.city) as city_profit,
store_profit / city_profit * 100 as store_percentage_of_city_profit
from store_sales as s1
order by branch_id;
+-----------+--------------+-------------+---------------------------------+
| BRANCH_ID | STORE_PROFIT | CITY_PROFIT | STORE_PERCENTAGE_OF_CITY_PROFIT |
|-----------+--------------+-------------+---------------------------------|
| 1 | 10000.00 | 25000.00 | 40.00000000 |
| 2 | 15000.00 | 25000.00 | 60.00000000 |
| 3 | 10000.00 | 19000.00 | 52.63157900 |
| 4 | 9000.00 | 19000.00 | 47.36842100 |
+-----------+--------------+-------------+---------------------------------+
Postgres bietet eine sogenannte Fensterfunktion namens OVER. In einer mehr oder weniger natürlichen Sprache teilen Sie SQL damit mit, dass Sie eine sum() OVER einem nach Zeit und/oder Produkt PARTITIONED (unterteilten) Datensatz wünschen.
SELECT ea_month, id, amount, ea_year, circle_id
, sum(amount) OVER (PARTITION BY circle_id
ORDER BY ea_year, ea_month) AS cum_amt
FROM tbl
ORDER BY circle_id, month;
Um tatsächlich einen YTD-Wert zu erhalten, muss das Jahr Teil von PARTITION sein, andernfalls erfolgt die Berechnung über den gesamten Zeitraum.
Die Berechnung erfolgt pro Zeile. Enthält die Tabelle also tägliche Daten, die Ausgabe ist jedoch auf monatlicher Basis gewünscht, müssen die Daten zuerst nach Monaten gruppiert und summiert werden, bevor ein YTD-Wert auf monatlicher Basis funktioniert.