Kurs SQL i MySQL · Moduł 10: Transakcje i zaawansowany SQL

PARTITION BY i agregaty okna - rankingi w grupach

2 min czytania ·
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

CechaGROUP BYFunkcja okna (OVER)
Liczba wierszyzwija do jednego na grupęzachowuje wszystkie
Widać szczegółynietak
Po co?podsumowaniaporó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. 1. Do czego służy PARTITION BY w funkcji okna?

  2. 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:

  1. PytanieKlauzula PARTITION BY
  2. PytanieAgregaty z OVER
  3. Zadanie z kodemRanking w kategoriach
  4. Ułóż w kolejnościOkno z PARTITION BY
  5. Zadanie z kodemŚrednia cena w kategorii
  6. Kliknij w kolejnościROW_NUMBER w każdej kategorii
  7. Ułóż w kolejnościZapytanie z funkcją okna
  8. PytanieFunkcje okna a wiersze
  9. Zadanie z kodemNumerowanie czytelników
  10. Ułóż w kolejnościPełna transakcja wypożyczenia
  11. Kliknij w kolejnościPełna definicja widoku dostepne_ksiazki
  12. Zadanie z kodemCTE z rankingiem
  13. Ułóż w kolejnościPoczątek wyrażenia CTE ranking
  14. PytanieKiedy używać transakcji

Przydatne artykuły