Jest to szczególnie przydatne, gdy trzeba obliczyć agregacje lub rankingi dla grup wierszy bez faktycznego zwijania całego zestawu wyników.
Dołącz do nas w tym samouczku, podczas którego dowiemy się wszystkiego, co należy wiedzieć, aby rozpocząć pracę z klauzulą OVER.
Wymagania:
Zanim zagłębimy się w funkcjonalność i działanie klauzuli OVER, upewnij się, że znasz podstawy języka SQL. Zakładamy również, że posiadasz dostęp do bazy danych, którą możesz wykorzystać do sprawdzenia swojej wiedzy.
W naszym przypadku będziemy korzystać z bazy MySQL z przykładową bazą danych Sakila. Upewnij się tylko, że masz wystarczające uprawnienia i że silnik bazy danych obsługuje funkcje okna.
Składnia:
Jak wspomnieliśmy wcześniej, w większości przypadków klauzulę OVER używamy głównie w połączeniu z funkcjami okna.
W związku z tym możemy wyrazić składnię klauzuli w następujący sposób:
[PODZIAŁ WEDŁUG wyrażenia_partycji, ...]
[ZAMÓW WEDŁUG wyrażenia_sortowania [ASC | DESC], ...]
[specyfikacja_ramki]
)
W podanej składni możemy rozbić każdy komponent w następujący sposób:
-
– Odnosi się do funkcji okna, którą chcemy zastosować w określonym oknie wierszy, takiej jak SUM(), AVG(), ROW_NUMBER(), RANK itp. - Wyrażenie – określa kolumnę lub wyrażenie, dla którego stosowana jest funkcja okna.
- PARTITION BY – jest to opcjonalna klauzula, która dzieli zbiór wyników na partycje, przy czym każda partycja stanowi osobną jednostkę, w której stosowana jest funkcja. Wiersze w tej samej partycji mają te same wartości w określonych kolumnach.
- ORDER BY – określa kolejność przetwarzania wierszy w każdej partycji.
- specyfikacja_ramki – jest to opcjonalna klauzula, która definiuje ramkę wierszy w obrębie partycji. Typowe specyfikacje ramek obejmują WIERSZE MIĘDZY
I lub ZAKRES POMIĘDZY I
Mając to na uwadze, przeanalizujmy kilka praktycznych przykładów, jak z niego korzystać.
Przykład:
Zademonstrujmy, jak używać klauzuli, korzystając z przykładowej bazy danych Sakila. Rozważmy przykład, w którym musimy określić całkowity przychód dla każdej kategorii filmów.
Możemy użyć funkcji okna sumy z klauzulą OVER i kilkoma instrukcjami łączenia, jak pokazano w poniższym przykładzie:
WYBIERAĆnazwa.kategorii AS nazwa_kategorii,
film.title AS film_title,
film.rental_rate,
SUM(kwota.płatności) OVER (PODZIAŁ WEDŁUG nazwy.kategorii) AS całkowity_przychód
Z
film
DOŁĄCZYĆ
kategoria_filmu WŁ
film.film_id = kategoria_filmu.film_id
DOŁĄCZYĆ
kategoria WŁĄCZONA
film_kategoria.kategoria_id = kategoria.kategoria_id
DOŁĄCZYĆ
inwentarz WŁĄCZONY
film.film_id = inwentarz.film_id
DOŁĄCZYĆ
wynajem WŁĄCZONY
Inventory.Inventory_id = Rental.Inventory_id
DOŁĄCZYĆ
płatność WŁĄCZONA
najem.rental_id = płatność.rental_id
ZAMÓW PRZEZ
Nazwa Kategorii,
tytuł filmu;
W podanym zapytaniu zaczynamy od wybrania tytułu filmu, stawki czynszu i za pomocą wyrażenia suma (płatność.kwota przez podział według kategorii.nazwa) określamy sumę podziału każdej kategorii według nazwy kategorii.
Musimy użyć klauzuli PARTITION BY, aby mieć pewność, że obliczanie sumy rozpocznie się od nowa dla każdej unikalnej kategorii.
Wynikowy wynik jest następujący:
Masz to!
Wniosek
W tym przykładzie omówiliśmy podstawy pracy z klauzulą OVER w języku SQL. Nie jest to klauzula podstawowa i wymaga wcześniejszej znajomości innych funkcji SQL.