Parę faktów o funkcjach okna cz.1: ROW_NUMBER, RANK, DENSE_RANK i NTILE

Funkcje okna są jednym z błogosławieństw dla deweloperów pracujących z SQL Server. Pozwalają one na osiągnięcie rezultatów, które wcześniej można było uzyskać tylko i wyłącznie poprzez wykorzystanie podzapytań inline czy różnego rodzaju konstrukcji przetwarzających dane wiersz po wierszu jak np. kursory czy pętle. Dziś powiemy sobie parę słów o tym jak działają funkcje ROW_NUMBER, RANK, DENSE_RANK oraz NTILE wraz z modyfikacjami klauzuli OVER.

Funkcja ROW_NUMBER jak sama nazwa wskazuje daje nam możliwość ponumerowania wierszy według określonych kryteriów. Sama składnia jest bardzo prosta – na poniższym przykładzie widzimy najprostsze jej zastosowanie (samo zapytanie numeruje wiersze poczynając od tych wierszy, gdzie pole Quantity ma najwyższą wartość):

SELECT 
	ROW_NUMBER() OVER(ORDER BY Quantity DESC) AS RN,
	[Customer Key],
	Quantity 
FROM Fact.Sale

Przy wywołaniu funkcji ROW_NUMBER obligatoryjnie musimy podać klauzulę OVER oraz jej parametr ORDER BY, który wskazuje według jakiego porządku sortowania wiersze mają być numerowane. Oczywiście jak możecie zauważyć na powyższym zrzucie ekranowym jeśli wiersze mają tę samą wartość to zostaną ponumerowane według tego jak zostaną zwrócone przez silnik bazodanowy – czyli tak naprawdę losowo bo bez jawnego wskazania nie mamy pewności w jakiej kolejności zostanie coś do nas zwrócone. Plan wykonania tak prostego zapytania również jest bardzo prosty w interpretacji:

Łatwo możemy zauważyć, że większość czasu zajęło sortowanie rezultatu bo aż 97%. Całe zadanie zostało wykonane “porcjami” a nie wiersz po wierszu ze względu na fakt, iż operator wykonywany był w trybie Batch przez co sortowanie takie odbyło się dużo szybciej niż analogiczne działanie w trybie Row (działam na SQL Server w wersji 2016 – wcześniej osiągnięcie trybu Batch było dużo trudniejsze szczególnie, że np. operator Window Aggregate został wprowadzony właśnie w tej wersji). Dlatego też w przypadku SQL Server 2016 i wyżej funkcje okna obsługujące tryb batchowy wykonują się znacznie szybciej niż podobne zapytania w poprzednich wersjach.

Oczywiście tego typu przetwarzanie i plan wykonania otrzymamy gdy występuje indeks kolumnowy, w przypadku baz OLTP w których (zazwyczaj) takowych indeksów nie uświadczymy zobaczymy nieco inne operatory. Powiemy sobie o tym w jednej z następnych publikacji, które już niebawem.

Wracając do tematu – na naszym planie zapytania mamy operator Window Aggregate, który odpowiada właśnie za realizację numerowania posortowanego zbioru. Jeżeli chcielibyśmy powstały zbiór przefiltrować tak aby pokazywał tylko 10 pierwszy rekordów nie możemy tego zrobić bezpośrednio w sekcji WHERE zapytania, które wywołuje funkcje okna:

SELECT 
	ROW_NUMBER() OVER( ORDER BY Quantity DESC) AS RN,
	[Customer Key],
	Quantity 
FROM Fact.Sale
WHERE ROW_NUMBER() OVER( ORDER BY Quantity DESC)<10

Bo otrzymamy stosowny komunikat błędu, który mówi nam, że funkcje okna mogą pojawić się jedynie w SELECT lub ORDER BY:

Msg 4108, Level 15, State 1, Line 6
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Dzieje się tak dlatego, że WHERE jest wykonywany przed funkcją okna co jest naturalnym i w pełni intuicyjnym zachowaniem. Jeśli chcielibyśmy filtrować po funkcji okna oczywiście możemy użyć np. podzapytania czy konstrukcji CTE:

WITH cte
AS
(
SELECT 
	ROW_NUMBER() OVER( ORDER BY Quantity DESC) AS RN,
	[Customer Key],
	Quantity 
FROM Fact.Sale
)
SELECT
	RN,
	[Customer Key],
	Quantity 
