Guida alle funzioni SQL a finestra

Ogni analista di dati può trarre vantaggio dalla conoscenza delle funzioni finestra SQL. Esse migliorano l'analisi dei dati e aprono le porte a un livello di analisi completamente nuovo. Questo articolo è il punto di partenza per conoscere il mondo delle funzioni finestra di SQL.

Le funzioni SQL window consentono di mostrare contemporaneamente tutte le righe di dati e i loro valori aggregati. Sembra un po' come GROUP BY con gli steroidi, non è vero? Ma non finisce qui. Poiché le funzioni finestra eseguono vari calcoli sull'insieme delle righe, consentono anche di dividere i set di dati in sottoinsiemi. Consentono di classificare facilmente i dati, di aggregarli, di calcolare le differenze tra periodi diversi (ad esempio, l'aumento/diminuzione dei profitti tra due mesi/trimestri/anni), di trovare somme cumulative, totali correnti, medie mobili, ecc.

Le funzioni delle finestre sono un argomento complesso, ma impararle è molto più facile con il nostro corso. Window Functions (Funzioni Finestra) corso. Si tratta di un corso interattivo con 218 esercizi che forniscono un apprendimento sistematico con un sacco di codice. Imparerete a conoscere le clausole essenziali delle funzioni finestra (ad esempio, OVER(), ORDER BY e PARTITION BY ) e a capire cos'è un frame di finestra. Imparerete poi a utilizzare tutto questo nel contesto di diverse funzioni finestra.

Dopo l'apprendimento, è il momento della pratica (che è anche apprendimento). Il nostro set di esercitazioni Window Functions (Funzioni Finestra) offre 100 esercizi interattivi aggiuntivi che vi permetteranno di mettere in pratica le vostre conoscenze.

La sintassi di SQL Window Functions (Funzioni Finestra)

Le funzioni finestra prendono il nome da un riquadro finestra, che è un insieme di righe correlate alla riga corrente.

Per eseguire un'operazione con una funzione finestra sulla cornice della finestra, è necessario conoscere la sintassi generale delle funzioni finestra:

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

Ci sono diverse parti essenziali di questa sintassi che devono essere spiegate:

• Clausola OVER: È la clausola obbligatoria per definire una cornice di finestra. Ad esempio, si riconosce la funzione finestra SUM(order_value) OVER() as a SUM(). Senza OVER(), si tratta solo di una normale funzione aggregata SUM().
• PARTIZIONE PER: È una clausola facoltativa che consente di suddividere l'insieme di dati in sottoinsiemi. Consente di applicare una funzione finestra a ciascuna partizione separatamente. Se questa clausola viene omessa, l'intero insieme dei risultati è una partizione.
• ORDER BY: Questa clausola opzionale (per alcune funzioni finestra) viene utilizzata per specificare l'ordine delle righe in un riquadro finestra. Se si omette questa clausola, l'ordine delle righe nella finestra sarà arbitrario.
• <window_frame>: Definisce i limiti superiore e inferiore di una cornice di finestra. Due clausole importanti sono ROWS e RANGE. ROWS definisce il numero di righe che precedono e seguono la riga corrente. La clausola RANGE definisce l'intervallo di righe in base al loro valore rispetto alla riga corrente. Per saperne di più, consultare il nostro articolo sulle differenze tra ROWS e RANGE. Questa parte della sintassi viene di solito omessa, poiché la cornice predefinita della finestra è ciò di cui gli utenti hanno più spesso bisogno.

La cornice predefinita, in questo caso, dipende dall'uso o meno della clausola ORDER BY in OVER(). In caso affermativo, la cornice è costituita da tutta la riga corrente e da tutte le righe che la precedono nella partizione corrente. Se non si specifica ORDER BY, la cornice della finestra è la riga corrente e tutte le righe che la precedono e la seguono nella partizione corrente. Nel secondo caso, il riquadro della finestra è fondamentalmente l'intero set di dati o l'intera partizione, se si utilizza anche PARTITION BY.

SQL comune Window Functions (Funzioni Finestra)

Esistono diverse funzioni di finestra. Ecco una panoramica di alcune delle più comuni:

Esempi di SQL Window Functions (Funzioni Finestra)

Ora che abbiamo passato in rassegna le nozioni di base, è il momento di mostrare alcuni esempi pratici di funzioni finestra.

In tutti gli esempi utilizzerò la stessa tabella. È intitolata exchange_rates e contiene i tassi di cambio della Banca centrale europea (BCE) di aprile 2024 per tre coppie di valute: EUR contro USD, EUR contro CHF e EUR contro JPY.

Ecco un'istantanea parziale dei dati.

Esempio di funzione Ranking Window

Questo esempio mostra il funzionamento di DENSE_RANK(). Le altre due funzioni di ranking window possono essere utilizzate allo stesso modo, ma potrebbero (a seconda dei dati) restituire risultati leggermente diversi.

Il codice seguente classifica i dati della tabella dal tasso di cambio più alto a quello più basso:

SELECT date,
	 currency_pair,
	 ecb_rate, 
	 DENSE_RANK() OVER (ORDER BY ecb_rate DESC) AS rank_ecb_rate
FROM exchange_rates;

Seleziono la data, la coppia di valute e il tasso. Ora ho scelto DENSE_RANK() per classificare i dati. Questo solo nel caso in cui ci siano gli stessi tassi di cambio (altamente improbabile, ma comunque...); voglio che siano classificati allo stesso modo e non voglio lacune nella classifica.

La funzione DENSE_RANK() è seguita dalla clausola OVER() che definisce la funzione finestra. Tra le parentesi della clausola, utilizzo un'altra clausola della funzione finestra:ORDER BY. In questo modo, dico alla funzione finestra DENSE_RANK() di classificare i dati in base al tasso della BCE in ordine decrescente.

Questo è l'output che ottengo:

Come si può vedere, ogni riga è stata classificata in base al valore del tasso. In caso di tassi uguali, DENSE_RANK() assegnerebbe la stessa classifica e non salterebbe la classifica. RANK() farebbe lo stesso, solo che salterebbe la sequenza di classifiche. ROW_NUMBER() assegnerebbe classifiche consecutive, anche se alcune righe hanno lo stesso tasso di cambio.

Esempio di funzione finestra aggregata

Ecco un esempio di come si possa utilizzare la funzione AVG() window per calcolare il tasso medio per ogni coppia di valute:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 AVG(ecb_rate) OVER (PARTITION BY currency_pair) AS avg_rate_by_currency
FROM exchange_rates
ORDER BY currency_pair, date;

Come nella normale funzione aggregata AVG(), è necessario scrivere la colonna di cui si vuole ottenere la media. Per renderla una funzione finestra, utilizzare la clausola OVER(). Questa volta, utilizzo PARTITION BY nella colonna currency_pair. In questo modo, divido i dati in sottoinsiemi in base alla coppia di valute.

In poche parole, calcolo il tasso medio per ogni coppia di valute separatamente.

Date un'occhiata al codice di output. È stato ordinato per coppie di valute e per data:

Il tasso medio EUR vs. CHF è 0,9793 e questo valore viene ripetuto per ogni riga EUR vs. CHF. Quando la funzione finestra raggiunge la coppia di valute successiva, la media si azzera e viene calcolata nuovamente; per EUR vs. JPY è 164,1211. Infine, la media per EUR vs. USD è 1,0795.

Questa funzione di finestra mi ha permesso di calcolare le medie separatamente e di mostrare i valori medi senza collassare le singole righe. In altre parole, posso vedere ogni tasso giornaliero insieme alla media di quella coppia di valute.

Esempi di funzioni analitiche a finestra

In questa sezione mostrerò tre esempi di diverse funzioni analitiche SQL a finestra.

LAG()

L'esempio LAG() mostra come calcolare la variazione giornaliera. Questa funzione viene utilizzata per accedere al valore delle righe precedenti. Un'altra funzione analitica a finestra è LEAD(), che fa esattamente il contrario: recupera i dati dalle righe successive. Entrambe le funzioni hanno sostanzialmente la stessa sintassi; si cambia solo il nome della funzione.

In questo esempio, voglio calcolare la variazione giornaliera dei tassi di cambio:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - LAG(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS daily_rate_change
FROM exchange_rates;

LAG() è una funzione che consente di accedere ai valori delle righe precedenti a quella corrente. Nell'esempio precedente, ho specificato la colonna ecb_rate in LAG(). Ciò significa che voglio accedere al valore del tasso della BCE. Non ho specificato esplicitamente il numero di righe a cui voglio tornare, quindi il valore predefinito sarà di una riga.

OVER() viene dopo il nome della funzione. In questo caso, divido il set di dati per coppia di valute, poiché voglio calcolare la variazione giornaliera dei tassi per ogni coppia di valute separatamente.

Uso anche ORDER BY per ordinare i dati all'interno delle partizioni. Poiché la logica è quella di tornare indietro di un giorno, i dati devono essere ordinati in modo ascendente per data.

Quindi, la parte LAG() del codice rappresenta il tasso di cambio del giorno precedente. Per ottenere la differenza giornaliera, è sufficiente sottrarre questo valore dal tasso di cambio attuale (ecb_rate - LAG(ecb_rate)).

Ecco l'output:

La prima riga è NULL perché non c'è una data precedente, quindi la differenza non può essere calcolata. Nella riga successiva, la variazione giornaliera del tasso è 0,9792-0,9765 = 0,0027. Lo stesso principio di prendere il valore della riga precedente e sottrarlo da quello attuale viene continuato con ogni riga.

Poiché il set di dati è suddiviso per coppia di valute, il calcolo viene reimpostato una volta raggiunte altre coppie di valute, ossia EUR vs. JPY e EUR vs. USD.

PRIMO_VALORE()

La funzione FIRST_VALUE() può essere utilizzata sui nostri dati per calcolare le differenze tra il tasso di cambio corrente e il primo tasso di cambio del mese.

È possibile farlo perché FIRST_VALUE() restituisce il primo valore all'interno della partizione. Il suo opposto è LAST_VALUE(), che restituisce l'ultimo valore della partizione. Entrambe le funzioni hanno sostanzialmente la stessa sintassi; solo il nome della funzione è diverso (e il risultato, ovviamente!).

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - FIRST_VALUE(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS difference_current_first
	  FROM exchange_rates
ORDER BY currency_pair, date;

Voglio il valore del tasso; ecco perché c'è la colonna ecb_rate in FIRST_VALUE(). I dati sono ancora una volta suddivisi per coppia di valute, poiché voglio un calcolo per ogni coppia.

Ok, quindi FIRST_VALUE() viene utilizzato per recuperare il valore dalla prima riga della partizione. Ma cosa succede se ordino i dati nella partizione in modo ascendente per data? Esatto, la prima riga è quella con il tasso del primo giorno del mese. Nel nostro caso, si tratta del primo tasso di cambio di aprile.

Ora, sottraete questo dato dal tasso di cambio attuale. Poiché sappiamo che i nostri dati si riferiscono solo ad aprile, otteniamo la differenza tra il tasso attuale e il primo tasso del mese.

Quando la differenza è pari a 0, il tasso attuale e quello precedente sono uguali. Per EUR vs. CHF, il primo tasso è 0,9765. Controlliamo le prime due righe: 0.9765 - 0.9765 = 0.0000; 0.9792 - 0.9765 = 0.0027.

Lo stesso principio si applica alle altre due coppie di valute.

NTILE()

L'ultimo esempio che mostrerò è la funzione NTILE(), che divide la finestra (o partizione) in gruppi. L'argomento tra le parentesi della funzione NTILE() specifica il numero di gruppi in cui si vuole dividere il set di dati.

La suddivisione avverrà in ordine cronologico, ordinando i dati in modo crescente per data:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 NTILE(3) OVER (ORDER BY date) AS group_number
FROM exchange_rates;

Ecco il risultato:

I dati sono stati suddivisi in tre gruppi. Poiché ci sono 27 righe di dati in totale, è possibile dividerli in gruppi uguali di nove righe.

Window Functions (Funzioni Finestra) vs. GROUP BY e funzioni aggregate

Le funzioni aggregate della finestra, come si può dedurre dal loro nome, sono utilizzate per aggregare i dati. Ma che dire delle funzioni aggregate "normali" e di GROUP BY? Anch'esse vengono utilizzate per aggregare i dati. In cosa differiscono le funzioni finestra?

La differenza principale è che le funzioni aggregate a finestra (e le funzioni a finestra in generale) non comprimono le singole righe mentre mostrano il valore aggregato. D'altra parte, le funzioni GROUP BY e aggregate possono mostrare solo i valori aggregati; esse comprimono le singole righe.

In parole povere, le funzioni finestra consentono di mostrare i dati analitici e aggregati allo stesso tempo.

SQL avanzato Uso delle funzioni finestra

Le funzioni finestra sono molto utilizzate nell'analisi dei dati, in quanto possono risolvere molti requisiti di reporting aziendale.

Ecco una panoramica di alcuni degli usi più complessi delle funzioni finestra.

Classificazione

Sì, ho già parlato della classificazione e vi ho mostrato un esempio di come farla. Esistono tre funzioni finestra per classificare i dati: ROW_NUMBER(), RANK(), e DENSE_RANK(). Non necessariamente restituiscono lo stesso risultato, in quanto hanno tutti modi leggermente diversi di classificare i dati. Quale utilizzare dipende dai dati e da ciò che si vuole ottenere.

Esecuzione di totali e medie mobili

Entrambi i calcoli sono solitamente utilizzati nell'analisi delle serie temporali. Le serie temporali sono dati che mostrano valori in determinati momenti. L'analisi di questi dati è, appunto, l'analisi delle serie temporali. Il suo scopo è quello di svelare le tendenze dei dati e trovare le possibili cause di deviazioni significative dalla tendenza.

Il totale corrente (o somma cumulativa) è la somma dei valori della riga corrente e di tutte le righe precedenti. Man mano che ci si sposta verso il futuro, la dimensione del time frame aumenta di una riga/punto dati e il valore viene aggiunto al totale corrente della riga precedente.

Le medie mobili sono il valore medio degli ultimi n periodi. Man mano che ci si sposta verso il futuro, il time frame si sposta, ma la sua dimensione rimane invariata. Questa tecnica è molto utilizzata nel settore finanziario, ad esempio la media mobile a 5 giorni nell'analisi dei prezzi delle azioni. In questo modo, il prezzo medio viene continuamente aggiornato e l'impatto di variazioni significative a breve termine sul prezzo delle azioni viene neutralizzato.

Differenza tra due righe o periodi di tempo

La funzione della finestra utilizzata per calcolare la differenza tra due righe è LAG(), che consente di accedere ai valori delle righe precedenti. La differenza tra due periodi di tempo è sostanzialmente la stessa cosa; si riferisce solo alla ricerca di differenze quando si lavora con le serie temporali. Nella sezione degli esempi è stato mostrato come fare.

Analisi delle serie temporali

Le funzioni delle finestre funzionano a meraviglia quando si devono analizzare le serie temporali. Non esiste solo la funzione LAG(), ma anche molte altre.

Problemi comuni con SQL Window Functions (Funzioni Finestra)

Tutti coloro che utilizzano le funzioni a finestra si imbattono prima o poi in diversi problemi:

1. Confondere le funzioni finestra con le funzioni aggregate e GROUP BY, di cui abbiamo già parlato.
2. Cercare di usare le funzioni finestra in WHERE. Questo non è possibile perché SQL elabora le condizioni WHERE prima delle funzioni finestra.
3. Cercare di utilizzare le funzioni finestra in GROUP BY, anche questo non è consentito a causa dell'ordine delle operazioni di SQL: le funzioni finestra vengono eseguite dopo GROUP BY.

Altre risorse per esercitarsi con l'SQL Window Functions (Funzioni Finestra)

Le funzioni finestra di SQL sono tra gli strumenti SQL più utili per gli analisti di dati. Questo è particolarmente vero quando si va oltre i report di base e si richiedono calcoli sofisticati e la possibilità di mostrare contemporaneamente dati analitici e aggregati.

Tutti gli argomenti trattati in questo articolo richiedono ulteriori esercitazioni su esempi pratici, che potete trovare nei seguenti articoli e corsi:

1. 11 esercizi di SQL Window Functions (Funzioni Finestra) con soluzioni
2. Le 10 migliori domande di intervista su SQL Window Functions (Funzioni Finestra)
3. SQL Window Functions (Funzioni Finestra) Cheat Sheet
4. Window Functions (Funzioni Finestra) Corso
5. Window Functions (Funzioni Finestra) Set di esercitazioni

Ricordate: la pratica rende perfetti! Quindi non limitatevi a leggere gli articoli, ma assicuratevi di fare anche un po' di pratica di codifica. Buon apprendimento!