Imparare la clausola OVER() in SQL con 7 esempi

Imparate a utilizzare la clausola OVER() nelle vostre query SQL e farete un passo avanti nella vostra conoscenza dell'SQL.

La maggior parte degli analisti di dati e degli sviluppatori SQL conosce bene le clausole SELECT, FROM, WHERE, GROUP BY e ORDER BY. Tuttavia, queste clausole sono solo un sottoinsieme limitato del linguaggio SQL. Esistono altre clausole, come OVER(), che ci permettono di creare report SQL flessibili e aggiungono un'enorme potenza espressiva al linguaggio SQL.

In questo articolo spiegheremo la clausola OVER() mostrando alcuni esempi del suo utilizzo. Iniziamo!

La clausola OVER() in SQL

L'SQL ha una curva di apprendimento abbastanza facile; chiunque può imparare a creare semplici query in SQL. Infatti, il professionista informatico medio o lo sviluppatore SQL gestisce con facilità le clausole di base SELECT, FROM, WHERE, GROUP BY e ORDER BY. Queste clausole erano disponibili in SQL-92, che ha 30 anni! Tuttavia, il linguaggio SQL non si ferma qui: dal 1992 sono stati aggiunti molti nuovi elementi. Uno di questi è la clausola OVER(), che ci permette di utilizzare le funzioni finestra nelle query SQL.

In SQL, le funzioni finestra sono simili a GROUP BY in quanto operano su un gruppo di righe. Tuttavia, le funzioni finestra si basano su una finestra di dati, ovvero un insieme di righe correlate alla riga corrente. A differenza di GROUP BY, le funzioni finestra non fanno collassare le righe, ma mantengono intatti i dettagli delle singole righe.

Per coloro che desiderano approfondire OVER() e le funzioni finestra in SQL, suggerisco il nostro corso onlineWindow Functions (Funzioni Finestra) . Contiene numerosi esempi di utilizzo di diverse funzioni finestra.

Ok, ora torniamo alla funzione OVER(). Per prima cosa, diamo un'occhiata ai nostri dati.

Introduzione ai dati

Le nostre query di esempio si baseranno sui seguenti dati.

Supponiamo di avere un ristorante di pesce sulla costa mediterranea. Abbiamo anche una tabella di database in cui memorizziamo lo storico delle attività del ristorante. Il nostro database ha una tabella chiamata restaurant_activity con queste colonne:

• table_number
• waiter_name
• start_date
• start_time
• served_time
• end_time
• total_diners
• amount_payment
• total_tips.

Di seguito è riportato un esempio di dati:

Restaurant_activity

Tutto ciò dovrebbe essere evidente, ma esaminiamo rapidamente alcune colonne. start_date e start_time sono la data e l'ora in cui i clienti hanno ordinato un pasto a table_number; served_time è il momento in cui il pasto è stato servito e end_time è il momento in cui i clienti hanno chiesto il conto. amount_payment è il costo del pasto, esclusa la mancia (che viene memorizzata come total_tips).

Query di esempio #1: un semplice uso di OVER()

Cominciamo a usare la clausola OVER() per calcolare la percentuale di ogni ordine sul totale delle entrate giornaliere su 5/5/2022. Possiamo calcolare l'incasso totale del ristorante in questo giorno (escluse le mance) usando OVER() combinato con la funzione finestra SUM(). La clausola OVER() è sempre associata a una funzione finestra; le funzioni finestra calcolano un valore sulla base di un insieme di record definiti dalla clausola OVER(). La query è:

SELECT 
  start_date AS date,
  SUM(amount_payment) OVER () AS daily_revenue,
  amount_payment AS total_order,
  (amount_payment / SUM(amount_payment) OVER ())*100 AS order_percentage
FROM   restaurant_activity
WHERE  start_date = ’5/5/2022’

La query precedente calcola il valore daily_revenue per 5/5/2022 sommando ogni importo di pagamento per questo giorno. La clausola OVER() è vuota, il che significa che la finestra di record utilizzata per calcolare la funzione SUM() è l'insieme completo dei record restituiti dalla query. In altre parole, la finestra è composta da tutti i record per questa data.

