Muszę umieć te funkcje okna, czy nie muszę?
Takie pytanie często pada na konsultacjach, które prowadzę.
Funkcje okna (po angielsku window functions) są w mojej opinii najtrudniejszymi funkcjami, jakie możemy spotkać w SQL, nie znaczy to jednak, iż są ekstremalną rzadkością w zastosowaniach komercyjnych.
Uważam, iż osoba początkująca czy przebranżawiająca się powinna przynajmniej być świadoma w jakich momentach te funkcje się stosuje, a osoba bardziej zaawansowana powinna umieć zastosować je w praktyce. Sama składnia jest dość skomplikowana (jak na SQL), ale zawsze można ją wygooglować – największym problemem jednak jest zrozumienie w jakim momencie należy po te funkcje sięgnąć.
Skąd się wzięła nazwa funkcja okna?
Okno odnosi się do zestawu wierszy, na których dana funkcja działa. Tradycyjnie funkcje agregujące (SUM, COUNT, AVG itd.) są wykonywane na całym zbiorze danych, a funkcje okna umożliwiają wykonanie ich tylko na pewnej liczbie wierszy powiązanych z obecnym rekordem, np. oblicz sumę z 3 wierszy powyżej.
Ja jednak chciałabym zaproponować inne skojarzenie: funkcje okna jako okno na świat.
Zazwyczaj w SQL nie mamy możliwości zobaczenia jak wartości z jednego wiersza mają się do wartości w innych wierszach. Są większe, mniejsze? Nasz wiersz jest pierwszy? Ostatni? Nie wiadomo. Każdy wiersz jest “zamknięty” i nie wie co się dzieje u jego sąsiadów. Analogicznie jak okno w domu, które umożliwia tej chytrej sąsiadce obserwatorce podglądnąć co tam się dzieje u sąsiadów, tak funkcje okna pozwalają podglądnąć co tam się dzieje u innych wierszy.
Analogia okna na świat moim zdaniem lepiej obrazuje zastosowanie funkcji okna, bo adekwatnie nie są one stosowane tylko i wyłącznie wtedy, gdy chcemy wykonać operacje na mniejszym zestawie wierszy, do czego odnosi się pierwotna nazwa. Często sięgniemy po funkcje okna chcąc wykonać operacje na całym zestawie danych, ale właśnie stosując odniesienie jak wartości z jednego rekordu wyglądają w stosunku do innych.
Sortowanie jest mega ważne
Sortowanie jest kluczową czynnością wykonywaną podczas użycia window functions. Zresztą jak ta chytra sąsiadka – nie po to się ogląda innych, aby oglądać, ale żeby wiedzieć kto ma lepiej, kto ma gorzej, kto ma więcej, a kto mniej. o ile nasze obliczenia wymagają operacji sortowania to od razu powinna nam się zapalić lampka w głowie “funkcje okna”!
Pomyślisz sobie – a co z ORDER BY stosowanym na końcu query? Oczywiście też umożliwia sortowanie wierszy, ale ORDER BY na końcu ma (głównie) zastosowanie w samym sortowaniu wyników końcowych lub gdy chcemy pokazać tylko N pierwszych lub ostatnich rekordów, np. 5 ostatnich transakcji. Sortowanie dzięki funkcji okna wykorzystujemy, gdy wyniki sortowania są nam potrzebne do wykonania samych obliczeń, a nie tylko pokazania wierszy na końcu.
Dlatego w window functions kluczowa będzie klauzula OVER (ORDER BY column ASC/DESC), która mówi dzięki jakiej kolumny (column) sortowanie powinno nastąpić oraz w którą stronę (ASC/DESC). Obligatoryjna jest też funkcja na samym początku, którą definiujemy co chcemy zrobić.
funkcja_okna() OVER (ORDER BY kolumna_do_sortowania ASC/DESC)Przykłady najczęstszych operacji, które wymagają sortowania wraz z funkcjami okna:
Przykłady można wypróbować uruchamiając ten kod na tej stronie w bazie danych PostgreSQL.
1. Numerowanie wierszy
Ponumeruj wiersze na podstawie kwoty transakcji malejąco:
SELECT *, RANK() OVER (ORDER BY amount) AS ranking FROM transactions
2. Wyciągnięcie N-tego wiersza (pierwszego, drugiego, piątego, dziesiątego itd.)
Wyciągnij drugą transakcję pod względem kwoty:
SELECT * FROM (SELECT *, RANK() OVER (ORDER BY amount) AS ranking FROM transactions) WHERE ranking = 2
3. Obliczenie mediany i percentyli
Oblicz medianę kwot transakcji:
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY amount) AS median_amount FROM transactions
4. Porównanie poprzedniej lub kolejnej wartości
Oblicz różnicę między sumą transakcji w danym roku a rokiem poprzednim:
SELECT year_, year_amount, LAG(year_amount) OVER (ORDER BY year_) AS last_year_amount, year_amount - LAG(year_amount) OVER (ORDER BY year_) AS last_year_difference FROM ( SELECT EXTRACT(year FROM created) AS year_, SUM(amount) AS year_amount FROM transactions GROUP BY year_ )
5. Uzupełnienie pierwszą lub ostatnią wartością
Oblicz różnicę między czasem transakcji a pierwszą transakcją:
SELECT *, FIRST_VALUE(created) OVER (ORDER BY created) AS first_created, created - FIRST_VALUE(created) OVER (ORDER BY created) AS first_created_difference from transactions
6. Obliczenie sumy narastającej (ang. cumulative sum)
Oblicz narastającą sumę w każdym roku:
SELECT year_, year_amount, SUM(year_amount) OVER (ORDER BY year_) AS cum_sum FROM ( SELECT EXTRACT(year FROM created) AS year_, SUM(amount) AS year_amount FROM transactions GROUP BY year_ )
Grupowanie
Mając z tyłu głowy, iż funkcje okna są głównie używane przy sortowaniu, możemy poznać ich kolejną funkcjonalność: operacje możemy wykonywać na wybranej grupie wierszy, nie tylko na całym zestawie. Czyli wracamy do tej pierwotnej nazwy skąd się wzięło “okno” – wykonujemy obliczenia na “oknie”, czyli zestawie komórek. Grupa / zestaw komórek jest definiowany poprzez klauzule PARTITION BY:
funkcja_okna() OVER (PARTITION BY kolumna_do_grupowania ORDER BY kolumna_do_sortowania ASC/DESC)Przykłady:
Ponumeruj transakcje ze względu na kwotę, numeruj każdy kraj osobno:
SELECT u.id, u.country, tr.amount, RANK() OVER (PARTITION BY u.country ORDER BY tr.amount) AS ranking FROM transactions tr JOIN users u on tr.user_id = u.id ORDER BY u.country, ranking
Dodaj kolumnę pokazującą pierwszy czas transakcji dla wszystkich kraju:
SELECT tr.*, u.country, FIRST_VALUE(tr.created) OVER (PARTITION BY u.country ORDER BY tr.created) AS first_created FROM transactions tr JOIN users u on tr.user_id = u.id ORDER BY u.country
Dodatkowo nie musimy definiować okna jako kategorii z wybranej kolumny, ale możemy stworzyć ruchome okno, np. liczące sumę z N ostatnich wierszy.
Policz sumę z 3 ostatnich wierszy (2 poprzednie oraz 1 z tej samej transakcji):
SELECT created, amount, SUM(amount) OVER (ORDER BY created ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_3_amount FROM transactions
Take-home messages
- Ważne jest, aby pamiętać kiedy warto sięgnąć po funkcje okna
- Zapamiętaj analogię: funkcje okna to okno na świat! Umożliwiają podglądnięcie co dzieje się u sąsiadów i odniesienie się do wartości z innych wierszy
- Kluczową operacją przy użyciu funkcji okna jest sortowanie
- Funkcje okna umożliwiają wykonywanie obliczeń nie tylko na całym zestawie, ale także na jego części. Można je definiować poprzez kategorie w kolumnach lub mogą to być ruchome okienka.
keywords: analityk danych, analityk danych co trzeba umieć, analityk danych jak zostać, analityk danych jak zacząć, analiza danych, analizowanie danych, big data, dane, data analyst, data science, jak zostać analitykiem danych, kursy analizy danych, nauka programowania, nauka SQL, nauka sql, nauka sequel, SQL co to, SQL co to jest, przebranżowienie, przebranżowienie do IT, SQL, analityk danych sql, SQL, język zapytań, bazy danych, jak zostać analitykiem danych, kursy SQL, jak się nauczyć SQL, SQL co oznacza, który SQL, SQL jak zacząć, jak, SQL jak się nauczyć, co to jest SQL, czym jest SQL, co znaczy SQL, kody SQL, bazy SQL, który SQL, czy SQL jest trudny, czy SQL jest darmowy, co SQL, co oznacza SQL, SQL co to znaczy, SQL co to za skrót, SQL co oznacza, SQL do czego, SQL do czego służy, SQL czy python, SQL czy warto, SQL blog, SQL czy trudny, SQL czy excel, SQL czy VBA, SQL vs excel, SQL dla pracujących w excelu, SQL dla początkujących, funkcje okna, window functions, funkcje okna SQL, window functions SQL, funkcje okna syntaks, funkcje okna kiedy użyć, funkcje okna kiedy uzyc, window functions vs group by, window functions vs order by, window function, window function sql, window functions vs aggregate functions, window functions examples, window functions how to use, window functions in sql
