Cos'è la clausola OVER di MySQL?

Se volete imparare le funzioni finestra in MySQL, dovete capire la clausola OVER. In questo articolo, utilizziamo esempi reali per spiegare cos'è la clausola OVER di MySQL, come funziona e perché è così fantastica.

Nel 2018, MySQL ha introdotto una nuova funzionalità: le funzioni finestra, a cui si accede tramite la clausola OVER. Le funzioni finestra sono una risorsa potentissima disponibile in quasi tutti i database SQL. Eseguono un calcolo specifico (ad esempio, somma, conteggio, media, ecc.) su un insieme di righe; questo insieme di righe è chiamato "finestra" ed è definito dalla clausola MySQL OVER.

In questo articolo spiegheremo come utilizzare la clausola OVER di MySQL in diversi scenari. Inoltre, vi presenteremo diverse funzioni di finestra. Spero che, dopo aver letto questo articolo, vi sarete convinti che vale la pena imparare le funzioni di finestra. Conoscerete anche le basi di come applicarle nelle vostre query!

Window Functions: Una funzione molto richiesta

Le funzioni finestra sono disponibili da tempo nella maggior parte dei principali database, ma fino al 2018 non erano disponibili in MySQL. Per mantenere MySQL al passo con i tempi, le funzioni finestra sono state introdotte in MySQL 8.02. Se avete intenzione di lavorare con la versione 8 di MySQL, vale la pena di imparare le funzioni finestra e la clausola OVER, perché sono molto potenti.

Quando si usano le funzioni finestra? In molti casi sono utili, ad esempio per calcolare lo stipendio medio di un certo gruppo di dipendenti. In questo caso, la definizione del gruppo è il punto centrale; non si vuole la media degli stipendi di tutti i dipendenti, e se si sbaglia il gruppo il risultato sarà sbagliato. La definizione di un gruppo di record è il motivo della clausola OVER: detta il punto di lavoro della funzione finestra.

Nel corso di questo articolo, creeremo alcuni esempi di query basate su un database contenente i record di un gruppo di agricoltori che producono arance. Gli agricoltori condividono i loro dati di produzione, che sono memorizzati nella tabella orange_production tabella che vedete qui sotto:

Il caso d'uso più semplice è una clausola OVER vuota; ciò significa che la finestra di record è l'insieme completo dei record restituiti dalla query. Ad esempio, se i nostri agricoltori vogliono avere un report di ogni record di agricoltore insieme al totale della produzione di arance nel 2017, scriveremo questa query:

SELECT farmer_name, 
       kilos_produced, 
       SUM(kilos_produced) OVER() total_produced
 FROM  orange_production 
WHERE  crop_year = 2017

Qui, la clausola OVER costruisce una finestra che include tutti i record restituiti dalla query, ovvero tutti i record dell'anno 2017. Il risultato è:

Come funziona una finestra scorrevole

Questo era un esempio molto semplice della clausola MySQL OVER. La finestra dei record era statica (la finestra era la stessa per tutte le righe restituite dalla query). Tuttavia, uno dei punti di forza della clausola OVER è la possibilità di creare una finestra dinamica di record (detta anche finestra scorrevole). La finestra scorrevole o dinamica significa che la finestra di record può essere diversa per ogni riga restituita dalla query. Inoltre, la finestra viene creata in base alla riga corrente della query, quindi le righe della finestra possono cambiare quando cambia la riga corrente.

Vediamo un esempio di finestra scorrevole. Supponiamo che i nostri agricoltori vogliano vedere la loro produzione e la produzione totale della stessa varietà di arance.

SELECT farmer_name, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety) AS total_same_variety
FROM orange_production 

La clausola OVER(PARTITION BY orange_variety) crea delle finestre raggruppando tutti i record che hanno lo stesso valore nella colonna orange_variety. Si ottengono così due finestre: "Golden" e "SuperSun". Nella tabella sottostante, ogni finestra è mostrata con un colore diverso:

Ora è possibile vedere il risultato della query:

Si noti che la colonna Total Same Variety (all'estrema destra) include la produzione per tutti gli anni. Forse ogni agricoltore preferisce confrontare la propria produzione con la produzione totale della stessa varietà nello stesso anno. Questo confronto permette di vedere la propria quota di produzione. Per farlo, è necessario aggiungere la colonna crop_year alla clausola PARTITION BY. La query sarà la seguente:

SELECT farmer, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety, crop_year)                                                                                                                    AS total_same_variety_year
   FROM orange_production 

La clausola OVER(PARTITION BY orange_variety, crop_year) crea delle finestre raggruppando tutti i record che hanno lo stesso valore nelle colonne orange_variety e crop_year. Di seguito, abbiamo utilizzato colori diversi per mostrare le finestre di righe create da questa clausola OVER:

E i risultati della query sono:

Se siete interessati ad approfondire le funzioni finestra di MySQL, vi suggerisco il corso LearnSQL.it' Window Functions , dove potrete trovare una descrizione completa di questo argomento e diversi esempi. Se si utilizzano spesso le funzioni di finestra, è molto utile questo cheat sheet, una guida rapida supercompleta alle funzioni di finestra.

Ma procediamo prima con l'esplorazione di OVER e delle funzioni finestra in MySQL.

Imparare la clausola OVER di MySQL con un esempio

In questa sezione esploreremo diverse query di esempio che mostrano diversi usi della clausola OVER in MySQL.

Per prima cosa, utilizzeremo la sotto-clausola ORDER BY nella clausola OVER. ORDER BY genererà una finestra con i record ordinati secondo un criterio definito. Alcune funzioni (come SUM(), LAG(), LEAD() e NTH_VALUE()) possono restituire risultati diversi a seconda dell'ordine delle righe all'interno della finestra. Supponiamo che l'agricoltore Pierre voglia conoscere la sua produzione cumulativa nel corso degli anni:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(ORDER BY crop_year) cumulative_previous_years
 FROM  orange_production
WHERE  farmer = ‘Pierre’

La funzione SUM(kilos_produced) OVER(ORDER BY crop_year) lavora su una finestra ordinata. E considera solo le righe attuali e precedenti (cioè i valori attuali e precedenti di crop_year ). Possiamo vedere il risultato di questo SUM() cumulativo nella tabella dei risultati:

Nel prossimo esempio, combineremo due sottoclausole (PARTITION BY e ORDER BY) nella clausola OVER. Supponiamo che gli agricoltori vogliano un report che mostri il totale prodotto da ciascun agricoltore ogni anno e il totale degli anni precedenti. Occorre quindi partizionare per la colonna farmer e ordinare per crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) cumulative_kilos_produced
   FROM orange_production

L'immagine seguente mostra le finestre partizionate per farmer_name in colori diversi; si noti che all'interno di ogni finestra le righe sono ordinate per crop_year.

L'espressione SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) ordina le righe nella partizione (che si basa sul valore farmer ) utilizzando i valori crop_year. Questo è visibile nella tabella dei risultati:

Per ulteriori informazioni sulla combinazione delle clausole PARTITION BY e ORDER BY, consultare l'articolo SQL Window Functions by Explanation.

Uso delle funzioni posizionali su finestre ordinate

È evidente l'importanza di poter ordinare le righe all'interno di una finestra. Infatti, alcune funzioni di MySQL per le finestre funzionano solo su finestre ordinate. Ad esempio, la funzione LAG() consente di ottenere il valore di una colonna dalla riga precedente (relativa alla riga corrente) in una finestra ordinata.

Supponiamo di voler creare un report che mostri la produzione dell'anno precedente rispetto a quella dell'anno in corso. A tale scopo, si utilizza la funzione LAG() su una finestra ordinata da crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced AS current_year_production, 
       LAG(kilos_produced) OVER(PARTITION BY farmer 
ORDER BY crop_year)AS previous_year_production
  FROM orange_production

La funzione LAG() può restituire qualsiasi valore di colonna dalla riga precedente a quella corrente, come mostra il risultato della seguente query:

La funzione LEAD() restituisce qualsiasi valore di colonna della riga successiva a quella corrente. Inoltre, la funzione NTH_VALUE() restituisce la riga in qualsiasi posizione specificata in una finestra ordinata (ad esempio, prima, seconda, ultima). Queste funzioni di MySQL window aiutano a creare facilmente report complessi.

Proviamo a eseguire un'operazione matematica utilizzando le funzioni di finestra di MySQL. Supponiamo che gli agricoltori vogliano un rapporto con il delta di produzione relativo all'anno precedente. Possiamo utilizzare la funzione LAG() per calcolare la differenza tra la produzione dell'anno precedente e quella dell'anno in corso:

SELECT farmer, 
       crop_year,
       kilos_produced current_year_production, 
       kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer 
                         ORDER BY crop_year) AS production_delta
   FROM orange_production

L'espressione kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) calcola il delta di produzione tra l'anno precedente e quello corrente, come si può vedere nei risultati:

Per i lettori che desiderano approfondire il tema delle funzioni posizionali, suggerisco l'articolo Common SQL Window Functions: Positional Functions, che contiene diversi esempi di questo interessante tipo di funzione finestra.

Ulteriori informazioni su MySQL OVER e Window Functions

In questo articolo abbiamo trattato diversi esempi di utilizzo della clausola MySQL OVER, da quelli più semplici a quelli più complessi. Sapere come funziona OVER e quali righe include nella finestra è fondamentale per utilizzare le funzioni finestra.

Esistono molte funzioni finestra di MySQL che si possono sperimentare: AVG(), MIN(), MAX(), LAG(), LEAD() e NTH_VALUE(). Tutte utilizzano la clausola OVER nello stesso modo in cui abbiamo appena spiegato.

Infine, per i lettori che vogliono saperne di più sulle funzioni finestra di MySQL, consiglio il corso interattivo LearnSQL Window Functions. Per saperne di più, consultate il nostro post Corso SQL del mese - Window Functions. Nel corso troverete spiegazioni dettagliate e molti esempi di utilizzo di diverse funzioni finestra. E ricordate: quando aumentate le vostre competenze in MySQL, aumentate le vostre risorse!