webentwicklung-frage-antwort-db.com.de

Berechnung der kumulativen Summe in PostgreSQL

Ich möchte die kumulative oder laufende Menge des Feldes finden und es von der Inszenierung zur Tabelle einfügen. Meine Staging-Struktur sieht ungefähr so ​​aus:

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

Ich möchte, dass meine Zieltabelle ungefähr so ​​aussieht:

ea_month    id       amount    ea_year    circle_id    cum_amt
February    92576    1000      2014        1           1000 
March       92573    3000      2014        1           4000
April       92570    2000      2014        1           6000
February    92577    3000      2014        2           3000
March       92574    2500      2014        2           5500
April       92571    3750      2014        2           9250
February    92578    2000      2014        3           2000
March       92575    2500      2014        3           4500
April       92572    1450      2014        3           5950

Ich bin wirklich sehr verwirrt, wie ich dieses Ergebnis erzielen soll. Ich möchte dieses Ergebnis mit PostgreSQL erreichen.

Kann jemand vorschlagen, wie man diese Ergebnismenge erreicht?

sqlpostgresqlwindow-functionsanalytic-functionscumulative-sum
58
Yousuf Sultan

Grundsätzlich benötigen Sie hier eine Fensterfunktion . Das ist heutzutage Standard. Zusätzlich zu echten Fensterfunktionen können Sie any Aggregatfunktion als Fensterfunktion in Postgres verwenden, indem Sie eine OVER -Klausel anhängen.

Die besondere Schwierigkeit dabei ist, Partitionen und Sortierreihenfolge richtig zu machen:

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;

Und neinGROUP BY Hier.

Die Summe für jede Zeile wird von der ersten Zeile in der Partition bis zur aktuellen Zeile berechnet - oder in Anführungszeichen gesetzt im Handbuch um genau zu sein:

Die Standardeinstellung ist RANGE UNBOUNDED PRECEDING. Dies entspricht RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Mit ORDER BY Wird der Frame so festgelegt, dass alle Zeilen von der Partition bis zum letzten Peer der aktuellen Zeile ORDER BY Gestartet werden .

... was ist die kumulative oder laufende Summe, die Sie suchen. Meine kühne Betonung.

Zeilen mit dem gleichen (circle_id, ea_year, ea_month) Sind "Peers" in dieser Abfrage. Alle diese zeigen die gleiche laufende Summe, wobei alle Peers zur Summe hinzugefügt werden. Aber ich nehme an, Ihre Tabelle ist UNIQUE auf (circle_id, ea_year, ea_month), Dann ist die Sortierreihenfolge deterministisch und keine Zeile hat Peers.

Jetzt funktioniert ORDER BY ... ea_month nicht mit Strings für Monatsnamen. Postgres wird alphabetisch nach der Ländereinstellung sortiert.

Wenn Sie aktuelle date Werte in Ihrer Tabelle gespeichert haben, können Sie richtig sortieren. Wenn nicht, schlage ich vor, ea_year Und ea_month Durch eine einzelne Spalte mon vom Typ date in Ihrer Tabelle zu ersetzen.

  • Verwandle was du hast mit to_date() :

    to_date(ea_year || ea_month , 'YYYYMonth') AS mon

  • Zur Anzeige können Sie Original-Strings mit to_char() erhalten:

    to_char(mon, 'Month') AS ea_month
to_char(mon, 'YYYY') AS ea_year

Bei dem unglücklichen Layout funktioniert Folgendes:

SELECT ea_month, id, amount, ea_year, circle_id
     , sum(amount) OVER (PARTITION BY circle_id ORDER BY mon) AS cum_amt
FROM   (SELECT *, to_date(ea_year || ea_month, 'YYYYMonth') AS mon FROM tbl)
ORDER  BY circle_id, mon;
95
Erwin Brandstetter