Funkcje analityczne w SQL

W tym artykule używam funkcji SQLite, które zostały dodane w wersji 3.28.0. jeżeli używasz SQLite do eksperymentowania upewnij się, iż korzystasz z wersji 3.28.0 bądź nowszej. Możesz to zrobić używając polecenia sqlite3 --version.

Czym są funkcje analityczne w SQL

W jednym zdaniu można powiedzieć, iż funkcje analityczne (ang. analytic functions) zwracają wartość na podstawie grupy wierszy powiązanych z aktualnym wierszem. Tę grupę nazywa się partycją. Sam opis może być skomplikowany, więc proszę spójrz na przykład poniżej:

Poza funkcją analityczną użyłem tu aliasu kolumny, sortowania i ograniczenia liczby zwracanych wierszy. W wyniku tego zapytania otrzymasz dziesięć wierszy:

To zapytanie zwraca cztery różne kolumny. Ostatnia z nich jest wynikiem działania funkcji analitycznej. Spróbuję rozłożyć ją na części pierwsze:

W pierwszej linijce widzisz funkcję SUM. Możesz ją pamiętać z artykułu o funkcjach w SQL. W poprzednim przypadku była ona użyta jako funkcja agregująca. Użycie słowa kluczowego OVER sprawia, iż jej zachowanie nieznacznie się zmienia. W tym przypadku SUM przez cały czas zwraca sumę, jednak w przypadku funkcji analitycznej pod uwagę brana jest partycja a nie cała tabela¹.

W ostatniej linijce znajduje się definicja partycji, która zostanie użyta do obliczenia wartości funkcji. W tym przypadku do partycji należą wiersze zawierające taką samą wartość kolumny customerid.

Zatem ta funkcja:

• oblicza sumę kolumny total (SUM(total)),
• sumując wiersze, które mają taką samą wartość kolumny customerid (PARTITION BY customerid).

Proszę spójrz na pierwszych siedem wierszy, które mają taką samą wartość kolumny customerid. Kolumna total sumowana jest w ramach partycji: 3.98 + 3.96 + 5.94 + 0.99 + 1.98 + 13.86 + 8.91 = 39.62. Wartość ta, będąca wynikiem działania funkcji, jest przypisywana do każdego wiersza z partycji.

Można powiedzieć, iż funkcje analityczne są podobne do standardowego grupowania przy użyciu klauzuli GROUP BY. Funkcje agregujące zwracają jeden wiersz dla grupy, funkcje analityczne zwracają wiele wierszy.

Pozostałe funkcje agregujące

Poniżej znajdziesz listę funkcji agregujących, których możesz użyć przed słowem kluczowym OVER:

• AVG – zwraca średnią wartość,
• COUNT – zwraca liczbę wierszy,
• MAX – zwraca maksymalną wartość,
• MIN – zwraca minimalną wartość,
• SUM – zwraca sumę wartości.

Klauzula PARTITION BY

W przykładzie wyżej wszystkie wiersze w tabeli invoice zostały podzielone na osobne partycje. Do podziału na partycje użyłem wyłącznie jednej kolumny. W klauzuli PARTITION BY możesz użyć wielu wyrażeń:

W tym przypadku tabela zostanie podzielona na więcej partycji. Do jednej partycji trafią wszystkie wiersze, które mają taką samą wartość kolumn customerid i billingcountry.

Istnieje też możliwość pominięcia klauzuli PARTITION BY:

W takim przypadku partycja równoznaczna jest z całą tabelą². dla wszystkich wynikowego wiersza SUM(total) OVER () zwróci sumę kolumny total we wszystkich wierszach.

Kiedy obliczana jest wartość funkcji analitycznej

Funkcje analityczne mogą być użyte wyłącznie w klauzuli SELECT i ORDER BY. Wynika to z faktu, iż funkcje analityczne operują na „wirtualnej tabeli” (w modelu relacyjnym można mówić o relacji), która powstanie po filtrowaniu i grupowaniu wierszy.

Można powiedzieć, iż zapytanie wykonywane jest w następującej kolejności:

1. Wykonanie klauzuli WHERE,
2. Wykonanie klauzuli GROUP BY,
3. Wykonanie klauzuli HAVING,
4. Wykonanie funkcji analitycznych,
5. Wykonanie klauzuli ORDER BY,
6. Wykonanie klauzuli LIMIT.

