Parę faktów o funkcjach okna cz.2: Agregacja wartości i ramki

TSQL_WindowAggregates_00

Kontynuujemy naszą serię dotyczącą funkcji okna (poprzednią część znajdziecie tutaj). Dziś podejdziemy do tematu z nieco innej strony i przyjrzymy się ich zastosowaniu związanemu z agregacją danych. To właśnie dzięki funkcjom okna możliwa jest agregacja danych z użyciem standardowych funkcji takich jak SUM czy AVG bez konieczności używania GROUP BY na zdefiniowanym przez nas podzbiorze(oknie) danych. Funkcje okna wykorzystane w ten sposób dają nam możliwość budowania nawet bardzo wyszukanych algorytmów użytecznych na potrzeby analityczne ale nie tylko – zapraszam do lektury!

Przejdźmy bezpośrednio do przykładów praktycznych tj. użyjmy funkcji SUM z obligatoryjną klauzulą OVER(), która w naszym przykładzie nie przyjmie żadnych parametrów (korzystamy z bazy WideWorldImportersDW):

Powyższe zapytanie zwróciło listę transakcji w podziale na kolor oraz rozmiar sprzedanego przedmiotu. Kolumna TotalQty używa funkcji okna i zwraca liczbę wszystkich sprzedanych przedmiotów (brak parametrów w klauzuli OVER() jest tożsama z agregacją wszystkich elementów). Jak możecie zauważyć nie musieliśmy użyć GROUP BY aby coś agregować. Spróbujmy nieco zmodyfikować nasze zapytanie tak aby pokazać sumę sprzedanych przedmiotów w podziale na kolor oraz rozmiar:

Dodajmy teraz funkcję okna podobną do tej z pierwszego przykładu, z tym wyjątkiem, że nasza funkcja będzie działała już na zagregowanych wartościach. Gdybyśmy chcieli to zapisać bezpośrednio w tej części zapytania gdzie mamy standardową funkcję agregacji to możemy być nieco zaskoczeni…

… bo niestety taki zapis nie jest dozwolony i otrzymamy komunikat błędu:

Dzieje się tak ze względu na to, że wszystko co znajduje się w sekcji SELECT musi być albo funkcją agregacji albo musi być umieszczone w sekcji GROUP BY. Problem ten jednakże nie jest trudny do pokonania i możemy to w łatwy sposób naprawić używając funkcji agregacji na funkcji agregacji funkcji okna:

Jeśli ten zapis nie jest dla nas intuicyjny możemy zawsze użyć CTE lub podzapytania – rezultat będzie dokładnie taki sam, a wydajność się nie zmieni:

Mając pożądany przez nas rezultat możemy go rozszerzać i obliczyć np. procentowy udział każdego z kolorów w całkowitej sprzedaży (pomijając kolor niezidentyfikowany czyli N/A):

Prosto szybko i bez żadnego problemu. Ponadto możemy stworzyć podobny udział procentowy – tym razem biorąc pod uwagę udział przedmiotów o poszczególnych kolorach  w poszczególnych rozmiarach. Aby to zrobić możemy użyć słów kluczowych PARTITION BY:

Tak więc np. dla rozmiaru XXS 22% stanowiła sprzedaż przedmiotów w kolorze białym, niecałe 6% w kolorze niebieskim oraz 71% w kolorze czarnym. Taki sam udział został obliczony dla każdego rozmiaru jaki został sprzedany z pominięciem niezidentyfikowanego koloru N/A który wykluczyliśmy z naszych obliczeń.

Obliczenie tego typu udziałów jak widać nie powinno być dla nas wielkim wyzwaniem, szczególnie gdy mamy do dyspozycji funkcje okna. Również wydajność takich obliczeń z wersji na wersję SQL Server jest coraz lepsza, a inne konstrukcje jak np. skorelowane podzapytania, które mogły dawać nam ten sam rezultat nie oferują nawet zbliżonej wydajności. Oczywiście suma to nie wszystko na co możemy liczyć, możemy również użyć innych agregatów tj.:

  • AVG
  • CHECKSUM_AGG
  • COUNT
  • COUNT_BIG
  • MAX
  • MIN
  • STDEV
  • STDEVP
  • SUM
  • VAR
  • VARP

Patrząc na powyższą listę mamy duże pole do popisu i użycie każdej z powyższych funkcji wygląda dokładnie tak samo jak pokazanej wcześniej funkcji SUM.

Udziały procentowe to nie jedyne zastosowanie naszych agregatów – możemy również obliczyć np. sumę narastająco. Najlepszym przykładem będzie tutaj sprzedaż na przestrzeni kolejnych dni:

Schemat działania funkcji okna w postaci takiej jak powyżej został przedstawiony poniżej.

