Panoramica delle funzioni finestra di MySQL

Le funzioni finestra di MySQL sono molto utili quando si desidera creare rapporti significativi con SQL. In questo articolo illustreremo le funzioni finestra più comuni in MySQL e spiegheremo come utilizzarle.

MySQL 8.0 ha introdotto una nuova caratteristica: le funzioni finestra. Queste funzioni sono molto utili per gli analisti di dati e per tutti coloro che creano report con MySQL. Grazie ad esse è possibile calcolare facilmente medie mobili, somme cumulative e altri calcoli su sottoinsiemi specifici di dati. Il tutto senza creare complesse query SQL o tabelle temporanee.

Chi utilizza abitualmente SQL potrebbe essersi imbattuto nelle funzioni finestra nel proprio lavoro. Mentre molti database dispongono di queste funzioni da tempo, MySQL è rimasto indietro fino al 2018. Con MySQL 8, la piattaforma è ora al passo con i tempi!

In questo articolo esploreremo i vantaggi delle funzioni finestra in MySQL e come possono migliorare l'analisi dei dati. Se volete fare pratica con le funzioni a finestra, date un'occhiata al nostro corso interattivo Window Functions in MySQL 8. Offre oltre 200 esercizi interattivi sulle funzioni finestra di MySQL.

Cosa sono le Window Functions in MySQL?

Una funzione finestra SQL esegue calcoli su un insieme di righe della tabella che sono correlate alla riga corrente. Questo insieme di righe è chiamato finestra o cornice di finestra, da cui deriva il termine "funzioni finestra".

Cominciamo con un semplice esempio. Immaginiamo di voler calcolare la somma dei valori di tutte le righe, ma di voler visualizzare il risultato su ogni riga. Questa informazione potrebbe servire per confrontare i singoli valori con il totale durante l'analisi dei dati.

Questo è un gioco da ragazzi se si sanno usare le funzioni delle finestre! Il risultato della vostra query sarà simile a questo:

Avrete notato che le funzioni finestra sono simili alle funzioni aggregate. Entrambe calcolano un valore aggregato per un certo gruppo di righe. Tuttavia, a differenza della clausola GROUP BY, le funzioni finestra in SQL non comprimono le righe. Al contrario, la tabella risultante mostra sia i valori individuali che quelli aggregati. Questo può essere utile nei report in cui è necessario lavorare con i valori aggregati e non aggregati allo stesso tempo.

Come definire una funzione finestra: La clausola OVER()

Le funzioni finestra si definiscono con la clausola OVER():

SELECT …,
   <window_function> OVER(...),
   …
FROM …

La clausola OVER() indica al database di utilizzare una funzione finestra. La forma più semplice della finestra è quando le parentesi sono lasciate vuote, come in questo caso: OVER(). Ciò significa che la finestra è costituita da tutte le righe della tabella.

È possibile includere altre clausole all'interno della clausola OVER() per definire ulteriormente la finestra. In questo articolo, ci concentreremo sulle clausole PARTITION BY e ORDER BY. Esistono altre clausole che possono essere utilizzate all'interno di OVER(), ma non saranno trattate in questo articolo. Se volete approfondire l'argomento, date un'occhiata al nostro corso Window Functions in MySQL 8. In alternativa, potete visitare questo articolo sulle funzioni finestra di MySQL , che fornisce alcuni ottimi esempi di come utilizzarle nelle vostre query.

Esempi di query con funzioni finestra in MySQL

Esaminiamo alcuni esempi di query per capire meglio dove e come è possibile utilizzare le funzioni finestra.

Nel nostro scenario di esempio, abbiamo un sito web che permette agli utenti di partecipare a dei quiz. Ci sono varie categorie di quiz e il numero massimo di punti che i partecipanti possono ottenere è 100. Per memorizzare i punteggi dei partecipanti, si utilizza una query di questo tipo.

Per memorizzare i punteggi dei partecipanti, questo sito web utilizza la tabella participant tabella. Essa ha le seguenti colonne:

• id - L'ID del partecipante, che è anche la chiave primaria (PK) della tabella.
• name - Il nome del partecipante.
• quiz_score - Il punteggio del partecipante.
• quiz_date - La data in cui è stato tentato il quiz.
• quiz_category - La categoria del quiz.

Qui si può vedere una manciata di righe della tabella:

Ora che conoscete i dati, passate all'uso delle funzioni della finestra! Questo foglio di istruzioni di SQL Window Functions può essere utile come guida di riferimento rapida durante la lettura degli esempi.

Esempio 1: Clausola OVER() vuota - Calcolo di una statistica per tutte le righe

Supponiamo di voler restituire il punteggio di ogni partecipante, la categoria del quiz che ha tentato e il punteggio più alto mai raggiunto in tutti i quiz. Possiamo farlo utilizzando una clausola vuota OVER(). In questo modo, la finestra includerà tutte le righe della query.

Ecco la query da eseguire:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER() AS max_score
FROM participant;

Il risultato sarà il seguente:

La funzione MAX() è stata applicata a tutte le righe della query. Si può notare che il punteggio più alto è 100 e viene visualizzato per tutte le righe insieme ai singoli punteggi.

È possibile utilizzare la clausola vuota OVER() con altre funzioni, come COUNT(), SUM(), AVG() e altre. Ciò consente di calcolare una statistica complessiva per tutte le righe della query; è possibile confrontare questa statistica complessiva con il valore di ogni singola riga.

Per ulteriori informazioni sull'uso della clausola OVER() in MySQL, consultare l'articolo Cos'è la clausola OVER di MySQL?

Esempio 2: OVER() con ORDER BY - Calcolo di una classifica

Quando viene utilizzata all'interno della clausola OVER(), ORDER BY determina l'ordine delle righe nel riquadro della finestra. Vediamo un esempio:

Possiamo utilizzare questa query per creare una classifica dei punteggi dei quiz:

SELECT 
  name, 
  quiz_score,
  quiz_category,
  RANK() OVER(ORDER BY quiz_score DESC) AS rank
FROM participant;

La funzione finestra RANK() assegna un rango a ogni riga all'interno di una partizione; questo rango si basa sul valore di un'espressione specificata. La prima riga ottiene il rango 1, la seconda il rango 2, ecc.

Più precisamente, la funzione RANK() assegna un rango unico a ogni valore distinto dell'espressione all'interno della partizione. Le righe con lo stesso valore avranno lo stesso rango e il rango successivo verrà saltato. Ad esempio, se due righe hanno lo stesso valore e ricevono un rango pari a 1, il rango successivo assegnato sarà 3, saltando il rango 2. Per ulteriori informazioni sulle funzioni di ranking window in SQL, consultare il nostro blog.

In questo caso, utilizziamo la funzione RANK() per calcolare la classifica del punteggio di ogni partecipante al quiz. La clausola OVER() con la clausola ORDER BY determina l'ordine di applicazione della funzione RANK(). In questo caso, la clausola ORDER BY è impostata su quiz_score DESC, il che significa che i punteggi dei quiz sono ordinati in ordine decrescente (dal più alto al più basso) prima di calcolare la classifica. La prima riga (con il valore più alto) ottiene il rango 1, la seconda riga il rango 2 e così via.

Ecco cosa restituisce il codice:

Ben fatto! Siamo riusciti ad assegnare a ciascun partecipante un rango.

Utilizzate la clausola OVER (ORDER BY) in MySQL quando volete applicare una funzione alle righe in un ordine specifico. Questo può essere utile per calcolare totali, medie mobili e creare varie classifiche.

Esempio 3: OVER() con PARTITION BY - Calcolo di una statistica per ogni partizione

Facciamo un ulteriore uso della colonna categoria. Ricordate il primo esempio di query? Per ogni partecipante, abbiamo visualizzato il suo punteggio, la categoria del quiz che ha tentato e il punteggio più alto mai ottenuto in tutti i quiz. Questa volta vogliamo fare qualcosa di simile. Tuttavia, invece di mostrare il punteggio più alto mai ottenuto in tutti i quiz, mostreremo il punteggio più alto mai ottenuto nella categoria di quel quiz.

Per questo, avremo bisogno della clausola OVER() con PARTITION BY. Partire i dati in SQL significa dividere un insieme di righe in gruppi più piccoli in base a una o più colonne specificate. È un po' simile alla clausola GROUP BY, ma le funzioni finestra non comprimono le righe.

Possiamo usare questa query:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER(PARTITION BY quiz_category) 
    AS max_score_in_category
FROM participant;