Czym jest okno

Tak naprawdę, to funkcja do obliczenia wartości bierze pod uwagę tak zwane okno. Każdy wiersz w partycji ma swoje własne okno, które jest podzbiorem partycji. jeżeli okno nie jest zdefiniowane wówczas przyjmuje ono wartość całej partycji. Istnieje wiele możliwości na ograniczenie okna dla funkcji analitycznej. Najprostszym z nich jest użycie klauzuli ORDER BY.

Ćwiczenia do samodzielnego wykonania

Teraz czas na Twoje eksperymenty. Spróbuj samodzielnie uruchomić przykładowe zapytanie. Możesz je także zmodyfikować:

• zmień limit zwracanych wierszy,
• zwróć wyłącznie wiersze z parzystą wartością kolumny customerid.

Sortowanie w funkcjach analitycznych

Nieznacznie zmodyfikuję definicję partycji z pierwszego zapytania. Przykład poniżej używa dwóch funkcji. Druga z nich używa ORDER BY invoiceid po definicji partycji:

Proszę spójrz na wynik zapytania. Zwróć uwagę na wartości kolumn customer_total_sum i customer_total_increasing_sum:

Użycie ORDER BY w definicji funkcji analitycznej powoduje zmianę okna dla wszystkich z wierszy. ORDER BY tworzy „narastające okna” dla wszystkich kolejnego wiersza:

• okno dla pierwszego wiersza to wyłącznie pierwszy wiersz (3.98 = 3.98),
• okno dla drugiego wiersza to dwa pierwsze wiersze (3.98 + 3.96 = 7.94),
• okno dla trzeciego wiersza to trzy pierwsze wiersze (3.98 + 3.96 + 5.94 = 13.88),
• itd.

Zauważ, iż w tym przykładzie użyłem dwóch klauzul ORDER BY. Pierwsza z nich służy do określenia okna dla funkcji analitycznej, druga służy do sortowania wyników całego zapytania.

Partycje a sortowanie

Zapytanie używające partycji zwraca dane posortowane zgodnie z definicją partycji. Na przykład wyniki poniższego zapytania będą posortowane używając kolumny customerid:

Chociaż dane będą zwrócone w ten sposób nie polegałbym na tym zachowaniu. jeżeli zależy Ci na uzyskaniu posortowanych danych określ to jasno używając klauzuli ORDER BY. W ten sposób jasno określasz swoje intencje:

We wszystkich przykładach w artykule dodałem klauzulę ORDER BY.

Ćwiczenia do samodzielnego wykonania

Teraz czas na Twoje eksperymenty. Spróbuj samodzielnie uruchomić przykładowe zapytanie zawierające dwie funkcje analityczne. Możesz je także zmodyfikować:

• sprawdź jak na wynik zapytania wpływają różne kolumny użyte do sortowania,
• użyj kilku kolumn do sortowania wyników/wierszy w partycji,
• użyj DESC/ASC do z zmiany wyniku sortowania.

Unikanie duplikacji – nazwane partycje

Wyobraź sobie sytuację, w której chcesz zwrócić wynik różnych funkcji analitycznych, jednak używając tej samej definicji partycji. Spójrz na przykład poniżej:

W tym przykładzie definicja partycji jest prosta. Możesz jednak trafić na przypadek, w którym będzie ona dużo bardziej skomplikowana. Takie zapytanie zawiera duplikację definicji partycji. Duplikacja w większości przypadków jest zła. Nie inaczej jest w przypadku zapytań SQL. W takiej sytuacji z pomocą przychodzi klauzula WINDOW. Proszę spójrz na przykład poniżej, jest on równoznaczny z poprzednim zapytaniem:

Oba zapytania zwrócą ten sam wynik:

Co więcej partycje zdefiniowane w ten sposób możesz dodatkowo rozszerzać:

W tym przykładzie suma kolumny total jest narastająca:

Funkcje okna

Jak już wiesz funkcje analityczne działają w oparciu o partycje. Dodatkowo funkcje te pozwalają Ci na zdefiniowanie tak zwanego okna. Domyślnie okno zawiera:

