5 esempi pratici di utilizzo della clausola ROWS BETWEEN in SQL

Le funzioni SQL a finestra sono estremamente utili per calcolare aggregazioni complesse come le medie mobili o i totali progressivi. La clausola ROWS permette di specificare le righe per i calcoli, consentendo di creare finestre ancora più sofisticate. Ecco cinque esempi pratici di utilizzo della clausola ROWS BETWEEN in SQL.

Lefunzioni finestra (chiamate anche funzioni OVER) calcolano il risultato in base a una finestra scorrevole (cioè un insieme di righe). Sono simili alle funzioni aggregate, in quanto possono calcolare la media, il totale o il valore minimo/massimo di un gruppo di righe. Tuttavia, vi sono alcune importanti differenze:

• Le funzioni finestra non comprimono le righe come le funzioni aggregate. Pertanto, è ancora possibile mescolare gli attributi di una singola riga con i risultati di una funzione finestra.
• Lefunzioni finestra consentono di scorrere le cornici della finestra, il che significa che l'insieme delle righe utilizzate per il calcolo di una funzione finestra può essere diverso per ogni singola riga.

La sintassi di una funzione finestra è mostrata nel testo blu qui sotto:

SELECT , ,
  OVER (
 	PARTITION BY <...>
   	ORDER BY <...>
    	) 
FROM ;

Quando si utilizza una funzione finestra nell'istruzione SELECT, in pratica si calcola un'altra colonna con questa funzione:

• Si inizia specificando una funzione (ad esempio AVG(), SUM(), o COUNT()).
• Quindi, si utilizza la parola chiave OVER per definire un insieme di righe. Opzionalmente, è possibile:
  • Raggruppate le righe con PARTITION BY in modo che le funzioni vengano calcolate all'interno di questi gruppi invece che nell'intero insieme di righe.
  • Ordinare le righe all'interno di un riquadro di finestra usando ORDER BY se l'ordine delle righe è importante (ad esempio per il calcolo dei totali).
  • Specificare la relazione del riquadro della finestra con la riga corrente (ad esempio, il riquadro deve essere la riga corrente e le due precedenti, oppure la riga corrente e tutte le righe successive, ecc.)

Una cornice viene definita utilizzando le clausole ROWS, RANGE e GROUPS. In questo articolo ci concentreremo sulla clausola ROWS e sulle sue opzioni. Per saperne di più sulle funzioni finestra e sulla definizione dei riquadri finestra, consultate questo articolo con esempi di funzioni finestra, questa guida esplicativa e, naturalmente, il nostro foglio informativo di due pagine su SQL Window Functions .

Clausola ROWS: Sintassi e opzioni

Lo scopo della clausola ROWS è quello di specificare la cornice della finestra in relazione alla riga corrente. La sintassi è la seguente:

ROWS BETWEEN lower_bound AND upper_bound

I limiti possono essere una qualsiasi delle cinque opzioni seguenti:

• UNBOUNDED PRECEDING - Tutte le righe prima della riga corrente.
• n PRECEDING - n righe prima della riga corrente.
• CURRENT ROW - Solo la riga corrente.
• n FOLLOWING - n righe dopo la riga corrente.
• UNBOUNDED FOLLOWING - Tutte le righe dopo la riga corrente.

Fonte: SQL Window Functions Cheat Sheet

Ecco un paio di cose da tenere a mente quando si definiscono le cornici delle finestre con la clausola ROWS:

• La cornice della finestra viene valutata separatamente all'interno di ogni partizione.
• L'opzione predefinita dipende dall'uso di ORDER BY:
  • Con ORDER BY, la cornice predefinita è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • Senza ORDER BY, la cornice predefinita è ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• Se uno dei limiti è una riga corrente, è possibile non specificare questo limite e utilizzare una versione più breve della definizione della cornice della finestra:
  • UNBOUNDED PRECEDING è uguale a BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • n PRECEDING è uguale a BETWEEN n PRECEDING AND CURRENT ROW.
  • n FOLLOWING è lo stesso di BETWEEN CURRENT ROW AND n FOLLOWING.
  • UNBOUNDED FOLLOWING è la stessa di BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Passiamo agli esempi per vedere come funziona in pratica.

5 Esempi pratici di utilizzo di ROWS in Window Functions

Esempio 1

Per iniziare a usare la clausola ROWS, utilizzeremo la seguente tabella con i dati di vendita di una libreria.

Nel nostro primo esempio, vogliamo aggiungere un'altra colonna che mostri il ricavo totale dalla prima data fino alla data della riga corrente (cioè il totale corrente). Ecco la query da utilizzare:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) running_total
FROM sales
ORDER BY date;

Per calcolare il totale corrente utilizzando una funzione finestra, si procede come segue:

