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:

0001

0 Kommentare:

Kommentar posten