Az ablak (vagy Windowing) függvények nagyszerű módja annak, hogy különböző nézőpontokat kapjunk egy adathalmazra anélkül, hogy ismételten meg kellene hívnunk a kiszolgálót az adatokért. Például összegyűjthetjük egy oszlop összegét, és megjeleníthetjük egymás mellett a részletszintű adatokkal, így az “SalesAmount” és a “SUM(SalesAmount)” megjelenhet ugyanabban a sorban. Olyan analitikus függvényeket is elvégezhetünk, mint a PERCENT_RANK és rangsoroló függvényeket, mint a ROW_NUMBER, mindezt anélkül, hogy megváltoztatnánk az eredményhalmaz szemcsézettségét, vagy további utakat kellene tennünk, hogy újra és újra megszerezzük ugyanazokat a forrásadatokat.
“Nézze, ahogy könnyedén egyensúlyozom Val Kilmer két vesekövét. Quite bully!”
Az ablakfüggvények mindegyike használja az OVER() záradékot, amely meghatározza a függvény kiértékelésének módját. Az OVER() záradék három különböző argumentumot fogad el:
- PARTITION BY: Visszaállítja a számlálóját minden alkalommal, amikor a megadott oszlop(ok) értéke megváltozik.
- ORDER BY: Rendezi a sorokat, amelyeket a függvény kiértékel. Ez nem rendezi a teljes eredményhalmazt, csak azt, ahogyan a függvény végigmegy a sorokon.
- ROWS BETWEEN: Megadja, hogyan korlátozza tovább a függvény által kiértékelt sorokat.
Tegyük fel, hogy egy súlyemelőverseny egyszerűsített adatait nézzük. Íme néhány kódpélda (mindegyiket egy SELECT utasításban végezzük el, mert az ablakfüggvények hozzáadása/eltávolítása semmilyen módon nem változtatja meg a visszakapott sorok számát):
|
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 a sor számát fogja listázni, LiftID szerint rendezve.
A számláló a LiftDate és a LiftPersonID minden új kombinációjával visszaáll */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM összeadja az emelt súlyokat.
Az első SUM megmutatja a teljes eredményhalmaz végösszegét.
A második SUM megmutatja az adott sor emelési dátumához tartozó összes emelési súlyt.
A harmadik SUM az adott sor felvonási dátumához és személyéhez tartozó teljes felvonósúlyt mutatja. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG az átlagos emelt súlyt mutatja.
Az első AVG az átlagos emelési súlyt mutatja az adott sor emelési dátumához.
A második AVG az adott sor emelési dátumának és személyének átlagos emelési súlyát mutatja. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG és LEAD lehetővé teszi, hogy az aktuális sor a mögötte vagy előtte lévő sorok adataira is jelentsen.
Ez a LAG függvény az 1 sorral mögötte lévő LiftWeight-ot adja vissza (a LiftID sorrendjében), és ha nem talál értéket, akkor NULL helyett 0-t ad vissza.
A LEAD függvény a 3 sorral előtte lévő LiftWeight-ot kapja meg. Mivel nem adtuk meg az opcionális alapértelmezett értéket (mint a “0”, amit a LAG függvénynek adtunk), NULL-t fog visszaadni, ha nincs 3 sorral előrébb lévő sor. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE ÉS LAST_VALUE a megadott oszlop első és utolsó értékét adja vissza az eredményhalmazban.
Ez a FIRST_VALUE függvény az eredményhalmaz első LiftWeight értékét adja vissza.
Ez a LAST_VALUE függvény az eredményhalmaz utolsó LiftWeight értékét adja vissza.
***FIGYELEM: a ROWS BETWEEN nélkül a LAST_VALUE függvényben nem várt eredményeket kaphat.***
*/
, 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
/* A ROWS BETWEEN-t használó SUM szűkíti az ablakfüggvény által kiértékelt hatókörét.
A függvény ott kezdődik és ott végződik, ahol a ROWS BETWEEN megadja.
Az első SUM összeadja az összes LiftWeight értéket a sorokban az aktuális sorig bezárólag.
A második SUM összeadja az összes LiftWeight értéket a sorokban az aktuális sor és az azt megelőző 3 sor között.
*/
, 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
|
Az ablakfüggvényekkel kapcsolatos megfontolások
Ha nem rendelkezik SQL Server 2012 vagy újabb SQL Serverrel, az ablakfüggvények szekrénye eléggé csupasz; az SQL Server 2005-től 2008 R2-ig csak a PARTITION BY-t engedélyezte az összesítő függvény OVER záradékában, és csak RANK() és ROW_NUMBER() volt. Nagyjából ennyi volt. Ha Ön olyan fejlesztő, aki még mindig a korábbi verziók valamelyikét használja, akkor ez egy meggyőző érv a 2012-es vagy újabb verzióra való áttérés mellett. Gondoljon bele, mennyi időt takaríthat meg azzal, hogy nem kell több CTE-t írnia, és mennyivel gyorsabbak lesznek a lekérdezései.
Apropó gyors…
Azzal, hogy elkerüljük a szerverre történő körutazásokat ugyanazon adatokért, csökkentjük a táblák I/O-ját. Ha indexeket támadunk meg, akkor valóban csökkenthetjük az ezzel járó olvasásokat. Van egy kompromisszum, de ez általában nagyon kedvező. Az ablakfüggvények megkövetelik, hogy az SQL Server felépítse az ablakot és kiszámítsa a függvényt (ez olyan feladatokként jelenik meg, mint az Ablak-tekercs, a Szegmens, a Sorozatprojekt és a Skála kiszámítása). Ennek során olvasásokat ad hozzá a Munkatáblához. Mégis, ez általában kevésbé költséges, mintha többször visszamennénk a forrásadatokért, szükség esetén aggregálnánk, és mindezt összekapcsolnánk. Emellett a Worktable a tempdb-ben létezik, amely – ideális esetben – a leggyorsabb tárolási szinten van.
Végezetül ne feledje, hogy az ablakfüggvényre tett korlátozások – PARTITION BY, ORDER BY vagy ROWS BETWEEN – azért vannak, hogy az ablakfüggvényre kontextust alkalmazzanak, és semmiképpen sem vonatkoznak az eredményhalmaz egészére. Más szóval, a SELECT utasításodat nem befolyásolja semmi, amit az ablakfüggvénynek mondasz.