Quando si usano le funzioni finestra SQL?

Avete sentito uno dei vostri colleghi vantarsi dell'uso delle funzioni SQL? Conoscete l'SQL di base ma non sapete molto o nulla delle funzioni finestra? Se volete imparare a conoscere le funzioni finestra e a usarle in un contesto aziendale, siete nel posto giusto!

Succede spesso. Si raggiunge un certo livello di conoscenza e ci si sente il re (o la regina) del mondo. Poi sentite i vostri colleghi parlare di qualcosa di cui non avete mai sentito parlare (in questo caso, le funzioni delle finestre). Immediatamente vi vergognate di non conoscere già le funzioni delle finestre. Vi suona familiare?

Questa sensazione non è piacevole. Ma può essere una buona cosa se vi spinge a chiedervi cosa sono le funzioni di finestra. Quindi, cercate su Google e trovate alcuni articoli utili (come questo). Improvvisamente, l'imbarazzo si trasformerà in un'iniezione di fiducia quando vi renderete conto che le funzioni di window non sono misteriose o non si possono imparare. Di nuovo, vi sentirete il re del mondo. O la regina. Forse anche entrambe le cose.

Andiamo verso questa sensazione regale! Cercherò di non bombardarvi con l'SQL. Al contrario, farò il contrario. Vi fornirò diversi esempi aziendali reali che vi mostreranno l'uso pratico delle funzioni di window.

Che cosa sono Window Functions?

Una funzione finestra è, in parole povere, una funzione che esegue calcoli su un insieme di righe di una tabella. Il nome deriva dal fatto che l'insieme di righe è chiamato finestra o cornice.

Ecco un esempio di come si presenta una finestra. Nella tabella sottostante, le finestre sono contrassegnate da colori diversi. La somma cumulativa viene calcolata per ogni regione, quindi in questo caso le finestre sono definite per regione.

La sintassi della funzione finestra è:

window_function ([ALL] expression)
OVER ([PARTITION BY partition_list] [ORDER BY order_list] [window_frame_clause])

La parte della sintassi relativa alla funzione finestra può essere una funzione aggregata, come SUM() o AVG(). Può anche essere un altro tipo di funzione, come una funzione analitica o di classificazione.

La finestra, ovvero il numero di righe su cui verranno eseguiti determinati calcoli, è definita dalla clausola OVER().

PARTITION BY è una clausola opzionale nelle funzioni finestra. Definisce una partizione su cui la funzione finestra eseguirà l'operazione. Se PARTITION BY non è definita, la funzione finestra eseguirà l'operazione sull'intera tabella. Per saperne di più, consultare questo articolo.

ORDER BY ordinerà le righe all'interno di ogni partizione nell'ordine desiderato. Se questa clausola non è definita, la clausola utilizzerà l'intera tabella.

La clausola window frame definisce il numero di righe su cui opererà la funzione window utilizzando due parole chiave. Una è ROWS, utilizzata per limitare il numero di righe specificando il numero di righe che precedono o seguono la riga corrente. L'altra è RANGE, utilizzata per limitare il numero di righe specificando un intervallo di valori rispetto al valore della riga corrente. Per questo motivo la clausola window frame è chiamata anche clausola ROW o RANGE.

Per imparare e mettere in pratica questa sintassi, il corso LearnSQL.it sulle funzioni di finestra è il posto giusto. Inoltre, se siete interessati a ulteriori spiegazioni sulle funzioni di finestra, potete trovarle qui, insieme ad alcuni esempi.

Passiamo ora alla parte più divertente, la risoluzione dei problemi!

Esempio 1: Calcolo dello stipendio medio e confronto tra lo stipendio di Individual e quello medio

Questo è un semplice esempio di un problema comune nel mondo degli affari. Supponiamo che esista una tabella employeeche contiene i dati relativi agli stipendi dei dipendenti. È composta dalle seguenti righe:

• id - id univoco
• first_name - nome del dipendente
• last_name - cognome del dipendente
• department - reparto del dipendente
• salary - stipendio mensile del dipendente

Utilizzando questi dati, occorre innanzitutto calcolare lo stipendio medio dell'intera azienda. Quindi, è necessario calcolare di quanto è superiore o inferiore alla retribuzione media lo stipendio di ciascun dipendente. Il codice seguente lo farà rapidamente:

SELECT	first_name,
		last_name,
		department,
		salary,
		AVG(salary) OVER() AS avg_salary,
		salary - AVG(salary) OVER() AS diff_salary
FROM employee;

Poiché avete familiarità con SQL, probabilmente riconoscerete almeno una parte di questo codice. Seleziona first_name, last_name, department, e salary dalla tabella employee. Niente di nuovo.

