Fönsterfunktioner är ett utmärkt sätt att få olika perspektiv på en uppsättning data utan att behöva ringa upprepade samtal till servern för att få fram dessa data. Vi kan till exempel samla summan av en kolumn och visa den sida vid sida med data på detaljnivå, så att ”SalesAmount” och ”SUM(SalesAmount)” kan visas i samma rad. Vi kan också göra analytiska funktioner som PERCENT_RANK och rangordningsfunktioner som ROW_NUMBER, allt utan att ändra resultatuppsättningens granularitet eller göra ytterligare resor för att hämta samma källdata om och om igen.
”Titta på när jag utan ansträngning balanserar två av Val Kilmers njurstenar. Quite bully!”
Fönsterfunktioner använder alla klausulen OVER(), som används för att definiera hur funktionen utvärderas. Klausulen OVER() accepterar tre olika argument:
- PARTITION BY:
- ORDER BY: Återställer räknaren varje gång den angivna kolumnen eller kolumnerna ändrar värde: Ordnar de rader som funktionen ska utvärdera. Detta ordnar inte hela resultatmängden, utan endast hur funktionen går igenom raderna.
- ROWS BETWEEN: Anger hur man ytterligare begränsar de rader som utvärderas av funktionen.
Låt oss låtsas att vi tittar på förenklade data från en tävling i tyngdlyftning. Här är några kodexempel (vi gör dem alla i ett SELECT-meddelande eftersom tillägg/ borttagning av fönsterfunktioner inte på något sätt ändrar antalet rader som vi får tillbaka):
|
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 anger radnumret, ordnat efter LiftID.
Räknaren nollställs med varje ny kombination av LiftDate och LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM kommer att summera de lyftta vikterna.
Den första summan visar totalsumman för hela resultatuppsättningen.
Den andra summan visar den totala lyftvikten för den radens lyftdatum.
Den tredje summan visar den totala lyftvikten för radens lyftdatum och person. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG visar den genomsnittliga vikten som lyfts.
Den första AVG visar den genomsnittliga lyftvikten för den radens lyftdatum.
Den andra AVG visar den genomsnittliga lyftvikten för radens lyftdatum och person. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG och LEAD gör det möjligt för den aktuella raden att rapportera om data i rader bakom eller före den.
Den här LAG-funktionen returnerar LiftWeight från 1 rad bakom den (i ordning efter LiftID) och om inget värde hittas returnerar den 0 i stället för NULL.
LEAD-funktionen hämtar LiftWeight från 3 rader framåt. Eftersom vi inte specificerade det valfria standardvärdet (som ”0” som vi gav LAG-funktionen) kommer den att returnera NULL om det inte finns någon rad 3 rader framåt. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE returnerar den angivna kolumnens första och sista värde i resultatmängden.
Denna FIRST_VALUE-funktion returnerar den första lyftvikten i resultatmängden.
Denna LAST_VALUE-funktion returnerar den sista lyftvikten i resultatmängden.
***VARNING: utan ROWS BETWEEN i LAST_VALUE kan du få oväntade resultat.***
*/
, 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 med hjälp av ROWS BETWEEN kommer att begränsa det område som utvärderas av fönsterfunktionen.
Funktionen börjar och slutar där ROWS BETWEEN anger.
Den första SUMMEN adderar alla lyftviktsvärden i rader upp till och med den aktuella raden.
Den andra SUMMEN adderar alla lyftviktsvärden i rader mellan den aktuella raden och de tre raderna före 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
|
Observeringar för fönsterfunktioner
Om du inte har SQL Server 2012 eller senare är ditt fönsterfunktionsskåp ganska tomt; SQL Server 2005 till 2008 R2 tillät bara PARTITION BY i OVER-klausulen i en aggregeringsfunktion, och du fick RANK() och ROW_NUMBER(). Det var ungefär allt. Om du är en utvecklare som fortfarande använder en av dessa tidigare versioner är detta ett övertygande argument för att flytta till 2012 eller senare. Tänk på hur mycket tid du kan spara genom att inte skriva flera CTE:er och hur mycket snabbare dina frågor kommer att gå.
På tal om snabbt…
Då vi undviker rundresor till servern för att få samma data, minskar vi I/O på dessa tabeller. Om vi använder index kan vi verkligen minska antalet läsningar. Det finns en kompromiss, men den är vanligtvis mycket fördelaktig. Fönsterfunktioner kräver att SQL Server konstruerar fönstret och beräknar funktionen (visas som uppgifter som Window Spool, Segment, Sequence Project och Compute Scalar). När den gör det lägger den till läsningar i arbetsbordet. Ändå är detta i allmänhet mindre kostsamt än att gå tillbaka för att hämta källdata flera gånger, aggregera vid behov och sammanfoga det hela. Dessutom finns Worktable i tempdb, som – i bästa fall – finns på din snabbaste lagringsnivå.
För det sista ska du komma ihåg att de begränsningar som du sätter på en fönsterfunktion – PARTITION BY, ORDER BY eller ROWS BETWEEN – är till för att tillämpa kontexten för fönsterfunktionen och inte på något sätt tillämpas på resultatuppsättningen som helhet. Med andra ord påverkas ditt SELECT-meddelande inte av vad du säger åt en fönsterfunktion att göra.