Cosa sono le funzioni SQL Window?

Avete bisogno di migliorare la vostra analisi dei dati? Imparate queste funzioni SQL a finestra e porterete le vostre capacità di analisi a un livello superiore. Esercizi pratici e spiegazioni dettagliate inclusi!

Quando ho sentito parlare per la prima volta delle funzioni SQL a finestra, ho pensato che si trattasse di uno strano connubio tra SQL e Windows. Mi sbagliavo. Le funzioni SQL window non hanno nulla a che fare con il famoso sistema operativo. Sono funzioni SQL che eseguono calcoli sull'insieme di righe relative alla riga corrente. Questo insieme di righe è chiamato finestra o cornice di finestra, da cui il nome della funzione.

Si potrebbe anche sentire parlare di funzioni di windowing in SQL, di funzioni analitiche o di funzioni OVER(). Questi sono solo nomi alternativi per le funzioni finestra di SQL, un insieme di strumenti estremamente utili per l'analisi dei dati.

In questo articolo vi mostreremo cosa si può fare con le funzioni a finestra e come. Inizierò con una breve storia delle funzioni SQL window e spiegherò perché si chiamano così. Poi vi guiderò attraverso la sintassi e vi mostrerò come funziona con diversi esempi. Dopo aver fatto pratica con la sintassi, saremo pronti per gli esempi reali di funzioni finestra nella vita di un analista di dati. Ed ecco la parte più interessante: dopo ogni esempio, c'è un esercizio da risolvere e da imparare attraverso la codifica.

Tuttavia, la fonte principale delle vostre conoscenze su questo argomento dovrebbe essere il nostro corso. Window Functions corso. I suoi 218 esercizi interattivi coprono in dettaglio le funzioni della finestra SQL. In altre parole, imparerete a conoscere le cornici delle finestre e le clausole OVER(), PARTITION BY e ORDER BY. Tutto questo è necessario per aggregare, classificare e analizzare i dati utilizzando le funzioni finestra.

Breve storia dell'SQL Window Functions

Le funzioni finestra sono state introdotte per la prima volta nel database Oracle8i, rilasciato nel 1998. Tuttavia, sono state incluse nello standard SQL cinque anni dopo, con SQL:2003.

Poi Microsoft le ha incluse in SQL Server 2005. Altri sistemi di gestione di database (DBMS) le hanno seguite; PostgreSQL le supporta dal rilascio di PostgreSQL 8.4 nel 2009; MariaDB le ha incluse con la versione 10.2 (2016) e MySQL le ha aggiunte alla versione 8 nel 2018.

Le funzioni finestra sono una caratteristica piuttosto nuova di SQL. Per questo motivo, non fanno parte del normale curriculum SQL. Imparandole, sarete più avanti rispetto a molti utenti di SQL.

Cos'è una finestra?

Un insieme di righe correlate alla riga corrente è chiamato finestra o cornice di finestra. Da qui il nome di queste funzioni: il loro risultato si basa su una finestra scorrevole.

Ad esempio, è possibile calcolare una somma cumulativa come mostrato di seguito:

La finestra per la somma cumulativa 2023-10-04 è evidenziata in verde. Essa comprende la riga corrente (per il 2023-10-04) e tutte le righe precedenti. La somma cumulativa è quindi calcolata come la somma di tutte le vendite precedenti e attuali: 4.241 + 2.389 + 1.580 + 3.395 = 11.605. (Si noti che la riga delineata con i punti rossi non è inclusa nella finestra o nella somma).

Quando si passa alla riga successiva, anche la finestra si sposta: ora include tutte le righe precedenti (verde) e la riga attuale (tratteggiata in rosso). Ora la somma cumulativa è 4.241 + 2.389 + 1.580 + 3.395 + 1.265 = 12.870.

La finestra è quindi l'insieme delle righe relative alla riga corrente che vengono utilizzate nei calcoli per questa riga. La finestra cambia (scorre) man mano che ci si sposta sulle righe; grazie a queste immagini di una finestra scorrevole, otteniamo il nome di queste funzioni.

Sintassi delle funzioni SQL Window

La sintassi delle funzioni finestra è la seguente:

SELECT column_1,
       column_2,
	 <window_function> OVER(PARTITION BY … ORDER BY … ) AS column_alias
FROM table;

Ecco cosa fa ogni parte:

• <window_function> - Specifica la funzione da applicare a quella finestra.
• OVER() - Definisce la finestra (insieme di righe) e indica che si tratta di una funzione finestra; senza questa clausola, non è una funzione finestra.
• <window_frame> - Definisce la dimensione della cornice della finestra (opzionale).
• PARTITION BY - Divide la finestra in gruppi più piccoli chiamati partizioni (opzionale); se omesso, l'intero set di risultati è una partizione.
• ORDER BY - Ordina le righe all'interno del riquadro della finestra (facoltativo), cioè decide in quale ordine verrà eseguita l'operazione della finestra; se omesso, l'ordine delle righe all'interno della partizione è arbitrario.

Le clausole aggiuntive possono definire ulteriormente la finestra. La loro sintassi è la seguente:

[<ROWS or RANGE clause> BETWEEN <lower_bound> AND <upper_bound>]

La clausola ROWS definisce la finestra in termini di numero fisso di righe rispetto alla riga corrente.

La clausola RANGE fa lo stesso. Ma prende in considerazione anche tutte le righe con gli stessi valori nelle colonne specificate nella clausola ORDER BY della riga corrente.

I limiti della finestra possono essere definiti come

• UNBOUNDED PRECEDING - Tutte le righe prima della riga corrente.
• n PRECEDING - Un numero definito di righe prima della riga corrente.
• CURRENT ROW - Include la riga corrente.
• n FOLLOWING - Un numero definito di righe dopo la riga corrente.
• UNBOUNDED FOLLOWING - Tutte le righe dopo la riga corrente.

Vediamo ora come funziona in pratica.

Esempi di set di dati e sintassi

In tutti gli esempi utilizzeremo la tabella album_catalogue in tutti questi esempi. Potete crearla voi stessi utilizzando questo script. Di seguito è riportata un'istantanea dei dati:

Il dataset è un elenco di album con la loro durata, il genere, l'artista e i dati di vendita, compreso il numero di copie vendute e il periodo (trimestri). I dati arrivano fino al terzo trimestre del 2023.

Vi mostrerò prima diversi esempi, spiegandovi ogni parte cruciale della sintassi delle funzioni di windowing.

Esempio di sintassi #1: OVER ()

È possibile utilizzare la funzione SUM() con la sola clausola OVER() per ottenere il totale delle vendite nel quarto trimestre del 2022:

SELECT sales_period,
 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER() AS sold_in_4Q_2022
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Voglio la somma delle copie vendute, quindi specifico questa colonna in SUM(). La clausola OVER() è obbligatoria. Se si vuole usare OVER() senza nessuna delle clausole opzionali, basta lasciare le parentesi vuote.

Uso WHERE per visualizzare solo i dati del trimestre desiderato.

Quando si scrive una query come questa, con una OVER() vuota, l'intero set di risultati (colonne selezionate, filtri applicati, ecc.) viene preso in considerazione quando si eseguono i calcoli della funzione finestra. In questo caso, il risultato mostra le vendite individuali di ogni album venduto nel quarto trimestre del 2022. Mostra anche le vendite totali di tutti gli album venduti in quel periodo.

Con l'aiuto di SUM() e OVER(), sono in grado di mostrare le vendite di ogni singolo album e il totale trimestrale.

Esempio di sintassi #2: OVER (ORDER BY)

È possibile aggiungere altre clausole all'interno della clausola OVER() per modificare la definizione della finestra. Una di queste clausole è ORDER BY. La clausola ORDER BY definisce l'ordinamento delle righe all'interno di una finestra: le righe possono essere elaborate dalla funzione finestra in un determinato ordine.

Vediamo un esempio. È possibile calcolare la somma cumulativa aggiungendo ORDER BY alla query precedente. In questo esempio, voglio vedere le vendite dell'album 'In the Right Place' nel tempo e il numero cumulativo di album venduti fino a un determinato periodo di tempo. Ecco la query:

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(ORDER BY sales_period ASC) AS cumulative_sum
FROM album_catalogue
WHERE album_title = 'In the Right Place';

La stessa colonna copies_sold è presente in SUM(). Questa volta, OVER() contiene la clausola ORDER BY. Si vogliono mostrare le vendite cumulative dal primo all'ultimo trimestre. Ecco perché servono sales_period e ASC in ORDER BY.

