Okenní funkce (neboli Windowing) jsou skvělým způsobem, jak získat různé pohledy na sadu dat, aniž by bylo nutné tato data opakovaně volat na server. Můžeme například shromáždit součet sloupce a zobrazit jej vedle dat na úrovni detailů, takže se v jednom řádku může objevit „SalesAmount“ a „SUM(SalesAmount)“. Můžeme také provádět analytické funkce, jako je PERCENT_RANK, a funkce řazení, jako je ROW_NUMBER, a to vše bez nutnosti měnit granularitu výsledné sady nebo provádět další cesty za stejnými zdrojovými daty znovu a znovu.
„Sledujte, jak bez námahy vyvažuju dva ledvinové kameny Vala Kilmera. Docela šikana!“
Všechny okenní funkce používají klauzuli OVER(), která definuje způsob vyhodnocení funkce. Klauzule OVER() přijímá tři různé argumenty:
- PARTITION BY:
- ORDER BY: Vynuluje svůj čítač pokaždé, když se změní hodnota uvedeného sloupce (sloupců): Uspořádá řádky, které funkce vyhodnotí. Neuspořádá celou množinu výsledků, pouze způsob, jakým funkce postupuje po řádcích.
- ROWS BETWEEN: Určuje způsob dalšího omezení řádků vyhodnocovaných funkcí.
Představme si, že se díváme na zjednodušená data ze soutěže ve vzpírání. Zde je několik příkladů kódu (všechny provedeme v jednom příkazu SELECT, protože přidání/odebrání okenních funkcí nijak nemění počet řádků, které dostaneme zpě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 vypíše číslo řádku seřazené podle LiftID.
Počítadlo se vynuluje s každou novou kombinací LiftDate a LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM sečte zvednuté váhy.
První SUM zobrazí celkový součet za celou sadu výsledků.
Druhý SUM zobrazí celkovou váhu zdvihu pro datum zdvihu daného řádku.
Třetí SUM zobrazí celkovou hmotnost zdvihu pro datum a osobu daného řádku. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG zobrazí průměrnou zvednutou hmotnost.
První AVG zobrazí průměrnou zvednutou hmotnost pro datum zvednutí daného řádku.
Druhé AVG zobrazí průměrnou hmotnost zdvihu pro datum zdvihu a osobu daného řádku. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG a LEAD umožňují aktuálnímu řádku vykazovat data v řádcích za ním nebo před ním.
Tato funkce LAG vrátí LiftWeight z 1 řádku za ním (v pořadí podle LiftID) a pokud nenajde žádnou hodnotu, vrátí místo NULL 0.
Funkce LEAD získá LiftWeight ze 3 řádků před ním. Protože jsme nezadali nepovinnou výchozí hodnotu (stejně jako „0“, kterou jsme zadali funkci LAG, vrátí NULL, pokud není žádný řádek 3 řádky před námi. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE A LAST_VALUE vrátí první a poslední hodnotu zadaného sloupce v souboru výsledků.
Tato funkce FIRST_VALUE vrátí první hodnotu LiftWeight v souboru výsledků
Tato funkce LAST_VALUE vrátí poslední hodnotu LiftWeight v souboru výsledků.
***UPOZORNĚNÍ: bez ROWS BETWEEN v LAST_VALUE můžete získat neočekávané výsledky.***
*/
, 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 pomocí ROWS BETWEEN zúží rozsah vyhodnocovaný funkcí okna.
Funkce bude začínat a končit tam, kde je uvedeno ROWS BETWEEN.
První SUMA sečte všechny hodnoty LiftWeight v řádcích až do aktuálního řádku včetně.
Druhá SUMA sečte všechny hodnoty LiftWeight v řádcích mezi aktuálním řádkem a 3 řádky před ním.
*/
, 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
|
Pozor na okenní funkce
Pokud nemáte SQL Server 2012 nebo novější, je vaše skříňka s okenními funkcemi dost prázdná; SQL Server 2005 až 2008 R2 umožňoval v klauzuli OVER agregační funkce pouze PARTITION BY a vy jste dostali RANK() a ROW_NUMBER(). To bylo asi tak všechno. Pokud jste vývojáři, kteří stále používají některou z těchto starších verzí, je to pádný důvod pro přechod na verzi 2012 nebo novější. Pomyslete na to, kolik času můžete ušetřit tím, že nebudete psát několik CTE, a o kolik budou vaše dotazy rychlejší.
Když už mluvíme o rychlosti…
Pokud se vyhneme obcházení serveru kvůli stejným datům, snížíme I/O v těchto tabulkách. Pokud zasahujeme do indexů, můžeme skutečně zkrátit související čtení. Je to určitý kompromis, ale obvykle velmi příznivý. Okenní funkce vyžadují, aby SQL Server zkonstruoval okno a vypočítal funkci (zobrazují se jako úlohy Window Spool, Segment, Sequence Project a Compute Scalar). Přitom přidává čtení do Worktable. Přesto je to obecně méně nákladné než se několikrát vrátit pro zdrojová data, v případě potřeby je agregovat a vše spojit. Navíc Worktable existuje v tempdb, které je – v ideálním případě – na vaší nejrychlejší úrovni úložiště.
Nakonec nezapomeňte, že omezení, která kladete na okenní funkci – PARTITION BY, ORDER BY nebo ROWS BETWEEN – slouží k použití kontextu okenní funkce a v žádném případě se nevztahují na množinu výsledků jako celek. Jinými slovy, váš příkaz SELECT nebude ovlivněn ničím, co okenní funkci přikážete provést.