La clausola PARTITION BY con la clausola OVER() determina la partizione dei dati su cui viene applicata la funzione MAX(). In questo caso, la clausola PARTITION BY è impostata su quiz_category, il che significa che i dati sono divisi in partizioni basate sulla categoria del quiz. È esattamente quello che volevamo!

Ecco il risultato:

Per ogni partecipante, abbiamo visualizzato sia il suo punteggio individuale sia il punteggio più alto della sua categoria. Sembra giusto, visto che il quiz di musica poteva essere più difficile di quello di scienze!

OVER (PARTITION BY) in MySQL è utile per calcolare somme cumulative o valori medi, creare classifiche all'interno di gruppi, identificare i migliori o i peggiori risultati e molto altro ancora. Questa clausola fornisce flessibilità e funzionalità avanzate alle query SQL, consentendo una potente analisi e manipolazione dei dati all'interno di sottoinsiemi di dati. Per ulteriori informazioni sull'uso di SQL PARTITION BY con OVER, consultare il nostro blog.

È stato facile, vero? Proviamo qualcosa di più complicato!

Esempio 4: OVER() con PARTITION BY e ORDER BY - Calcolo di una statistica per ogni partizione in un ordine specifico

Possiamo usare contemporaneamente PARTITION BY e ORDER BY? Certamente! Questa combinazione è utile in molte situazioni.

In MySQL, l'uso della clausola OVER() con PARTITION BY e ORDER BY consente di eseguire calcoli e analisi su partizioni specifiche di dati, controllando l'ordine in cui i calcoli vengono applicati all'interno di ciascuna partizione.

La clausola PARTITION BY divide l'insieme dei risultati in partizioni distinte basate su colonne o espressioni specificate. Ogni partizione viene trattata separatamente per il calcolo o l'analisi.

La clausola ORDER BY, se usata con OVER(), determina l'ordine di elaborazione dei dati all'interno di ogni partizione. Specifica la colonna o l'espressione in base alla quale i dati devono essere ordinati.

Vediamo questa coppia in azione. In questo esempio, calcoleremo la media cumulativa dei punteggi dei quiz per categoria. Una media cumulativa è la media di un insieme di valori fino a un certo punto.

Questa è la query che utilizzeremo:

SELECT
  name,
  quiz_date,
  quiz_score,
  quiz_category,
  ROUND(
    AVG(quiz_score) 
OVER(PARTITION BY quiz_category ORDER BY quiz_date)
  ) AS cumulative_avg
FROM participant;

Vorremmo vedere la media cumulativa dei punteggi dei quiz per categoria. Per ottenere questo risultato, abbiamo utilizzato PARTITION BY quiz_category, proprio come abbiamo fatto l'ultima volta. Inoltre, è logico che la media cumulativa sia calcolata dalla data più vecchia a quella più recente, quindi abbiamo usato ORDER BY quiz_date. Ciò significa che i dati all'interno di ogni partizione vengono ordinati in base alla data del quiz in ordine crescente (dal più grande al più piccolo) prima che venga applicata la funzione AVG().

Ecco come appare l'output della query:

Utilizzando PARTITION BY e ORDER BY insieme alla clausola OVER(), il calcolo della media viene applicato separatamente per ogni categoria di quiz. All'interno di ogni categoria, la media viene calcolata nell'ordine delle date dei quiz. Ciò significa che per ogni riga del set di risultati, il calcolo della media considera solo le righe della stessa categoria di quiz e le ordina per data. La colonna cumulative_avg rifletterà il punteggio medio fino alla riga corrente per ogni categoria di quiz, tenendo conto dell'ordine delle date dei quiz.

Pronti a fare pratica con MySQL Window Functions?

Come potete vedere, le funzioni finestra di MySQL sono uno strumento molto potente che può aiutarvi a creare rapporti complessi. È possibile utilizzare le funzioni finestra in MySQL per creare classifiche e calcolare metriche anno su anno, medie mobili e molto altro ancora!

In questo articolo abbiamo solo scalfito la superficie di tutti i modi in cui è possibile utilizzare le funzioni finestra. Se volete approfondire ed esplorare altri esempi di utilizzo, date un'occhiata a questi articoli sulle funzioni finestra di SQL e sulla clausola OVER() di MySQL.

E per una maggiore pratica, ricordatevi di dare un'occhiata al nostro corso Window Functions in MySQL 8!