Window (of Windowing) functies zijn een geweldige manier om verschillende perspectieven op een set van gegevens te krijgen zonder herhaalde oproepen naar de server te hoeven doen voor die gegevens. We kunnen bijvoorbeeld de som van een kolom verzamelen en deze naast de gegevens op detailniveau weergeven, zodat “Verkoopbedrag” en “SUM(Verkoopbedrag)” in dezelfde rij kunnen verschijnen. We kunnen ook analytische functies zoals PERCENT_RANK en rangschikkingsfuncties zoals ROW_NUMBER uitvoeren, allemaal zonder de granulariteit van de resultatenreeks te wijzigen of extra reizen te maken om dezelfde brongegevens steeds opnieuw te verkrijgen.
“Kijk hoe ik moeiteloos twee van Val Kilmer’s nierstenen balanceer. Nogal bully!”
Windowfuncties gebruiken allemaal de OVER()-clausule, die wordt gebruikt om aan te geven hoe de functie wordt geëvalueerd. De OVER() clausule accepteert drie verschillende argumenten:
- PARTITION BY: Stelt de teller opnieuw in telkens wanneer de opgegeven kolom (kolommen) van waarde verandert (veranderen).
- ORDER BY: Ordent de rijen die de functie zal evalueren. Hiermee wordt niet de hele resultatenreeks geordend, maar alleen de manier waarop de functie door de rijen gaat.
- ROWS BETWEEN: Geeft aan hoe de rijen die door de functie worden geëvalueerd, verder moeten worden beperkt.
Laten we eens doen alsof we vereenvoudigde gegevens van een gewichthefwedstrijd bekijken. Hier zijn enkele code voorbeelden (we doen ze allemaal in één SELECT statement omdat het toevoegen/verwijderen van window functies op geen enkele manier het aantal rijen dat we terugkrijgen verandert):
|
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 geeft het nummer van de rij weer, geordend op LiftID.
De teller wordt gereset bij elke nieuwe combinatie van LiftDate en LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM zal de opgetilde gewichten optellen.
De eerste SUM toont het eindtotaal voor de hele reeks resultaten.
De tweede SUM toont het totale getilde gewicht voor de liftdatum van die rij.
De derde SUM toont het totale gewicht voor de datum van de lift en de persoon in die rij. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG toont het gemiddelde gewicht dat is getild.
De eerste AVG toont het gemiddelde getilde gewicht voor de datum van de lift van die rij.
De tweede AVG toont het gemiddelde gewicht van de lift voor de datum van de lift van die rij en de persoon. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG en LEAD laten de huidige rij toe om te rapporteren over gegevens in rijen erachter of ervoor.
Deze LAG functie zal de LiftWeight van 1 rij erachter teruggeven (in volgorde van LiftID) en als er geen waarde wordt gevonden, zal het 0 teruggeven in plaats van NULL.
De LEAD functie zal de LiftWeight van 3 rijen ervoor krijgen. Omdat we de optionele standaardwaarde niet hebben opgegeven (zoals de “0” die we aan de LAG functie hebben gegeven, zal deze NULL teruggeven als er geen rij 3 rijen vooruit is. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE EN LAST_VALUE zullen de gespecificeerde kolom’s eerste en laatste waarde in de resultaatverzameling teruggeven.
Deze FIRST_VALUE functie zal de eerste LiftWeight in de resultaatverzameling teruggeven.
Deze LAST_VALUE functie zal de laatste LiftWeight in de resultaatverzameling teruggeven.
***WAARSCHUWING: zonder de ROWS BETWEEN in de LAST_VALUE, kunt u onverwachte resultaten krijgen.***
*/
, FIRST_VALUE(LiftWeight) OVER (ORDER BY LiftDate) AS FirstLift
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* SUM using ROWS BETWEEN will narrow the scope evaluated by the window function.
De functie zal beginnen en eindigen waar de ROWS BETWEEN specificeren.
De eerste SUM zal alle LiftWeight waarden optellen in rijen tot en met de huidige rij.
De tweede SUM zal alle LiftWeight waarden optellen in rijen tussen de huidige rij en de 3 rijen ervoor.
*/
, 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
|
Overwegingen voor vensterfuncties
Als u geen SQL Server 2012 of later hebt, is uw kast met vensterfuncties behoorlijk kaal; SQL Server 2005 tot en met 2008 R2 stonden alleen PARTITION BY in de OVER-clausule van een aggregaatfunctie toe, en u kreeg RANK() en ROW_NUMBER(). Dat was het wel zo’n beetje. Als u een ontwikkelaar bent die nog met een van deze eerdere versies werkt, is dit een overtuigend argument om over te stappen naar 2012 of later. Bedenk hoeveel tijd je kunt besparen door niet meerdere CTE’s te schrijven en hoeveel sneller je queries zullen gaan.
Over snel gesproken…
Door het vermijden van round-trips naar de server voor dezelfde gegevens, verminderen we de I/O op die tabellen. Als we indexen gebruiken, kunnen we het aantal lezingen verminderen. Er is een wisselwerking, maar het is meestal een zeer gunstige. Window functies vereisen dat SQL Server het venster construeert en de functie berekent (weergegeven als taken zoals Window Spool, Segment, Sequence Project, en Compute Scalar). Door dit te doen, voegt het gegevens toe aan de werklijst. Toch is dit over het algemeen minder duur dan meerdere keren terug te gaan om de brongegevens op te halen, indien nodig te aggregeren, en alles samen te voegen. Bovendien, Worktable bestaat in tempdb, die – idealiter – op je snelste storage tier staat.
Ten slotte, onthoud dat de beperkingen die je op een window functie zet – PARTITION BY, ORDER BY, of ROWS BETWEEN – er zijn om context toe te passen op de window functie en op geen enkele manier van toepassing zijn op de resultatenset als een geheel. Met andere woorden, je SELECT-instructie wordt niet beïnvloed door wat je een window-functie opdraagt te doen.