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

TSQLWindowingfunctions_13

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

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:

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

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:

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

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

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

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:

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:

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:

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:

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:

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:

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

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.

Adrian Chodkowski
Follow me

Adrian Chodkowski

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

Leave a Comment

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