FROM cte
WHERE RN<10

Tak jak wspomniałem już wcześniej, ROW_NUMBER wymaga sortowania po określonym polu i jest to zarazem najkosztowniejsza operacja związana z tą funkcją. Zdarza się, że porządek sortowania nie jest dla nas ważny, a chcemy ponumerować wiersze – czy możemy tego dokonać? Sprawdźmy! Napiszmy zapytanie z sortowaniem po stałej (w tym wypadku NULL):

SELECT 
	ROW_NUMBER() OVER( ORDER BY NULL) AS RN, 
	[Customer Key], 
	Quantity 
FROM Fact.Sale

Niestety taka konstrukcja nie jest wspierana i otrzymamy błąd:

Msg 5309, Level 16, State 1, Line 1
Windowed functions and NEXT VALUE FOR functions do not support constants 
as ORDER BY clause expressions.

Ale zawsze możemy być sprytniejsi od parsera! Spróbujmy użyć zapisu z podzapytaniem:

SELECT 
	ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) AS RN, 
	[Customer Key], 
	Quantity 
FROM Fact.Sale

Rezultat taki jak chcieliśmy – bez kosztownego sortowania wszystkie wiersze zostały ponumerowane:

Porównując powyższy plan z planem z operatorem sortowania mamy również potwierdzenie, że pozbyliśmy się dużej części kosztu:

Oczywiście takie proste zastosowania to nie wszystko co mamy do dyspozycji! Jak możecie zauważyć funkcja ROW_NUMBER() ponumerowała kolejno wiersze mimo, że mają one tą samą wartość. Przypuśćmy, że chcemy aby wiersze mające tą samą wartość pola Quantity otrzymały ten sam numer w polu RN – najłatwiej coś takiego osiągnąć funkcją DENSE_RANK:

SELECT 
	DENSE_RANK() OVER( ORDER BY Quantity DESC) AS RN,
	[Customer Key],
	Quantity 
FROM Fact.Sale

Tak więc pierwsze 206 wierszy były oznaczone numerem 1 kolejne numerem 2 itd. Może się również okazać, że chcemy aby kolejne wiersze nie otrzymały numeru 2 tak jak w powyższym przykładzie tylko kolejny numer wiersza w naszym przypadku 207 – osiągniemy to siostrzaną funkcją RANK:

SELECT 
	RANK() OVER( ORDER BY Quantity DESC) AS RN,
	[Customer Key],
	Quantity 
FROM Fact.Sale

Ostatnią funkcją o której chciałbym wspomnieć w ramach niniejszego artykułu jest NTILE, która dzieli zbiór danych na określone grupy przy czym ilość tych grup można podać jako parametr tejże funkcji. Dla przykładu w poniższym zapytaniu agregujemy wszystkie transakcje po klientach i wybieramy dwunastu, którzy kupili od nas najwięcej towarów. Po tym grupujemy ich w trzy grupy z wykorzystaniem NTILE:

with cteGrouping
AS
(
SELECT TOP 12
 [Customer Key], 
 SUM(Quantity) AS Quantity 
 FROM Fact.Sale
 GROUP BY [Customer Key]
 ORDER BY Quantity DESC
 )
 SELECT  
 [Customer Key], 
 Quantity,
 NTILE(3) OVER( ORDER BY Quantity DESC) AS [Group]
 FROM cteGrouping

W tym przypadku liczba wierszy pozwalała na to, że rozłożyły się one po równo w każdej z grup. Co natomiast gdy liczba wierszy nie będzie pasowała do liczby grup? Zapewne pomyślicie, że pod spodem jest jakiś wyszukany algorytm jednakże tak nie jest, po prostu w przypadku wystąpienia jednej nadmiarowej wartości to pierwsza grupa otrzyma jeden wiersz więcej, gdy mamy dwie nadmiarowe wartości to zostaną one przypisane do grupy 1 oraz 2 itd. W poniższym przykładzie widzimy 15 wierszy i tylko 4 grupy – nie można było ich po równo rozłożyć, dlatego pierwsze 3 grupy otrzymały po 4 wiersze, a ostatnia otrzymała 3:

with cteGrouping
AS
(
SELECT TOP 15
 [Customer Key], 
 SUM(Quantity) AS Quantity 
 FROM Fact.Sale
 GROUP BY [Customer Key]
 ORDER BY Quantity DESC
 )
 SELECT  
 [Customer Key], 
 Quantity,
 NTILE(4) OVER( ORDER BY Quantity DESC) AS [Group]
 FROM cteGrouping

