La clausola RANGE nelle funzioni SQL Window: 5 esempi pratici

Che cos'è un CAMPO nelle funzioni SQL a finestra? Cinque esempi pratici vi mostreranno come e quando usarla.

La clausola RANGE viene utilizzata piuttosto raramente nelle funzioni SQL a finestra. Non so perché, forse le persone non sono abituate a usarla. È un peccato, perché è tutt'altro che una clausola inutile; può essere molto utile e ve lo mostrerò in cinque esempi.

Sintassi della clausola RANGE

Quando parlo della clausola RANGE, mi riferisco a quella utilizzata nelle funzioni di SQL window, che hanno la seguente sintassi:

OVER (   
  [ <PARTITION BY clause> ]
  [ <ORDER BY clause> ]
  [ <ROW or RANGE clause> ]
) 

Osservando la sintassi sopra riportata, si nota che sia ROW che RANGE possono far parte della funzione finestra. La sintassi è la seguente:

[<clausola RANGE> TRA <inizio expr> E <fine expr>].

La finestra predefinita senza ORDER BY è l'intera partizione. Ma quando si usa l'ORDER BY, la finestra predefinita è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

È normale dimenticare questa sintassi, soprattutto se è nuova o se non la si usa molto spesso. Non esitate a tenere aperto il Cheat Sheet di Window Functions mentre leggete questo articolo.

Siete pronti a tuffarvi? Perfetto! Innanzitutto, parliamo di RANGE e ROW.

Le clausole RANGE e ROW sono uguali?

No, non lo sono. Tuttavia, hanno lo stesso scopo: specificare i punti iniziali e finali all'interno della partizione, con l'obiettivo di limitare le righe. Tuttavia, ogni clausola lo fa in modo diverso. La clausola ROW lo fa specificando un numero fisso di righe che precedono o seguono la riga corrente.

La clausola RANGE, invece, limita le righe in modo logico, specificando l'intervallo di valori in relazione al valore della riga corrente.

Quali tipi di dati si possono usare nella clausola RANGE?

Con la clausola RANGE è possibile utilizzare due tipi di dati: i tipi numerici e i tipi data/ora.

È possibile utilizzare i tipi di dati numerici e la clausola RANGE in quasi tutti i database più diffusi. Purtroppo, Microsoft SQL Server non la supporta.

Per quanto riguarda i tipi di dati data/ora, solo pochissimi database popolari supportano l'uso di questi tipi di dati con la clausola RANGE. Si tratta di PostgreSQL, MySQL e Oracle DB.

Passiamo ora agli esempi e vediamo come funziona in pratica RANGE! Se non conoscete le funzioni finestra di SQL, forse dovreste prima verificare cosa sono le funzioni finestra e come si confrontano con le funzioni aggregate. Tornate quando avete finito di leggere e continueremo.

Esempio 1 - Calcolo del totale progressivo

I dati con cui lavorerò sono contenuti nella tabella revenue. Le colonne sono:

• id - L'ID della data e la chiave primaria (PK) della tabella.
• date - La data del ricavo.
• revenue_amount - L'importo delle entrate.

Il compito è quello di calcolare i totali correnti delle entrate utilizzando la clausola RANGE. Per prima cosa, non utilizziamo l'SQL. Se si dispone dei seguenti dati, quale sarà il totale progressivo (noto anche come somma cumulativa)?

Il totale progressivo per il 2021-05-01 è uguale al ricavo per quella data: 12.573,25. Questo perché non ci sono righe precedenti da includere nel calcolo. Poi viene il 2021-05-02. Il totale corrente è costituito dalle entrate di oggi sommate a quelle del giorno precedente: 11,348.22 + 12,573.25 = 23,921.47.

Si noti che c'è un'altra riga con un importo di entrate diverso per il 2021-05-02. Forse si tratta di un'altra filiale, paese, prodotto o altro. Funziona allo stesso modo: 14.895,13 + 23.921,47 = 38.816,60. (La clausola RANGE funziona anche se ci sono più righe con la stessa data). Segue il 2021-05-03. Il totale corrente per questa data sarà 14.388,14 + 38.816,60 = 53.204,74. Infine, il totale corrente per il 2021-05-04 sarà 18.847,54 + 53.204 = 72.052,28.

Come si fa a fare lo stesso usando la clausola RANGE? Si potrebbe fare così:

