Funzioni della finestra SQL: tutto ciò che devi sapere sull’utilizzo di esse

La versatilità di SQL come linguaggio di query DBMS è aumentata nel corso degli anni. La sua ampia utilità e versatilità lo rendono uno dei preferiti di tutti i tempi per ogni analista di dati.

Ci sono parecchie funzioni di livello avanzato oltre a quelle normali di SQL. Queste funzioni sono comunemente note come funzioni di finestra. Se hai a che fare con dati complessi e desideri eseguire calcoli avanzati, puoi utilizzarli per utilizzare al meglio i tuoi dati.

L’importanza delle funzioni della finestra

Diverse funzioni della finestra sono disponibili in SQL e ognuna ti aiuterà a eseguire una serie di calcoli. Dalla creazione di partizioni alla classificazione delle righe o all’assegnazione di numeri di riga, queste funzioni della finestra fanno un po’ di tutto.

Le funzioni finestra sono utili quando applichi funzioni di aggregazione su un set di dati specifico o una raccolta di righe. Queste funzioni vanno ben oltre le funzioni di aggregazione fornite da GROUP BY. Tuttavia, la differenza principale è che, a differenza della funzione di raggruppamento, i dati non vengono combinati in un’unica riga.

Non è possibile utilizzare le funzioni finestra all’interno delle istruzioni WHERE , FROM e GROUP BY .

Sintassi di una funzione finestra

Quando si fa riferimento a qualsiasi funzione della finestra, è necessario seguire la struttura della sintassi predefinita, in modo che funzioni correttamente. Se strutturi il comando in modo errato, riceverai un errore e il tuo codice non verrà eseguito.

Ecco la sintassi predefinita:

SELECT columnname1,
{window_function}(columnname2)
OVER([PARTITION BY columnname1] [ORDER BY columnname3]) AS new_column
FROM table_name;

Dove:

• coulmnname1 è il nome della prima colonna che desideri selezionare.
• {window_function} è il nome di una funzione aggregata come sum, avg, count, row_number, rank o dense_rank.
• columnname2 è il nome della colonna su cui stai applicando la funzione window.
• nomecolonna3 è il nome della terza colonna, che costituirà la base per la partizione.
• new_column è un’etichetta per la nuova colonna che puoi applicare utilizzando la parola chiave AS .
• table_name è il nome della tabella di origine.

Le funzioni della finestra sono diverse da alcuni dei comandi SQL più basilari. A differenza delle funzioni di aggregazione in SQL, è possibile utilizzare queste funzioni di finestra per eseguire funzioni avanzate.

Preparazione del set di dati

È possibile utilizzare l’ istruzione CREATE TABLE per creare una nuova tabella in SQL. Ecco un set di dati di esempio che questa guida utilizzerà per definire alcune funzioni della finestra:

Spiegazione della funzione somma

Supponiamo di voler calcolare le vendite totali per ogni valore all’interno della colonna categoria. Ecco come puoi farlo:

SELECT category, color,
sum(sale_price)
OVER (order by category) AS total_sales
FROM sahil.sample;

Nel codice precedente, l’istruzione SQL estrae la categoria e il colore dal set di dati originale. La funzione sum somma la colonna sale_price. Lo fa per categoria, poiché la clausola OVER specifica l’ordinamento in base alla colonna della categoria. Il risultato finale è il seguente:

Come utilizzare la funzione finestra Avg()

Come per la funzione somma, puoi calcolare la media per riga di dati con la funzione media . Invece della somma, otterrai una colonna con le vendite medie.

SELECT category, color,
avg(sale_price)
OVER (order by category) AS avg_sales
FROM sahil.sample;

Impara a usare la funzione della finestra Count()

Simile alle funzioni sum e avg, la funzione count window in SQL è piuttosto semplice e funziona sulla stessa linea delle altre due funzioni. Quando passi la funzione count, ottieni il conteggio totale di ogni valore all’interno della nuova colonna.