In ogni riga si possono vedere le vendite per ogni trimestre e la somma cumulativa, cioè la somma del trimestre corrente e di tutti i trimestri precedenti. Ad esempio, l'album ha venduto 94 copie nel terzo trimestre del 2022. Il totale delle vendite nel 2022 fino a quel momento (o nei tre trimestri) è: 222 + 208 + 94 = 524.

Esempio di sintassi n. 3: OVER (PARTITION BY)

Un'altra clausola che si può usare in OVER() è PARTITION BY. PARTITION BY viene usata per dividere la finestra in segmenti più piccoli in base ad alcuni criteri. Ad esempio, è possibile elencare gli album, i dati di vendita relativi al quarto trimestre del 2022 e le vendite per genere di quel trimestre:

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM (copies_sold) OVER(PARTITION BY album_genre) AS sales_by_genre
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Anche in questo caso utilizziamo la stessa funzione finestra SUM(). Questa volta, però, utilizziamo PARTITION BY per dividere la finestra in segmenti più piccoli in base al genere dell'album. Tutto il resto rimane invariato.

La query restituisce il risultato qui sotto. Si tratta di un'analisi dei dati di vendita degli album per genere nell'ultimo trimestre del 2022.

Ad esempio, la somma cumulativa per gli album folk è 809 + 246 + 400 + 214 + 412 = 2.081.

Esempio di sintassi n. 4: OVER (ORDER BY PARTITION BY)

È anche possibile utilizzare sia PARTITION BY che ORDER BY in OVER(). Le righe vengono suddivise in segmenti con PARTITION BY ed elaborate in un determinato ordine da ORDER BY.

Utilizzando la query sottostante, posso mostrare tutti i dati analitici degli album e calcolare la somma cumulativa per ogni album separatamente:

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(PARTITION BY album_title ORDER BY sales_period ASC) AS cumulative_sum_by_album
FROM album_catalogue;

Calcolo questo dato con l'aiuto della funzione finestra di SUM(), come ho fatto in precedenza. Ho suddiviso la finestra per album. Ciò significa che la somma verrà cumulata finché la funzione non raggiunge l'ultima riga di un determinato album. Quando raggiunge un altro album, si azzera e ricomincia ad accumulare la somma dall'inizio.

Uso anche ORDER BY per indicare alla funzione di cumulare la somma dal primo all'ultimo trimestre.

Si può notare che la somma cumulativa per "A Whole New Thing" è 3.654. L'album successivo ("Blue") inizia con la riga successiva, quindi il cumulo viene azzerato: la somma cumulativa è uguale alle vendite individuali dell'album nel primo trimestre del 2022. Quindi, si accumula fino a raggiungere l'album successivo. Il risultato arriva fino all'ultimo album, che nel nostro caso è "Wednesday Morning, 3 A.M.".

Con questi esempi vi ho mostrato i modi più comuni di definire le cornici delle finestre. Ma questi non sono gli unici modi. È possibile utilizzare anche le clausole ROW o RANGE con la sintassi e i limiti che abbiamo spiegato in precedenza. Non preoccupatevi. Ne vedrete l'uso pratico negli esempi reali.

In questi esempi ho utilizzato una sola funzione, SUM(), che è una delle tante funzioni di finestra. Si tratta di una delle tante funzioni della finestra; esaminiamo rapidamente alcune delle altre.

Quali sono le più comuni Window Functions?

Le funzioni finestra più comuni possono essere suddivise in tre categorie:

1. Aggregato Window Functions:

• COUNT() - Conta il numero di righe all'interno di una finestra.
• SUM() - Totalizza i valori dati all'interno di una finestra.
• AVG() - Calcola la media di valori dati all'interno di una finestra.
• MIN() - Trova il valore più piccolo all'interno di una finestra.
• MAX() - Trova il valore più grande all'interno di una finestra.

2. Classifica Window Functions:

• ROW_NUMBER() - Classifica i valori in modo sequenziale, con ranghi diversi per i valori uguali.
• RANK() - Classifica i valori usando lo stesso rango per i valori legati; salta il rango successivo dopo i legami (ad esempio, 1, 2, 2, 4).
• DENSE_RANK() - Classifica i valori usando lo stesso rango per i valori legati; non salta il rango successivo dopo i legami (ad esempio, 1,2,2,3,4).

3. Analitico Window Functions:

• LEAD() - Ottiene i dati da un offset definito (cioè un numero di righe stabilito) dopo la riga corrente.
• LAG() - Ottiene i dati da un offset definito (cioè un numero di righe stabilito) prima della riga corrente.

Esistono altre funzioni finestra che potrebbero essere utili. Consultate il nostro foglio informativo gratuito su SQL Window Functions.

Esempi di funzioni finestra SQL nel mondo reale

Finora mi sono concentrato sulla sintassi delle funzioni finestra SQL. Ora vi mostrerò gli usi pratici più comuni delle funzioni finestra e come possono aiutare gli analisti di dati nel loro lavoro.

Questi esempi utilizzeranno lo stesso set di dati di prima.

Esempio #1: Percentuale del totale

Mostriamo le informazioni su ciascun album e le relative vendite nel primo trimestre del 2023. Inoltre, mostreremo le vendite trimestrali per ogni genere. Quindi, calcoliamo quanto ogni album (in percentuale) contribuisce alle vendite del genere.

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM(copies_sold) OVER (PARTITION BY album_genre) AS sales_by_genre,
	 (copies_sold*1.0/ SUM(copies_sold) OVER (PARTITION BY album_genre))*100.0 AS percent_of_genre_sales
FROM album_catalogue
WHERE sales_period = '2023_1Q'
ORDER BY album_genre, copies_sold DESC;

Per ottenere le vendite per genere, utilizzo nuovamente la funzione SUM(). Nella clausola OVER(), utilizzo solo PARTITION BY. In questo modo, posso suddividere la finestra in base al genere dell'album.

Nella riga di codice successiva, divido le copie vendute (di ciascun album) e le divido per le vendite del genere. Per farlo, è sufficiente copiare il calcolo dalla riga precedente. Quindi, moltiplicare il quoziente per 100 per ottenere la percentuale. Si noterà che ho anche moltiplicato copies_sold per 1,0. Questo serve per convertire i numeri interi in cifre. Questo serve per convertire i numeri interi in valori decimali.

Filtrare il trimestre desiderato utilizzando WHERE. Infine, ordinate il risultato in ordine alfabetico per genere e decrescente per copie vendute.

Ecco il risultato:

Verifichiamo il calcolo per la prima riga. L'album di Simon & Garfunkel ha venduto 809 copie. Il totale delle vendite di album folk per quel trimestre è stato di 2.081 copie. Quindi la percentuale delle vendite dei singoli album sul totale delle vendite del genere è 809/2.081*100 = 38,88%.

La somma delle percentuali per ciascun genere dovrebbe essere pari al 100%. Verifichiamo questo dato su un genere folk: 38,88% + 19,80% + 19,22% + 11,82% + 10,28% = 100%.

Funzione finestra vs. funzione aggregata vs. GROUP BY

Sto nuovamente utilizzando la funzione aggregata come funzione finestra. Avrei potuto utilizzare una semplice funzione aggregata SUM() con GROUP BY per ottenere le vendite di ciascun genere per il trimestre specificato. Qual è la differenza, allora?

Una funzione finestra consente di mostrare sia i dati analitici che quelli aggregati (le vendite individuali con le vendite per genere e il quoziente di questi valori), mentre una funzione aggregata utilizzata con GROUP BY farebbe collassare la singola riga e mostrerebbe solo il valore aggregato (la somma delle vendite del trimestre).

Risolvere questo esercizio per esercitarsi

Utilizzando le funzioni finestra, riscrivete la query precedente in modo da mostrare la vendita media per genere. Inoltre, mostrare quanto le vendite di ogni album sono superiori o inferiori alla media del genere (in percentuale). Mostrare solo le vendite del terzo trimestre del 2023. Mostra il titolo dell'album, l'artista, le copie vendute e il genere dell'album. Ordinare il risultato in modo ascendente per genere e vendite dei singoli album.

Soluzione:

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 AVG(copies_sold) OVER (PARTITION BY album_genre) AS average_sales_by_genre,
	   ((copies_sold/AVG(copies_sold) OVER (PARTITION BY album_genre))-1)*100 AS pct_from_average	   
FROM album_catalogue
WHERE sales_period = '2023_3Q'
ORDER BY album_genre, copies_sold;

Output:

Esempio n. 2: Classifica dei dati

In questo esempio, utilizzerò una funzione finestra per classificare i dati. Voglio mostrare ogni titolo di album distinto e la sua lunghezza e classificarli in base alla lunghezza. L'album più lungo verrà classificato per primo.