Tak więc dla przykładowych czterech wierszy mamy cztery różne okna gdzie każde kolejne jest coraz większe i zawiera poprzednie. To jest właśnie mniej więcej to czego byśmy oczekiwali jeśli chodzi o agregaty narastające. Co natomiast gdybyśmy chcieli aby sumowane były dane 2 ostatnich wierszy aż do bieżącego? W jaki sposób osiągnąć coś takiego? Również możemy do tego użyć funkcji okna! Do tego typu operacji możemy skonstruować nowe okno używając kilku dostępnych słów kluczowych. Zacznijmy znów od prostego zastosowania stosując zapis ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, które umieścimy wewnątrz klauzuli OVER po ORDER BY:

Na powyższym zrzucie z rezultatem zaznaczyłem 3 wiersze które zostały posumowane aby uzyskać rezultat 26558 w kolumnie Total Qty wiersza numer osiem tj. 13072+5739+7747=26558. Tak więc nasze okno działa w taki sposób, że bierze pod uwagę wiersze będące w określonej relacji do bieżącego wiersza. Cofanie się o N wierszy wstecz jest możliwe dzięki zapisowi N PRECEDING – możemy również odwoływać się do wierszy znajdujących za naszym bieżącym:

 

Przy takim zapisie wartość w wierszu szóstym tj. 13486 powstała na skutek wykonania działa 5739+7747. Jeśli chcemy odwoływać się do wierszy poprzedzających lub następujących możemy użyć następujących słów kluczowych:

  • BETWEEN – wskazanie, że będziemy odwoływać się do zakresu
  • N PRECEDING – odwołanie do N wierszy przed bieżącym
  • N FOLLOWING – odwołanie do N wierszy po bieżącym
  • CURRENT ROW – odwołanie do bieżącego wiersza
  • UNBOUNDED PRECEDING – odwołanie do wszystkich wierszy przed bieżącym
  • UNBOUNDED FOLLOWING – odwołanie do wszystkich wierszy po bieżącym

Dla lepszego zrozumienia możemy na schemat działania w sposób graficzny:

Oprócz słowa kluczowego ROWS mamy również do dyspozycji RANGE – różnica pomiędzy nimi może być dosyć trudna do zrozumienia dlatego też wytłumaczymy to na najbardziej przejrzystym przykładzie porównując właśnie do nowo poznanego ROWS:

Na powyższym zrzucie ekranowym możecie dostrzec różnicę, zapis z RANGE zsumował wszystkie wiersze dla daty 2013-01-01 bez względu na to czy wystąpiły one przed bieżącym wierszem czy też nie, ROWS z kolei posumował wszystkie wartości dla daty 2013-01-01, które wystąpiły do bieżącego wiersza. Jeżeli szukali byśmy analogii to można powiedzieć, że RANGE jest podobny do przedstawionej w poprzednim poście funkcji RANK, a ROWS do funkcji ROW_NUMBER.

Oczywistym może wydawać się fakt, że w przypadku gdy mamy unikalny zbiór danych oba słowa kluczowe będą zwracały te same wartości:

Jednakże to ROWS powinien być naszym domyślnym wyborem gdyż automatycznie daje nam większą wydajność. Na naszym przykładzie nie zauważyłem różnic wydajnościowych pomiędzy oboma podejściami:

Ogólnie rzecz biorąc w niektórych przypadkach gdy występuje operator Window Spool to razem z RANGE zamiast tabeli in-memory używana jest tabela dyskowa w tempdb co może wiązać się z negatywnymi skutkami – bliżej temu zjawisku przyjrzymy się w kolejnym artykule.

Pamiętacie jak w poprzednim poście obliczaliśmy różnicę procentową pomiędzy poszczególnymi miejscami w rankingu z wykorzystaniem ROW_NUMBER? Teraz wreszcie możemy to zrobić tak jak należy z wykorzystaniem nowo poznanych funkcji:

Porównując otrzymany plan zapytania z  otrzymanym w poprzednim artykule (klik) przewaga wydajnościowa również jest bardzo duża:

Powiem Wam przy okazji, że nawet to nie jest najlepsze możliwe podejście! Możemy jeszcze użyć funkcji LAG/LEAD (o tych funkcjach pisałem bardzo dawno temu tutaj):

Mimo, że koszt obu zapytań jest zbliżony to zapytanie z LAG generuje prostszy plan i nie występuje w nim operator Window Spool:

O samej wydajności obu podejść powiemy sobie szerzej w ramach osobnego artykułu jednakże na ten moment możemy sobie powiedzieć, że im mniejszy plan i bez operatorów Window Spool (które w gorszym scenariuszu mogą być zrzucane do tempdb lub w lepszym przetwarzane w pamięci) tym plan może być szybszy.Póki co to by było wszystko na temat funkcji agregujących wchodzących w skład funkcji okna. W następnych artykułach powiemy sobie nieco o wydajności zapytań opartych o funkcje okna, indeksowaniu z wykorzystaniem indeksów Columnstore oraz Rowstore oraz o ogólnych wskazówkach wydajnościowych.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *