Fensterfunktionen (oder Windowing-Funktionen) sind eine großartige Möglichkeit, verschiedene Perspektiven auf einen Datensatz zu erhalten, ohne den Server für diese Daten wiederholt aufrufen zu müssen. So können wir beispielsweise die Summe einer Spalte erfassen und sie Seite an Seite mit den Daten auf der Detailebene anzeigen, so dass „SalesAmount“ und „SUM(SalesAmount)“ in derselben Zeile erscheinen können. Wir können auch analytische Funktionen wie PERCENT_RANK und Ranking-Funktionen wie ROW_NUMBER verwenden, ohne die Granularität des Ergebnissatzes zu verändern oder zusätzliche Reisen zu unternehmen, um dieselben Quelldaten wieder und wieder zu erhalten.
„Schauen Sie zu, wie ich mühelos zwei von Val Kilmers Nierensteinen ausbalanciere. Quite bully!“
Fensterfunktionen verwenden alle die OVER()-Klausel, die verwendet wird, um zu definieren, wie die Funktion ausgewertet wird. Die OVER() Klausel akzeptiert drei verschiedene Argumente:
- PARTITION BY: Setzt ihren Zähler jedes Mal zurück, wenn die angegebene(n) Spalte(n) Werte ändern.
- ORDER BY: Ordnet die Zeilen, die von der Funktion ausgewertet werden. Damit wird nicht die gesamte Ergebnismenge geordnet, sondern nur die Art und Weise, wie die Funktion die Zeilen durchläuft.
- ROWS BETWEEN: Gibt an, wie die von der Funktion ausgewerteten Zeilen weiter eingeschränkt werden sollen.
Stellen wir uns vor, wir betrachten vereinfachte Daten aus einem Gewichtheberwettbewerb. Hier sind einige Code-Beispiele (wir führen sie alle in einer SELECT-Anweisung aus, da das Hinzufügen/Entfernen von Fensterfunktionen die Anzahl der zurückgegebenen Zeilen in keiner Weise ändert):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SELECT
LiftID
, LiftDate
, LiftPersonID
, LiftWeight
/* ROW_NUMBER listet die Nummer der Zeile, geordnet nach LiftID.
Der Zähler wird mit jeder neuen Kombination von LiftDate und LiftPersonID zurückgesetzt */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM addiert die gehobenen Gewichte.
Die erste SUMME zeigt die Gesamtsumme für die gesamte Ergebnismenge an.
Die zweite SUMME zeigt das Gesamtgewicht für das Hebedatum dieser Zeile an.
Die dritte SUMME zeigt das Gesamtgewicht für das Hebedatum und die Person in dieser Zeile an. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG zeigt das durchschnittlich gehobene Gewicht.
Die erste AVG zeigt das durchschnittliche Hebegewicht für das Hebedatum dieser Zeile an.
Die zweite AVG zeigt das durchschnittliche Hebegewicht für das Hebedatum und die Person in dieser Zeile an. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG und LEAD erlauben es der aktuellen Zeile, über Daten in Zeilen hinter oder vor ihr zu berichten.
Diese LAG-Funktion gibt das LiftWeight von einer Zeile hinter ihr zurück (in der Reihenfolge der LiftID), und wenn kein Wert gefunden wird, gibt sie 0 statt NULL zurück.
Die LEAD-Funktion holt das LiftWeight von drei Zeilen vor ihr. Da wir den optionalen Standardwert nicht angegeben haben (wie die „0“, die wir der LAG-Funktion gegeben haben), wird sie NULL zurückgeben, wenn es keine Zeile 3 Zeilen voraus gibt. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE geben den ersten und letzten Wert der angegebenen Spalte in der Ergebnismenge zurück.
Diese Funktion FIRST_VALUE gibt den ersten LiftWeight-Wert in der Ergebnismenge zurück.
Diese Funktion LAST_VALUE gibt den letzten LiftWeight-Wert in der Ergebnismenge zurück.
***WARNUNG: Ohne die ROWS BETWEEN in der LAST_VALUE-Funktion können Sie unerwartete Ergebnisse erhalten.***
*/
, ERSTER_WERT(Hebegewicht) OVER (ORDER BY Hebedatum) AS ERSTER HEBEN
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* SUMME unter Verwendung von ROWS BETWEEN engt den von der Fensterfunktion ausgewerteten Bereich ein.
Die Funktion beginnt und endet dort, wo ROWS BETWEEN angegeben ist.
Die erste SUMME addiert alle LiftWeight-Werte in Zeilen bis einschließlich der aktuellen Zeile.
Die zweite SUMME addiert alle LiftWeight-Werte in Zeilen zwischen der aktuellen Zeile und den drei Zeilen davor.
*/
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WeightRunningTotal
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS WeightSumLast4
FROM dbo.Lifts
|
Betrachtungen zu Fensterfunktionen
Wenn Sie SQL Server 2012 oder später nicht haben, ist Ihr Fensterfunktionsschrank ziemlich leer; SQL Server 2005 bis 2008 R2 erlaubten nur PARTITION BY in der OVER-Klausel einer Aggregatfunktion, und Sie haben RANK() und ROW_NUMBER(). Das war’s dann auch schon. Wenn Sie ein Entwickler sind, der noch mit einer dieser früheren Versionen arbeitet, ist dies ein überzeugendes Argument für den Wechsel zu 2012 oder höher. Bedenken Sie, wie viel Zeit Sie sparen können, wenn Sie nicht mehrere CTEs schreiben müssen, und wie viel schneller Ihre Abfragen sein werden.
Apropos schnell…
Durch die Vermeidung von Roundtrips zum Server für dieselben Daten reduzieren wir die E/A in diesen Tabellen. Wenn wir auf Indizes zugreifen, können wir die erforderlichen Lesevorgänge wirklich reduzieren. Es gibt zwar einen Kompromiss, aber der ist in der Regel sehr vorteilhaft. Bei Fensterfunktionen muss SQL Server das Fenster konstruieren und die Funktion berechnen (angezeigt als Aufgaben wie Window Spool, Segment, Sequence Project und Compute Scalar). Dabei werden Lesevorgänge in der Worktable hinzugefügt. Dennoch ist dies in der Regel kostengünstiger, als die Quelldaten mehrfach abzurufen, ggf. zu aggregieren und dann zusammenzuführen. Außerdem befindet sich die Worktable in der tempdb, die sich idealerweise auf der schnellsten Speicherebene befindet.
Schließlich sollten Sie daran denken, dass die Einschränkungen, die Sie einer Fensterfunktion auferlegen – PARTITION BY, ORDER BY oder ROWS BETWEEN – nur dazu dienen, die Fensterfunktion zu kontextualisieren, und in keiner Weise für die Ergebnismenge als Ganzes gelten. Mit anderen Worten: Ihre SELECT-Anweisung bleibt von allem, was Sie einer Fensterfunktion vorgeben, unberührt.