SELECT *, 
	 RANK() OVER (ORDER BY album_length DESC) AS album_length_rank
FROM (SELECT DISTINCT album_title,
	       album_length
	FROM album_catalogue) AS distinct_album;

Iniziamo spiegando la sottoquery: la usiamo per selezionare album distinti e la loro lunghezza.

Quindi, utilizziamo la query principale per selezionare tutti i dati dalla subquery. Ora si utilizza la funzione RANK() window per classificare gli album. È possibile utilizzare anche altre funzioni di classificazione, a seconda dei dati e delle attività.

Affinché la classificazione funzioni nel modo desiderato, utilizzare la clausola ORDER BY in OVER(). Specificare la colonna in base alla quale si desidera classificare e in quale ordine. In questo caso, l'ordine è decrescente per lunghezza.

Ecco la classifica:

Risolvere questo esercizio per esercitarsi

Classificare ogni singolo album in base alle vendite all'interno del suo genere. Mostrare solo i dati relativi al primo trimestre del 2023. Mostrare il titolo dell'album, le vendite, il genere e la classifica. Se ci sono album con lo stesso numero di vendite, classificateli allo stesso modo e non saltate la classifica successiva.

Soluzione:

SELECT *, 
	 DENSE_RANK() OVER (PARTITION BY album_genre ORDER BY copies_sold DESC) AS album_sales_rank
FROM (SELECT DISTINCT album_title,
	       copies_sold,
	       album_genre
	FROM album_catalogue
	WHERE sales_period = '2023_1Q') AS distinct_album;

Output:

Esempio #3: Totale corrente

In questo esempio, mostrerò il periodo di vendita di un particolare album, il titolo, l'artista e le copie vendute. Aggiungerò anche un totale progressivo delle copie vendute che includerà tre righe: la riga corrente e le due precedenti. La somma deve essere calcolata dal primo all'ultimo trimestre.

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'In a Silent Way';

Anche in questo caso utilizzo la funzione SUM(). Poi c'è un ORDER BY in OVER() per ordinare le vendite in modo da poterle sommare in modo ascendente.

Successivamente, è necessario definire la cornice della finestra mobile. Il totale corrente deve includere la riga corrente e le due righe precedenti. Questi sono i limiti inferiori e superiori specificati nella clausola ROWS. Il limite inferiore è costituito dalle due righe precedenti, cioè BETWEEN 2 PRECEDING. Il limite superiore è CURRENT ROW. I due limiti vengono uniti in una cornice di finestra utilizzando la parola chiave AND.

Voglio mostrare il calcolo per l'album "In a Silent Way" di Miles Davis, quindi filtro i dati usando WHERE.

Ecco i totali in corso:

Verifichiamo il risultato e spieghiamo cos'è un totale progressivo.

Un totale progressivo è simile a un totale cumulativo (o somma), ma non sono la stessa cosa. Il totale cumulativo fornisce la somma della riga corrente e di tutte le righe precedenti, cioè il riquadro della finestra aumenta a ogni riga. Il totale progressivo è una somma all'interno di una finestra definita che mantiene la stessa dimensione ma si sposta a ogni riga. Nel nostro caso, la finestra è definita come la riga corrente e le due righe precedenti.

Osservare i valori evidenziati. Il totale corrente per il primo trimestre del 2022 è 428, lo stesso della singola vendita. Non ci sono righe precedenti, quindi il totale corrente include solo la riga corrente.

Il totale progressivo successivo è 428 + 1.053 = 1.481. Somma la riga corrente e quella precedente, poiché esiste solo una riga precedente.

Il totale progressivo per il terzo trimestre del 2022 è 428 + 1.053 + 19 = 1.500. Questa è la prima volta che si ottiene l'intera finestra, cioè la riga corrente e le due righe precedenti.

Quando si passa alla riga successiva, la finestra si sposta ma le sue dimensioni rimangono invariate. Il totale corrente per il trimestre successivo è 428 + 1.053 + 19 + 65 = 1.137. Anche in questo caso, si tratta della riga corrente e delle due righe precedenti, ma diverse rispetto al trimestre.

Risolvete questo esercizio per esercitarvi

