Funcțiile de fereastră (sau Windowing) sunt o modalitate excelentă de a obține perspective diferite asupra unui set de date fără a fi nevoie să efectuați apeluri repetate la server pentru acele date. De exemplu, putem aduna suma unei coloane și o putem afișa alături de datele la nivel de detaliu, astfel încât „SalesAmount” și „SUM(SalesAmount)” pot apărea în același rând. De asemenea, putem efectua funcții analitice precum PERCENT_RANK și funcții de clasificare precum ROW_NUMBER, toate acestea fără a modifica granularitatea setului de rezultate sau fără a face drumuri suplimentare pentru a obține din nou și din nou aceleași date sursă.
„Priviți cum echilibrez fără efort două dintre pietrele la rinichi ale lui Val Kilmer. Destul de bulversant!”
Funcțiile de fereastră folosesc toate clauza OVER(), care este utilizată pentru a defini modul în care este evaluată funcția. Clauza OVER() acceptă trei argumente diferite:
- PARTITION BY: Își resetează contorul de fiecare dată când coloana (coloanele) declarată (declarate) își schimbă valorile.
- ORDER BY: Ordonează rândurile pe care funcția le va evalua. Aceasta nu ordonează întregul set de rezultate, ci doar modul în care funcția trece prin rânduri.
- ROWS BETWEEN: Specifică modul în care se limitează în continuare rândurile evaluate de funcție.
Să ne imaginăm că analizăm date simplificate de la o competiție de ridicare a greutăților. Iată câteva exemple de cod (le vom face pe toate într-o singură instrucțiune SELECT, deoarece adăugarea/eliminarea funcțiilor de fereastră nu modifică în niciun fel numărul de rânduri pe care le primim înapoi):
|
1
2
3
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 va enumera numărul rândului, ordonat după LiftID.
Contorul se va reseta cu fiecare nouă combinație de LiftDate și LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM va însuma greutățile ridicate.
Primul SUM va afișa totalul general pentru întregul set de rezultate.
Al doilea SUM va afișa greutatea totală de ridicare pentru data de ridicare a acelui rând.
Al treilea SUM va arăta greutatea totală a ridicării pentru data ridicării și persoana din acel rând. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG va arăta greutatea medie ridicată.
Primul AVG va arăta greutatea medie de ridicare pentru data de ridicare a acelui rând.
Al doilea AVG va arăta greutatea medie de ridicare pentru data de ridicare a rândului respectiv și pentru persoana respectivă. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG și LEAD permit rândului curent să raporteze cu privire la datele din rândurile din spatele sau din fața sa.
Această funcție LAG va returna LiftWeight din 1 rând din spatele său (în ordinea LiftID) și, dacă nu se găsește nicio valoare, va returna 0 în loc de NULL.
Funcția LEAD va obține LiftWeight din 3 rânduri înainte. Deoarece nu am specificat valoarea implicită opțională (ca și „0” pe care am dat-o funcției LAG, aceasta va returna NULL dacă nu există un rând cu 3 rânduri înainte. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE va returna prima și ultima valoare a coloanei specificate în setul de rezultate.
Această funcție FIRST_VALUE va returna primul LiftWeight din setul de rezultate.
Această funcție LAST_VALUE va returna ultimul LiftWeight din setul de rezultate.
***Atenție: fără ROWS BETWEEN în LAST_VALUE, este posibil să obțineți rezultate neașteptate.***
*/
, 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.
Funcția va începe și se va termina acolo unde se specifică ROWS BETWEEN.
Prima SUM va adăuga toate valorile LiftWeight din rândurile până la rândul curent inclusiv.
A doua SUM va adăuga toate valorile LiftWeight din rândurile dintre rândul curent și cele 3 rânduri anterioare acestuia.
*/
, 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
|
Considerații pentru funcțiile de fereastră
Dacă nu aveți SQL Server 2012 sau o versiune ulterioară, dulapul cu funcții de fereastră este destul de gol; SQL Server 2005 până în 2008 R2 permitea doar PARTITION BY în clauza OVER a unei funcții agregate și ați primit RANK() și ROW_NUMBER(). Cam asta a fost tot. Dacă sunteți un dezvoltator aflat încă pe una dintre aceste versiuni anterioare, acesta este un argument convingător pentru a trece la 2012 sau la o versiune ulterioară. Gândiți-vă cât de mult timp ați putea economisi dacă nu ați scrie mai multe CTE-uri și cât de repede vor merge interogările dvs.
Vorbind de rapiditate…
Evitând călătoriile dus-întors către server pentru aceleași date, reducem I/O pe acele tabele. Dacă atingem indicii, putem reduce cu adevărat citirile implicate. Există un compromis, dar de obicei este unul foarte favorabil. Funcțiile de fereastră necesită ca SQL Server să construiască fereastra și să calculeze funcția (prezentate ca sarcini precum Window Spool, Segment, Sequence Project și Compute Scalar). Făcând acest lucru, acesta adaugă citiri la Worktable. Totuși, acest lucru este, în general, mai puțin costisitor decât să te întorci pentru a obține datele sursă de mai multe ori, să le agreghezi, dacă este necesar, și să le unești pe toate. De asemenea, Worktable există în tempdb, care – în mod ideal – se află pe cel mai rapid nivel de stocare.
În cele din urmă, amintiți-vă că limitările pe care le puneți pe o funcție de fereastră – PARTITION BY, ORDER BY sau ROWS BETWEEN – sunt acolo pentru a aplica contextul funcției de fereastră și nu se aplică în niciun caz setului de rezultate ca întreg. Cu alte cuvinte, instrucțiunea SELECT nu va fi afectată de nimic din ceea ce îi spuneți unei funcții fereastră să facă.
.