Nel calcolo della colonna order_percentage, dividiamo l'importo del singolo ordine per il ricavo totale giornaliero per ottenere la percentuale; questo è un punto centrale della query, perché stiamo combinando le colonne a livello di riga con i risultati della funzione finestra in un'unica espressione.

Capire quali record fanno parte della finestra è fondamentale per comprendere il funzionamento delle funzioni finestra; torneremo su questo punto più avanti nell'articolo. Per ora guardiamo i risultati:

Prima di addentrarci in esempi più complessi di OVER(), vorrei suggerire il nostro set di eserciziWindow Functions (Funzioni Finestra) . Se volete imparare davvero a usare OVER(), questi 100 esercizi interattivi faranno al caso vostro.

Query di esempio n. 2: Uso della sottoclausola PARTITION BY

In questo esempio, utilizzeremo la sottoclausola PARTITION BY, che lavora con OVER() per definire le finestre di dati.

Supponiamo di volere un report simile a quello precedente, ma esteso a qualsiasi giorno del maggio 2022. Per ogni giorno, vogliamo vedere il ricavo totale giornaliero, l'importo del singolo ordine per tutti gli ordini e la percentuale del ricavo giornaliero attribuita a ciascun ordine. Ecco la query:

SELECT start_date AS date,
  		SUM(amount_payment) OVER (PARTITION BY start_date) AS daily_revenue,
  		amount_payment AS total_order,
  		(amount_payment / SUM(amount_payment) OVER (PARTITION BY start_date)) * 100 AS
    order_percentage
FROM restaurant_activity
WHERE start_date BETWEEN ’5/1/2022’ AND ’5/31/2022’ 

Abbiamo detto che le funzioni finestra funzionano in base a una finestra di record (o a un insieme di record) relativi al record corrente. La query precedente utilizza la sottoclausola PARTITION BY start_date per definire quali record appartengono a ciascuna finestra. Nel nostro esempio, tutti i record con lo stesso valore start_date della riga corrente faranno parte della finestra. Poiché abbiamo solo due valori unici per start_date, abbiamo solo due finestre diverse. I risultati sono visualizzati in verde e in rosso:

Esempio di query #3: Uso della sottoclausola ORDER BY

Introduciamo ora la sottoclausola ORDER BY, che consente di inserire le righe in un ordine specifico all'interno della finestra.

Supponiamo di voler creare un semplice report con le cinque ordinazioni più costose insieme al cameriere, alla data e al numero del tavolo. La query è:

WITH ranking AS (
	SELECT 
table_number, 
amount_payment, 
waiter_name, 
start_date, 
	       RANK() OVER (ORDER BY amount_payment DESC) AS position
	FROM restaurant_activity
)
SELECT 
  amount_payment, 
  waiter_name, 
  start_date, 
  table_number, 
  position
FROM ranking
WHERE position <= 5
ORDER BY position

In questo caso, abbiamo usato una CTE (o Common Table Expression) per costruire la classifica. Nella CTE, abbiamo calcolato la posizione di ogni ordine utilizzando la funzione finestra RANK() combinata con la seguente clausola OVER():

RANK() OVER (ORDER BY amount_payment DESC)

La clausola precedente definisce una finestra di record formata da tutti i record della tabella, quindi tutti gli ordini del ristorante sono inclusi. Questa finestra viene ordinata da amount_payment in ordine decrescente: l'importo maggiore è il primo record della finestra e così via. La funzione RANK() restituisce la posizione della riga corrente nella finestra ordinata, memorizzando questo valore nella colonna position della CTE.

Dopo aver creato la CTE di classificazione, il resto della query utilizza ranking come qualsiasi altra tabella. Filtriamo i record per ottenere solo le prime 5 posizioni, quindi ordiniamo i risultati in base a position. Ecco cosa otteniamo:

A questo punto, vorrei suggerire l'articolo SQL Window Functions (Funzioni Finestra) vs. GROUP BY: Qual è la differenza? Spiega le differenze tra la clausola GROUP BY e le funzioni finestra attraverso diversi esempi di query SQL. Un altro articolo interessante con ulteriori dettagli è Cos'è la clausola OVER() in SQL.

Query di esempio #4: Calcolo del tempo libero della tabella con OVER() e LAG()

Le funzioni di Window includono molti modi per semplificare calcoli complessi. Una di queste è la funzione LAG(), che restituisce un valore da qualsiasi colonna della riga precedente relativa alla riga corrente della finestra.

Il proprietario di un ristorante vuole sapere quanto tempo i tavoli sono liberi, ad esempio il tempo che intercorre tra un cliente e l'altro. Per farlo, si può creare un report con le colonne table_number, date, free_start, free_end e free_time_duration.

Per calcolare il tempo libero, dobbiamo accedere a due righe. Abbiamo bisogno di end_time dell'occupazione precedente e di start_time dell'occupazione successiva; poi possiamo calcolare il tempo trascorso tra le due. Qui entra in gioco la funzione LAG(), perché LAG() consente di accedere a qualsiasi colonna del record precedente. Ecco la query da utilizzare:

    SELECT 
start_date AS date,
table_number,
-- ending time of the previous occupation 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS start_free_time,
-- starting time of current occupation
start_time AS end_free_time,
-- calculating the free time when the table was unoccupied
start_time - 
COALESCE(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00') AS free_time_duration
    FROM   restaurant_activity

La chiave della query precedente è la funzione LAG() window. L'abbiamo usata per ottenere la end_time dell'occupazione precedente. La clausola ...

OVER (PARTITION BY start_date, table_number ORDER BY start_time)

... definisce una finestra (o insieme di righe) per ogni coppia distinta di <start_date, table_number>, e ciascuna di queste finestre è ordinata per start_time. Quindi LAG(end_time) restituisce l'ora di fine della precedente occupazione della tabella.

Avrete notato che abbiamo usato LAG() due volte. La prima è usata per ottenere l'ora di inizio del periodo libero e la seconda per calcolare la durata del tempo libero usando la seguente espressione:

start_time - 
coalesce(LAG(end_time) OVER (PARTITION BY start_date, table_number 
                             ORDER BY start_time),'11:00')

I risultati della query sono:

Prima di passare alla sezione successiva, vi suggerisco i seguenti articoli per maggiori dettagli:

• Esempio di funzione SQL Window con spiegazioni
• Come classificare le righe in SQL: Guida completa
• La funzione LAG e la funzione LEAD in SQL
• Come calcolare la lunghezza di una serie con SQL

Query di esempio #5: Calcolo delle classifiche con la clausola OVER()

In questa sezione, tratteremo una situazione aziendale in cui la clausola SQL OVER() può essere applicata per creare una classifica, come quella dei 10 venditori migliori o dei 5 prodotti più venduti. È possibile utilizzare la clausola OVER() combinata con la funzione RANK() per ottenere questo tipo di report. Vediamo un esempio di query che restituisce le due maggiori mance del giorno e il cameriere che le ha ricevute:

SELECT  *
FROM (
  		SELECT 	waiter_name,
  			start_date AS date,
			total_tips AS tip_amount,
			RANK() OVER(PARTITION BY start_date ORDER BY total_tips DESC) AS ranking
  	  	FROM restaurant_activity
     	     ) AS ranking_table
WHERE ranking <= 2;

Abbiamo usato una sottoquery nella clausola FROM per creare una "tabella" temporanea chiamata ranking_table. Utilizza la colonna ranking per memorizzare la posizione della mancia nella classifica giornaliera delle mance. La posizione nella classifica viene calcolata con la funzione RANK(). Le altre colonne della tabella temporanea sono waiter_name, date e tip_amount.

Nella query esterna si filtrano solo i suggerimenti classificati 1 e 2. Il risultato della query è mostrato di seguito:

Query di esempio n. 6: calcolo delle differenze rispetto a un periodo precedente

Un'altra possibilità interessante è quella di mostrare la differenza di valore tra il periodo precedente e quello attuale. La clausola OVER() combinata con le funzioni finestra come LEAD() e LAG() sono utilizzate per creare questo tipo di report. Per ulteriori informazioni, vedere Come calcolare la differenza tra due righe in SQL.

Supponiamo di voler vedere le entrate giornaliere di ogni cameriere insieme alla differenza rispetto al giorno precedente. Nella stessa riga, vogliamo anche vedere la differenza espressa in percentuale. Ecco la query:

SELECT	
  waiter_name,
  date,
  today_revenue,
  -- revenue variation ----------------------------------------------
  LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) yesterday_revenue,
  today_revenue - LAG(today_revenue) OVER (
    PARTITION BY waiter_name ORDER BY date) AS revenue_variation,
  -- -----------------------------------------------------------------
  -- revenue variation percentage ------------------------------------------------
  round((today_revenue - 
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date)) /
   LAG(today_revenue) OVER (PARTITION BY waiter_name ORDER BY date),2) * 100 
