Kurs SQL i MySQL · Moduł 10: Transakcje i zaawansowany SQL
PARTITION BY i agregaty okna - rankingi w grupach
W tej lekcji4
Poznałeś już ROW_NUMBER() i RANK() liczone dla całej tabeli. Teraz wejdziemy na sam szczyt Strażnicy: nauczymy się dzielić wiersze na grupy i liczyć funkcje okna osobno w każdej grupie. Służy do tego klauzula PARTITION BY.
PARTITION BY - osobne okno dla każdej grupy
Wyobraź sobie, że chcesz ranking ksiąg według ceny, ale wewnątrz każdej kategorii z osobna. Bez PARTITION BY numerowalibyśmy wszystkie księgi razem. Z nią - licznik resetuje się przy każdej nowej kategorii:
SELECT tytul, kategoria_id, cena,
ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC) AS miejsce
FROM ksiazki;W kategorii 1 najdroższa księga dostanie miejsce = 1, kolejna 2, i tak dalej. Gdy przejdziemy do kategorii 2, licznik zaczyna od nowa. To jak osobny ranking na każdym regale.
Kolejność wewnątrz PARTITION BY
W klauzuli OVER (...) najpierw piszemy PARTITION BY (czym dzielimy), a potem ORDER BY (jak porządkujemy wewnątrz grupy):
OVER (PARTITION BY kategoria_id ORDER BY cena DESC)Agregaty jako funkcje okna
Najciekawsze jest to, że zwykłe agregaty - AVG, SUM, COUNT, MAX, MIN - też mogą działać jako funkcje okna. Wystarczy dodać OVER (...). Wtedy nie zwijają wierszy, tylko dopisują wynik obok każdego z nich:
SELECT tytul, kategoria_id, cena,
AVG(cena) OVER (PARTITION BY kategoria_id) AS srednia_kat
FROM ksiazki;Każda księga zachowa swój wiersz, ale obok pojawi się średnia cena jej kategorii. Dzięki temu od razu widać, czy dana księga jest droższa, czy tańsza od średniej na swoim regale - bez osobnego zapytania z GROUP BY.
Okno kontra GROUP BY
| Cecha | GROUP BY | Funkcja okna (OVER) |
|---|---|---|
| Liczba wierszy | zwija do jednego na grupę | zachowuje wszystkie |
| Widać szczegóły | nie | tak |
| Po co? | podsumowania | porównania wiersz po wierszu |
To najpotężniejsze narzędzie analityczne Biblioteki. Z PARTITION BY Strażnik widzi jednocześnie całość i szczegół - i nic mu nie umknie.
Widzisz błąd w tej lekcji?
Sprawdź się
Odpowiedz na pytania z tej lekcji. Wybierz odpowiedź, a od razu zobaczysz, czy jest poprawna.
1. Do czego służy PARTITION BY w funkcji okna?
2. Co zrobi AVG(cena) OVER (PARTITION BY kategoria_id) w zapytaniu?
To 2 z 4 pytań do tej lekcji. Pozostałe rozwiążesz w grze.
Zadania praktyczne w grze
- Edytor kodu
Napisz zapytanie, które pobierze tytul, kategoria_id oraz cena z tabeli ksiazki, a dodatkowo doda kolumnę o aliasie miejsce zawierającą wynik ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC).
- Układanie w poziomie
Ułóż w jednej linii klauzulę okna z podziałem na kategorie i porządkiem po cenie:
- Edytor kodu
Napisz zapytanie, które pobierze tytul, kategoria_id oraz cena z tabeli ksiazki, a dodatkowo doda kolumnę o aliasie srednia_kat zawierającą wynik AVG(cena) OVER (PARTITION BY kategoria_id).
- Klikanie w kolejności
Kliknij elementy w kolejności, aby zbudować funkcję okna z podziałem i porządkiem:
- Układanie w pionie
Ułóż linie zapytania z funkcją okna w poprawnej kolejności (od góry do dołu):
- Edytor kodu
Napisz zapytanie, które pobierze imie oraz nazwisko z tabeli czytelnicy, a dodatkowo doda kolumnę o aliasie numer zawierającą wynik ROW_NUMBER() OVER (ORDER BY data_zapisu).
- Układanie w pionie
Ułóż pełną transakcję wypożyczenia w poprawnej kolejności (od góry do dołu):
- Klikanie w kolejności
Kliknij elementy w kolejności, aby zbudować definicję widoku dostepne_ksiazki:
- Edytor kodu
Napisz wyrażenie CTE o nazwie ranking, które wybierze tytul, cena oraz ROW_NUMBER() OVER (ORDER BY cena DESC) AS numer z tabeli ksiazki. Następnie w głównym zapytaniu pobierz wszystkie kolumny z ranking. Użyj składni WITH ranking AS (SELECT ...) SELECT * FROM ranking;
- Układanie w poziomie
Ułóż w jednej linii początek wyrażenia CTE o nazwie ranking:
Zadania po tej lekcji
- PytanieKlauzula PARTITION BY
- PytanieAgregaty z OVER
- Zadanie z kodemRanking w kategoriach
- Ułóż w kolejnościOkno z PARTITION BY
- Zadanie z kodemŚrednia cena w kategorii
- Kliknij w kolejnościROW_NUMBER w każdej kategorii
- Ułóż w kolejnościZapytanie z funkcją okna
- PytanieFunkcje okna a wiersze
- Zadanie z kodemNumerowanie czytelników
- Ułóż w kolejnościPełna transakcja wypożyczenia
- Kliknij w kolejnościPełna definicja widoku dostepne_ksiazki
- Zadanie z kodemCTE z rankingiem
- Ułóż w kolejnościPoczątek wyrażenia CTE ranking
- PytanieKiedy używać transakcji