PARTITION BY i agregaty okna - rankingi w grupach

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:

1SELECT tytul, kategoria_id, cena,
2       ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC) AS miejsce
3FROM 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):

1OVER (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:

1SELECT tytul, kategoria_id, cena,
2       AVG(cena) OVER (PARTITION BY kategoria_id) AS srednia_kat
3FROM 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.