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

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):

SELECT 
	F.[Sale Key],
	F.Description,
	S.Color,
	S.Size,
	F.Quantity,
	SUM(F.Quantity) OVER() AS TotalQty
FROM Fact.Sale AS F
JOIN Dimension.[Stock Item] AS S
ON S.[Stock Item Key]=F.[Stock Item Key]

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:

SELECT 
	S.Color,
	S.Size,
	SUM(F.Quantity) AS Qty
FROM Fact.Sale AS F
JOIN Dimension.[Stock Item] AS S
ON S.[Stock Item Key]=F.[Stock Item Key]
GROUP BY
	S.Color,
	S.Size

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…

SELECT 
	S.Color,
	S.Size,
	SUM(F.Quantity) AS Qty,
	SUM(F.Quantity) OVER() AS TotalQty
FROM Fact.Sale AS F
JOIN Dimension.[Stock Item] AS S
ON S.[Stock Item Key]=F.[Stock Item Key]
GROUP BY
	S.Color,
	S.Size

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

Msg 8120, Level 16, State 1, Line 5
Column 'Fact.Sale.Quantity' is invalid in the 
select list because it is not contained in either an 
aggregate function or the GROUP BY clause.

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:

SELECT 
	S.Color,
	S.Size,
	SUM(F.Quantity) AS Qty,
	SUM(SUM(F.Quantity)) OVER() AS TotalQty
FROM Fact.Sale AS F
JOIN Dimension.[Stock Item] AS S
ON S.[Stock Item Key]=F.[Stock Item Key]
GROUP BY
	S.Color,
	S.Size

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:

with cte
AS
(
SELECT 
	S.Color,
	S.Size,
	SUM(F.Quantity) AS Qty
FROM Fact.Sale AS F
JOIN Dimension.[Stock Item] AS S
ON S.[Stock Item Key]=F.[Stock Item Key]
GROUP BY
	S.Color,
	S.Size
)
SELECT 
	Color,
	Size
	Qty,
	SUM(Qty) OVER() AS TotalQty
FROM
	cte

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):

with cte
AS
(
SELECT 
	S.Color,
	SUM(F.Quantity) AS Qty
FROM Fact.Sale AS F
JOIN Dimension.[Stock Item] AS S
ON S.[Stock Item Key]=F.[Stock Item Key]
WHERE S.Color<>'N/A'
GROUP BY
	S.Color
)
SELECT 
	Color,
	Qty,
	SUM(Qty) OVER() AS TotalQty,
	CAST(CAST(Qty AS Decimal(10,2))/SUM(Qty) OVER()*100 AS DECIMAL(10,2)) AS Share
FROM
	cte
ORDER BY Share DESC

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:

SELECT 
	F.[Invoice Date Key],
	SUM(F.Quantity) AS Qty,
	SUM(SUM(F.Quantity)) OVER(ORDER BY F.[Invoice Date Key] ASC) AS TotalQty
FROM 
	Fact.Sale AS F
GROUP BY 
	F.[Invoice Date Key]
ORDER BY 
	F.[Invoice Date Key]

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:

SELECT 
	F.[Invoice Date Key],
	SUM(F.Quantity) AS Qty,
	SUM(SUM(F.Quantity)) OVER(ORDER BY F.[Invoice Date Key] ASC 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS TotalQty
FROM 
	Fact.Sale AS F
GROUP BY 
	F.[Invoice Date Key]
ORDER BY 
	F.[Invoice Date Key] ASC

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:

SELECT 
	F.[Invoice Date Key],
	SUM(F.Quantity) AS Qty,
	SUM(SUM(F.Quantity)) OVER(ORDER BY F.[Invoice Date Key] ASC 
        ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS TotalQty
FROM 
	Fact.Sale AS F
GROUP BY 
	F.[Invoice Date Key]
ORDER BY 
	F.[Invoice Date Key]

 

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:

SELECT 
	F.[Invoice Date Key],
	F.Quantity AS Qty,
	SUM(F.Quantity) OVER(ORDER BY F.[Invoice Date Key] ASC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RowsTotalQty,
	SUM(F.Quantity) OVER(ORDER BY F.[Invoice Date Key] ASC 
            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RangeTotalQty
FROM 
	Fact.Sale AS F

ORDER BY 
	F.[Invoice Date Key]

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:

with cteGrouping
AS
(
SELECT 
 [Customer Key], 
 SUM(Quantity) AS Quantity 
 FROM Fact.Sale
 GROUP BY [Customer Key]
 ),cteNumbering
 AS
 (
 SELECT 
 ROW_NUMBER() OVER( ORDER BY Quantity DESC) AS RN,
 SUM(Quantity) OVER(ORDER BY Quantity DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS [PreviousCustomerQty],
 [Customer Key],
 Quantity AS CustomerQty
 FROM cteGrouping
 )
 SELECT 
	RN,
	[Customer Key],
    CustomerQty,
	PreviousCustomerQty,
	CAST(CAST(CustomerQty AS DECIMAL(10,2))/PreviousCustomerQty AS DECIMAL(10,2)) AS [% Diff]
 FROM cteNumbering

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):

;with cteGrouping
AS
(
SELECT 
 [Customer Key], 
 SUM(Quantity) AS Quantity 
 FROM Fact.Sale
 GROUP BY [Customer Key]
 ),cteNumbering
 AS
 (
 SELECT 
 ROW_NUMBER() OVER( ORDER BY Quantity DESC) AS RN,
 LEAD(Quantity) OVER(ORDER BY Quantity DESC) AS [PreviousCustomerQty],
 [Customer Key],
 Quantity AS CustomerQty
 FROM cteGrouping
 )
 SELECT 
	RN,
	[Customer Key],
    CustomerQty,
	PreviousCustomerQty,
	CAST(CAST(CustomerQty AS DECIMAL(10,2))/PreviousCustomerQty AS DECIMAL(10,2)) AS [% Diff]
 FROM cteNumbering

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.