Selles õpetuses õpime tundma SQL-i klausli PARTITION BY toimimist ja avastame, kuidas saame seda kasutada andmete jaotamiseks üksikasjalikuma alamhulga jaoks.
Süntaks:
Alustame lause PARTITION BY süntaksist. Süntaks võib sõltuda kontekstist, milles seda kasutate, kuid siin on üldine süntaks:
VALI veerg1, veerg2, ...
ÜLE (PARTITSIOON partitsiooni_veerg1, partitsiooni_veerg2, ...)
FROM tabeli_nimi
Antud süntaks esindab järgmisi elemente:
- veerg1, veerg2 – see viitab veergudele, mida soovime tulemuskomplekti kaasata.
- PARTITION BY veergude järgi – see klausel määratleb, kuidas me soovime andmeid sektsioonida või rühmitada.
Näidisandmed
Loome näidisandmetega põhitabeli, et näidata, kuidas PARTITION BY klauslit kasutada. Selle näite jaoks loome põhitabeli, mis salvestab tooteteabe.
CREATE TABLE tooted (
product_id INT PRIMARY KEY AUTO_INCREMENT,
toote_nimi VARCHAR( 255 ),
kategooria VARCHAR( 255 ),
hind DECIMAAL( 10 , 2 ),
kogus INT,
expiration_date DATE,
vöötkood BIGINT
);
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'koka müts 25 cm' ,
'pagariäri' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Vutimunad – konserveeritud' ,
'sahver' ,
17.99 ,
67 ,
'2023-09-29' ,
1708039594250 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Kohv – muna-nog capuccino' ,
'pagariäri' ,
92,53 ,
10 ,
'2023-09-22' ,
8704051853058 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Pirn – kipitav' ,
'pagariäri' ,
65,29 ,
48 ,
'2023-08-23' ,
5174927442238 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Pasta – inglijuuksed' ,
'sahver' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Vein – Prosecco Valdobiaddene' ,
'tootma' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Kondiitritooted – prantsuse miniassortii' ,
'sahver' ,
36,73 ,
52 ,
'2023-05-29' ,
5963886298051 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Apelsin – konserveeritud, mandariin' ,
'tootma' ,
65,0 ,
1 ,
'2023-04-20' ,
6131761721332 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Sea õlg' ,
'tootma' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
sisestada
sisse
tooted (toote_nimi,
kategooria,
hind,
kogus,
expiration_date,
vöötkood)
väärtused ( 'Dc Hikiage Hira Huba' ,
'tootma' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Kui oleme andmenäidise seadistanud, saame jätkata ja kasutada klauslit PARTITION BY.
Põhikasutus
Oletame, et tahame arvutada eelmises tabelis iga tootekategooria kaupade kogusumma. Saame kasutada jaotist PARTITION BY, et jagada üksused unikaalsetesse kategooriatesse ja seejärel määrata iga kategooria kogusumma.
Näide on järgmine:
VALI
tootenimi,
kategooria,
kogus,
SUM(kogus) ÜLE (PARTITSIOON kategooria järgi) AS total_items
FROM
tooted;
Pange tähele, et antud näites jagame andmed veeru 'kategooria' abil. Seejärel kasutame koondfunktsiooni SUM(), et määrata iga kategooria üksuste koguarv eraldi. Tulemus näitab iga kategooria üksuste koguarvu.
PARTITION BY klausli kasutamine
Kokkuvõtteks võib öelda, et PARTITION BY klausli kõige levinum kasutusjuht on koos aknafunktsioonidega. Aknafunktsiooni rakendatakse igale sektsioonile eraldi.
Mõned levinumad aknafunktsioonid, mida koos PARTITION BY-ga kasutada, on järgmised:
- SUM() – iga partitsiooni veeru summa arvutamine.
- AVG() – arvutab iga partitsiooni veeru keskmise.
- COUNT() – loendab iga partitsiooni ridade arvu.
- ROW_NUMBER() – määrake igale sektsiooni igale reale kordumatu reanumber.
- RANK() – määrake igale partitsiooni reale auaste.
- DENSE_RANK() – määrake igale sektsiooni igale reale tihe auaste.
- NTILE() – jagage andmed igas partitsioonis kvantilideks.
See on kõik!
Järeldus
Selles õpetuses õppisime, kuidas töötada SQL-i klausliga PARTITION BY, et jaotada andmed erinevateks segmentideks ja seejärel rakendada iga saadud partitsiooni jaoks eraldi toiming.