SQL Server 2012: Window Frame – Sliding Windows

Mit SQL Server 2012 wurde relativ still eine Erweiterung der Over Klausel eingeführt: Window Frame. Dann und wann liest man auch etwas von Window Function oder Sliding Windows (diese Bezeichnung finde ich optimal), aber Window Frame ist die offizielle Bezeichung.

 

Worum gehts? MIt dieser Erweiterungen lassen sich laufende Summe bilden. Ausgehen von einer bestimmten Zeilenposition. So lassen sich durchaus per Rows ausgehend von der aktuellen Position x-Werte eine Spalte vorher oder / und nachher auswerten. Wenn Sie nun sagen: “Hä.. Was?” Dann vestehe ich Sie vollkommen. Wollen wir nun mal diese Erweiterung genauer ansehen, um zu verstehen, wie diese Funktionieren.

Für die Beispiele liegt eine sehr simple Tabelle zugrunde:

 

CREATE TABLE #TMP1 (ID INT, Col1 CHAR(1), Col2 INT)
GO

INSERT INTO #TMP1 VALUES(1,'A', 1), (2, 'A', 2), (3, 'B', 3), (4, 'C', 4), (5, 'D', 5), (6,'D',6)
GO

Mit folgender Abfrage wollen wir die einzelnen Erweiterung betrachten:

SELECT *
  ,SUM(Col2) OVER(ORDER BY Col1 RANGE UNBOUNDED PRECEDING) "Range" 
  ,SUM(Col2) OVER(ORDER BY Col1 ROWS UNBOUNDED PRECEDING) "Rows"   
  ,SUM(Col2) OVER(ORDER BY Col1 ROWS Between CURRENT ROW and 2 Following )     
  ,SUM(Col2) OVER(ORDER BY Col1 ROWS Between 2 FOLLOWING and 3 Following )    
  ,SUM(Col2) OVER(ORDER BY Col1 ROWS Between 2 Preceding    and 2 Following ) 
FROM #TMP1

Sehen wir zuerst mal in der folgende Abfrage, die erste Summe genauer an:

SUM(Col2) OVER(ORDER BY Col1 RANGE UNBOUNDED PRECEDING)

 

RANGE

Mittels des OVER( ..COL1 ..RANGE) werden in der Spalte COL1 gleiche Werte als ein Bereich bewertet. Das bedeutet, dass in diesem Fall die Summe pro Bereich gebildet wird. Durch das UNBOUNDED PRECEDING werden die Bereiche addiert.

 

 

Nächste Beispiel. Diesesmal mit

ROWS

 

Nun werden pro Zeile alle darüber liegenden Werte addiert.

 

 

 

Rows mit Current Row und Following

Ausgehend von der aktuellen Zeile, zählen wir nun den aktuellen Wert einer Spalte mit den 2 nachfolgenden Werten zusammen.

 

 

ROW mit Between

 

Natürlich lassen sich auch ausgehend von einer bestimmter Zeile ausgehend auch mehrere Zeilen summieren. Wie im nachfolgenden Beispiel der nachfolgenden 2. und 3. Wert addiert werden

 

Rows mit Between  Preceding und Following

Das Aggregieren der Werte läßt sich durchaus auch mit vorhergehenden Werten und nachfolgenden Werten arrangieren:

 

Die Window Frame Erweiterung stellt somit ein mächtiges Werkzeug zur Verfügung, das mit rel. simplen Mitteln laufende Aggrgate erzeugen kann (Sliding window). Ohne diese Eweiterung wäre in früheren Versionen des SQL Server einen solche Aufgabe nicht unöglich gewesen, aber mit Sicherheit sehr aufwendig.

SQL 2008 Administration Training