Ecco come puoi calcolare il conteggio:

SELECT category, color,
count(category)
OVER (order by category) AS item_count
FROM sahil.sample;

La funzione della finestra Row_Number()

A differenza di alcune delle altre funzioni della finestra sopra elencate, row_number() funziona in modo leggermente diverso. La funzione row_number() assegna un numero di riga a ciascuna riga, a seconda della clausola order by. Il numero di riga iniziale è 1; il row_number assegna un valore corrispondente a ciascuna riga fino alla fine.

Ecco la struttura di base di una funzione row_number():

SELECT category, color,
row_number()
OVER (order by category) AS item_number
FROM sahil.sample;

Ma cosa succede se si desidera assegnare numeri di riga separati a ciascun elemento della categoria? La sintassi precedente imposta un numero di serie scorrevole, indipendentemente dagli elementi archiviati all’interno della categoria. Ad esempio, la categoria degli elettrodomestici dovrebbe avere la sua numerazione esclusiva, seguita dai raccoglitori e così via.

È possibile utilizzare la funzione di partizione per eseguire questo compito semplice ma pratico. La parola chiave partition assegna numeri di riga designati in base a ciascun elemento di categoria.

SELECT category, color,
row_number()
OVER (partition by category order by category) AS item_number
FROM sahil.sample;

Le funzioni Rank() e Dense_Rank()

La funzione rank() funziona diversamente dalla funzione row_number() . È necessario specificare il nome della colonna all’interno dell’ordine per funzione, per utilizzarlo come base per definire i valori di rango. Ad esempio, nell’esempio di codice seguente è possibile utilizzare la colonna color all’interno della funzione order by. La query utilizzerà quindi tale ordine per assegnare un valore di rango a ciascuna riga.

È possibile utilizzare la sintassi del codice seguente per passare una funzione di rango in SQL:

SELECT category, color,
rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Dai un’occhiata all’output per capire come funziona questa funzione.

La funzione order by ordina la categoria di colore, mentre la funzione rank assegna un rango a ciascun colore. Tuttavia, tutti gli stessi valori di colore hanno lo stesso rango, mentre i diversi colori hanno ranghi separati. Il colore nero ricorre tre volte all’interno del set di dati; invece di assegnare un valore di rango di 1, 2 e 3, gli oggetti di colore nero ottengono un rango di 1.

Tuttavia, il colore successivo, il marrone, ottiene un rango 4 anziché un rango 2. La funzione rango salta i valori e assegna il valore cronologico successivo alle diverse voci. Se vuoi assegnare un valore di rango più significativo, puoi usare la funzione dense_rank() .

La funzione dense_rank non ignora alcun valore di rango durante l’ordinamento per funzione. Ad esempio, i primi tre elementi di colore (Nero) avranno rango 1. Tuttavia, il colore successivo (Marrone) non avrà un rango 4, ma un rango 2, che è il numero cronologico successivo nell’elenco di numerazione. La funzione dense_rank è una funzione finestra più pratica in quanto assegna un valore significativo all’elenco di elementi.

Ecco come puoi utilizzare la funzione dense_rank in SQL:

SELECT category, color,
dense_rank()
OVER (order by color) AS item_rank
FROM sahil.sample;

Ed ecco un esempio di come sarà l’output di questa funzione:

Funzioni SQL in soccorso

Le funzioni di finestra di SQL sono ideali per eseguire operazioni analitiche avanzate. Tuttavia, puoi utilizzare molti altri comandi SQL per assicurarti che le tue abilità computazionali siano di prim’ordine. Quando combini e calcoli più risultati in una volta sola, non c’è niente di meglio che utilizzare le sottoquery di SQL.

Le sottoquery sono un ottimo strumento per eseguire funzioni avanzate, migliorando la qualità dei risultati. A seconda delle necessità del momento, puoi personalizzare le tue query e renderle più efficaci per soddisfare le tue esigenze.