Funkcje okna SQL

Zaczynasz tutaj? Ta lekcja jest częścią pełnego samouczka dotyczącego używania języka SQL do analizy danych. Sprawdź początek.

W tej lekcji omówimy:

• Wprowadzenie do funkcji okien
• Podstawowa składnia okienkowa
• Zwykli podejrzani: SUMA, COUNT i AVG
• ROW_NUMBER ()
• RANK () i DENSE_RANK ()
• NTILE
• LAG i LEAD
• Definiowanie aliasu okna
• Zaawansowane techniki okienkowania

W tej lekcji wykorzystano dane z programu Capital Bikeshare w Waszyngtonie, który publikuje szczegółowe dane historyczne z podróży na ich stronie internetowej. Dane zostały pobrane w lutym 2014 r., Ale są ograniczone do danych zebranych w pierwszym kwartale 2012 r. Każdy wiersz przedstawia jedną jazdę. Większość pól nie wymaga wyjaśnień, z wyjątkiem rider_type: „Zarejestrowany” oznacza miesięczne członkostwo w programie wspólnych przejazdów, „Przypadkowy” oznacza, że pasażer kupił 3-dniowy bilet. Pola start_time i end_time zostały wyczyszczone z ich pierwotnych postaci, aby dopasować je do formatowania daty SQL – są one przechowywane w tej tabeli jako sygnatury czasowe.

Wprowadzenie do funkcji okien

Dokumentacja PostgreSQL doskonale wprowadza pojęcie Funkcji okien:

Funkcja okna wykonuje obliczenia w zbiorze wierszy tabeli, które są w jakiś sposób powiązane z bieżącym wierszem. Jest to porównywalne z typem obliczeń, które można wykonać za pomocą funkcji agregującej. Jednak w przeciwieństwie do zwykłych funkcji agregujących użycie okna funkcja nie powoduje grupowania wierszy w jeden wiersz wyjściowy – wiersze zachowują swoje oddzielne tożsamości. Za kulisami funkcja okna ma dostęp do więcej niż tylko bieżącego wiersza wyniku zapytania.

Najbardziej praktycznym przykładem jest suma bieżąca:

Możesz zobaczyć, że powyższe zapytanie tworzy agregację (running_total) bez użycia GROUP BY. Przełóżmy składnię i zobaczmy, jak to działa.

Podstawowa składnia okienkowa

Pierwsza część powyższej agregacji, SUM(duration_seconds) wygląda bardzo podobnie do każdej innej agregacji. Dodanie OVER oznacza, że jest to funkcja okna. Powyższą agregację można odczytać jako „weź sumę duration_seconds w całym zestawie wyników, w kolejności start_time. „

Jeśli chcesz zawęzić okno z całego zbioru danych do poszczególnych grup w zbioru danych, możesz użyć PARTITION BY, aby to zrobić:

Powyższe zapytania grupują i porządkują zapytanie według start_terminal . W ramach każdej wartości start_terminal jest ona uporządkowana według start_time, a sumy bieżące z bieżącego wiersza i wszystkich poprzednich wierszy duration_seconds. Przewiń w dół, aż wartość start_terminal się zmieni, a zauważysz, że running_total zaczyna się od nowa. Tak właśnie się dzieje, gdy grupujesz za pomocą PARTITION BY. W przypadku, gdy „nadal będziesz zaskoczony przez ORDER BY, po prostu uporządkuje według wskazanej kolumny (s) w taki sam sposób, jak klauzula ORDER BY, z tą różnicą, że traktuje każdą partycję jako osobną. Tworzy również sumę bieżącą – bez ORDER BY, każda wartość będzie po prostu sumą wszystkich wartości duration_seconds w odpowiednim start_terminal. Spróbuj uruchomić powyższe zapytanie bez ORDER BY, aby uzyskać pomysł:

ORDER i PARTITION zdefiniuj to, co jest określane jako” okno „- uporządkowany podzbiór danych, na których wykonywane są obliczenia.

Uwaga: nie można używać funkcji okna i standardowych kwerendy. Dokładniej, nie można dołączyć funkcji okna do klauzuli GROUP BY.

Zwykli podejrzani: SUMA, COUNT i ŚREDNIA

Korzystając z funkcji okna, możesz zastosować te same agregacje, co w normalnych okolicznościach— div id = „891e999974”> , COUNT i AVG. Najłatwiejszym sposobem ich zrozumienia jest ponowne uruchomienie poprzedniego przykładu z kilkoma dodatkowymi funkcjami. Make

Alternatywnie, te same funkcje z ORDER BY:

Upewnij się, że podłączasz te dwa poprzednie zapytania do trybu i uruchamiasz je. Ten następny problem praktyczny jest bardzo podobny do przykładów, więc spróbuj zmodyfikować powyższy kod, zamiast zaczynać od zera.

