Le window functions in SQL

Nelle lezioni precedenti abbiamo visto come utilizzare le funzioni di aggregazione come SUM(), AVG() e COUNT() insieme a GROUP BY.

Con GROUP BY, però, più righe vengono raggruppate in una sola riga di risultato. Questo è utile quando vogliamo ottenere, ad esempio, il totale delle vendite per ogni categoria, ma non quando vogliamo mostrare contemporaneamente il totale e i singoli ordini che lo compongono.

Le window functions, o funzioni finestra, permettono di risolvere proprio questo problema: possiamo eseguire calcoli su un insieme di righe mantenendo comunque ogni riga del risultato originale.

Cosa sono le window function

Una window function esegue un calcolo considerando una finestra di righe associate alla riga che stiamo elaborando. A differenza di GROUP BY, le righe non vengono unite.

Per capire, supponiamo di avere la tabella ordini:

Con GROUP BY possiamo calcolare il totale per cliente:

SELECT cliente, SUM(totale) AS totale
FROM ordini
GROUP BY cliente;

La query di GROUP BY, però, restituirà come righe Marco, Andrea e Giovanni, perché gli ordini di Marco vengono uniti insieme. Quindi, le quattro righe originali sono diventate tre righe.

Con una window function possiamo invece mantenere tutti gli ordini.

La sintassi di OVER()

Le window functions utilizzano la clausola OVER() per definire su quali righe effettuare il calcolo.

La sintassi più semplice è: funzione() OVER (). Per esempio:

SELECT
    nome,
    prezzo,
    AVG(prezzo) OVER () AS prezzo_medio
FROM prodotti;

La funzione calcola la media considerando tutte le righe, ma il risultato viene mantenuto su ogni riga. Possiamo modificare la finestra utilizzando PARTITION BY e ORDER BY.

Usare PARTITION BY

PARTITION BY divide le righe in gruppi indipendenti, chiamati partizioni.

Prendiamo un esempio per capire:

SELECT
    nome,
    categoria,
    prezzo,
    AVG(prezzo) OVER (
        PARTITION BY categoria
    ) AS media_categoria
FROM prodotti;

La media viene calcolata separatamente per ogni categoria. È importante ricordare che PARTITION BY non elimina le righe, come invece fa GROUP BY.

Usare ORDER BY

ORDER BY all'interno di OVER() stabilisce l'ordine delle righe all'interno della finestra.

In questo caso, ad esempio, gli ordini vengono considerati in base alla data_ordine:

SELECT
    id,
    data_ordine,
    totale,
    SUM(totale) OVER (
        ORDER BY data_ordine
    ) AS totale_progressivo
FROM ordini;

ORDER BY all'interno di OVER() è diverso dall'ORDER BY della query principale:

Possiamo utilizzare entrambi nella stessa query.

Funzioni di aggregazione come window functions

Diverse funzioni di aggregazione che abbiamo già visto nel corso SQL possono essere utilizzate anche come window functions.

Tra le più comuni troviamo: SUM() OVER(), AVG() OVER() e COUNT() OVER().

SUM() OVER()

Con la sintassi SUM() OVER() possiamo calcolare il totale di tutte le righe. Ad esempio:

SELECT
    id,
    totale,
    SUM(totale) OVER () AS totale_complessivo
FROM ordini;

Possiamo anche utilizzare PARTITION BY per far sì che, nel risultato, il totale venga calcolato separatamente per ogni gruppo di righe.

AVG() OVER()

Possiamo calcolare la media di tutte le righe utilizzando AVG() OVER(). Ad esempio:

SELECT
    nome,
    prezzo,
    AVG(prezzo) OVER () AS prezzo_medio
FROM prodotti;

COUNT() OVER()

Possiamo utilizzare COUNT() per contare le righe senza raggrupparle. Ad esempio:

SELECT
    id, nome,
    COUNT(*) OVER () AS numero_prodotti
FROM prodotti;

Il numero totale di prodotti verrà mostrato su ogni riga.

Con PARTITION BY possiamo invece contare le righe di ogni gruppo:

SELECT
    nome,
    categoria,
    COUNT(*) OVER (
        PARTITION BY categoria
    ) AS prodotti_categoria
FROM prodotti;