• Calcolo del totale delle entrate con la funzione aggregata SUM().
• Ordinare i record nel riquadro della finestra in base alla data (l'ordine predefinito è ascendente), poiché l'ordine delle righe è importante quando si calcola un totale progressivo.
• Specificare la cornice della finestra definendo il limite inferiore come UNBOUNDED PRECEDING e il limite superiore come CURRENT ROW. Questo includerà tutte le righe fino a quella corrente. Si noti che il comportamento predefinito senza la clausola ROWS sarebbe lo stesso in questo caso. Il frame predefinito utilizza RANGE e non ROWS. Poiché ogni giorno compare una sola volta nella tabella, il risultato sarà lo stesso per RANGE e ROWS. Si può quindi utilizzare la seguente query per ottenere gli stessi risultati:

SELECT date, revenue,
	SUM(revenue) OVER (
      ORDER BY date) running_sum
FROM sales
ORDER BY date;

Come si vede, la query ha funzionato come previsto e abbiamo ottenuto il totale corrente nella terza colonna. Il primo giorno equivale alle vendite di questo giorno - 1515,45 dollari; il secondo giorno equivale alla somma delle vendite del primo e del secondo giorno - 3860,80 dollari; nella riga successiva otteniamo la somma delle vendite dei primi tre giorni - 4764,79 dollari, ecc.

Nei prossimi esempi vedremo come funziona la clausola ROWS quando i record sono divisi in più gruppi.

Per esercitarsi a definire le cornici delle finestre, consultate questo corso interattivo con Window Functions con oltre 200 sfide di codifica.

Esempio 2

Per i prossimi due esempi, utilizzeremo la tabella seguente. Essa contiene dati fittizi sulla temperatura media (in °C) e sulle precipitazioni totali (in mm) in due città italiane (Roma e Firenze) per cinque giorni consecutivi.

Vogliamo calcolare la temperatura media mobile a tre giorni separatamente per ogni città. Per separare i calcoli per le due città, includeremo la clausola PARTITION BY. Poi, quando specifichiamo la finestra, consideriamo il giorno corrente e i due giorni precedenti:

Notate anche che abbiamo inserito la nostra funzione finestra all'interno della funzione ROUND() in modo che la media mobile a tre giorni venga arrotondata a un decimale. Ecco il risultato:

La media mobile è stata calcolata separatamente per Firenze e Roma. Per il 1° settembre, la media mobile è uguale alla temperatura media giornaliera, poiché non abbiamo alcun record precedente. Poi, il 2 settembre, la media mobile è calcolata come la temperatura media dell'1 e del 2 (17,5 °C a Firenze e 18,8 °C a Roma, rispettivamente). Il 3 settembre si hanno finalmente dati sufficienti per calcolare la temperatura media di tre giorni (i due precedenti e il giorno in corso), che risulta essere di 18,0 °C a Firenze e 19,2 °C a Roma. Quindi, la media mobile di tre giorni per il 4 settembre viene calcolata come media delle temperature del 2, 3 e 4, e così via.

Un'altra cosa da notare: l'ordine dei record nella finestra ha un ruolo fondamentale nello specificare quali righe considerare.

Nella query qui sopra, abbiamo ordinato i record nella finestra per data in ordine crescente (utilizzando l'impostazione predefinita), cioè partendo dalla data più antica. Poi, per includere nei nostri calcoli due giorni prima del giorno corrente, abbiamo impostato il limite inferiore come 2 PRECEDING.

Tuttavia, si può ottenere la stessa finestra ordinando i record in ordine decrescente e modificando l'opzione ROWS per includere 2 FOLLOWING invece di 2 PRECEDING:

SELECT city, date, temperature,
	ROUND(AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date DESC
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING), 1) mov_avg_3d_city
FROM weather
ORDER BY city, date;

Questa query produce lo stesso identico risultato.

Esempio 3

In questo esempio, calcoleremo le precipitazioni totali degli ultimi tre giorni (cioè un totale di tre giorni) separatamente per due città.

SELECT city, date, precipitation,
	SUM(precipitation) OVER (
      PARTITION BY city
      ORDER BY date
      ROWS 2 PRECEDING) running_total_3d_city
FROM weather
ORDER BY city, date;

In questa query, i dati vengono nuovamente suddivisi per città. Utilizziamo la funzione SUM() per calcolare il livello totale delle precipitazioni degli ultimi tre giorni, compreso il giorno corrente. Inoltre, si noti che si utilizza un'abbreviazione per definire la finestra specificando solo il limite inferiore: 2 PRECEDING.

Ecco l'output della query di cui sopra:

Al 3 settembre, otteniamo un totale di tre giorni di precipitazioni a Firenze: 30 mm. Si tratta della somma dei 5 mm di precipitazione del 1° settembre, dei 15 mm del 2° e dei 10 mm del 3°.

Sapete come abbiamo ottenuto il totale progressivo di 12 mm per Roma il 5 settembre? Provate a seguire i risultati nella nostra tabella di output per assicurarvi di aver capito come funzionano le funzioni delle finestre con i telai specifici delle finestre.

Passiamo ora a nuovi dati ed esempi.

Esempio 4

Per i prossimi due esempi utilizzeremo i dati mostrati di seguito. Questi dati includono informazioni giornaliere sul numero di nuovi iscritti a tre social network: Instagram, Facebook e LinkedIn.

Cominciamo a calcolare i totali progressivi del numero di nuovi iscritti separatamente per ogni rete. In pratica, per ogni giorno, vogliamo vedere quante persone si sono iscritte da quando abbiamo iniziato a raccogliere i dati fino alla data della riga corrente.

Ecco una query SQL che soddisfa questa richiesta:

SELECT social_network, date, new_subscribers,
	SUM(new_subscribers) OVER (
      PARTITION BY social_network
      ORDER BY date
      ROWS UNBOUNDED PRECEDING) running_total_network
FROM subscribers
ORDER BY social_network, date;

Si inizia calcolando il numero totale di nuovi iscritti utilizzando la funzione aggregata SUM(). Quindi si utilizza la clausola PARTITION BY per effettuare calcoli separati per ogni rete. Inoltre, ordiniamo i record per data in ordine crescente (per impostazione predefinita). Infine, definiamo la cornice della finestra come UNBOUNDED PRECEDING per includere tutti i record fino a quello corrente.

L'output appare come segue:

Nella tabella dei risultati, si può notare come il numero di nuovi iscritti venga aggiunto al totale cumulativo per ogni nuovo record. Il totale progressivo viene calcolato separatamente per ogni rete, come specificato nella funzione finestra.

Esempio 5

Nell'ultimo esempio, vogliamo dimostrare come sia possibile visualizzare il primo e l'ultimo valore di una serie specifica di record utilizzando le funzioni finestra e la clausola ROWS. Questa volta aggiungiamo due colonne all'output:

• Il numero di nuovi abbonati aggiunti il primo giorno, e
• Il numero di nuovi iscritti aggiunti l'ultimo giorno.

Con queste informazioni calcolate separatamente per ogni social network, possiamo vedere come le prestazioni di ogni giorno si confrontano con il punto di partenza e con il punto di arrivo.

Ecco la query SQL per ottenere l'output richiesto:

SELECT social_network, date, new_subscribers,
    FIRST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date) AS first_day,
    LAST_VALUE(new_subscribers) OVER(
      PARTITION BY social_network
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_day
FROM subscribers
ORDER BY social_network, date;

Come si vede, utilizziamo le funzioni FIRST_VALUE() e LAST_VALUE() per ottenere le informazioni rispettivamente sul primo e sull'ultimo giorno. Si noti anche come viene specificata la cornice della finestra per ciascuna delle funzioni:

• Non includiamo la clausola ROWS con la funzione FIRST_VALUE() perché il comportamento predefinito (cioè RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) va bene per i nostri scopi.
• Tuttavia, specifichiamo il riquadro della finestra con la funzione LAST_VALUE() perché l'opzione predefinita utilizzerebbe il valore della riga corrente come ultimo valore per ogni record; non è quello che stiamo cercando in questo esempio. Specifichiamo la cornice della finestra come ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING per assicurarci che tutti i record vengano considerati.

Ecco l'insieme dei risultati:

Come richiesto, abbiamo il numero di nuovi iscritti del primo e dell'ultimo giorno calcolato separatamente per ogni social network.

Esercitarsi a usare le righe in SQL Window Functions

Dopo aver esaminato gli esempi precedenti, si spera che si sia motivati a imparare più a fondo le funzioni finestra di SQL e le opzioni ROWS. Questo toolkit consente di specificare una finestra scorrevole e di calcolare aggregazioni complesse come le medie mobili e i totali progressivi.

Se volete acquisire familiarità con le funzioni a finestra, vi consiglio il corso interattivo di LearnSQL.itWindow Functions di . Vi mostrerà come calcolare totali e medie consecutive, costruire diversi tipi di classifiche, analizzare le tendenze nel tempo e molto altro ancora. Inoltre, gli esercizi vengono svolti da soli, il che è il modo migliore per imparare.

Se volete imparare a usare SQL per l'analisi dei dati, il nostro percorso di apprendimento include anche Advanced SQL percorso di apprendimento include anche GROUP BY Extensions in SQL e le espressioni di tabella comuni (CTE). È un ottimo modo per approfondire la conoscenza delle funzioni di finestra.

Volete iniziare a leggere qualcosa? Ecco gli 8 articoli più importanti sulle funzioni finestra di SQL.

Grazie per aver letto e buon apprendimento.