Come definire un riquadro di finestra in SQL Funzioni di finestra

La funzione finestra è una risorsa potentissima del linguaggio SQL. Al centro di ogni funzione finestra c'è un insieme di record, chiamato cornice della finestra, definito con la clausola OVER. Sapere quali record sono presenti nella finestra, come sono ordinati e quali sono i loro limiti superiori e inferiori è fondamentale per capire come funzionano le funzioni finestra. In questo articolo analizzeremo e spiegheremo con esempi come si possono definire diversi tipi di window frame. Continuate a leggere per compiere un passo importante nella crescita delle vostre competenze in SQL!

Uso di PARTITION BY per definire una cornice a finestra

Le funzioni SQL a finestra eseguono calcoli basati su un insieme di record. Ad esempio, si potrebbe voler calcolare lo stipendio medio di un gruppo specifico di record di dipendenti. Questo gruppo di record è chiamato window frame e la sua definizione è fondamentale per capire come funzionano le funzioni window e come sfruttarle.

La cornice della finestra è un insieme di righe relative alla riga corrente in cui viene utilizzata la funzione finestra per il calcolo. Il riquadro della finestra può essere un insieme di righe diverso per la riga successiva nel risultato della query, poiché dipende dalla riga corrente che viene elaborata. Ogni riga dell'insieme dei risultati della query ha una propria finestra.

Nel resto dell'articolo, mostreremo degli esempi di query basate su un database di un gruppo di concessionari di auto. Il gruppo memorizza le informazioni sulle vendite raggruppate per mese in una tabella chiamata monthly_car_sales. Di seguito è riportata la tabella con alcuni dati di esempio:

monthly_car_sales

Un modo semplice per creare una cornice è utilizzare una clausola OVER con una sottoclausola PARTITION BY. Nell'esempio SQL che segue, generiamo un report dei ricavi per marca di autovettura per l'anno 2021.

SELECT make,
       SUM(revenue) OVER (PARTITION BY make) AS total_revenue
FROM   monthly_car_sales
WHERE  year = 2021

Di seguito, le finestre generate dalla query precedente sono mostrate con colori diversi (rosso per Ford e blu per Renault). Tutti i record con lo stesso valore nella colonna make (le righe codificate con i colori) appartengono alla stessa finestra. Poiché abbiamo solo due valori diversi nella colonna make, abbiamo due finestre.

Il risultato della query è:

Vorrei suggerirvi due articoli in cui potete trovare molte informazioni introduttive sulle funzioni finestra di SQL: "Corso SQL del mese - Window Functions " e "Quando si usa SQL Window Functions?".

Ordinare le righe in una finestra con ORDER BY

Oltre a PARTITION BY, è possibile utilizzare una sottoclausola ORDER BY per ordinare le righe all'interno di una finestra. Ordinare il riquadro della finestra in base ad alcuni criteri consente di utilizzare funzioni analitiche della finestra come LEAD(), LAG(), e FIRST_VALUE(), tra le altre.

Ad esempio, se si vuole ottenere la differenza di fatturato tra mesi consecutivi, si può ordinare la finestra per mese. Quindi, data una qualsiasi riga corrente, la funzione finestra LAG() può restituire qualsiasi colonna del mese precedente. Vediamo un esempio per ottenere la differenza di fatturato tra ogni coppia di mesi consecutivi.

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER ( ORDER BY month) AS previous_month_revenue,
	 revenue - LAG (revenue) OVER (ORDER BY month) AS delta_revenue
FROM monthly_car_sales
WHERE year = 2021
  AND model = 'Mustang'

Il risultato della query precedente è:

La prima riga del risultato presenta i valori NULL nelle colonne previous_month_revenue e delta_revenue. Questo perché non esiste un mese precedente a gennaio. La colonna delta_reddito è calcolata tra il reddito del mese corrente e il reddito del mese precedente, quest'ultimo ottenuto con la funzione finestra LAG().

Quando si usa ORDER BY in una clausola OVER, c'è un nuovo elemento da considerare: i limiti della finestra. Se non si specifica alcuna clausola ORDER BY, l'intera partizione diventa la cornice della finestra. Tuttavia, quando si utilizza una sottoclausola ORDER BY, la riga corrente diventa il limite superiore della cornice della finestra. In altre parole, le righe successive alla riga corrente (in base ai criteri di ORDER BY ) non sono incluse nella cornice della finestra. Nella prossima sezione verrà illustrato in dettaglio il concetto di limiti della cornice della finestra.

Definizione dei limiti della cornice della finestra con ROWS

Una caratteristica molto interessante della clausola OVER è la possibilità di specificare i limiti superiori e inferiori di un riquadro di finestra. Questi limiti possono essere specificati utilizzando una delle due sottoclausole della clausola OVER: ROWS o RANGE. In questa sezione verrà spiegato come utilizzare la sottoclausola ROWS della clausola OVER.

Il riquadro della finestra è un insieme di righe in qualche modo correlate alla riga corrente. I loro limiti possono essere definiti per ogni riga del risultato della query con una sottoclausola ROWS, che ha la seguente sintassi:

ROWS BETWEEN lower_bound AND upper_bound

Come abbiamo già detto nella sezione precedente, è importante sapere quali sono i limiti predefiniti della cornice della finestra. Quando si specifica una sottoclausola ORDER BY, la riga corrente è il limite superiore della cornice della finestra per impostazione predefinita. Tuttavia, in alcuni casi, è necessario modificare questo limite superiore (o inferiore), come vedremo di seguito.

Vediamo un esempio in cui è necessario specificare i limiti di una cornice di finestra. Supponiamo di voler ottenere un report con il totale delle vendite del mese corrente, il totale delle vendite del mese precedente e il massimo delle vendite in ogni singolo mese dell'anno, per marca e modello. La query per ottenere tale rapporto è la seguente:

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER (PARTITION BY make, model ORDER BY month) AS    
                                                   prev_month,
	 MAX(revenue) OVER (PARTITION BY make, model ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_year_revenue
FROM monthly_car_sales
WHERE year = 2021

Se avessimo omesso la sottoclausola ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING nella funzione MAX() window della query precedente, avremmo ottenuto il massimo tra il primo mese e il mese corrente. Questo è sbagliato, perché vogliamo il massimo delle entrate mensili considerando l'intero anno (compresi i mesi successivi a quello corrente). Dobbiamo quindi includere nella tabella tutti i mesi disponibili. Per fare ciò, aggiungiamo la sottoclausola ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING alla clausola OVER.

L'immagine successiva mostra tutti i possibili limiti che si possono specificare per definire i limiti inferiori e superiori di una finestra:

Le opzioni per i limiti inferiori e superiori nella clausola OVER sono:

• UNBOUNDED PRECEDING
• N PRECEDING
• CURRENT ROW
• N FOLLOWING
• UNBOUNDED FOLLOWING

Dobbiamo assicurarci che il limite inferiore sia inferiore a quello superiore.

Infine, vi suggerisco l'articolo Perché imparare SQL Window Functions nel 2021? dove potrete imparare a utilizzare le funzioni finestra.

Definizione dei limiti della finestra con RANGE

Nella sezione precedente abbiamo definito i limiti di una finestra in termini di ROWS. In questa sezione spiegheremo come utilizzare la sottoclausola RANGE per specificare i limiti di una finestra in intervalli di righe. La sintassi della sottoclausola RANGE è la seguente:

RANGE BETWEEN lower_bound AND upper_bound

Un intervallo è un insieme di righe con lo stesso valore per il criterio PARTITION BY. Ad esempio, se abbiamo un mese PARTITION BY, possiamo vedere la differenza nell'immagine successiva quando usiamo ROWS o RANGE per definire una cornice di finestra:

OVER ( PARTITION BY …... 

             ORDER BY month 

             ROWS BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

OVER ( PARTITION BY ….. 

             ORDER BY month 

             RANGE BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

Se si desidera un report delle entrate per marca per il mese corrente e per ciascuno degli ultimi tre mesi, è possibile utilizzare la seguente query:

SELECT make,
       model,
       month,
	 revenue AS model_revenue_current_month,
       SUM(revenue) OVER ( PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 0 PRECEDING AND CURRENT ROW
                         ) AS make_current_month,
	 SUM(revenue) OVER (PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                        ) AS make_last_2_months,
	   SUM(revenue) OVER (PARTITION BY make
                            ORDER BY month
                            RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                        ) AS make_last_3_months
FROM monthly_car_sales
WHERE year = 2021
ORDER BY 1,3,2

La query precedente utilizza la sottoclausola RANGE per specificare una finestra con tutti i record della marca corrente per un intervallo di N mesi. Ad esempio:

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                  ) AS make_last_2_months

La precedente sottoclausola RANGE BETWEEN 1 PRECEDING AND CURRENT ROW specifica una finestra che include il mese precedente e il mese corrente. Quindi, la funzione SUM() restituirà il fatturato totale degli ultimi due mesi.

Analogamente, si può utilizzare la seguente clausola OVER per ottenere le entrate totali degli ultimi tre mesi.

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                  ) AS make_last_3_months

Sono disponibili diverse abbreviazioni per semplificare la sintassi di queste clausole di delimitazione:

Infine, ma non per questo meno importante, ecco il mio cheat sheet preferito sulle funzioni finestra con molti dettagli sulla sintassi: Foglio informativo sulle funzioni finestra di SQL.

Approfittate di Window Functions!

Un'idea centrale della funzione finestra è la cornice della finestra, cioè il gruppo di record su cui lavora la funzione finestra. In questo articolo abbiamo spiegato che la cornice della finestra dipende dalla riga corrente ed è definita dalla clausola OVER. Abbiamo anche mostrato diversi esempi per definire quali record includere nella cornice della finestra, ordinare le righe al suo interno e definirne i limiti.

Per chi volesse approfondire, suggerisco il corso online Window Functions SQL, con numerosi esempi di utilizzo di diverse funzioni finestra. Sviluppate le vostre capacità e aumentate il vostro patrimonio!