• wszystkie wiersze partycji jeżeli nie użyjesz klauzuli ORDER BY,
• wiersze „do aktualnego wiersza” jeżeli użyjesz klauzuli ORDER BY.

Domyślną zawartość okna możesz zmienić. Okno pozwala na dalsze ograniczenie wierszy branych pod uwagę przez funkcję. Składnię można rozszerzyć do:

Okno może być jednego z trzech rodzajów:

• ROWS – granice okna określone są przez liczbę wierszy przed i po aktualnym wierszu,
• GROUPS – granice okna określone są przez liczbę „grup” przed i po aktualnej „grupie”. Do grupy zalicza się te wartości, które są „równe” w trakcie sortowania przy użyciu ORDER BY,
• RANGE – granice okna określone są przez różnicę wartości względem aktualnego wiersza.

Dla uproszczenia w definicji okna będę używał wyłącznie BETWEEN x PRECEDING AND y FOLLOWING. Oznacza to, iż okno będzie obejmowało zakres x przed aktualnym wierszem i y po aktualnym wierszu. Składania pozwala na dużo bardziej zaawansowane modyfikacje, jednak ich znajomość nie jest niezbędna do zrozumienia działania samego mechanizmu. jeżeli jesteś zainteresowany tymi szczegółami odsyłam Cię do dokumentacji silnika bazy danych, którego używasz.

Mam świadomość, iż to wszystko brzmi jak łacina bez konkretnego przykładu. Postaram się to poprawić ;)

Okno typu ROWS

Proszę spójrz na pierwszy z nich:

W wyniku tego zapytania otrzymasz 10 wierszy:

W tym przypadku SUM(total) sumuje jedynie wiersze należące do okna, a nie całej partycji.

• dla pierwszego wiersza oknem są wiersze pierwszy i drugi: 3.98 + 3.96 = 7.94 (brak poprzedniego wiersza w partycji),
• dla drugiego wiersza oknem są wiersze pierwszy, drugi i trzeci: 3.98 + 3.96 + 5.94 = 13.88 ,
• dla siódmego wiersza oknem są wiersze szósty i siódmy: 13.86 + 8.91 = 22.77 (brak następnego wiersza w partycji).

Okno typu GROUPS

Tym razem do utworzenia partycji posłużę się kolumną billingcountry:

W wyniku tego zapytania otrzymasz 13 wierszy:

Także tym przypadku SUM(total) sumuje jedynie wiersze należące do okna, a nie całej partycji:

• dla pierwszego wiersza oknem jest pierwszych pięć wierszy. Grupa do której należy pierwszy wiersz i następna grupa: 3 * 1.98 + 2 * 1.99 = 9.92 (brak poprzedniej grupy w partycji),
• dla piątego wiersza oknem jest pierwszych siedem wierszy. Grupa poprzedzająca, grupa do której należy piąty wiersz i następna grupa: 3 * 1.98 + 2 * 1.99 + 2 * 3.96 = 17.84 ,
• dla przedostatniego wiersza oknem są wiersze 10., 11., 12. i 13.: 2 * 8.91 + 2 * 13.86 (brak następnej grupy w partycji).

Okno typu RANGE

W tym przypadku okno definiowane jest jako „odległość” 2 przed i po wartości kolumny total:

W wyniku tego zapytania otrzymasz dziesięć wierszy:

Także tutaj SUM(total) sumuje jedynie wiersze należące do okna, a nie całej partycji.

• dla pierwszego wiersza oknem są pierwsze dwa wiersze. Dzieje się tak ponieważ wartość kolumny total dla tych wierszy jest w zakresie <0.99 - 2, 0.99 + 2>,
• dla drugiego wiersza oknem są pierwsze cztery wiersze. Dzieje się tak ponieważ wartość kolumny total dla tych wierszy jest w zakresie <1.98 - 2, 1.98 + 2>,
• dla trzeciego wiersza oknem są wiersze drugi, trzeci, czwarty i piąty. Dzieje się tak ponieważ wartość kolumny total dla tych wierszy jest w zakresie <3.96 - 2, 3.96 + 2>.

Filtrowanie okna