ROW_NUMBER ()

ROW_NUMBER() robi to, na co wygląda – wyświetla numer danego wiersza. Zaczyna się 1 i numeruje wiersze zgodnie z ORDER BY częścią instrukcji window. ROW_NUMBER() nie wymaga podania zmiennej w nawiasach:

Użycie klauzuli PARTITION BY pozwoli Ci zacznij ponownie liczyć 1 w każdej partycji. Następujące zapytanie ponownie rozpoczyna liczenie dla każdego terminala:

RANK () i DENSE_RANK ()

RANK() różni się nieco od ROW_NUMBER(). Jeśli zamówisz na przykład start_time, może się zdarzyć, że niektóre terminale mają przejażdżki z dwoma identycznymi czasami rozpoczęcia. W tym przypadku mają one tę samą rangę, podczas gdy ROW_NUMBER() nadaje im różne liczby. W poniższym zapytaniu zauważysz czwartą i piątą obserwację dla start_terminal 31000 – obie mają rangę 4, a następujący wynik otrzymuje rangę 6:

W zależności od aplikacji możesz również użyć DENSE_RANK() zamiast RANK(). Wyobraź sobie sytuację, w której trzy wpisy mają tę samą wartość. Używając któregokolwiek polecenia, wszyscy otrzymają tę samą rangę. Na potrzeby tego przykładu powiedzmy, że jest to „s” 2 ”. Oto jak te dwie komendy inaczej oceniają następne wyniki:

• RANK() nadałoby identycznym wierszom rangę 2, a następnie pominęłyby rangę 3 i 4, więc następny wynik to 5
• DENSE_RANK() nadal da wszystkim identycznym wierszom rangę 2, ale następny wiersz będzie miał 3 – nie rangi zostałyby pominięte.

NTILE

Za pomocą funkcji okna można określić, do jakiego percentylu (lub kwartylu lub innego podziału) należy dany wiersz. Składnia to NTILE(*# of buckets*). W tym przypadku ORDER BY określa, której kolumny użyć do określenia kwartyli (lub dowolnej liczby „kafelków określić). Na przykład:

Patrząc na wyniki powyższego zapytania, widać, że kolumna percentile nie oblicza dokładnie tak, jak można by się spodziewać. Jeśli tylko miał dwa rekordy i mierzyłeś percentyle, można by oczekiwać, że jeden rekord będzie określał pierwszy percentyl, a drugi 100. percentyl. Korzystając z funkcji NTILE, w rzeczywistości zobaczysz jeden rekord w 1. percentylu i jeden w 2. percentylu. Możesz to zobaczyć w wynikach dla start_terminal 31000 – kolumna percentile wygląda jak ranking liczbowy. Jeśli przewiniesz w dół do start_terminal 31007, widzi, że poprawnie oblicza percentyle, ponieważ istnieje ponad 100 rekordów dla tego start_terminal. Jeśli pracujesz z bardzo małymi oknami, miej to na uwadze i rozważ użycie kwartyli lub podobnie małych zespoły.

LAG i LEAD

Często przydatne może być porównanie wierszy z wierszami poprzedzającymi lub następnymi, zwłaszcza jeśli otrzymałeś dane w odpowiedniej kolejności. Możesz użyć LAG lub LEAD, aby utworzyć kolumny pobierające wartości z innych wiersze – wszystko, co musisz zrobić, to wpisać, z której kolumny chcesz wyciągać i ile rzędów dalej chcesz ciągnąć. LAG pobiera z poprzednich wierszy, a LEAD pobiera z następujących wierszy:

Jest to szczególnie przydatne, jeśli chcesz obliczyć różnice między wierszami:

Pierwszy wiersz kolumny difference jest pusty, ponieważ nie ma poprzedniego wiersza, z którego można pobrać. Podobnie użycie LEAD spowoduje utworzenie wartości null na końcu zbioru danych. Jeśli chcesz, aby wyniki były trochę czystsze, możesz zawinąć je w zewnętrzne zapytanie, aby usunąć wartości null:

Definiowanie aliasu okna

Jeśli planujesz pisać kilka funkcji okna do tego samego zapytania, używając tego samego okna, możesz utworzyć alias.Weź powyższy przykład NTILE:

Można to przepisać jako:

WINDOW klauzula, jeśli jest dołączona, powinna zawsze występować po klauzuli WHERE.

Zaawansowane techniki okienkowania

Możesz sprawdzić pełną listę okien funkcje w Postgresie (używane w trybie składni) w dokumentacji Postgres. Jeśli używasz funkcji okna na podłączonej bazie danych, powinieneś spojrzeć na odpowiedni przewodnik po składni dla twojego systemu.