Riscrivete la query precedente in modo che calcoli il totale progressivo per l'album "The New Folk Sound of Terry Callier". Il totale progressivo deve essere calcolato dal primo all'ultimo trimestre. Dovrebbe includere quattro trimestri: i due precedenti, il trimestre in corso e quello successivo. Indicare inoltre il periodo di vendita, il titolo dell'album, l'artista e il numero di copie vendute.

Soluzione:

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'The New Folk Sound of Terry Callier';

Output:

Esempio #4: Differenza da trimestre a trimestre

In quest'ultimo esempio, mostrerò come utilizzare le funzioni della finestra per calcolare la differenza di vendite tra i trimestri:

SELECT *,
	 LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS previous_quarter_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS quarterly_sales_difference
FROM (SELECT sales_period,
	SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

Per prima cosa scrivo una sottoquery che calcola le vendite totali per ogni trimestre. Utilizzo la funzione aggregata SUM() e raggruppo i risultati in base al periodo di vendita.

Quindi, seleziono tutti i dati della sottoquery nella query principale.

Ora devo ottenere le vendite del trimestre precedente. Scriverò la funzione LAG() window, che viene utilizzata per accedere ai valori delle righe precedenti. Il valore a cui voglio accedere è specificato nella funzione. In questo caso, si tratta delle copie trimestrali vendute dalla sottoquery. Definendo l'argomento offset, la funzione mi permette di definire quanto indietro voglio andare. Non l'ho definito, quindi l'offset predefinito è uno. In altre parole, la funzione otterrà i dati dalla riga/trimestre precedente. Ma se si vuole andare indietro di due righe/quarti, si deve scrivere LAG(quarterly_copies_sold, 2).

Uso anche ORDER BY in OVER() per assicurarmi che i valori all'interno della cornice siano ordinati dal trimestre più vecchio a quello più recente.

L'uso della funzione finestra serve a rendere più chiaro ciò che farò nella riga di codice successiva. È qui che viene eseguito il calcolo effettivo del confronto tra le vendite del trimestre in corso e quelle del trimestre precedente. Ora è semplice: sottrarre la funzione finestra definita sopra dalla colonna quarterly_copies_sold.

Ecco il risultato:

Non ci sono valori precedenti per il 2022_1Q, poiché non esiste un trimestre precedente. Le vendite trimestrali per il 2022_2Q sono 7.581. Le vendite nel trimestre precedente sono state 9.519. Il calcolo mostra che le vendite attuali sono inferiori di 1.938 copie (7.581 - 9.519) rispetto alle vendite del trimestre precedente.

È possibile analizzare il resto della produzione nello stesso modo.

Risolvere questo esercizio per esercitarsi

Riscrivete la query precedente in modo che mostri la differenza tra le vendite trimestrali su base annua, ad esempio confrontando il primo trimestre del 2023 con il primo trimestre del 2022. Mostrate il periodo di vendita, le copie vendute nel trimestre, le vendite dello stesso trimestre dell'anno precedente e la differenza di anno in anno tra i trimestri.

Soluzione:

SELECT *,
	 LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_difference
FROM (SELECT sales_period,
	   SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

Output:

Se volete saperne di più, ecco altri esempi di funzioni finestra. Per esercitarsi, date un'occhiata a questi 11 esercizi sulle funzioni finestra di SQL.

SQL Window Functions: una finestra per una migliore analisi dei dati

Questo articolo si è rivelato piuttosto completo sulle funzioni finestra di SQL. Avete imparato a conoscere le funzioni finestra e a capire come funziona ogni parte cruciale della loro sintassi.

Sapete anche che esistono diverse categorie di funzioni finestra. Le più utilizzate sono le funzioni aggregate, classificate e analitiche. Gli esempi pratici hanno mostrato come le funzioni finestra possono essere utilizzate nelle più comuni attività di analisi dei dati.

Si spera che non abbiate saltato gli esercizi dell'articolo. Se lo avete fatto, vi consiglio ancora una volta di risolverli. Solo con la pratica è possibile comprendere appieno il significato delle funzioni finestra di SQL.

La risorsa più ricca per imparare ed esercitarsi è il nostro corso. Window Functions corso. Si tratta di un corso interattivo che contiene oltre 200 esercizi pratici e copre l'intera sintassi delle funzioni finestra. Se avete in programma dei colloqui di lavoro, assicuratevi di esaminare queste domande sulle funzioni SQL a finestra. Buona fortuna e continuate a imparare l'SQL!