AS revenue_variation_percentage
  -- -----------------------------------------------------------------------------		
FROM (
  SELECT  DISTINCT 
    start_date::date AS "date",
    waiter_name,
    SUM(total_tips) OVER ( 
      PARTITION BY waiter_name, start_date::date ORDER BY start_date)
AS today_revenue
  FROM 	restaurant_activity
) AS daily_revenue_per_waiter;

Abbiamo creato una sottoquery nella clausola FROM chiamata daily_revenue_per_waiter che contiene i dati waiter_name, date e il ricavo totale realizzato da questo cameriere in questo giorno. Nella query esterna, utilizziamo la funzione finestra LAG() per ottenere le entrate del giorno precedente, quindi otteniamo la variazione delle entrate tra ieri e oggi e la variazione percentuale. Il risultato è:

Esempio di query #7: media mobile

Le medie mobili sono una metrica spesso utilizzata per attenuare le fluttuazioni a breve termine. Per saperne di più, consultare l'articolo Cos'è una media mobile e come calcolarla in SQL; vediamo come costruirne una utilizzando la clausola OVER().

A titolo di esempio, supponiamo che il proprietario di un ristorante voglia conoscere l'importo medio pagato a persona sull'ultimo tavolo servito in un determinato momento. Utilizza questa media come metrica per sapere quanto pagano i clienti e per attivare alcune promozioni o sconti. Possiamo calcolare facilmente questa media con l'espressione amount_payment/total_diners; tuttavia, il proprietario si è reso conto che questa metrica ha forti fluttuazioni, quindi ha deciso di utilizzare l'importo medio pagato a persona negli ultimi 3 e 6 tavoli serviti. La query per calcolare questo report è la seguente:

SELECT start_date AS "date",
start_time AS "time",
table_number,
amount_payment AS total_amount,
total_diners,
       	ROUND(amount_payment/total_diners,2) AS diner_avg,
ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_3_tables_served,
	ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 
     AS diner_moving_avg_last_6_tables_served
FROM   restaurant_activity

La query precedente calcola 3 diverse medie. La prima è una media semplice basata sull'espressione:

ROUND(amount_payment/total_diners,2)

La seconda media è la media mobile per gli ultimi 3 tavoli serviti; la terza media è la stessa, ma per gli ultimi 6 tavoli serviti:

ROUND(AVG(amount_payment/total_diners) 
     OVER (ORDER BY start_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2)
…
OVER (ORDER BY start_date, start_time ROWS BETWEEN 5 PRECEDING AND CURRENT ROW),2) 

Il termine "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" specifica che la media è calcolata sulla base di 3 righe: la riga corrente e le 2 righe immediatamente precedenti. La finestra è ordinata in base all'ora di inizio del tavolo. Ecco il risultato:

Altri usi della clausola OVER() includono il calcolo dei totali progressivi (utile in tutti i tipi di analisi finanziaria) e il calcolo della lunghezza di una serie di dati.

Pronti a fare pratica con la clausola SQL OVER()?

Abbiamo mostrato diverse query con la clausola SQL OVER(). Poiché OVER() deve essere utilizzata in combinazione con una funzione finestra, abbiamo trattato anche alcune di queste: SUM(), AVG(), LAG() e RANK().

Se volete mettere in pratica le vostre nuove conoscenze sulla clausola SQL OVER(), vi consiglio il nostro corso interattivo Window Functions (Funzioni Finestra) seguito dal nostro set di esercitazioniWindow Functions (Funzioni Finestra) . Potete leggere il corso nell'articolo Corso SQL del mese - Window Functions (Funzioni Finestra). Potete anche ottenere una copia del nostro foglio informativo gratuito su SQL Window Functions (Funzioni Finestra) per aiutarvi nel vostro percorso di apprendimento. Sviluppate le vostre competenze e aumentate il vostro patrimonio!