În acest tutorial, vom afla despre funcționarea clauzei PARTITION BY în SQL și vom descoperi cum o putem folosi pentru a partiționa datele pentru un subset mai granular.
Sintaxă:
Să începem cu sintaxa clauzei PARTITION BY. Sintaxa poate depinde de contextul în care o utilizați, dar aici este sintaxa generală:
SELECTAȚI coloana1, coloana2,...
OVER (PARTIȚIE DIN coloana_partiție1, coloana_partiție2, ...)
FROM table_name
Sintaxa dată reprezintă următoarele elemente:
- coloana1, coloana2 – Aceasta se referă la coloanele pe care dorim să le includem în setul de rezultate.
- PARTITION BY coloane – Această clauză definește modul în care dorim să partiționăm sau să grupăm datele.
Eșantion de date
Să creăm un tabel de bază cu un exemplu de date pentru a demonstra cum să folosim clauza PARTITION BY. Pentru acest exemplu, să creăm un tabel de bază care stochează informațiile despre produs.
produse CREATE TABLE (
product_id INT PRIMARY KEY AUTO_INCREMENT,
nume_produs VARCHAR( 255 ),
categoria VARCHAR( 255 ),
pret DECIMAL( 10 , 2 ),
cantitate INT,
expiration_date DATE,
cod de bare BIGINT
);
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Pălărie de bucătar 25 cm” ,
'brutărie' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( 'Ouă de prepeliță - Conserve' ,
'cămară' ,
17.99 ,
67 ,
„2023-09-29” ,
1708039594250 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Cafea - Capuccino cu ciugul de ou” ,
'brutărie' ,
92,53 ,
10 ,
'2023-09-22' ,
8704051853058 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( 'Pere - înțepător' ,
'brutărie' ,
65,29 ,
48 ,
„2023-08-23” ,
5174927442238 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Paste – păr de înger” ,
'cămară' ,
48,38 ,
59 ,
„2023-08-05” ,
8008123704782 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Vin - Prosecco Valdobiaddene” ,
'legume şi fructe' ,
44.18 ,
3 ,
„2023-03-13” ,
6470981735653 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Pateterie - Mini asortat francez” ,
'cămară' ,
36,73 ,
52 ,
„2023-05-29” ,
5963886298051 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Portocale – Conserve, Mandarin” ,
'legume şi fructe' ,
65,0 ,
1 ,
„2023-04-20” ,
6131761721332 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Carne de porc – Umăr” ,
'legume şi fructe' ,
55,55 ,
73 ,
„2023-05-01” ,
9343592107125 );
introduce
în
produse (nume_produs,
categorie,
Preț,
cantitate,
data expirării,
cod de bare)
valori ( „Dc Hikiage Hira Huba” ,
'legume şi fructe' ,
56,29 ,
53 ,
„2023-04-14” ,
3354910667072 );
Odată ce avem configurarea datelor eșantion, putem continua și folosi clauza PARTITION BY.
Utilizare de bază
Să presupunem că dorim să calculăm totalul articolelor pentru fiecare categorie de produse din tabelul anterior. Putem folosi PARTITION BY pentru a împărți articolele în categorii unice și apoi pentru a determina totalul cantității din fiecare categorie.
Un exemplu este următorul:
SELECTAȚI
numele produsului,
categorie,
cantitate,
SUM(cantitate) OVER (PARTIȚIE PE categorie) AS total_items
DIN
produse;
Observați că în exemplul dat, împărțim datele folosind coloana „categorie”. Apoi folosim funcția de agregare SUM() pentru a determina elementele totale din fiecare categorie separat. Rezultatul arată totalul articolelor din fiecare categorie.
Utilizarea clauzei PARTITION BY
Pentru a rezuma, cel mai frecvent caz de utilizare al clauzei PARTITION BY este în combinație cu funcțiile ferestrei. Funcția fereastră este aplicată fiecărei partiții separat.
Unele dintre funcțiile comune ale ferestrei de utilizat cu PARTITION BY includ următoarele:
- SUM() – Calculați suma unei coloane din fiecare partiție.
- AVG() – Calculați media unei coloane din fiecare partiție.
- COUNT() – Numărați numărul de rânduri din fiecare partiție.
- ROW_NUMBER() – Atribuiți un număr unic de rând fiecărui rând din fiecare partiție.
- RANK() – Atribuiți un rang fiecărui rând din fiecare partiție.
- DENSE_RANK() – Atribuiți un rang dens fiecărui rând din fiecare partiție.
- NTILE() – Împărțiți datele în cuantile în fiecare partiție.
Asta este!
Concluzie
În acest tutorial, am învățat cum să lucrăm cu clauza PARTITION BY în SQL pentru a partiționa datele în diferite segmente și apoi aplicăm o operație specifică fiecărei partiții rezultate separat.