Tak więc pod NTILE nie ma żadnej wyszukanej logiki tylko proste przyporządkowanie. O NTILE często jednak zapominamy, a może rozwiązać niejeden problem. Dla przykładu wystarczy wspomnieć o testowaniu działania naszych obliczeń w TSQL gdzie z wykorzystaniem NTILE możemy podzielić dane na zbiór testowy i zbiór testujący, czy też podzielić wiersze w funkcji partycjonującej gdy nie mamy zdeterminowanego klucza partycjonującego, a chcemy równo rozłożyć wiersze pomiędzy partycje.

Wszystko łatwo, szybko i przyjemnie. Oczywiście każda funkcja okna może być partycjonowana według określonego warunku, do tego celu możemy wykorzystać słowa kluczowe PARTITION BY umieszczane wewnątrz klauzuli OVER. Tak więc poniższe zapytanie zwraca ranking bazujący na największej ilości kupionych przedmiotów w ramach pojedynczej transakcji przez każdego z klientów:

SELECT 
	RANK() OVER(PARTITION BY [Customer Key] ORDER BY Quantity DESC) AS RN,
	[Customer Key],
	Quantity 
FROM Fact.Sale

Modyfikując powyższe zapytanie możemy pokazać top 3 najwięcej zakupionych produktów w ramach pojedynczej transakcji wraz z ilością wystąpień takiego operacji dla każdego z klientów. Brzmi skomplikowanie? Kiedyś może tak, obecnie to nic trudnego! Wystarczy pamiętać o kolejności wykonywania poszczególnych elementów zapytania oraz o tym jak wykorzystać funkcje okna:

with cte
AS
(
SELECT 
	RANK() OVER(PARTITION BY [Customer Key] ORDER BY Quantity DESC) AS RN,
	[Customer Key],
	Quantity,
	COUNT(*) AS Cnt 
FROM Fact.Sale
GROUP BY 
	[Customer Key],
	Quantity 
)
SELECT
	[Customer Key],
	RN,
	Quantity,
	Cnt
FROM
	cte
WHERE RN<=3

Wszystko dzieje się ekstremalnie szybko nawet dla bardzo dużych tabel co bez wątpienia jest zasługą indeksu kolumnowego i batchowego trybu przetwarzania. Wyobrażacie sobie osiągnięcie czegoś analogicznego bez użycia funkcji okna? Ja sobie wyobrażam bo miałem okazje coś takiego pisać swego czasu ale nie wspominam tego miło, jedną z naturalnie przychodzących mi do głowy możliwości jest podzapytanie w postaci krótszej lub dłuższej.

Oczywiście z wykorzystaniem powyższych funkcji nie powinniście mieć żadnych problemów, a scenariuszy użycia znajdziecie zapewne bardzo wiele. Dla przykładu poniższe zapytanie zwróci ranking  ze względu na sumę kupionych towarów dla określonego klienta oraz zwróci różnicę procentową pomiędzy poszczególnymi miejscami w rankingu (wiem, że można to osiągnąć znaczeni łatwiej innymi funkcjami ale wykorzystamy ROW_NUMBER):

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,
 ROW_NUMBER() OVER( ORDER BY Quantity DESC)-1 AS RN_Prev,
 [Customer Key],
 Quantity
 FROM cteGrouping
 )
 SELECT 
	N1.RN,
	N1.[Customer Key],
	N1.Quantity AS CustomerQty,
	N2.Quantity AS PreviusCustomerQty,
	CAST(CAST(N1.Quantity AS DECIMAL(10,2))/N2.Quantity -1 AS DECIMAL(10,2))*100 AS [% Diff]
 FROM cteNumbering AS N1
 JOIN cteNumbering AS N2
 ON N1.RN=N2.RN_Prev

Całkiem fajne zastosowanie choć można je wykonać w nieco inny sposób dużo łatwiej i wydajniej, ale o tym innym razem. Takich zastosowań znajdziecie bardzo dużo, a o wydajności i indeksowaniu pod funkcje okna powiemy sobie jeszcze w ramach innego artykułu ponieważ planuję napisać kilka części z tym związanych. Póki co mam nadzieję, że ten artykuł okazał się pomocny.