I denne øvelse lærer vi om PARTITION BY-klausulens virkemåde i SQL og opdager, hvordan vi kan bruge den til at partitionere dataene til et mere granulært undersæt.
Syntaks:
Lad os starte med syntaksen for PARTITION BY-sætningen. Syntaksen kan afhænge af den kontekst, du bruger den i, men her er den generelle syntaks:
VÆLG kolonne1, kolonne2, ...
OVER (PARTITION BY partition_column1, partition_column2, ...)
FRA tabelnavn
Den givne syntaks repræsenterer følgende elementer:
- kolonne1, kolonne2 – Dette refererer til de kolonner, som vi ønsker at inkludere i resultatsættet.
- PARTITION BY kolonner – Denne klausul definerer, hvordan vi ønsker at partitionere eller gruppere dataene.
Eksempel på data
Lad os oprette en grundlæggende tabel med eksempeldata for at demonstrere, hvordan man bruger PARTITION BY-sætningen. Lad os i dette eksempel oprette en grundlæggende tabel, der gemmer produktinformationen.
OPRET TABLE produkter (
product_id INT PRIMARY KEY AUTO_INCREMENT,
produktnavn VARCHAR( 255 ),
kategori VARCHAR( 255 ),
pris DECIMAL( 10 , 2 ),
mængde INT,
udløbsdato DATE,
stregkode BIGINT
);
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Kokkehat 25 cm' ,
'bageri' ,
24,67 ,
57 ,
'2023-09-09' ,
2854509564204 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Vagtelæg - dåse' ,
'spisekammer' ,
17,99 ,
67 ,
'2023-09-29' ,
1708039594250 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Kaffe - Egg Nog Capuccino' ,
'bageri' ,
92,53 ,
10 ,
'2023-09-22' ,
8704051853058 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Pære - stikkende' ,
'bageri' ,
65,29 ,
48 ,
'23-08-2023' ,
5174927442238 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Pasta - Angel Hair' ,
'spisekammer' ,
48,38 ,
59 ,
'2023-08-05' ,
8008123704782 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Vin - Prosecco Valdobiaddene' ,
'fremstille' ,
44,18 ,
3 ,
'2023-03-13' ,
6470981735653 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Brød - Fransk Mini Assorted' ,
'spisekammer' ,
36,73 ,
52 ,
'2023-05-29' ,
5963886298051 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Orange - dåse, mandarin' ,
'fremstille' ,
65,0 ,
1 ,
'2023-04-20' ,
6131761721332 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Grise skulder' ,
'fremstille' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
indsætte
ind i
produkter (produktnavn,
kategori,
pris,
antal,
udløbsdato,
stregkode)
værdier ( 'Dc Hikiage Hira Huba' ,
'fremstille' ,
56,29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Når vi har prøvedataopsætningen, kan vi fortsætte og bruge PARTITION BY-klausulen.
Grundlæggende brug
Antag, at vi ønsker at beregne det samlede antal varer for hver produktkategori i den foregående tabel. Vi kan bruge PARTITION BY til at opdele varerne i unikke kategorier og derefter bestemme summen af mængden i hver kategori.
Et eksempel er som følger:
VÆLG
produktnavn,
kategori,
antal,
SUM(antal) OVER (OPDELING EFTER kategori) SOM total_items
FRA
Produkter;
Bemærk, at i det givne eksempel partitionerer vi dataene ved hjælp af kolonnen 'kategori'. Vi bruger derefter aggregatfunktionen SUM() til at bestemme det samlede antal varer i hver kategori separat. Resultatet viser det samlede antal varer i hver kategori.
Brug af PARTITION BY-klausulen
For at opsummere er den mest almindelige anvendelse af PARTITION BY-sætningen i forbindelse med vinduesfunktionerne. Vinduesfunktionen anvendes på hver partition separat.
Nogle af de almindelige vinduesfunktioner til brug med PARTITION BY inkluderer følgende:
- SUM() – Beregn summen af en kolonne inden for hver partition.
- AVG() – Beregn gennemsnittet af en kolonne inden for hver partition.
- COUNT() – Tæl antallet af rækker inden for hver partition.
- ROW_NUMBER() – Tildel et unikt rækkenummer til hver række inden for hver partition.
- RANK() – Tildel en rang til hver række inden for hver partition.
- DENSE_RANK() – Tildel en tæt rang til hver række inden for hver partition.
- NTILE() – Opdel dataene i kvantiler inden for hver partition.
Det er det!
Konklusion
I denne øvelse lærte vi, hvordan man arbejder med PARTITION BY-klausulen i SQL for at opdele dataene i forskellige segmenter og derefter anvende en specifik handling på hver af de resulterende partitioner separat.