Šajā apmācībā mēs uzzināsim par klauzulas PARTITION BY darbību SQL un uzzināsim, kā mēs varam to izmantot, lai sadalītu datus sīkākai apakškopai.
Sintakse:
Sāksim ar klauzulas PARTITION BY sintaksi. Sintakse var būt atkarīga no konteksta, kurā to lietojat, taču šeit ir vispārīgā sintakse:
ATLASĪT kolonnu 1, kolonnu 2, ...
OVER (PARTITION BY partition_column1, partition_column2, ...)
NO tabulas_nosaukums
Dotā sintakse attēlo šādus elementus:
- kolonna 1, kolonna 2 — tas attiecas uz kolonnām, kuras mēs vēlamies iekļaut rezultātu kopā.
- PARTITION BY kolonnas — šī klauzula nosaka, kā mēs vēlamies sadalīt vai grupēt datus.
Datu paraugi
Izveidosim pamata tabulu ar datu paraugu, lai parādītu, kā izmantot klauzulu PARTITION BY. Šajā piemērā izveidosim pamata tabulu, kurā tiek glabāta informācija par produktu.
CREATE TABLE produkti (
product_id INT PRIMARY KEY AUTO_INCREMENT,
produkta_nosaukums VARCHAR( 255 ),
kategorija VARCHAR( 255 ),
cena DECIMAL( 10 , 2 ),
daudzums INT,
expiration_date DATE,
svītrkods BIGINT
);
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Pavāra cepure 25 cm' ,
'maizes ceptuve' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Paipalu olas - konservētas' ,
'pieliekamais' ,
17.99 ,
67 ,
'2023-09-29' ,
1708039594250 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( “Kafija – olu kapučīno” ,
'maizes ceptuve' ,
92.53 ,
10 ,
“2023-09-22” ,
8704051853058 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Bumbieris — dzeloņdūris' ,
'maizes ceptuve' ,
65.29 ,
48 ,
'2023-08-23' ,
5174927442238 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Makaroni - eņģeļu mati' ,
'pieliekamais' ,
48.38 ,
59 ,
“2023-08-05” ,
8008123704782 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Vīns - Prosecco Valdobiaddene' ,
'ražot' ,
44.18 ,
3 ,
“2023-03-13” ,
6470981735653 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Konditorejas izstrādājumi — franču mini asorti' ,
'pieliekamais' ,
36.73 ,
52 ,
'2023-05-29' ,
5963886298051 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Apelsīns - konservēts, mandarīns' ,
'ražot' ,
65.0 ,
1 ,
“2023-04-20” ,
6131761721332 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Cūkgaļa - plecs' ,
'ražot' ,
55.55 ,
73 ,
“2023-05-01” ,
9343592107125 );
ievietot
iekšā
produkti (produkta_nosaukums,
kategorija,
cena,
daudzums,
derīguma termiņš,
svītrkods)
vērtības ( 'Dc Hikiage Hira Huba' ,
'ražot' ,
56.29 ,
53 ,
“2023-04-14” ,
3354910667072 );
Kad esam iestatījuši datu paraugu, varam turpināt un izmantot klauzulu PARTITION BY.
Pamata lietošana
Pieņemsim, ka mēs vēlamies aprēķināt kopējo preču skaitu katrai produktu kategorijai iepriekšējā tabulā. Mēs varam izmantot PARTITION BY, lai sadalītu vienumus unikālās kategorijās un pēc tam noteiktu kopējo daudzumu katrā kategorijā.
Piemērs ir šāds:
ATLASĪT
produkta nosaukums,
kategorija,
daudzums,
SUM(daudzums) OVER (DAĻA PĒC kategorijas) AS total_items
NO
produkti;
Ņemiet vērā, ka dotajā piemērā mēs sadalām datus, izmantojot kolonnu “kategorija”. Pēc tam mēs izmantojam SUM() apkopošanas funkciju, lai noteiktu kopējo vienumu skaitu katrā kategorijā atsevišķi. Rezultāts parāda kopējo vienumu skaitu katrā kategorijā.
Izmantojot klauzulas PARTITION BY
Rezumējot, visizplatītākais klauzulas PARTITION BY lietošanas gadījums ir saistīts ar loga funkcijām. Logu funkcija tiek piemērota katram nodalījumam atsevišķi.
Dažas no izplatītākajām logu funkcijām, ko izmantot kopā ar PARTITION BY, ir šādas:
- SUM() — aprēķina kolonnas summu katrā nodalījumā.
- AVG() — aprēķina kolonnas vidējo vērtību katrā nodalījumā.
- SKAITĪT () — saskaitiet rindu skaitu katrā nodalījumā.
- ROW_NUMBER() — piešķiriet unikālu rindas numuru katrai rindai katrā nodalījumā.
- RANK() – piešķiriet rangu katrai rindai katrā nodalījumā.
- DENSE_RANK() – piešķiriet blīvu rangu katrai rindai katrā nodalījumā.
- NTILE() — sadaliet datus kvantilēs katrā nodalījumā.
Tieši tā!
Secinājums
Šajā apmācībā mēs uzzinājām, kā strādāt ar klauzulu PARTITION BY SQL, lai sadalītu datus dažādos segmentos un pēc tam piemērotu konkrētu darbību katram no iegūtajiem nodalījumiem atsevišķi.