Mit SQL CTE Werte aus vorheriger Zeile ermitteln

Dieses Beispiel soll die Problemstellung erkennen lassen. Es zeigt eine Tabelle mit fortschreitenden Verbrauch und Betriebsstundenzähler.

WerteTabelle

Betriebsstunden	Verbrauch
1	1,8
2	4,6
3	6,8
4	7,7
5	9,0
6	12,3
7	14,8
8	15,9

Unser Interesse gilt der Berechnung des Verbrauchs pro Stunde. Dazu muss man nur jeweils die Differenz aller Verbrauchswerte berechnen.

Betriebsstunden	Verbrauch	Minus	Verbrauch Zeile vorher	ist	Verbrauch pro Stunde
1	1,8	-	0,0	=	1,8
2	4,6	-	1,8	=	2,8
3	6,8	-	4,6	=	2,2
5	7,7	-	6,8	=	0,9
6	9,0	-	7,7	=	1,3
7	12,3	-	9,0	=	3,3
8	14,8	-	12,3	=	2,5
9	15,9	-	14,8	=	1,1

Wie mach man das mit SQL? Mein erster "Reflex" einer Lösung ging über ein CURSOR in einer Stored Procedure. Dies klappt natürlich auch. Besinnt man sich aber darauf, dass SQL eine Mengenabfragesprache ist, so kann man eine elegantere Lösung finden. Wir haben zwei Mengen. Unserer Ausgangstabelle und die Menge der Ausgangstabelle um eine Zeile verschoben. Wir JOINen beide Mengen und subtrahieren die Verbrauchswerte.

Wie aber nun einen JOIN auf die Zeile vorher machen?

Seit SQL 2005 gibt es die TSQL Funktion ROW_NUMBER(). Sie gibt als Integer die Nummer der Zeile zurück. Wir können den erforderlichen JOIN auf die Zeile vorher durchführen, indem wir in der zweiten Menge die ROW_Number minus 1 zurückgeben:

Menge von Spalte Verbrauch mit ROW_Number	Menge von Spalte Verbrauch mit ROW_Number minus 1
Verbrauch ROW_NUMBER 1,8 1 4,6 2 6,8 3 7,7 4 9,0 5 12,3 6 14,8 7 15,9 8	ROW_NUMBER - 1 Verbrauch 1-1 = 0 1,8 2 -1 = 1 4,6 3 -1 = 2 6,8 4 -1 = 3 7,7 5 -1 = 4 9,0 6 -1 = 5 12,3 7 -1 = 6 14,8 8 -1 = 7 15,9

Mit ROW_NUMBER und ROW_NUMBER minus 1 erhält man die zwei Mengen für der JOIN auf die vorherige Spalte:

Verbrauch	ROW_NUMBER	ROW_NUMBER - 1	Verbrauch
		0	1,8
1,8	1	1	4,6
4,6	2	2	6,8
6,8	3	3	7,7
7,7	4	4	9,0
9,0	5	5	12,3
12,3	6	6	14,8
14,8	7	7	15,9
15,9	8

Mit einer CTE können wir nun das Ergebnis gleich in einem Rutsch ausgeben:

   1:  WITH WerteTabelle(Betriebsstunden, Verbrauch) 

   2:  AS 

   3:  ( 

   4:    SELECT Betriebsstunden = 1, Verbrauch = 1.8  UNION 

   5:    SELECT Betriebsstunden = 2, Verbrauch = 4.6  UNION 

   6:    SELECT Betriebsstunden = 3, Verbrauch = 6.8  UNION 

   7:    SELECT Betriebsstunden = 4, Verbrauch = 7.7  UNION 

   8:    SELECT Betriebsstunden = 5, Verbrauch = 9.0  UNION 

   9:    SELECT Betriebsstunden = 6, Verbrauch = 12.3 UNION 

  10:    SELECT Betriebsstunden = 7, Verbrauch = 14.8 UNION 

  11:    SELECT Betriebsstunden = 8, Verbrauch = 15.9 

  12:  ) 

  13:  ,Menge1(Nr, Betriebsstunden, Verbrauch) 

  14:  AS 

  15:  ( 

  16:    SELECT 

  17:      ROW_NUMBER() OVER (ORDER BY Betriebsstunden) AS 'Nr', 

  18:      Betriebsstunden, 

  19:      Verbrauch 

  20:    FROM WerteTabelle 

  21:  ) 

  22:  ,Menge2(NrMinusEins, Betriebsstunden, Verbrauch) 

  23:  AS 

  24:  ( 

  25:    SELECT 

  26:      (ROW_NUMBER() OVER (ORDER BY Betriebsstunden) - 1) AS 'NrMinusEins',  

  27:      Betriebsstunden, 

  28:      Verbrauch 

  29:    FROM WerteTabelle 

  30:  ) 

  31:   

  32:  SELECT 

  33:    Menge1.Betriebsstunden, 

  34:    Menge1.Verbrauch, 

  35:    Menge2.Verbrauch AS 'Verbrauch Zeile Vorher', 

  36:    Menge2.Verbrauch - Menge1.Verbrauch AS 'Verbrauch pro Stunde' 

  37:  FROM 

  38:    Menge1 

  39:  INNER JOIN Menge2 ON Menge1.Nr = Menge2.NrMinusEins

  40:   

Ergebnis: