In questo tutorial impareremo il funzionamento della clausola PARTITION BY in SQL e scopriremo come utilizzarla per partizionare i dati per un sottoinsieme più granulare.
Sintassi:
Cominciamo con la sintassi della clausola PARTITION BY. La sintassi può dipendere dal contesto in cui la usi, ma ecco la sintassi generale:
SELEZIONA colonna1, colonna2, ...
OVER (PARTIZIONE PER partizione_colonna1, partizione_colonna2, ...)
DA nome_tabella
La sintassi data rappresenta i seguenti elementi:
- colonna1, colonna2 – Si riferisce alle colonne che desideriamo includere nel set di risultati.
- Colonne PARTITION BY – Questa clausola definisce come desideriamo partizionare o raggruppare i dati.
Dati campione
Creiamo una tabella di base con dati di esempio per dimostrare come utilizzare la clausola PARTITION BY. Per questo esempio, creiamo una tabella di base che memorizza le informazioni sul prodotto.
prodotti CREA TAVOLA (
product_id INT CHIAVE PRIMARIA AUTO_INCREMENT,
nome_prodotto VARCHAR( 255 ),
categoria VARCHAR( 255 ),
prezzo DECIMALE( 10 , 2 ),
quantità INT,
data_scadenza DATA,
codice a barre BIGINT
);
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Cappello da Cuoco 25cm' ,
'forno' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Uova di quaglia - in scatola' ,
'dispensa' ,
17.99 ,
67 ,
'29-09-2023' ,
1708039594250 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Caffè - Cappuccino allo Zabaione' ,
'forno' ,
92,53 ,
10 ,
'2023-09-22' ,
8704051853058 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Pera - Spinoso' ,
'forno' ,
65.29 ,
48 ,
'23-08-2023' ,
5174927442238 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Pasta - Capelli d'angelo' ,
'dispensa' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Wine - Prosecco Valdobiaddene' ,
'produrre' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Pasticceria - Mini Francese Assortita' ,
'dispensa' ,
36.73 ,
52 ,
'29-05-2023' ,
5963886298051 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Arancia - In scatola, Mandarino' ,
'produrre' ,
65.0 ,
1 ,
'2023-04-20' ,
6131761721332 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Spalla di maiale' ,
'produrre' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
inserire
in
prodotti (nome_prodotto,
categoria,
prezzo,
quantità,
data di scadenza,
codice a barre)
valori ( 'Dc Hikiage Hira Huba' ,
'produrre' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Una volta configurati i dati di esempio, possiamo procedere e utilizzare la clausola PARTITION BY.
Utilizzo di base
Supponiamo di voler calcolare il totale degli articoli per ciascuna categoria di prodotto nella tabella precedente. Possiamo utilizzare PARTITION BY per dividere gli articoli in categorie uniche e quindi determinare il totale della quantità in ciascuna categoria.
Un esempio è il seguente:
SELEZIONARE
nome del prodotto,
categoria,
quantità,
SUM(quantità) OVER (PARTIZIONE PER categoria) AS total_items
DA
prodotti;
Si noti che nell'esempio fornito, partizioniamo i dati utilizzando la colonna 'categoria'. Utilizziamo quindi la funzione di aggregazione SUM() per determinare separatamente gli articoli totali in ciascuna categoria. Il risultato mostra gli articoli totali in ciascuna categoria.
Utilizzando la clausola PARTITION BY
Per riassumere, il caso d'uso più comune della clausola PARTITION BY è in combinazione con le funzioni finestra. La funzione finestra viene applicata separatamente a ciascuna partizione.
Alcune delle funzioni di finestra comuni da utilizzare con PARTITION BY includono quanto segue:
- SUM() – Calcola la somma di una colonna all'interno di ciascuna partizione.
- AVG() – Calcola la media di una colonna all'interno di ciascuna partizione.
- COUNT(): conta il numero di righe all'interno di ciascuna partizione.
- ROW_NUMBER() – Assegna un numero di riga univoco a ciascuna riga all'interno di ciascuna partizione.
- RANK() – Assegna un rango a ciascuna riga all'interno di ciascuna partizione.
- DENSE_RANK() – Assegna un rango denso a ciascuna riga all'interno di ciascuna partizione.
- NTILE() – Divide i dati in quantili all'interno di ciascuna partizione.
Questo è tutto!
Conclusione
In questo tutorial, abbiamo imparato come lavorare con la clausola PARTITION BY in SQL per partizionare i dati in vari segmenti e quindi applicare un'operazione specifica separatamente a ciascuna delle partizioni risultanti.