Vinduesfunktioner (eller vinduesfunktioner) er en fantastisk måde at få forskellige perspektiver på et sæt data på uden at skulle foretage gentagne opkald til serveren for at få fat i disse data. Vi kan f.eks. samle summen af en kolonne og vise den side om side med data på detaljeniveau, således at “SalesAmount” og “SUM(SalesAmount)” kan vises i den samme række. Vi kan også lave analytiske funktioner som PERCENT_RANK og rangordningsfunktioner som ROW_NUMBER, alt sammen uden at ændre granulariteten af resultatmængden eller foretage yderligere ture for at hente de samme kildedata igen og igen.
“Se, hvordan jeg ubesværet balancerer to af Val Kilders nyresten. Quite bully!”
Vinduesfunktioner bruger alle OVER()-klausulen, som bruges til at definere, hvordan funktionen evalueres. OVER()-klausulen accepterer tre forskellige argumenter:
- PARTITION BY:
- ORDER BY: Nulstiller tælleren, hver gang den/de angivne kolonne(r) ændrer værdier.
- ORDER BY: Ordner de rækker, som funktionen skal evaluere. Dette ordner ikke hele resultatmængden, men kun den måde, hvorpå funktionen fortsætter gennem rækkerne.
- ROWS BETWEEN: Angiver, hvordan de rækker, der evalueres af funktionen, skal begrænses yderligere.
Lad os lade som om, vi ser på forenklede data fra en vægtløftningskonkurrence. Her er nogle kodeeksempler (vi laver dem alle i én SELECT-anvisning, fordi tilføjelse/fjernelse af vinduesfunktioner på ingen måde ændrer antallet af rækker, vi får tilbage):
|
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 viser nummeret på rækken, ordnet efter LiftID.
Tælleren nulstilles ved hver ny kombination af LiftDate og LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM vil lægge de løftede vægte sammen.
Den første SUM viser den samlede sum for hele resultatmængden.
Den anden SUM viser den samlede løftevægt for den pågældende rækkes løftedato.
Den tredje SUM viser den samlede løftevægt for den pågældende rækkes løbedato og person. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG viser den gennemsnitlige vægt, der er løftet.
Den første AVG viser den gennemsnitlige løftevægt for den pågældende rækkes løftedato.
Den anden AVG viser den gennemsnitlige løftevægt for den pågældende rækkes løftedato og person. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG og LEAD gør det muligt for den aktuelle række at rapportere om data i rækker bagved eller foran den.
Denne LAG-funktion returnerer LiftWeight fra 1 række bag den (i rækkefølge efter LiftID), og hvis der ikke findes nogen værdi, returnerer den 0 i stedet for NULL.
LEAD-funktionen henter LiftWeight fra 3 rækker foran. Da vi ikke har angivet den valgfrie standardværdi (ligesom “0”, som vi gav LAG-funktionen), vil den returnere NULL, hvis der ikke findes nogen række 3 rækker foran. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE OG LAST_VALUE returnerer den angivne kolonnes første og sidste værdi i resultatmængden.
Denne FIRST_VALUE-funktion returnerer den første LiftWeight i resultatmængden.
Denne LAST_VALUE-funktion returnerer den sidste LiftWeight i resultatmængden.
***WARNING: uden ROWS BETWEEN i LAST_VALUE kan du få uventede resultater.***
*/
, 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 ved hjælp af ROWS BETWEEN indskrænker det område, der evalueres af vinduesfunktionen.
Funktionen begynder og slutter der, hvor ROWS BETWEEN angiver.
Den første SUM vil tilføje alle LiftWeight-værdierne i rækker op til og inklusive den aktuelle række.
Den anden SUM vil tilføje alle LiftWeight-værdierne i rækker mellem den aktuelle række og de 3 rækker før den.
*/
, 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
|
Overvejelser for vinduesfunktioner
Hvis du ikke har SQL Server 2012 eller nyere, er dit vinduesfunktionsskab temmelig tomt; SQL Server 2005 til 2008 R2 tillod kun PARTITION BY i OVER-klausulen i en aggregatfunktion, og du fik RANK() og ROW_NUMBER(). Det var det hele. Hvis du er en udvikler, der stadig bruger en af disse tidligere versioner, er dette et overbevisende argument for at flytte til 2012 eller nyere. Tænk på, hvor meget tid du kan spare ved ikke at skrive flere CTE’er, og hvor meget hurtigere dine forespørgsler vil gå.
Apropos hurtigt…
Da vi undgår rundrejser til serveren for de samme data, reducerer vi I/O på disse tabeller. Hvis vi rammer indekser, kan vi virkelig skære ned på de involverede læsninger. Der er en afvejning, men det er normalt en meget gunstig en. Vinduesfunktioner kræver, at SQL Server konstruerer vinduet og beregner funktionen (vist som opgaver som Window Spool, Segment, Sequence Project og Compute Scalar). Ved at gøre dette tilføjer den læsninger til arbejdstabellen. Alligevel er dette generelt mindre dyrt end at gå tilbage for at hente kildedataene flere gange, aggregering om nødvendigt og sammenføje det hele. Desuden findes Worktable i tempdb, som – ideelt set – befinder sig på dit hurtigste lagringsniveau.
Husk endelig, at de begrænsninger, du sætter på en vinduesfunktion – PARTITION BY, ORDER BY eller ROWS BETWEEN – er der for at anvende kontekst på vinduesfunktionen og gælder på ingen måde for resultatmængden som helhed. Med andre ord vil din SELECT-angivelse ikke blive påvirket af noget som helst, du fortæller en vinduesfunktion, at den skal gøre.