Funkcje okna (lub Windowing) są świetnym sposobem na uzyskanie różnych perspektyw na zestaw danych bez konieczności powtarzania wywołań do serwera w celu uzyskania tych danych. Na przykład, możemy zebrać sumę kolumny i wyświetlić ją obok siebie z danymi na poziomie szczegółowym, tak że „SalesAmount” i „SUM(SalesAmount)” mogą pojawić się w tym samym wierszu. Możemy również wykonywać funkcje analityczne, takie jak PERCENT_RANK i funkcje rankingowe, takie jak ROW_NUMBER, a wszystko to bez zmiany ziarnistości zestawu wyników lub dodatkowych podróży w celu uzyskania tych samych danych źródłowych ponownie i ponownie.
„Patrz, jak bez wysiłku równoważę dwa kamienie nerkowe Vala Kilmera. Całkiem niezłe!”
Wszystkie funkcje okna używają klauzuli OVER(), która jest używana do określenia sposobu obliczania funkcji. Klauzula OVER() przyjmuje trzy różne argumenty:
- PARTITION BY: Resetuje swój licznik za każdym razem, gdy określona kolumna (kolumny) zmienia wartości.
- ORDER BY: Porządkuje wiersze, które zostaną ocenione przez funkcję. Nie jest to zamówienie całego zestawu wyników, a jedynie sposób, w jaki funkcja przechodzi przez wiersze.
- ROWS BETWEEN: Określa sposób dalszego ograniczenia wierszy ocenianych przez funkcję.
Upozorujmy, że patrzymy na uproszczone dane z zawodów w podnoszeniu ciężarów. Oto kilka przykładów kodu (wykonamy je wszystkie w jednej instrukcji SELECT, ponieważ dodawanie/usuwanie funkcji okna w żaden sposób nie zmienia liczby wierszy, które otrzymamy z powrotem):
|
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 wyświetli numer wiersza, uporządkowany według LiftID.
Licznik będzie resetowany przy każdej nowej kombinacji LiftDate i LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM zsumuje podniesione ciężary.
Pierwsze SUM pokaże sumę całkowitą dla całego zestawu wyników.
Drugie SUM pokaże całkowitą wagę podniesioną dla daty podniesienia w tym wierszu.
Trzecie SUM pokaże całkowitą wagę wyciągu dla daty wyciągu i osoby w tym wierszu. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG pokaże średnią podniesioną wagę.
Pierwszy AVG pokaże średnią wagę podniesioną dla daty podniesienia w tym wierszu.
Drugie AVG pokaże średnią wagę podniesienia dla daty podniesienia tego wiersza i osoby. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG i LEAD pozwalają bieżącemu wierszowi na raportowanie danych w wierszach przed lub za nim.
Funkcja LAG zwróci LiftWeight z 1 wiersza za nim (w kolejności LiftID) i jeśli nie zostanie znaleziona żadna wartość, zwróci 0 zamiast NULL.
Funkcja LEAD pobierze LiftWeight z 3 wierszy przed nim. Ponieważ nie określiliśmy opcjonalnej wartości domyślnej (jak „0”, które daliśmy funkcji LAG, zwróci ona NULL, jeśli nie ma żadnego wiersza 3 rzędy przed nami. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE I LAST_VALUE zwrócą pierwszą i ostatnią wartość określonej kolumny w zbiorze wyników.
Ta funkcja FIRST_VALUE zwróci pierwszą wartość LiftWeight w zbiorze wyników.
Ta funkcja LAST_VALUE zwróci ostatnią wartość LiftWeight w zbiorze wyników.
***UWAGA: bez ROWS BETWEEN w LAST_VALUE, możesz otrzymać nieoczekiwane wyniki.***
*/
, 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 używając ROWS BETWEEN zawęzi zakres oceniany przez funkcję okna.
Funkcja będzie zaczynać się i kończyć w miejscu określonym przez ROWS BETWEEN.
Pierwsza SUM doda wszystkie wartości LiftWeight w wierszach do bieżącego wiersza włącznie.
Druga SUM doda wszystkie wartości LiftWeight w wierszach pomiędzy bieżącym wierszem a 3 wierszami przed nim.
*/
, 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
|
Rozważania dotyczące funkcji okienkowych
Jeśli nie masz SQL Server 2012 lub nowszego, twoja szafka z funkcjami okienkowymi jest całkiem pusta; SQL Server 2005 do 2008 R2 pozwalał tylko na PARTITION BY w klauzuli OVER funkcji agregującej, a ty miałeś RANK() i ROW_NUMBER(). To było wszystko. Jeśli jesteś programistą wciąż na jednej z tych wcześniejszych wersji, jest to przekonujący argument za przejściem na 2012 lub nowszą wersję. Pomyśl, ile czasu możesz zaoszczędzić, nie pisząc wielu CTE i o ile szybciej pójdą twoje zapytania.
Mówiąc o szybkości…
Unikając wycieczek do serwera po te same dane, zmniejszamy I/O na tych tabelach. Jeśli uderzamy w indeksy, możemy naprawdę zmniejszyć liczbę zaangażowanych odczytów. Jest to pewien kompromis, ale zazwyczaj jest on bardzo korzystny. Funkcje okienkowe wymagają, aby SQL Server skonstruował okno i obliczył funkcję (zadania takie jak Window Spool, Segment, Sequence Project i Compute Scalar). Robiąc to, dodaje odczyty do tabeli roboczej. Jest to jednak mniej kosztowne niż wielokrotne pobieranie danych źródłowych, agregowanie ich w razie potrzeby i łączenie w całość. Ponadto Worktable istnieje w tempdb, która – najlepiej – znajduje się na najszybszym poziomie pamięci masowej.
Na koniec pamiętaj, że ograniczenia, które nakładasz na funkcję okna – PARTITION BY, ORDER BY lub ROWS BETWEEN – są po to, aby zastosować kontekst do funkcji okna i w żaden sposób nie odnoszą się do zestawu wyników jako całości. Innymi słowy, na twoją instrukcję SELECT nie będzie miało wpływu nic, co każesz zrobić funkcji okna.