W tym samouczku dowiemy się, jak działa klauzula PARTITION BY w języku SQL i odkryjemy, jak możemy jej użyć do podziału danych w celu uzyskania bardziej szczegółowego podzbioru.
Składnia:
Zacznijmy od składni klauzuli PARTITION BY. Składnia może zależeć od kontekstu, w którym jej używasz, ale oto ogólna składnia:
WYBIERZ kolumnę 1, kolumnę 2, ...
OVER (PODZIAŁ BY kolumna_partycji1, kolumna_partycji2, ...)
Z nazwa_tabeli
Podana składnia reprezentuje następujące elementy:
- kolumna1, kolumna2 – odnosi się do kolumn, które chcemy uwzględnić w zestawie wynikowym.
- PARTITION BY kolumny – Ta klauzula definiuje, w jaki sposób chcemy podzielić lub pogrupować dane.
Przykładowe dane
Stwórzmy podstawową tabelę z przykładowymi danymi, aby zademonstrować sposób użycia klauzuli PARTITION BY. W tym przykładzie utwórzmy podstawową tabelę przechowującą informacje o produkcie.
UTWÓRZ TABELI produktów (
id_produktu INT KLUCZ PODSTAWOWY AUTO_INCREMENT,
nazwa_produktu VARCHAR( 255 ),
kategoria VARCHAR( 255 ),
cena DZIESIĘTNA( 10 , 2 ),
ilość INT,
data_wygaśnięcia DATA,
kod kreskowy BIGINT
);
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Kapelusz szefa kuchni 25 cm” ,
'piekarnia' ,
24,67 ,
57 ,
„2023-09-09” ,
2854509564204 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Jajka Przepiórcze – Konserwowe” ,
'spiżarnia' ,
17,99 ,
67 ,
„2023-09-29” ,
1708039594250 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Kawa - Nog Jajeczny Capuccino” ,
'piekarnia' ,
92,53 ,
10 ,
„22.09.2023” ,
8704051853058 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Gruszka - Kłująca” ,
'piekarnia' ,
65,29 ,
48 ,
„23.08.2023” ,
5174927442238 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Makaron - Włosy Anioła” ,
'spiżarnia' ,
48,38 ,
59 ,
„2023-08-05” ,
8008123704782 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Wino – Prosecco Valdobiaddene” ,
'produkować' ,
44.18 ,
3 ,
„2023-03-13” ,
6470981735653 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Ciasto francuskie Mini różne” ,
'spiżarnia' ,
36,73 ,
52 ,
„2023-05-29” ,
5963886298051 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Pomarańczowy – konserwowy, mandaryński” ,
'produkować' ,
65,0 ,
1 ,
„2023-04-20” ,
6131761721332 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( 'Łopatka wieprzowa' ,
'produkować' ,
55,55 ,
73 ,
„2023-05-01” ,
9343592107125 );
wstawić
do
produkty (nazwa_produktu,
Kategoria,
cena,
ilość,
termin ważności,
kod kreskowy)
wartości ( „Dc Hikiage Hira Huba” ,
'produkować' ,
56,29 ,
53 ,
„14.04.2023” ,
3354910667072 );
Po skonfigurowaniu przykładowych danych możemy kontynuować i użyć klauzuli PARTITION BY.
Podstawowe użycie
Załóżmy, że chcemy obliczyć łączną liczbę pozycji dla każdej kategorii produktów z poprzedniej tabeli. Możemy użyć PARTITION BY, aby podzielić elementy na unikalne kategorie, a następnie określić całkowitą ilość w każdej kategorii.
Przykład jest następujący:
WYBIERAĆ
Nazwa produktu,
Kategoria,
ilość,
SUMA(ilość) OVER (PODZIAŁ WEDŁUG kategorii) AS suma_items
Z
produkty;
Zauważ, że w podanym przykładzie dzielimy dane za pomocą kolumny „kategoria”. Następnie używamy funkcji agregującej SUM() w celu oddzielnego określenia łącznej liczby pozycji w każdej kategorii. Wynik pokazuje łączną liczbę elementów w każdej kategorii.
Korzystanie z klauzuli PARTITION BY
Podsumowując, najczęstszym przypadkiem użycia klauzuli PARTITION BY jest połączenie z funkcjami okna. Funkcja okna stosowana jest do każdej partycji osobno.
Niektóre z typowych funkcji okna, których można używać z PARTITION BY, obejmują:
- SUM() – Oblicz sumę kolumny w każdej partycji.
- AVG() — Oblicza średnią kolumnę w każdej partycji.
- COUNT() – Policz liczbę wierszy w każdej partycji.
- ROW_NUMBER() – Przypisz unikalny numer wiersza do każdego wiersza w każdej partycji.
- RANK() – Przypisz rangę do każdego wiersza w każdej partycji.
- DENSE_RANK() – Przypisz gęstą rangę do każdego wiersza w każdej partycji.
- NTILE() – Podziel dane na kwantyle w obrębie każdej partycji.
Otóż to!
Wniosek
W tym samouczku nauczyliśmy się, jak korzystać z klauzuli PARTITION BY w języku SQL, aby podzielić dane na różne segmenty, a następnie zastosować określoną operację osobno do każdej z powstałych partycji.