Specificare le righe da considerare

Possiamo avere un controllo più preciso sulle righe considerate dalla window function utilizzando un window frame.

La sintassi è: ROWS BETWEEN inizio AND fine.

Ad esempio, per considerare tutte le righe dall'inizio della finestra fino alla riga corrente, usiamo :

SUM(totale) OVER (
    ORDER BY data_ordine
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Possiamo anche utilizzare PRECEDING per considerare un numero specifico di righe precedenti. Per esempio:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

In questo caso, si considera la riga corrente e le due righe precedenti.

Un esempio molto comune è il calcolo della media mobile, cioè la media degli ultimi valori disponibili. Supponiamo di voler calcolare la media degli ultimi tre ordini:

SELECT
    data_ordine,
    totale,
    AVG(totale) OVER (
        ORDER BY data_ordine
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS media_mobile
FROM ordini;

Per ogni riga vengono quindi considerate la riga corrente, la riga precedente e la seconda riga precedente.

Funzioni di classificazione

Le window functions mettono a disposizione diverse funzioni per assegnare una posizione o una classificazione alle righe di una tabella.

Le principali sono quattro:

Vediamo un esempio con NTILE():

SELECT
    nome, prezzo,
    NTILE(4) OVER (
        ORDER BY prezzo DESC
    ) AS gruppo
FROM prodotti;

NTILE() divide le righe in quattro gruppi, cercando di distribuirle nel modo più uniforme possibile.

Funzioni di navigazione

Le window functions permettono anche di accedere ai valori di altre righe rispetto a quella corrente. Le funzioni principali sono LAG() e LEAD().

Come funziona LAG()

LAG() restituisce il valore di una riga precedente.

Ad esempio, per ogni ordine vogliamo ottenere il totale dell'ordine precedente:

SELECT
    data_ordine, totale,
    LAG(totale) OVER (
        ORDER BY data_ordine
    ) AS totale_precedente
FROM ordini;

Per la prima riga non esiste una riga precedente, quindi viene restituito NULL. Per le altre righe, la colonna totale_precedente conterrà il totale dell'ordine precedente.

Possiamo anche specificare quante righe indietro andare: ad esempio, con LAG(totale, 2) si recupera il valore di due righe precedenti.

Come funziona LEAD()

LEAD() funziona al contrario di LAG(): restituisce il valore di una riga successiva.

Ad esempio, per conoscere il costo del prodotto successivo per ogni riga:

SELECT
    data_ordine, totale,
    LEAD(totale) OVER (
        ORDER BY data_ordine
    ) AS totale_successivo
FROM ordini;

FIRST_VALUE() e LAST_VALUE()

Con OVER() è possibile usare anche FIRST_VALUE() e LAST_VALUE(). FIRST_VALUE() permette di ottenere il primo valore della finestra, LAST_VALUE() permette invece di ottenere l'ultimo valore della finestra.

Per esempio:

SELECT
    nome, prezzo,
    FIRST_VALUE(prezzo) OVER (
        ORDER BY prezzo DESC
    ) AS prezzo_massimo
FROM prodotti;

In questo caso il valore restituito sarà il prezzo del primo prodotto nell'ordinamento, quindi il prezzo massimo.

Prendiamo anche un esempio di LAST_VALUE():

SELECT
    nome, prezzo,
    LAST_VALUE(prezzo) OVER (
        ORDER BY prezzo
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS prezzo_massimo
FROM prodotti;

Nota: è necessario usare ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING per indicare che la finestra deve comprendere tutte le righe. Senza questa specificazione, LAST_VALUE() restituisce il valore dell'ultima riga del frame corrente, anziché dell'intera finestra.

Riutilizzare una finestra con WINDOW

Quando utilizziamo più window functions con la stessa configurazione, possiamo evitare di ripetere ogni volta PARTITION BY e ORDER BY. MySQL permette di definire una finestra con la clausola WINDOW.

Per esempio, definiamo finestra per usarla più volte:

SELECT
    nome, categoria, prezzo,
    AVG(prezzo) OVER finestra AS media_categoria,
    RANK() OVER finestra AS posizione
FROM prodotti
WINDOW finestra AS (
    PARTITION BY categoria
    ORDER BY prezzo DESC
);