Jakby tego było mało do tego wszystkiego dochodzi możliwość filtrowania :). Oznacza to tyle, iż możesz użyć filtrowania jak w klauzuli WHERE, żeby dodatkowo ograniczyć wiersze „pasujące” do definicji okna. Proszę spójrz na przykład poniżej:

Zwróć uwagę na wartości kolumn rolling_sum i filtered_rolling_sum:

filtered_rolling_sum ma wartość 39.62 - 3.96 = 35.66. Zatem funkcja analityczna w przypadku partycji gdzie customerid = 1 nie wzięła pod uwagę filtrowanego wiersza. Wiersz, w którym invoiceid = 121 nie został wzięty pod uwagę podczas sumowania. Dla przypomnienia odsyłam cię do artykułu opisującego klauzulę WHERE.

Lista funkcji

Bazy danych posiadają szereg funkcji dedykowanych do użycia z klauzulą OVER. Poniżej znajdziesz listę zawierającą część z nich. Podobnie jak w innych przypadkach odsyłam Cię do dokumentacji Twojej bazy danych, jeżeli chcesz znać ich komplet:

• ROW_NUMBER() – Numeruje wiersze w partycji zaczynając od 1. Bierze pod uwagę klauzulę ORDER BY,
• RANK(), DENSE_RANK() – Funkcje numerujące unikalne wartości w partycji. RANK zostawia „dziury” w numeracji. Pokażę to na przykładzie poniżej. Bez klauzuli ORDER BY każdy z wierszy ma numer 1,
• NTILE(N) – Dzieli partycję na N „możliwie równych” i przydziela wiersze do grup o wartości od 1 do N.

Pierwszy przykład pokazuje działanie funkcji ROW_NUMBER:

Drugi przykład porównuje funkcje RANK i DENSE_RANK. Proszę zwróć uwagę na wyniki tych funkcji dla 10. i 11. wiersza:

Ostatni przykład pokazuje sposób podziału partycji przez funkcję NTILE z użyciem różnych argumentów:

Funkcje analityczne a klauzula WHERE

Jak już wiesz funkcje analityczne mogą być użyte wyłącznie w klauzuli SELECT i ORDER BY. Co jeżeli musisz użyć wyniku funkcji analitycznej do filtrowania? Z pomocą przychodzą podzapytania. Na przykład poniższe zapytanie zwróci wyłącznie te faktury wystawione dla klienta, których suma będzie mniejsza niż 10:

Nie przejmuj się, jeżeli to zapytanie będzie dla Ciebie zbyt skomplikowane. To nic dziwnego, używa ono wielu elementów składki SQL. Postaraj się przeanalizować je jeszcze raz. Spróbuj też samodzielnie eksperymentować. Zacznij od wywołania podzapytania i przeanalizowania jego wyników.

Dodatkowe materiały do nauki

Artykuł nie wyczerpuje tematu funkcji analitycznych. Zachęcam Cię do rzucenia okiem na dodatkowe materiały do nauki. Pamiętaj, iż dokumentacja Twojego silnika baz danych jest niezastąpiona ;) i zawiera dużo bardziej szczegółowe informacje.

• Tutorial dotyczący funkcji analitycznych dla PostgreSQL,
• Składnia funkcji analitycznych w PostgreSQL,
• Funkcje okna w PostgreSQL,
• Składnia funkcji analitycznych w SQLite,
• Materiały wykładowe z Politechniki Poznańskiej.

Podsumowanie

Po przeczytaniu tego artykułu wiesz już czym są funkcje analityczne. Wiesz czym takie funkcje różnią się od zwykłego grupowania. Wiesz czym są funkcje okna i jak ich używać. Po przerobieniu ćwiczeń możesz śmiało powiedzieć, iż udało Ci się sprawdzić wiedzę w praktyce. Gratulacje ;), funkcje analityczne to jedne z bardziej zaawansowanych elementów składki SQL.

Mam nadzieję, iż artykuł był dla Ciebie pomocny. Proszę podziel się nim ze swoimi znajomymi. Dzięki temu pozwolisz mi dotrzeć do nowych Czytelników, za co z góry dziękuję. jeżeli nie chcesz pominąć kolejnych artykułów dopisz się do samouczkowego newslettera i polub Samouczka Programisty na Facebooku.

Do następnego razu!