Funzioni finestra SQL: classifiche, totali progressivi, confronti

Con GROUP BY si ottengono totali e medie, ma si perdono le singole righe: dieci vendite diventano un'unica riga con la somma. Spesso però serve l'uno e l'altro insieme, per esempio ogni vendita accompagnata dal totale progressivo del mese o dalla posizione in classifica del venditore. Le funzioni finestra, o window functions, fanno esattamente questo: calcolano un valore su un gruppo di righe collegate alla riga corrente, senza ridurre il numero di righe del risultato.

Sono disponibili in tutti i principali database relazionali, compresi PostgreSQL, MySQL dalla versione 8, SQL Server, Oracle e SQLite.

La tabella di esempio

Useremo una tabella vendite con queste colonne: data, venditore, importo.

La sintassi di base

Una funzione finestra si riconosce dalla clausola OVER:

SELECT data, venditore, importo,
       SUM(importo) OVER () AS totale_generale
FROM vendite;

Ogni riga resta al suo posto e riceve in più la colonna totale_generale, che vale 1150 per tutte. Le parentesi vuote dopo OVER indicano che la finestra è l'intera tabella.

PARTITION BY: gruppi separati

SELECT data, venditore, importo,
       SUM(importo) OVER (PARTITION BY venditore) AS totale_venditore
FROM vendite;

Ora la somma è calcolata separatamente per ciascun venditore: le righe di Elena riportano 600, quelle di Luca 550. È simile a un GROUP BY, ma il dettaglio resta visibile. Con questo valore si può calcolare subito la quota di ogni vendita sul totale del venditore, dividendo importo per totale_venditore.

ORDER BY: totali progressivi

Aggiungere un ordinamento dentro OVER cambia il significato della finestra: va dall'inizio della partizione fino alla riga corrente.

SELECT data, venditore, importo,
       SUM(importo) OVER (PARTITION BY venditore ORDER BY data) AS progressivo
FROM vendite;

È il modo standard per calcolare saldi e totali cumulativi.

Classifiche: ROW_NUMBER, RANK e DENSE_RANK

Tre funzioni assegnano una posizione alle righe, e differiscono solo nella gestione dei pareggi. Se due righe hanno lo stesso valore:

• ROW_NUMBER() assegna comunque numeri diversi: 1, 2, 3, 4;
• RANK() dà la stessa posizione e salta la successiva: 1, 2, 2, 4;
• DENSE_RANK() dà la stessa posizione senza saltare: 1, 2, 2, 3.

Un uso molto pratico di ROW_NUMBER è trovare il record più recente per ciascun gruppo, per esempio l'ultima vendita di ogni venditore:

SELECT *
FROM (
  SELECT v.*,
         ROW_NUMBER() OVER (PARTITION BY venditore ORDER BY data DESC) AS n
  FROM vendite v
) t
WHERE n = 1;

Le funzioni finestra non si possono usare direttamente nel WHERE, perché vengono calcolate dopo il filtro: per questo serve la sottoquery esterna. Lo stesso schema è utile anche per individuare e rimuovere i duplicati, tenendo solo la riga con n = 1.

Confronti con le righe vicine: LAG e LEAD

LAG restituisce il valore di una riga precedente nella finestra, LEAD di una successiva. Servono per calcolare variazioni:

SELECT data, importo,
       LAG(importo) OVER (ORDER BY data) AS precedente,
       importo - LAG(importo) OVER (ORDER BY data) AS variazione
FROM vendite;

Sulla prima riga LAG restituisce NULL, perché non c'è una riga precedente; si può indicare un valore predefinito come terzo argomento. Con dati mensili, LAG permette di calcolare la variazione rispetto al mese prima in una sola query, senza unire la tabella con se stessa.

Medie mobili e cornici della finestra

Si può definire con precisione quali righe includere con la clausola ROWS BETWEEN. Una media mobile su tre giorni:

SELECT data, importo,
       AVG(importo) OVER (
         ORDER BY data
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS media_3
FROM vendite;

Ogni riga riceve la media del proprio importo e dei due precedenti. È utile per smussare dati con forti oscillazioni giornaliere.

Una sottigliezza: quando c'è ORDER BY senza cornice esplicita, molti database usano per impostazione una cornice di tipo RANGE, che include anche le righe successive con lo stesso valore di ordinamento. Se due vendite hanno la stessa data, il totale progressivo le somma insieme. Specificare ROWS rende il comportamento prevedibile.

Altre funzioni utili

• NTILE(4) divide le righe in quattro gruppi di dimensione simile, per esempio quartili di clienti per fatturato.
• FIRST_VALUE e LAST_VALUE restituiscono il primo e l'ultimo valore della finestra.
• PERCENT_RANK indica la posizione relativa di una riga, da 0 a 1.

In sintesi

• Le funzioni finestra calcolano valori su gruppi di righe senza ridurre il risultato.
• PARTITION BY crea gruppi separati, ORDER BY rende i calcoli progressivi.
• ROW_NUMBER, RANK e DENSE_RANK differiscono solo nei pareggi; ROW_NUMBER trova l'ultimo record per gruppo.
• LAG e LEAD confrontano ogni riga con quelle vicine.
• La clausola ROWS BETWEEN definisce cornici precise, come le medie mobili.