Podobnie jak funkcje grupujące, funkcje okna służą do analizy danych i obliczeń. Różnica pomiędzy nimi polega na sposobie definiowania zbiorów wierszy z którymi dana funkcja ma pracować. Dla funkcji grupujących używamy pogrupowanych zapytań takich które porządkują wyniki w grupy, i dla każdej grupy jest wykonywana funkcja agregująca. Daje to jeden wiersz wynikowy dla każdej grupy. Używając funkcji okna definiuje się zbiór wierszy dla każdej funkcji. Wynik zwracany jest dla każdego wiersza i użytej funkcji.Zbiór wierszy dla funkcji okna definiuje się za pomocą klauzuli OVER. Funkcje okna dzielą się na trzy grupy w zależności od ich działania: agregujące, rankingu i przesunięcia.
Agregujące funkcje okna
Do grupy agregujących funkcji okna należą te same funkcjektórych używamy przy grupowaniu (np. SUM, COUNT, AVG, MIN, MAX). Jedną z zalet używania funkcji okna jest to, że w przeciwieństwie do zapytań grupujących, zapytania funkcji okna nie ukrywają detali, tzn zwracają wiersz wynikowy dla każdego wiersza biorącego udział w zapytaniu. Użycie klauzuli OVER powoduje zdefiniowanie zbiory wierszy który będzie użyty jako zbiór wejściowy funkcji okna a funkcja okna zostanie użyta dla każdego wiersza w zapytaniu. Klauzula OVER definiuje okno wierszy dla używanej funkcji. Okno to jest używane w stosunku do każdego wiersza zapytania. Używając pustych nawiasów po klauzuli OVER zostanie użyty cały bazowy zestaw wyników użytego zapytania. Np. wyrażenie SUM(val) OVER() wyliczy całkowitą liczbę wierszy w zapytaniu źródłowym. Można używać klauzuli dzielącej okno na partycje PARTITION BY. Np wyrażenie SUM(val) OVER(PARTITION BY custid) wyliczzy liczbę wszystkich wierszy dla klienta w danym wierszu. Poniżej przykład zapytania uzywającego tabeli Sales.OrderValues, zwracającego identyfikator klienta i zamówienia, wartość zamówienia i sumy wartości dla każdego klienta oraz sumę wszystkich wartości.
SELECT custid, orderid, val, SUM(val) OVER(PARTITION BY custid) AS custtotal, SUM(val) OVER() AS grandtotal FROM Sales.OrderValues WHERE custid IN (1,2);
custid orderid val custtotal grandtotal -------- --------- --- ------------ ------------- 1 10643 814.50 4273.00 1265793.22 1 10692 878.00 4273.00 1265793.22 1 10702 330.00 4273.00 1265793.22 1 10835 845.80 4273.00 1265793.22 1 10952 471.20 4273.00 1265793.22 1 11011 933.50 4273.00 1265793.22 2 10926 514.40 1402.95 1265793.22 2 10759 320.00 1402.95 1265793.22 2 10625 479.75 1402.95 1265793.22 2 10308 88.80 1402.95 1265793.22 Wartość kolumny z sumą całkowitą (grandtotal) jest taka sama dla wszystkich wierszy.Wartość kolumny z sumą klienta (custtotal) jest taka sama dla wszystkich wierszy z tym samym identyfikatorem klienta (custid). Funkcje okna posiadają jeszcze jedną użyteczną opcję filtrowania danych nazwaną framing (pol. kadrowanie). Ideą framing'u jest definiowanie kolejności wierszy użytych w partycji okna i wyodrębnienie ramki z wierszami pomiędzy dwoma ogranicznikami bazującej na kolejności wierszy. Ogramiczniki ramki definiuje się za pomocą specjalnej klauzuli. Klauzula wyodrębniająca ramkę danych wymaga użycia ORDER BY w klauzuli OVER. Definiując ramkę okna trzeba określić jednostkę(wiersze lub zakres) oraz ograniczniki. Dla jednostki wierszy (ROWS) definiujemy ograniczniki na jeden z trzech sposobów:
- UNBOUNDED PRECEDING lub FOLLOWING - co oznacza początek lub koniec partycji
- CURRENT ROW - co oznacza aktualny wiersz
- <n> ROWS PRECEDING lub FOLLOWING - co oznacza n wierszy przed lub po aktualnym wierszu
Jako przykład posłuży zapytanie na widoku Sales.OrderValues które zwróci sumę wartości od początku aktywności użytkownika aż do obecnego zamówienia. Do obliczenia wyniku trzeba użyć funkcji agregującej SUM, podzielić okno na partycje względem identyfikatora klienta (custid), posortować okno względem daty zamówienia (order date) i podzielić okno na ramki od początku partycji (UNBOUNDED PRECEDING) do aktualnego wiersza:
SELECT custid, orderid, orderdate, val, SUM(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runningtotal FROM Sales.OrderValues WHERE custid IN (1,2);
custid orderid orderdate val runningtotal -------- --------- ------------ ---- ---------------- 1 10643 2007-08-25 00:00:00.000 814.50 814.50 1 10692 2007-10-03 00:00:00.000 878.00 1692.50 1 10702 2007-10-13 00:00:00.000 330.00 2022.50 1 10835 2008-01-15 00:00:00.000 845.80 2868.30 1 10952 2008-03-16 00:00:00.000 471.20 3339.50 1 11011 2008-04-09 00:00:00.000 933.50 4273.00 2 10308 2006-09-18 00:00:00.000 88.80 88.80 2 10625 2007-08-08 00:00:00.000 479.75 568.55 2 10759 2007-11-28 00:00:00.000 320.00 888.55 2 10926 2008-03-04 00:00:00.000 514.40 1402.95 Dla użycia kadrowania można użyć skróconej notacji, zamiast: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; można użyć zapisu: ROWS UNBOUNDED PRECEDING. Użycie funkcji okna do wykonywania obliczeń takich jak sumy całkowite zwykle ma dużo lepszą wydajność niż używanie grupowania, podzapytań i łączenia wyników za pomocą JOIN. Funkcje okna są dobrze zoptymalizowane, szczególnie gdy używamy UNBOUNDED PRECEDING jako ogranicznik okna. Biorąc pod uwagę logikę przetwarzania zapytań, wynik zapytania jest wyliczony na poziomie fazy SELECT, po fazach FROM, WHERE, GROUP BY i HAVING. Ponieważ funkcje okna operują na wynikach zapytań to ich użycie jest możliwe tylko w w klauzulach SELECT i ORDER BY. Aby użyć wyniku funkcji okna w innej fazie należy posłużyć się wyrażeniem tablicowym w którym zapytanie używające funkcji okna będzie użyte w wewnętrznym zapytaniu a zapytanie zewnetrzne odwoła się do jego wyniku. Przykładem takiego zapytania jest wyfiltrowanie z ostatniego zapytania tylko tych wierszy które zwróciły całkowitą sumę mniejszą niż 1,000.00. Poniższe zapytanie realizuje takie filtrowanie z pomocą CTE:
;WITH RunningTotals AS ( SELECT custid, orderid, orderdate, val, SUM(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal FROM Sales.OrderValues WHERE custid IN (1,2) ) SELECT * FROM RunningTotals WHERE runningtotal < 1000.00;
custid orderid orderdate val runningtotal -------- --------- ------------ ---- ---------------- 1 10643 2007-08-25 00:00:00.000 814.50 814.50 2 10308 2006-09-18 00:00:00.000 88.80 88.80 2 10625 2007-08-08 00:00:00.000 479.75 568.55 2 10759 2007-11-28 00:00:00.000 320.00 888.55 Użycie zakresów (RANGE) do określenia wielkości ramki, bazując na standardzie SQL, pozwala na definiowanie ograniczników bazując na logicznym przesunięciu (offset) dla klucza sortującego aktualnego wiersza. Zakres ROWS używa ograniczników bazujących na fizycznym przesunięciu w stosunku do aktualnego wiersza. SQL Server 2012 implementuje użycie zakresu RANGE tylko w niewielkim stopniu, pozwalając na użycie tylko UNBOUNDED PRECEDING lub FOLLOWING i CURRENT ROW jako ograniczniki. Jedną różnicą między ROWS i RANGE jest to, że gdy używamy ich z tymi samymi ogranicznikami to ROWS nie bierze pod uwagę PEER'ów przy wyliczeniach (peer - wiersze powiązane kluczem sortowania). SQL Server znacznie lepiej obsługuje opcje ograniczenia ROWS niż RAGE. Użycie ROWS jest zwykle dużo bardziej wydajne dla tych samych ograniczników. Jeżeli używasz okien z sortowaniem ale bez użycia klauzuli kadrowania (frame), to domyślnie stosowane jest RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Funkcje rankingu
Używając rankingowych funkcji okna można nadać ocenę wierszom partycji wg ustalonej kolejności. Tak jak w pozostałych funkcjach okna jężeli partycje nie są zadeklarowane to domyślnie użyty zostanie cały zbiór wynikowy jako jedna partycja. Klauzula sortująca okna ORDER BY jest bardzo ważna. Funkcje rankingu nie dopuszczają użycia klauzuli ramek (frame). T-SQL wspiera następujące funkcje rankingu: ROW_NUMBER, RANK, DENSE_RANK i NTILE. Poniższe zapytanie demonstruje użycie tych funkcji.
SELECT custid, orderid, val, ROW_NUMBER() OVER(ORDER BY val) AS rownum, RANK() OVER(ORDER BY val) AS rnk, DENSE_RANK() OVER(ORDER BY val) AS densernk, NTILE(100) OVER(ORDER BY val) AS ntile100 FROM Sales.OrderValues WHERE custid in (1,2);
custid orderid val rownum rnk densernk ntile100 ------- ---------- ---- ---------- ---- ------------ ----------- 2 10308 88.80 1 1 1 1 2 10759 320.00 2 2 2 2 1 10702 330.00 3 3 3 3 1 10952 471.20 4 4 4 4 2 10625 479.75 5 5 5 5 2 10926 514.40 6 6 6 6 1 10643 814.50 7 7 7 7 1 10835 845.80 8 8 8 8 1 10692 878.00 9 9 9 9 1 11011 933.50 10 10 10 10 ROW_NUMBER jest funkcją okna która nadaje unikalny sekwencyjny numer każdemu wierszowi w partycji w kolejności wyznaczonej przez klauzulę ORDER BY startując od numeru 1. Ponieważ zapytanie nie posiada podziału na partycje to funkcja ROW_NUMBER nadała numer każdemu kolejnemu wierszowi wynikowemu w kolejności wzrastającej wartości (val). Jeżeli sortowanie w klauzuli ORDER BY nie daje wyniku unikalnego to funkcja ROW_NUMBER nie jest deterministyczna. Sytacja gdy dwa wiersze mają taką samą wartość klucza sortującego np val = 36, lecz wiersze mają dwa różne numery wierszy nadane przez funkcję. Dzieje sie tak dlatego, że funkcja musi nadać unikalny numer wiersza w partycji. Jeżeli chcemy aby wynik był deterministyczny to musimy dodać kolumny sortujące gwarantujące unikalność np klucz główny. RANK i DENSE_RANK nadają tę samą ocenę takim samym wartościom klucza sortującego. Funkcja RANK zwraca liczbę wierszy w partycji które mają mniejszą wartość klucza sortującego plus jeden. DANSE_RANK zwraca numer unikalnej wartości klucza sortującego plus jeden. Gdy używamy unikalnego klucza sortującego to obie te funkcje zwrócą taki sam wynik jak funkcja ROW_NUMBER. NTILE dzieli partycję na podaną jako parametr wejściowy liczbę części bazujących na podanej w klauzuli ORDER BY kolejności. Zapytanie które ma 830 wierszy i zostanie podzielone funkcją NTILE(100) na sto części zostanie podzielone na 30 części po 9 wierszy i 70 części po 8 wierszy ponieważ 830/100=8 i jako reszta zostaje 30. Dla pierwszych dziewięciu wierszy funkcja wyliczy wartość 1, dla następnych dziewięciu wartość 2 i tak, aż do 100. Dla nieunikalnego klucza sortującego funkcja NTILE jest niedeterministyczna. Ważną rzeczą do zapamiętania jest różnica pomiędzy kolejnością sortowania wyników a sortowaniem okna. Powyższe zapytanie nie ma klauzuli ORDER BY i dlatego nie ma żadnej narzuconej kolejności wyświetlania wyników. Sortowanie w oknie narzuca tylko kolejność rozpatrywania wierszy przez funkcję okna.
Funkcje przesunięcia
Funkcje okna służące do przesunięcia zwracają wiersz który jest wynikiem przesunięcia o określoną liczbę od wiersza bieżącego w aktualnie przetwarzanej partycji okna lub pierwszy albo ostatni wiersz w ramce okna. T-SQL wspiera następujące funkcje przesunięcia: LAG, LEAD, FIRST_VALUE i LAST_VALUE. Funkcje LAG i LEAD operują na przesunięciu w stosunku do aktualnego wiersza partycji. Funkcje FIRST_VALUE i LAST_VALUE operują na pierwszym i ostatnim wierszu ramki. Lag i LEAD używają partycji okna i klauzuli sortowania okna. Nie używają klauzuli kadrującej (frame). Funkcja LAG zwraca element z wiersza w aktualnej partycji który jest żądaną ilość wcześniej od aktualnego wg klucza sortującego. Domyślnym przesunięciem jest 1. LEAD zwraca element z wiersza który jest dalej niż aktualny wiersz partycji. Ponizszy przykład prezentuje użycie funkcji LAG i LEAD. Porównuje on dla każdego zamówienia wartość z wartością poprzedniego i następnego zamówienia.
SELECT custid, orderid, orderdate, val, LAG(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS prev_val, LEAD(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS next_val FROM Sales.OrderValues WHERE custid IN (1,2);
custid orderid orderdate val prev_val next_val -------- --------- ------------- --- ----------- ------------ 1 10643 2007-08-25 00:00:00.000 814.50 NULL 878.00 1 10692 2007-10-03 00:00:00.000 878.00 814.50 330.00 1 10702 2007-10-13 00:00:00.000 330.00 878.00 845.80 1 10835 2008-01-15 00:00:00.000 845.80 330.00 471.20 1 10952 2008-03-16 00:00:00.000 471.20 845.80 933.50 1 11011 2008-04-09 00:00:00.000 933.50 471.20 NULL 2 10308 2006-09-18 00:00:00.000 88.80 NULL 479.75 2 10625 2007-08-08 00:00:00.000 479.75 88.80 320.00 2 10759 2007-11-28 00:00:00.000 320.00 479.75 514.40 2 10926 2008-03-04 00:00:00.000 514.40 320.00 NULL Ponieważ wartość przesunięcia dla obu funkcji nie została zadeklarowana to obie używają domyślnie wartości 1. Wartość przesunięcia deklarujemy jako drugi argument funkcji np LAG(val, 3). Jeżeli żądany wiersz nie istnieje to stosowana jest wartość zastępcza NULL jako wynik działania funkcji. Jężeli chcemy użyć innej wartości zamiast NULL to deklarujemy ją jako trzeci argument funkcji np. LAG(val, 3, 0) . Fukcje FIRST_VALUE i LAST_VALUE zwracają wartości z pierwszego lub ostatniego wiersza ramki (frame). Funkcje te mogą używać partycjonowania, sortowania i klauzuli kadrowania. Przykładem jest zapytanie które zwraca wartość pierwszego i ostatniego zamówienie każdego klienta.
SELECT custid, orderid, orderdate, val, FIRST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_val, LAST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_val FROM Sales.OrderValues WHERE custid IN (1,2);
custid orderid orderdate val first_val last_val -------- --------- ------------ ---- ----------- ---------- 1 11011 2008-04-09 00:00:00.000 933.50 814.50 933.50 1 10952 2008-03-16 00:00:00.000 471.20 814.50 933.50 1 10835 2008-01-15 00:00:00.000 845.80 814.50 933.50 1 10702 2007-10-13 00:00:00.000 330.00 814.50 933.50 1 10692 2007-10-03 00:00:00.000 878.00 814.50 933.50 1 10643 2007-08-25 00:00:00.000 814.50 814.50 933.50 2 10926 2008-03-04 00:00:00.000 514.40 88.80 514.40 2 10759 2007-11-28 00:00:00.000 320.00 88.80 514.40 2 10625 2007-08-08 00:00:00.000 479.75 88.80 514.40 2 10308 2006-09-18 00:00:00.000 88.80 88.80 514.40 Gdy ramka jest używana w funkcji okna ale nie jest ona zadeklarowana to domyślnie używana jest RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. W takiej ramce użycie funkcji FIRST_VALUE zawróci wartość z pierwszego wiersza ramki. Natomiast funkcja LAST_VALUE zwróci wartość z wiersza aktualnego ponieważ jest on ostatnim wierszem w używanej ramce. Aby uzyskać ostatnią wartość partycji trzeba jawnie zadeklarować ramkę, czyli w tym przypadku użyć UNBOUNDED FOLLOWING.
Ćwiczenia
1. Napisz zapytanie wyciągające z widoku Sales.OrderValues średnią wartość zamówienia dla trzech ostatnich zamówień każdego klienta i każdego zamówienia.
SELECT o.custid, o.orderid, AVG(o.val) OVER (PARTITION BY o.custid ORDER BY o.orderdate, o.orderid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS orderavg FROM Sales.OrderValues AS o WHERE custid IN (1,2);
custid orderid orderavg -------- --------- ----------- 1 10643 814.500000 1 10692 846.250000 1 10702 674.166666 1 10835 684.600000 1 10952 549.000000 1 11011 750.166666 2 10308 88.800000 2 10625 284.275000 2 10759 296.183333 2 10926 438.050000 2. Napisz zapytanie na tabeli Sales.Orders które filtruje trzy zamówienia z najwyższą wartością dla każdego zamawiającego z użyciem identyfikatora zamówienia dla podziału unikalnego. Musisz użyć funkcji ROW_NUMBER która nie może być używana bezpośrednio w klauzuli WHERE. Aby to obejść trzeba użyć wyrażenia tablicowego.
;WITH cte AS( SELECT o.shipperid, o.orderid, o.freight, ROW_NUMBER() OVER (PARTITION BY o.shipperid ORDER BY o.freight DESC, o.orderid) AS rownum FROM Sales.Orders AS o ) SELECT shipperid, orderid, freight FROM cte WHERE rownum <= 3 ORDER BY shipperid, rownum;
shipperid orderid freight ----------- ----------- --------- 1 10430 458,78 1 10836 411,88 1 10658 364,15 2 10372 890,78 2 11030 830,75 2 10691 810,05 3 10540 1007,64 3 10479 708,95 3 11032 606,19 3. Napisz zapytanie używające widoku Sales.OrderValues które porównuje różnicę pomiędzy aktualną wartością zamówienia i wartością poprzedniego zamówienia klienta oraz różnicę między wartością aktualnego zamówienia i następnego zamówienia klienta.
SELECT o.custid, o.orderid, CAST (o.orderdate AS DATE) AS orderdate, o.val, o.val - LAG(val,1,0) OVER (PARTITION BY o.custid ORDER BY o.orderdate, o.orderid) AS difflastval, o.val - LEAD(val,1,0) OVER (PARTITION BY o.custid ORDER BY o.orderdate, o.orderid) AS diffnextval FROM Sales.OrderValues AS o WHERE custid IN (1,2) ORDER BY custid, orderdate, orderid;
custid orderid orderdate val difflastval diffnextval ------- ---------- ------------ ---- ------------ -------------- 1 10643 2007-08-25 814.50 814.50 -63.50 1 10692 2007-10-03 878.00 63.50 548.00 1 10702 2007-10-13 330.00 -548.00 -515.80 1 10835 2008-01-15 845.80 515.80 374.60 1 10952 2008-03-16 471.20 -374.60 -462.30 1 11011 2008-04-09 933.50 462.30 933.50 2 10308 2006-09-18 88.80 88.80 -390.95 2 10625 2007-08-08 479.75 390.95 159.75 2 10759 2007-11-28 320.00 -159.75 -194.40 2 10926 2008-03-04 514.40 194.40 514.40
Podsumowanie
- Funkcje okna służą do analizowania danych. Operują one na zbiorach wierszy zdefiniowanych dla każdego wiersza wynikowego wykonywanego zapytania poprzez klauzulę OVER.
- W przeciwieństwie do zapytań grupowanych funkcje okna nie ukrywają kolumn służących do zgrupowania wierszy. Zwracają one wiersz dla każdego wiersza wynikowego zapytania oraz wyniki funkcji okna w tym samym wierszu wynikowym.
- T-SQL wspiera funkcje okna agregujące, rankingu oraz przesunięcia. Wszystkie rodzaje używają partycjonowania oraz sortowania. Funkcje agregujące oraz FIRST_VALUE i LAST_VALUE używają także podziału okna na ramki (frame).