SELECT
  id,
  date,
  revenue_amount,
  SUM(revenue_amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM revenue;

Ho utilizzato la funzione SUM() sulla colonna revenue_amount; questa è l'operazione necessaria per ottenere il totale corrente. Affinché la funzione SUM() diventi una funzione finestra, è necessaria la clausola OVER(). Il calcolo della funzione finestra viene eseguito in ordine crescente; questo perché voglio assicurarmi che le entrate vengano sommate dalla data più vecchia a quella più recente. Poi viene la clausola RANGE. Limita la finestra alle date precedenti la data corrente (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) e alla data corrente. Queste sono le righe che verranno incluse nel calcolo del totale progressivo.

Ecco cosa si ottiene eseguendo il codice:

Si noti che quando ci sono più valori per una data (2021-05-02) il codice includerà entrambe le righe nel calcolo del totale corrente per quella data. Ecco perché nella colonna running_total c'è 38.816,60 per quella data.

Esempio 2 - Calcolo della media mobile

Vediamo ora come utilizzare la clausola RANGE con i tipi di dati data/ora - e senza la colonna id come aiuto! Utilizzeremo una tabella leggermente modificata, che ora si chiama revenue_per_shop. Contiene i dati sulle entrate di due negozi. Le colonne sono:

• date - La data del ricavo.
• shop - Il nome del negozio.
• revenue_amount - L'importo delle entrate di quel negozio in quella data.

Prima di tutto, assicuriamoci di aver capito cos'è una media mobile. Una media mobile a due giorni comprende il giorno corrente e il giorno precedente. Ecco alcuni esempi di dati che illustrano il funzionamento di una media mobile:

La media mobile a due giorni per il 2021-05-01 è il fatturato giornaliero stesso: 12,573.25. Questo perché non ci sono altre righe da includere nel calcolo. Il calcolo per il 2021-05-02 include due date: (12.573,25 + 11.348,22)/2 = 11.960,74. Le altre righe seguono la stessa logica delle due date: la data corrente e la data precedente.

Come si fa a calcolare la stessa metrica per ogni negozio separatamente? In questo modo:

SELECT
  shop,
  date,
  revenue_amount,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date ASC
    RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

Il codice seleziona innanzitutto alcune colonne dalla tabella. Poi viene la parte divertente. Uso la funzione AVG() sulla colonna revenue_amount perché voglio il ricavo medio. Anche in questo caso, si tratta di una funzione finestra, quindi deve avere la clausola OVER(). Uso la funzione PARTITION BY per specificare la colonna su cui voglio aggregare i dati; è la colonna negozio perché voglio la media mobile per ogni negozio separatamente. L'operazione è ancora una volta ordinata per data. Nella clausola RANGE mi limito a specificare quali righe includere nel calcolo. Poiché sto lavorando con le date, otterrò la data precedente dichiarando: BETWEEN INTERVAL '1' DAY PRECEDING.

Il lavoro con i dati di data e ora varia da un database all'altro. In alcuni database potrebbe essere necessario scrivere in questo modo: RANGE BETWEEN 1 DAY PRECEDING AND CURRENT ROW. Il risultato dovrebbe comunque essere lo stesso:

Media mobile per database che non supportano l'uso di RANGE con i tipi di dati data/ora

Cosa fare se il database non supporta l'uso di RANGE con i tipi di dati data/ora? C'è un modo per "ingannare" il database; anzi, probabilmente ci sono diversi modi. Ecco un modo che calcola la differenza tra ogni data e il 2021-05-01 (cioè la prima data nei dati). Si ottiene la differenza come numero intero, che può essere utilizzato al posto della data. Questo è il codice:

SELECT
  shop,
  date,
  revenue_amount,
  date - '2021_05_01' AS day_difference,
  AVG(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY (date - '2021_05_01')
    RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM revenue_per_shop;

Ho intenzionalmente incluso la colonna day_difference nel risultato, in modo che possiate capire la logica. Ho utilizzato la stessa differenza nella clausola ORDER BY in modo da poter utilizzare un numero intero con la clausola RANGE. La media mobile è la stessa dell'esempio precedente; verificate voi stessi. (Per risparmiare spazio mostro solo le prime cinque righe).

Esempio 3 - Trovare l'ultimo valore all'interno di un intervallo

L'uso della clausola RANGE consente di trovare l'ultimo valore all'interno di un intervallo definito. Ad esempio, utilizzando la tabella revenue_by_shopposso ottenere l'ultimo valore per ogni negozio separatamente. Per ultimo valore, in questo caso, si intende l'ultimo dato disponibile, ovvero il fatturato del 2021-05-15. Come si ottengono questi dati?

Utilizzando la clausola RANGE, naturalmente:

SELECT
  shop,
  date,
  revenue_amount,
  LAST_VALUE(revenue_amount) OVER (
    PARTITION BY shop
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value
FROM revenue_per_shop;

La funzione finestra che ho utilizzato questa volta è LAST_VALUE(). Ancora una volta, la uso sulla colonna revenue_amount. Ho partizionato i dati per negozio, come in precedenza. Ho ordinato i dati per data, come in precedenza. Per ottenere l'ultimo valore, ho usato RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Ricordate che l'intervallo predefinito con la clausola ORDER BY è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Se non lo cambiate, otterrete un risultato sbagliato. Il risultato corretto è:

Esempio 4 - Trovare il numero di elementi all'interno di un intervallo

Ecco un altro modo divertente e utile di utilizzare la clausola RANGE. Come trovare il numero di volte in cui il ricavo giornaliero di un negozio è stato compreso tra 1.000 (dollari, euro...) al di sotto e al di sopra del suo valore attuale?

Questo codice potrebbe essere d'aiuto:

SELECT
  shop,
  date,
  revenue_amount,
  COUNT(*) OVER (
    ORDER BY revenue_amount ASC
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) AS number_of_days
FROM revenue_per_shop;

Utilizzo la funzione COUNT() window. Poiché non mi interessa separare le entrate per negozi, non c'è PARTITION BY. Il conteggio verrà eseguito in ordine crescente in base all'importo delle entrate. L'intervallo è definito da RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING.

Ecco cosa restituirà il codice:

Vi spiego cosa vi dice questo risultato. Se prendiamo la prima riga, il risultato nella colonna number_of_days è 2. Ci sono due casi in cui il ricavo è compreso tra 3.987,56 e 5.987,56. Perché questo intervallo? Le entrate per il 2021-05-13 sono 4.987,56. Quindi 4.987,56 - 1.000 = 3.987,56 e 4.987,56 + 1.000 = 5.987,56. Volete verificare il risultato? Quali sono le due istanze comprese in questo intervallo? Ovviamente i primi due:

Vogliamo controllare la seconda riga? Dice che ci sono tre istanze tra 4.894,12 e 6.894,12 - queste tre:

La stessa logica si applica al resto dei dati.

Esempio 5 - Trovare il valore massimo

Questo non è così semplice come può sembrare: non sto parlando del valore massimo comune. Per trovarlo, non sarebbe necessaria la clausola RANGE. Ma che ne dite di trovare il valore massimo (o le entrate, in questo caso) in cinque giorni? Questi cinque giorni includono la data corrente, fino a tre giorni prima e un giorno dopo la data corrente. Probabilmente, dopo tutti questi esempi di utilizzo di RANGE, conoscete già la logica. Ecco la mia soluzione:

SELECT
  shop,
  date,
  revenue_amount,
  MAX(revenue_amount) OVER (
    ORDER BY DATE
    RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
  ) AS max_revenue
FROM revenue_per_shop;

Uso la funzione MAX() come funzione finestra. Ancora una volta, la utilizzo con la colonna revenue_amount. Non c'è PARTITION BY nella clausola OVER() perché non mi interessa separare i dati a nessun livello. Definire l'intervallo non è difficile: RANGE BETWEEN INTERVAL '3' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING. Questo includerà la data corrente, tre giorni prima e un giorno dopo. Ecco il risultato:

Controlliamo il risultato per 2021-05-05 - segnato in rosa. L'intervallo è contrassegnato in giallo. Per ottenere il ricavo massimo in questo intervallo, SQL confronterà i valori: 14,388.14, 18,847.54, 9,845.29, 14,574.56, 11,500.63, 16,897.21, 9,634.56, 14,255.87, 11,248.33, 21,489.22. Qual è il più alto? È 21.489,22.

Dopo aver imparato a trovare il valore massimo utilizzando la clausola RANGE, ho raggiunto il numero massimo di esempi previsti per questo articolo. Se volete altri esempi di funzioni finestra, potete sempre leggere questo articolo.

RANGE ha davvero una vasta gamma di usi, non è vero?

Credo che questi cinque esempi mostrino una buona gamma di possibilità della clausola RANGE. Non è l'unica cosa che si può fare con essa. Il suo utilizzo dipende dai dati a disposizione e probabilmente da un po' di immaginazione. Di questa clausola non si parla molto spesso, ed è un peccato. Consiglio a tutti di impararla. Potrebbe far risparmiare tempo nella creazione di soluzioni alternative. La clausola RANGE è elegante e non è molto complicata.

Dove si può imparare a conoscere RANGE? Nel nostro corso Window Functions , naturalmente. Non imparerete solo la clausola RANGE, ma ogni aspetto delle funzioni della finestra. Oppure potete usare il corso per esercitarvi in SQL. Se volete saperne di più, in questo articolo troverete tutte le informazioni sul corso e su ciò che offre.