Window Function in SQL: quali sono e come utilizzarle

Le Window Function (o Analytic function) sono delle istruzioni SQL che permettono di associare informazioni aggregate  a tutte le righe di una tabella, senza impattare il numero di righe e di colonne nel risulto in output. Con informazioni aggregate intendo principalmente:

  • somme;
  • medie;
  • classifiche che seguono un certo ordine (ad esempio numerare le fatture di ogni cliente dalla più recente alla meno recente);
  • altri indici statistici.

Alcune di queste informazioni potrebbero essere ottenute anche con una Group By ma, a differenza delle Window Function, avremo nell’output una sola riga per ogni combinazione dei valori inseriti nella Group By, perdendo tutte le altre informazioni di dettaglio. Nei prossimi paragrafi vedremo una serie di esempi che ci aiuteranno a capire meglio come utilizzare le Window Function.

Casi di utilizzo di una Window function

Partiamo con un esempio molto frequente:

“calcolare l’importo della fattura più recente per ogni cliente”.

Per quanto la richiesta sembra abbastanza chiara e comune, non è facilissimo risolverla con l’SQL. Da un lato infatti siamo portati a scrivere una Group By per calcolare la data massima associata a ogni fattura:

SELECT 
  IdCliente, 
  MAX(DataFattura) AS Max_Data
FROM  Fatture
GROUP BY IdCliente;

Il problema però è che in questo modo otteniamo la data della fattura più recente e non l’importo! Per raggiungere il nostro vero obiettivo potrebbe venirci in mente di scrivere questa query:

SELECT 
  IdCliente, 
  MAX(DataFattura) AS Max_Data, 
  Importo
FROM     Fatture
GROUP BY IdCliente;

ma otterremo su tutti i principali Database un errore di sintassi, a eccezione di MySQL, sul quale comunque otteniamo un importo casuale che quasi sicuramente non sarà quello associato alla data più recente. Per altre peculiarità della sintassi SQL su MySQL puoi leggere questo mio articolo https://www.yimp.it/mysql-caratteristiche-della-sintassi-sql/.

Un’altra idea sbagliata sarebbe scrivere

SELECT 
  IdCliente, 
  MAX(DataFattura) AS Max_Data, 
  MAX(Importo) AS Max_Importo
FROM   Fatture
GROUP BY IdCliente;

perché nessuno ci garantisce che il massimo importo sia stato registrato in contemporanea con la massima data. 

Un primo modo per risolvere questo problema è quello di utilizzare una Subquery o una CTE. L’idea infatti è di combinare l’output della prima query con la tabella originale, in modo da filtrare le sole righe relativa alla data massima. Scriveremo dunque

WITH CTE AS (
 SELECT 
   IdCliente, 
   MAX(DataFattura) AS Max_Data
 FROM   Fatture
 GROUP BY IdCliente)
SELECT 
  f.IdCliente, 
  f.IdFattura, 
  f.DataFattura, 
  f.Importo
FROM   Fatture AS  f
INNER JOIN Cte AS c
  ON F.IdCliente = c.IdCliente
  AND F.DataFattura = c.Max_Data;

Analogamente potrei raggiungere lo stesso risultato con una SubQuery. Questo approccio in molti casi è corretto, ma presenta tre punti di attenzione:

  1. Nel caso in cui a un cliente fossero associate più fatture nella relativa data più recente, mi ritroverei nell’output più di una fattura per ogni cliente e non avrei modo di inserire un ulteriore criterio di scelta. Per alcune tipologie di analisi questa potrebbe essere una limitazione importante.
  2. Cosa succede se voglio estrarre gli importi delle tre fatture più recenti per ogni cliente? Il codice scritto è quasi impossibile da generalizzare al caso in cui voglio considerare più date.
  3. Nel caso raro in cui tutte le fatture di un cliente abbiamo un NULL nella colonna DataFattura, questo cliente non sarà presente nell’output finale.

RANK, DENSE_RANK e ROW_NUMBER

Le limitazioni del paragrafo precedente possono essere superati con l’utilizzo delle Window Function. Consideriamo la query

SELECT
 *,
 RANK() OVER(PARTITION BY IdCliente 
             ORDER BY DataFattura DESC) AS Ordine
FROM  Fatture;

Tramite l’utilizzo della window function RANK() calcoliamo una nuova colonna che riporta una “posizione in classifica delle fatture per ogni cliente“. Il fatto che tale classifica parta da capo a ogni nuovo cliente è dovuto alla clausola PARTITION BY IdCliente. Sto chiedendo dunque al Database di lavorare su finestre di dati (da qui il nome window function) partizionate per i valori della colonna IdCliente. Invece il criterio con cui stabilisco le posizioni all’interno della partizione è quello scritto nella clausola ORDER BY: in questo caso per valori decrescenti della colonna DataFattura.

esempio window function

A questo punto posso utilizzare una CTE/Subquery per visualizzare soltanto le fatture in una determinata posizione.

WITH CTE AS(
 SELECT 
  *, 
  RANK() OVER(PARTITION BY IdCliente 
              ORDER BY DataFattura DESC) AS Ordine 
 FROM   Fatture )
SELECT 
  IdCliente, 
  IdFattura, 
  DataFattura, 
  Importo
FROM  CTE
WHERE Ordine = 1;

Vediamo ora come posso indirizzare i tre problemi precedenti:

  1. In caso di pari merito la funzione RANK restituisce più righe con valore 1. Ma in questo caso posso scegliere di disambiguare facilmente le righe in due modi:
    • inserendo un ulteriore criterio nella clausola Order By;
    • sostituendo RANK con ROW_NUMBER. Infatti, a parità di cliente, ROW_NUMBER non restituirà mai lo stesso valore, disambiguando le righe con lo stesso ordine in modo non deterministico.
  2. Se voglio estrarre gli importi delle cinque fatture più recenti mi basterà modificare la condizione della Where scrivendo Ordine <= 5.
  3. Con le impostazioni di default il Null è considerato come il valore di DataFattura più piccolo nel calcolo del RANK. Nel caso non voglia vedere quelle righe posso aggiungere un’ulteriore condizione nell’ultima WHERE come DataFattura IS NOT NULL.

Differenze tra RANK, DENSE_RANK e ROW_NUMBER

Abbiamo già accennato al fatto che all’interno della finestra definita dalla clausola PARTITION BY, la ROW_NUMBER associa sempre valori differenti, disambiguando eventuali “pari merito” in modo non deterministico (https://sqlperformance.com/2019/11/t-sql-queries/row-numbers-with-nondeterministic-order). La funzione RANK invece assocerà lo stesso valore. Ma cosa succede nelle righe successive il pari merito?

Rispondendo a questa domanda troveremo la differenza tra RANK e DENSE_RANK. Ad esempio:

  • dopo una coppia di righe con 1, il valore successivo restituito da RANK sarà 3;
  • dopo una coppia di righe con 1, il valore successivo restituito da DENSE_RANK sarà 2.

Utilizzare le window function per calcolare somme e medie

Vediamo ora un esempio di utilizzo diverso delle window function:

Estrarre per ogni fattura il rapporto tra il suo importo e il totale degli importi nel relativo anno

In questo caso scriveremo

SELECT  
 IdFattura,
 Importo,
 Importo /  
  SUM(Importo) OVER(PARTITION BY YEAR(DataFattura)) AS Rapporto
FROM   Fatture;

Facciamo attenzione a non inserire in questo caso una clausola ORDER BY all’interno dell’OVER in quanto verrebbe eseguita una tipologia di calcolo completamente differente di cui parleremo in un prossimo articolo.

Continua a imparare

Torna in alto
Torna su