V tej vadnici se bomo seznanili z delovanjem klavzule PARTITION BY v SQL in odkrili, kako jo lahko uporabimo za particioniranje podatkov za bolj zrnato podmnožico.
Sintaksa:
Začnimo s sintakso klavzule PARTITION BY. Sintaksa je lahko odvisna od konteksta, v katerem jo uporabljate, vendar je tukaj splošna sintaksa:
IZBERI stolpec1, stolpec2, ...
NAD (PARTICIJA BY partition_column1, partition_column2, ...)
FROM ime_tabele
Dana sintaksa predstavlja naslednje elemente:
- stolpec1, stolpec2 – To se nanaša na stolpce, ki jih želimo vključiti v niz rezultatov.
- PARTITION BY stolpci – Ta klavzula določa, kako želimo razdeliti ali združiti podatke.
Vzorčni podatki
Ustvarimo osnovno tabelo z vzorčnimi podatki, da pokažemo, kako uporabljati klavzulo PARTITION BY. Za ta primer ustvarimo osnovno tabelo, ki hrani informacije o izdelku.
CREATE TABLE izdelki (
product_id INT PRIMARY KEY AUTO_INCREMENT,
ime_izdelka VARCHAR( 255 ),
kategorija VARCHAR( 255 ),
cena DECIMAL( 10 , 2 ),
količina INT,
datum_izteka DATE,
črtna koda BIGINT
);
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Kuharska kapa 25 cm' ,
'pekarna' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Prepeličja jajca - konzervirana' ,
'shramba' ,
17,99 ,
67 ,
'29.09.2023' ,
1708039594250 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Kava - Egg Nog Capuccino' ,
'pekarna' ,
92.53 ,
10 ,
'22.09.2023' ,
8704051853058 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Hruška - Bodeča' ,
'pekarna' ,
65.29 ,
48 ,
'2023-08-23' ,
5174927442238 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Pasta - Angel Hair' ,
'shramba' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Vino - Prosecco Valdobiaddene' ,
'proizvajati' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Pecivo - francosko mini sortirano' ,
'shramba' ,
36.73 ,
52 ,
'2023-05-29' ,
5963886298051 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Pomaranča - konzervirana, mandarina' ,
'proizvajati' ,
65,0 ,
1 ,
'20.4.2023' ,
6131761721332 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Svinjina - pleče' ,
'proizvajati' ,
55.55 ,
73 ,
'2023-05-01' ,
9343592107125 );
vstavi
v
izdelki (ime_izdelka,
kategorija,
cena,
količina,
Datum veljavnosti,
črtna koda)
vrednote ( 'Dc Hikiage Hira Huba' ,
'proizvajati' ,
56.29 ,
53 ,
'14.4.2023' ,
3354910667072 );
Ko imamo nastavitev vzorčnih podatkov, lahko nadaljujemo in uporabimo klavzulo PARTITION BY.
Osnovna uporaba
Recimo, da želimo izračunati skupne postavke za vsako kategorijo izdelkov v prejšnji tabeli. Uporabimo lahko PARTICION BY, da razdelimo elemente v edinstvene kategorije in nato določimo skupno količino v vsaki kategoriji.
Primer je naslednji:
IZBERI
ime izdelka,
kategorija,
količina,
SUM(količina) NAD (PARTICIJA PO kategoriji) AS total_items
OD
izdelki;
Upoštevajte, da v danem primeru podatke razdelimo s stolpcem »kategorija«. Nato uporabimo agregatno funkcijo SUM(), da določimo skupne postavke v vsaki kategoriji posebej. Rezultat prikazuje skupno število elementov v vsaki kategoriji.
Uporaba klavzule PARTITION BY
Če povzamemo, najpogostejša uporaba klavzule PARTITION BY je v povezavi z okenskimi funkcijami. Funkcija okna se uporablja za vsako particijo posebej.
Nekatere običajne okenske funkcije za uporabo s PARTITION BY vključujejo naslednje:
- SUM() – Izračunajte vsoto stolpca znotraj vsake particije.
- AVG() – Izračunajte povprečje stolpca znotraj vsake particije.
- COUNT() – Preštejte število vrstic v vsaki particiji.
- ROW_NUMBER() – dodelite edinstveno številko vrstice vsaki vrstici znotraj vsake particije.
- RANK() – dodelite rang vsaki vrstici znotraj vsake particije.
- DENSE_RANK() – dodelite gosto uvrstitev vsaki vrstici znotraj vsake particije.
- NTILE() – Razdelite podatke na kvantile znotraj vsake particije.
To je to!
Zaključek
V tej vadnici smo se naučili delati s klavzulo PARTITION BY v SQL, da razdelimo podatke na različne segmente in nato uporabimo posebno operacijo za vsako od nastalih particij posebej.