Che cos'è la clausola OVER() in SQL?

Le funzioni finestra sono una delle risorse più potenti di SQL, ma non vengono utilizzate di frequente dallo sviluppatore medio di SQL. In questo articolo spiegheremo come è possibile definire diversi tipi di finestre utilizzando la clausola OVER.

La clausola OVER è essenziale per le funzioni finestra di SQL. Come le funzioni di aggregazione, le funzioni finestra eseguono calcoli basati su un insieme di record, ad esempio trovando lo stipendio medio di un gruppo di dipendenti.

In alcuni casi, le funzioni aggregate non possono essere utilizzate perché fanno collassare tutti i singoli record in un gruppo; ciò rende impossibile fare riferimento a valori specifici (come lo stipendio di un dipendente del gruppo). In queste situazioni, le funzioni finestra sono preferibili perché non comprimono le righe; è possibile fare riferimento a un valore di colonna a livello di riga e al valore aggregato.

Ci sono altri scenari in cui le funzioni finestra sono utili. Ad esempio, può essere necessario eseguire un'operazione aritmetica che coinvolga una singola colonna e un calcolo basato su un insieme di righe. Un esempio reale è il calcolo della differenza tra lo stipendio medio del reparto e lo stipendio di ciascun dipendente del reparto.

Quando si utilizzano le funzioni finestra, è fondamentale definire l'insieme di record in cui verrà calcolata la funzione. Questo insieme di record è chiamato cornice della finestra; lo definiamo utilizzando la clausola SQL OVER.

In questo articolo mostreremo le query SQL utilizzando il database di una piccola azienda di orologi di lusso. L'azienda memorizza le informazioni sulle vendite in una tabella chiamata sales:

Il window frame è un insieme di righe che dipende dalla riga corrente; pertanto, l'insieme di righe potrebbe cambiare per ogni riga elaborata dalla query. Per definire le cornici delle finestre si utilizza la clausola OVER. La sintassi è la seguente:

  OVER ([PARTITION BY columns] [ORDER BY columns])

La sottoclausola PARTITION BY definisce i criteri che i record devono soddisfare per far parte della finestra. In altre parole, PARTITION BY definisce i gruppi in cui vengono suddivise le righe; questo sarà più chiaro nel prossimo esempio di query. Infine, la clausola ORDER BY definisce l'ordine dei record nella finestra.

Vediamo la clausola SQL OVER in azione. Ecco una semplice query che restituisce la quantità totale di unità vendute per ogni articolo.

SELECT sale_day, sale_time, 
       branch, article, quantity, revenue,
       SUM(quantity) OVER (PARTITION BY article) AS total_units_sold
FROM   sales

Questa query mostrerà tutti i record della tabella sales con una nuova colonna che mostra il numero totale di unità vendute per l'articolo in questione. Possiamo ottenere la quantità di unità vendute utilizzando la funzione di aggregazione SUM, ma non potremmo mostrare i singoli record.

In questa query, la sottoclausola OVER PARTITION BY article indica che la cornice della finestra è determinata dai valori della colonna article; tutti i record con lo stesso valore article saranno in un gruppo. Di seguito è riportato il risultato di questa query:

La colonna total_units_sold del report è stata ottenuta tramite l'espressione:

SUM(quantity) OVER (PARTITION BY article) total_units_sold

Per i lettori che desiderano approfondire l'argomento, suggerisco i seguenti due articoli: Qual è la differenza tra GROUP BY e PARTITION BY e Window Functions in SQL Server: Prima parte: la clausola OVER()

La clausola OVER di SQL in azione

Per ogni articolo, supponiamo di voler confrontare la quantità totale di questo articolo venduto in ogni mese del 2021 con la quantità totale di questo articolo venduto nell'intero anno. Per farlo, creeremo un semplice report con le colonne article, month, units_sold_month e units_sold_year. La query è:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year
FROM  sales 
WHERE EXTRACT('YEAR' FROM sale_day) = 2021
ORDER BY article, month

I risultati della query sono:

In questo caso, abbiamo calcolato le unità totali vendute utilizzando due diverse granularità di raggruppamento: mese e anno. La prima clausola OVER...

OVER (PARTITION BY article, sale_month) 

... ci permette di ottenere il numero di unità di ogni articolo vendute in un mese. La seconda clausola OVER...

OVER (PARTITION BY article)

... ci permette di calcolare il numero totale di unità di un determinato articolo vendute nell'intero anno.

Nella prossima query, aggiungeremo semplicemente la colonna month_percentage per mostrare la percentuale che uno specifico mese occupa all'interno del totale annuale. Possiamo calcolarla con la seguente query:

SELECT DISTINCT article,
       EXTRACT('month' FROM sale_day) as month,
       SUM(quantity) OVER (PARTITION BY article, sale_month) AS units_sold_month,
       SUM(quantity) OVER (PARTITION BY article) AS units_sold_year,
       ( ( SUM(quantity) OVER (PARTITION BY article, sale_month)::decimal /
           SUM(quantity) OVER (PARTITION BY article)::decimal ) * 100
       ) AS month_percentage
FROM sales 
WHERE extract('YEAR' FROM sale_day) = 2021
ORDER BY article, month

Si noti che nella query precedente non abbiamo utilizzato clausole OVER diverse, ma abbiamo semplicemente riutilizzato le stesse funzioni della finestra e calcolato una percentuale. I risultati sono visibili qui sotto:

Ora creeremo un altro report che analizza l'andamento delle varie filiali. Vogliamo visualizzare le colonne branch e month. Abbiamo anche bisogno di calcoli per ottenere il:

• Entrate totali per quel mese.
• Entrate raggruppate per filiale e mese.
• Entrate medie mensili delle filiali.
• Differenza tra le entrate di ogni filiale e le entrate medie mensili.

SELECT DISTINCT branch,
       EXTRACT('month' FROM sale_day) AS month,
       SUM(revenue) OVER (PARTITION BY sale_month) AS total_revenue_month,
       SUM(revenue) OVER (PARTITION BY branch, sale_month) AS branch_revenue_month,
      
 -- Next column is the branch average revenue in the current month
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS average_month_branch,
 
 -- Next column is the difference between branch revenue and average branch revenue

        SUM(revenue) OVER (PARTITION BY branch, sale_month) -
	 ( 
		  SUM(revenue) OVER (PARTITION BY sale_month)::decimal 
		   / 
		  (SELECT COUNT(DISTINCT branch) FROM sales)::decimal 
	 ) AS gap_branch_average

FROM sales 
WHERE extract('YEAR' from sale_day) = 2021
ORDER BY branch, month

Ancora una volta abbiamo utilizzato solo due clausole OVER, ma abbiamo usato espressioni aritmetiche diverse per ottenere determinati valori. Abbiamo usato ...

SUM(revenue) OVER (PARTITION BY sale_month) 

... per calcolare il fatturato totale del mese, ma lo abbiamo anche utilizzato in un'espressione aritmetica per ottenere il fatturato medio mensile delle filiali.

Abbiamo usato ...

SUM(revenue) OVER (PARTITION BY branch, sale_month) 

... per calcolare le entrate mensili della filiale e la differenza tra le entrate mensili della filiale e la media.

La tabella successiva è il risultato della query. Si noti che la colonna gap_branch_average può contenere numeri positivi o negativi. Un numero negativo indica che il fatturato mensile di questa filiale è stato inferiore al fatturato medio.

Per ulteriori informazioni sulle funzioni finestra in SQL, suggerisco SQL Window Function Example With Explanations, un articolo di base sulle funzioni finestra. Per i lettori più esperti, Come classificare le righe all'interno di una partizione in SQL mostra come creare classifiche nei report utilizzando la funzione finestra RANK().

La clausola OVER e l'analitica Window Functions

Nelle query precedenti abbiamo usato le funzioni finestra per confrontare i numeri mensili (rispettivamente fatturato e unità vendute) con quelli annuali. In questa sezione utilizzeremo le finestre ordinate, che ci permettono di scegliere un record nella finestra in base alla sua posizione. Ad esempio, possiamo scegliere il primo record della finestra, il record precedente a quello attuale o il record successivo a quello attuale. Queste funzioni analitiche della finestra forniscono una grande potenza espressiva all'SQL.

Nella seguente query, mostreremo l'aumento/diminuzione dei ricavi per la stessa filiale in due mesi contigui. A tale scopo, è necessario calcolare la differenza tra le entrate del mese corrente e quelle del mese precedente. A tale scopo, è necessaria la funzione analitica LAG(), che può ottenere il valore di una colonna da una riga precedente a quella corrente.

WITH branch_month_sales AS (
	SELECT    DISTINCT
		    branch,
		    EXTRACT('MONTH' FROM sale_day) AS month,
		    SUM(revenue) OVER (PARTITION BY branch, sale_month ) AS revenue
	FROM sales
)
SELECT branch, 
	month,
	revenue AS revenue_current_month,
	LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_prev_month,
	revenue - LAG(revenue) OVER (PARTITION BY branch ORDER BY month) AS revenue_delta
FROM branch_month_sales
ORDER BY branch, month

In questa query, abbiamo utilizzato un'espressione di tabella comune (CTE) chiamata branch_month_sales per memorizzare le entrate totali di ogni filiale e di ogni mese. Poi abbiamo scritto una seconda query che utilizza la funzione finestra LAG() per ottenere le entrate del mese precedente (utilizzando le informazioni di branch_month_sales). Si noti che la finestra è ordinata per mese.

Ecco i risultati:

In tutte le query presentate in questo articolo, abbiamo utilizzato solo alcune funzioni finestra. Esistono molte altre funzioni finestra in SQL. Ecco un elenco di ciascuna di esse:

Per saperne di più sulla clausola OVER di SQL

In questo articolo abbiamo trattato la clausola OVER e le sottoclausole PARTITION BY e ORDER BY.

Se volete continuare a conoscere le funzioni finestra, ci sono un paio di articoli che vorrei condividere. Il primo è 8 Best SQL Window Function Articles, che vi indirizzerà ad altri ottimi articoli. Il secondo è un foglio informativo sulle funzioni finestra che include sintassi, esempi e immagini; è il mio articolo preferito sulle funzioni finestra.

Per chi vuole approfondire, suggerisco il nostro corso interattivo Window Functions SQL. Se volete migliorare le vostre conoscenze di SQL in generale, provate il corso SQL dalla A alla Z. È una panoramica completa di tutto ciò che dovete sapere per lavorare efficacemente con SQL.