Was sind Running Totals?
Mit “Running Total” oder “laufender Summe” ist ein Szenario gemeint, in dem in jeder Zeile die Summe der Werte einer bestimmten Spalte der vorangegangenen Zeilen steht. Ein gutes Beispiel dafür sind Punkte verschiedener Spiele, die aufaddiert werden. Wichtig für eine laufende Summe ist dabei natürlich, dass die Zeilen in einer eindeutigen Sortierung durchlaufen werden, da sonst beliebige Werte bei unserer Berechnung herauskommen werden.
Wie könnt ihr ein Running Total berechnen?
Eine laufende Summe ist ähnlich wie die bereits behandelten RANK und ROW_NUMBER-Funktionen eine Window-Funktion, also eine Funktion, die auf einem gewissen Satz an Zeilen operiert und diese zusammenfasst. Wir haben also ein Randori von vier Teams über mehrere Runden und zählen in jeder Runde die Punkte der Teams. Diese speichern wir in einer Tabelle:
DROP TABLE IF EXISTS #games
CREATE TABLE #games
(
RandoriNumber INT NOT NULL IDENTITY(1,1)
,Team1Score INT NOT NULL DEFAULT 0
,Team2Score INT NOT NULL DEFAULT 0
,Team3Score INT NOT NULL DEFAULT 0
,Team4Score INT NOT NULL DEFAULT 0
)
Wir haben in dieser Tabelle eine Kampf-Nummer (RandoriNumber) hinterlegt, nach der wir die Zeilen hinterher eineindeutig sortieren können. Nun füllen wir über einen Trainingszyklus mit 5 Kämpfen die Resultate in die Tabelle:
DECLARE @nRows int
SELECT @nRows = COUNT(*) FROM #games
WHILE @nRows < 5 BEGIN
INSERT INTO #games
(Team1Score, Team2Score, Team3Score, Team4Score)
VALUES
(CAST(10*RAND()-1 AS INT), CAST(10*RAND()-1 AS INT), CAST(10*RAND()-1 AS INT), CAST(10*RAND()-1 AS INT))
SELECT @nRows = COUNT(*) FROM #games
END
Wir fügen hier Zufallszahlen von Null bis Zehn in die Tabelle ein, Eure Ergebnisse werden am Ende also anders aussehen als die, die wir hier zeigen. Nun möchten wir zu jedem Wettkampf wissen, welche Punktzahl das jeweilige Team an diesem Tag hatte. Dafür bilden wir für jede Zeile die Summen der vier Team-Scores sortiert nach der Randori-Nummer. Das ist sehr einfach, da wir hierfür der SUM-Funktion nur ein OVER (ORDER BY) Fenster mitgeben müssen.
SELECT
RandoriNumber
,Team1Score
,Team2Score
,Team3Score
,Team4Score
,Team1RunningTotal = SUM(Team1Score) OVER (ORDER BY RandoriNumber)
,Team2RunningTotal = SUM(Team2Score) OVER (ORDER BY RandoriNumber)
,Team3RunningTotal = SUM(Team3Score) OVER (ORDER BY RandoriNumber)
,Team4RunningTotal = SUM(Team4Score) OVER (ORDER BY RandoriNumber)
FROM #games
Das Ergebnis sollte so ähnlich aussehen, wie hier:
Ihr seht bei Team1 in Zeile 2 als Running Total den Wert 5, der sich zusammensetzt aus der Score 5 für den ersten Randori und der Score 1 für den zweiten Randori und so weiter. Nun könnten SQL-Shoguns anmerken, dass unklar ist, warum dieses Statement eine laufende Summe bildet, was nicht ganz inkorrekt wäre. Denn immerhin sagen wir dem Fenster für die Summen-Funktion zwar, wie wir sortieren wollen, aber dass nur vorangehende Zeilen summiert werden, geben wir nicht an. Das könnten wir beispielsweise explizit tun, indem wir im Fenster ausdrücklich angeben, dass wir alle vorangegangen Zeilen bis zur aktuellen Zeile auswerten möchten, indem wir ein ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mit angeben:
SELECT
RandoriNumber
,Team1Score
,Team2Score
,Team3Score
,Team4Score
,Team1RunningTotal = SUM(Team1Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
,Team2RunningTotal = SUM(Team2Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
,Team3RunningTotal = SUM(Team3Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
,Team4RunningTotal = SUM(Team4Score) OVER (ORDER BY RandoriNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
FROM #games s
Wir haben aber Glück, in diesem speziellen Fall passiert genau das gleiche, da ein nicht angegebener ROWS/RANGE-Wert, ein BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW angenommen wird, also immer automatisch über die Zeilen summiert wird, die vor der aktuellen Zeile im Window liegen. Das funktioniert natürlich nur, wenn mit der OVER clause überhaupt ein Fenster definiert wird.