La riga AVG(salary) OVER() AS avg_salary calcola il salario medio nella finestra definita da OVER(). Il risultato sarà mostrato nella colonna avg_salary.

La riga salary - AVG(salary) OVER() AS diff_salary calcola la differenza tra lo stipendio di ciascun dipendente e lo stipendio medio, Il risultato sarà mostrato nella colonna diff_salary.

L'esecuzione di questo codice fornirà una tabella utile. Vediamone un estratto qui di seguito:

Esempio 2: Calcolo del numero medio di prodotti venduti per data e regione

Immaginate di lavorare in un'azienda che opera in tre regioni. La direzione vuole conoscere il numero medio di prodotti venduti in ogni regione. Inoltre, si vuole conoscere il numero medio di prodotti venduti complessivamente per ogni data. Le funzioni della finestra vi permetteranno di farlo facilmente.

In questo esempio, i dati sono memorizzati nella tabella sales che ha tre colonne:

• date - data di vendita del prodotto
• region - nome della regione
• products_sold - numero di prodotti venduti

Ecco il codice necessario per creare questo report:

SELECT	date,
		region,
		products_sold,
		AVG(products_sold) OVER(PARTITION BY date) AS avg_date,
		AVG(products_sold) OVER(PARTITION BY region) AS avg_region
FROM sales
ORDER BY region, date;

Questo codice seleziona le colonne date, region e products_sold. Quindi, calcola il numero medio di prodotti venduti in ciascuna data. Questa è definita dalla clausola PARTITION BY(). Il risultato sarà mostrato nella colonna avg_date.

La riga successiva calcola anche il numero medio di prodotti venduti, questa volta in ogni regione. Il risultato sarà mostrato nella colonna avg_region. Quindi, i risultati vengono ordinati in base alla regione e alla data utilizzando la clausola ORDER BY.

Si vedano i risultati qui sotto:

Esempio 3: Calcolo della somma cumulativa dei prodotti venduti per regione

La direzione era soddisfatta del vostro precedente rapporto! Ora vuole che si calcoli la somma cumulativa (o il totale progressivo) dei prodotti venduti in ogni regione. Le funzioni della finestra sono utili per eseguire tali calcoli.

Il calcolo verrà eseguito sulla tabella sales utilizzata nell'esempio 2. Questo codice vi permetterà di fornire rapidamente i numeri richiesti:

SELECT	date,
		region,
		products_sold,
		SUM(products_sold) OVER(PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_region
FROM sales
ORDER BY region, date;

La prima parte del codice seleziona le stesse colonne del codice dell'Esempio 2. Poi calcola la somma dei prodotti venduti in ogni regione. Poi calcola la somma dei prodotti per regione. Questa è definita dalla clausola PARTITION BY(). Naturalmente, è necessaria la somma cumulativa, non la somma totale. Per questo motivo, la finestra viene ordinata in base alla data utilizzando il comando ORDER BY.

Ora che tutto è definito, occorre dire a SQL di aggiungere il valore della riga corrente alla somma delle righe precedenti all'interno della finestra. Questo viene fatto con il comando ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

La funzione finestra sommerà tutte le righe precedenti alla riga corrente (ecco perché UNBOUNDED PRECEDING) e nessuna riga successiva alla riga corrente (ecco perché CURRENT ROW) all'interno della finestra specificata. Il risultato verrà mostrato nella colonna cumulative_region.

Il risultato sarà ordinato per regione e data, come nell'esempio precedente.

Dopo aver eseguito il codice, si otterrà la tabella seguente:

Esempio 4: Confronto dei prezzi delle azioni con i prezzi minimi e massimi del mercato

La vostra azienda sta monitorando i prezzi delle azioni sul NASDAQ e sul NYSE. La direzione vuole che si calcolino i prezzi minimi e massimi delle azioni su entrambi i mercati negli ultimi 365 giorni. Vogliono anche vedere come il prezzo di ogni azione scambiata nel 2020 differisce dal prezzo minimo e massimo sul mercato.

La tabella stockprice contiene i dati dal 15/3/2019 al 14/3/2020 ed è composta dalle seguenti colonne:

• date - data di negoziazione
• stock_name - nome del titolo
• stock_price - prezzo del titolo
• stock_market - mercato in cui viene negoziato il titolo
• max_price - prezzo massimo sul mercato negli ultimi 365 giorni
• min_price - prezzo minimo sul mercato negli ultimi 365 giorni
• diff_max - prezzo del titolo meno il prezzo massimo sul mercato negli ultimi 365 giorni
• diff_min - prezzo del titolo meno il prezzo minimo sul mercato negli ultimi 365 giorni

Il codice che creerà rapidamente il report richiesto si presenta come segue:

SELECT	date,
		stock_name,
		stock_price,
		stock_market,
		MAX(stock_price) OVER(PARTITION BY stock_market) as max_price,
		MIN(stock_price) OVER(PARTITION BY stock_market) as min_price,
		stock_price - MAX(stock_price) OVER(PARTITION BY stock_market) AS diff_max,
		stock_price - MIN(stock_price) OVER(PARTITION BY stock_market) AS diff_min
FROM stockprice
WHERE date > '2019-12-31'
ORDER BY date;

La prima parte del codice seleziona le colonne originali della tabella: date, stock_name, stock_price, e stock_market. Quindi, la funzione finestra MAX() con la clausola PARTITION BY calcola il prezzo massimo per ciascun mercato, NASDAQ e NYSE, separatamente. Il risultato sarà mostrato nella colonna max_price.

La riga di codice successiva funziona allo stesso modo, ma ora calcola il prezzo minimo. Il risultato sarà mostrato nella colonna min_price.

Le righe di codice seguenti calcolano la differenza tra il prezzo dell'azione e il prezzo massimo e minimo, rispettivamente, per ogni mercato. I risultati saranno mostrati nelle colonne diff_max e diff_min.

Poiché il report deve mostrare solo i dati del 2020, ho utilizzato la clausola WHERE. Infine, la tabella risultante è ordinata in base alla data, come è logico per un report di questo tipo.

Ecco come appaiono le prime righe del report:

Esempio 5: Calcolo della percentuale di variazione dei prezzi e delle medie mobili

La direzione ora sa che siete in grado di fornire report con grande precisione e velocità. Sono impressionati! Non si rendono conto che avete imparato le funzioni della finestra SQL e che quello che vi hanno chiesto è facile da realizzare.

Poi vi chiedono qualcosa che dovrebbe portarvi via molto più tempo. Ma non è così! Avete una tabella prezzi che contiene tutte le price variazioni di un titolo nel 2020. A volte c'è solo una variazione giornaliera, a volte ce ne sono di più. La tabella è composta dalle seguenti colonne:

• date - data del prezzo
• stock_price - prezzo del titolo

La direzione vi ha chiesto di inviare un report che prenda ogni prezzo e lo confronti con il prezzo precedente. Inoltre, vi hanno chiesto di calcolare la media mobile del prezzo delle azioni. Questo è il modo in cui lo farete:

SELECT	date,
		stock_price,
		(stock_price/LAG(stock_price) OVER(ORDER BY date)) - 1 AS percent_change,
AVG(stock_price) OVER(ORDER BY date ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING) AS moving_avg
FROM price;

Cosa fa questo codice? Innanzitutto, seleziona le colonne originali della tabella: date e stock_price.

La riga successiva introduce una nuova funzione finestra, LAG(). Questa funzione accede ai dati della riga precedente, il che è ideale per questo compito. stock_price viene diviso per il prezzo precedente (da cui la funzione LAG() ). Quindi si sottrae 1 dal risultato per ottenere una percentuale. Il risultato viene mostrato nella colonna percent_change.

La riga seguente calcola la media mobile. Utilizza la funzione finestra AVG(), che già conoscete. Nella clausola OVER() i dati vengono ordinati in base alla data.

La direzione non ha specificato come vuole che venga calcolata la media mobile. Ho quindi deciso di calcolarla utilizzando cinque variazioni di prezzo, come specificato in ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING. Nel calcolare la media mobile, il codice prenderà in considerazione la riga corrente e le quattro successive, per un totale di cinque.

Si può giocare con questa parte del codice, poiché non esiste un numero definito di dati da prendere in considerazione per il calcolo delle medie mobili. Dipende dalla quantità e dal tipo di dati, oltre che dalle preferenze individuali. Modificando i numeri che precedono PRECEDING e FOLLOWING, è possibile modificare facilmente e rapidamente il calcolo, a seconda della metodologia che si decide di utilizzare.

Si vedano i risultati qui sotto:

Pensate che imparare Window Functions possa aiutarvi nel vostro lavoro?

Ho fornito esempi aziendali reali come punto di partenza, invece di una pura analisi del codice delle funzioni di Window. Tutti e cinque gli esempi sono stati affrontati nel corso della mia carriera.

Questo è il modo in cui ho imparato SQL. Prima ho avuto un problema da risolvere. Poi ho cercato di capire come risolverlo usando SQL. Se pensate che le funzioni finestra siano utili per il vostro lavoro, questo corso di LearnSQL.it è un ottimo modo per saperne di più.

Se avete trovato questi esempi interessanti o volete condividere alcuni esempi reali, non esitate a commentare qui sotto!