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.