Come utilizzare le window functions?

1. Introduzione

Sviluppando query complesse nasce la necessità di partizionare ed eventualmente ordinare un set di righe prima dell’applicazione di una funzione. SQL Server (e PostgreSQL) forniscono la clausola OVER e PARTITION BY per poter raggruppare i dati secondo un set di righe specificato dall’utente. Successivamente una funzione calcola un valore per ogni riga del set.
Non tutti i DBMS offrono le window function, per esempio MySQL non le supporta, ma esistono delle alternative.

È possibile utilizzare la clausola OVER con le funzioni per calcolare i valori aggregati, ad esempio medie, aggregazioni cumulative, totali parziali o i primi N risultati per gruppo.

Segue la definizione (da documentazione) di come utilizzare tale comando.

OVER ( 
[ <partition by clause> ]
[ <order by clause> ] 
[ <row or range clause> ]
) 

  • PARTITION BY: Suddivide il set di risultati della query in partizioni (raggruppamenti). La funzione viene applicata a ogni singola partizione e il calcolo viene effettuato per ogni partizione.
    Si deve specificare la colonna (o più colonne) secondo la quale il risultato della query viene partizionato (raggruppato). Il PARTITION BY è analogo al GROUP BY, segue le sue stesse regole.
  • ORDER BY: E’ possibile definire l’ordine logico delle righe di ogni partizione. Quindi specifica l’ordine logico secondo il quale la funzione viene eseguita.
  • ROWS | RANGE: Limita ulteriormente le righe all’interno della partizione specificando i punti iniziali e finali. La clausola ROWS limita le righe all’interno di una partizione specificando un numero fisso di righe precedenti o successive alla riga corrente. In alternativa, la clausola RANGE limita logicamente le righe all’interno di una partizione specificando un intervallo di valori rispetto al valore nella riga corrente.

E’ importante notare come le righe considerate da una window function sono quelle della tabella virtuale prodotta dalla query filtrata per WHERE e GROUP BY, per esempio una riga rimossa dal WHERE non viene in alcun modo vista dalla window function.

2. OVER vs GROUP BY

La differenza con le funzioni di aggregazione (GROUP BY) è che una window function non raggruppa i risultati in una singola riga, ma questi rimangono separati in entità distinte.

Consideriamo, per esempio, la seguente tabella in cui vi è un misuratore che rileva il Consumo di energia

SELECT IDConsumoMisuratori, IDMisuaratore, Consumo
FROM ConsumoMisuratori
WHERE IDMisuaratore = 999

IDConsumoMisuratoriIDMisuaratoreConsumo
1999NULL
299931.0
399918.0
4999NULL
5999NULL

La seguente è una classica query per il calcolo della media del Consumo con il GROUP BY

SELECT IDMisuaratore, AVG(Consumo) AS ConsumoMedio
FROM ConsumoMisuratori AS gl
WHERE IDMisuaratore = 999
GROUP BY IDMisuaratore

IDMisuaratoreConsumoMedio
99924.500000

La seguente invece usa l’OVER PARTITION (sempre raggruppando per IDMisuaratore)

SELECT IDMisuaratore
, AVG(Consumo) OVER (PARTITION BY IDMisuaratore) AS ConsumoMedio
FROM ConsumoMisuratori AS gl
WHERE IDMisuaratore = 999

IDMisuaratoreConsumoMedio
99924.500000
99924.500000
99924.500000
99924.500000
99924.500000

Come si può notare l’OVER PARTITION esegue la media sui risultati raggruppati dalla clausola PARTITION BY ma senza unire i risultati in una unica riga ma mantenendoli su righe distinte.

3.1 OVER()

La chiamata di PARTITION BY non è obbligatoria: se la mia partizione sono tutte le righe posso omettere la dichiarazione:
Nell’esempio seguente eseguo un filtro sull’IDMisuaratore (WHERE IDMisuaratore = 999).
Conseguentemente dichiarare la PARTITION (OVER (PARTITION BY IDMisuaratore)) o ometterla OVER () fornisce lo stesso identico risultato

SELECT IDMisuaratore
, AVG(Consumo) OVER () AS ConsumoMedio
FROM ConsumoMisuratori AS gl
WHERE IDMisuaratore = 999

IDMisuaratoreConsumoMedio
99924.500000
99924.500000
99924.500000
99924.500000
99924.500000

Se invece non ho un filtraggio sull’IDMisuaratore la media dei consumi sarà su tutta la tabella

SELECT IDMisuaratore
, AVG(Consumo) OVER () AS ConsumoMedio
FROM ConsumoMisuratori AS gl

IDMisuaratoreConsumoMedio
999-289.5
999-289.5
999-289.5

3.2 ORDER BY

Ora aggiungiamo un ORDER BY al PARTITION BY:

SELECT IDConsumoMisuratori, IDMisuaratore
, AVG(Consumo) OVER (PARTITION BY IDMisuaratore ORDER BY IDConsumoMisuratori) AS ConsumoMedio
FROM ConsumoMisuratori AS gl
WHERE IDMisuaratore = 999

Ottengo il seguente risultato:

IDConsumoMisuratoriIDMisuaratoreConsumo Metodo eseguito
1999NULL>AVG(NULL)
299931.0>AVG(NULL, 31)
399924.50>AVG(NULL, 31, 18)
499924.50>AVG(NULL, 31, 18, NULL)
599924.50>AVG(NULL, 31, 18, NULL, NULL)

Come si può notare viene eseguita una specie di cumulata, la funzione AVG utilizza come parametro di ingresso non tutta la partizione, ma iterativamente la completa inserendo record ordinati con l’ORDER BY.
Ricordo che l’ORDER BY della partition non deve necessariamente corrispondere con l’ORDER BY della query.
Analogamente a sopra, eseguendo un filtraggio sull’IDMisuaratore posso omettere di eseguire il partizionamento, scrivendo quindi

OVER ( ORDER BY IDMisuaratore )

ottenendo lo stesso risultato.
Se elimino il filtraggio sull’IDMisuaratore posso ottenere come varia la media al crescere dell’IDMisuaratore (la partizione è tutta la tabella).

SELECT IDMisuaratore
, AVG(Consumo) OVER ( ORDER BY IDMisuaratore ) AS ConsumoMedio
FROM ConsumoMisuratori AS gl

IDMisuaratoreConsumoMedio
111NULL
99924.500000
99924.500000
33358.600000
10003055.375000

4. Window frame

Un altro concetto importante associato alle window function è quello di window frame, spesso la funzione che lavora sulle partizioni non lavora su tutta la partizione, ma solo su un suo sottoinsieme.
Le window function sono permesse solo dentro la SELECT, se ho la necessità invece di raggruppare righe dopo che il calcolo della window function è stato eseguito devo usare una sottoquery.

4.1 Associare IDMisuaratore ai primi n consumi

Per prima cosa scrivo una query che mi fornisca, per ogni IDMisuaratore, il massimo del Consumo.
Questa query è facilmente esprimibile con un GROUP BY:

SELECT IDMisuaratore, max(Consumo) AS ConsumoMassimo
FROM ConsumoMisuratori AS gl
GROUP BY IDMisuaratore

IDMisuaratoreConsumoMassimo
111NULL
99931.0
333156.0

Ora provo ad ottenere gli stessi risultati utilizzando una window function invece che un GROUP BY.
Per prima cosa creo un rowset che associa, per ogni IDMisuaratore, il suo Consumo, ordinata per Consumo con la funzione RANK():

SELECT IDMisuaratore, Consumo
, RANK() OVER ( PARTITION BY IDMisuaratore ORDER BY Consumo DESC ) AS Pos
FROM ConsumoMisuratori AS gl

Che ottiene

IDMisuaratoreConsumoMassimoPos
111NULL1
99931.01
99918.02
999NULL3

Come si può notare a Pos=1 corrisponde il valore massimo.
Posso inserire questa query con subquery nel seguente modo:

SELECT IDMisuaratore
    , Consumo AS ConsumoMassimo
FROM (
    SELECT IDMisuaratore
        , Consumo
        , RANK() OVER (
            PARTITION BY IDMisuaratore ORDER BY Consumo DESC
            ) AS Pos
    FROM ConsumoMisuratori AS gl
    )
WHERE Pos = 1

Mettendo il filtro a Pos = 1 ottengo esattamente lo stesso risultato della query con il** GROUP BY** e il MAX

IDMisuaratoreConsumoMassimo
111NULL
99931.0
333156.0

Usare le window function per calcolare il massimo è ovviamente inutile, però assumiamo per esempio di calcolare i due valori più alti (o genericamente gli n valori più alti), e non solo il massimo. Con il MAX e GROUP BY è impossibile, ma così non è con le PARTITION.
Basta estendere la precedente query con Pos &lt; 3 e ottengo, per ogni IDMisuaratore, i primi due valori per il Consumo (se esistono)

IDMisuaratoreConsumoMassimo
111NULL
99931.0
99918.0
333156.0
33366.0

Genericamente scrivendo Pos < n ottengo i primi n valori del Consumo, per ogni IDMisuaratore.

4.2 JOIN solo su un sottoset di dati

Assumiamo di avere una relazione 1-n tra la tabella Container e la tabella Images.
Ora voglio selezionare tutti i Container insieme alle prime 3 Images ivi contenute. Questo problema è risolto con le window function, in quanto vado a fare il JOIN non su Images, ma su una tabella virtuale partizionata solo con in cui ad ogni Images è associato il suo ROW_NUMBER().

Posso poi quindi partizionare solo le Images con ROW_NUMBER() <= 3, conseguentemente eseguo il JOIN solo sulle prime 3 immagini.

SELECT img.*
, Container.*
FROM Container
INNER JOIN (
SELECT *
    , ROW_NUMBER() OVER (
        PARTITION BY Container_id ORDER BY creatioon_date
        ) AS rn
FROM Images
) AS img ON img.Container_id = Container.id
AND img.rn <= 3

4.3 Correlare dati tra tabelle con il ROW_NUMBER

Le window function sono un argomento molto ampio e meriteranno un approfondimento in un articolo ad hoc, ma riassumendo queste funzioni permettono di partizionare ed eventualmente ordinare un set di righe prima dell’applicazione di una funzione.
La clausola OVER definisce un set di righe specificato dall’utente all’interno di un set di risultati della query. Successivamente una funzione calcola un valore per ogni riga del set.

4.3.1 Definizione del dataset

Ora lavoriamo sulle seguenti tabelle, che sono delle tabelle provvisorie create ad hoc, queste sono una parte di dei raggruppamenti per la colonna Foo e filtrate per Foo=2183098.

SELECT * INTO #temp_table_1
FROM (
SELECT N'2183098' AS Foo, N'142230' AS Bar UNION ALL
SELECT N'2183098' AS Foo, NULL AS Bar UNION ALL
SELECT N'2183098' AS Foo, NULL AS Bar ) t;

FooBar
2183098142230
2183098NULL
2183098NULL
SELECT * INTO #temp_table_2
FROM (
SELECT N'2183098' AS Foo, N'142228' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142229' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142230' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142231' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142232' AS Bar UNION ALL
SELECT N'2183098' AS Foo, N'142233' AS Bar ) t;

FooBar
2183098142228
2183098142229
2183098142230
2183098142231
2183098142232
2183098142233

Il mio obiettivo è associare le due tabelle (come se ci fosse una specie di JOIN) per ottenere il seugente risultato

FooBar tabella 1Bar tabella 2
2183098142230142228
2183098NULL142229
2183098NULL142230

Questo può essere utile in caso di update o di confronto dati tra le due tabelle e così via.

4.3.2 Estrazione del ROW_NUMBER

Analizzando approfonditamente il problema, si nota che quello che voglio fare è associare le tabelle per numero di riga: è necessario conseguentemente eseguire un SELECT sulle tabelle che aggiunga anche il numero di righe al dataset ottenuto dalle tabelle.

Per fare questo utilizzo la funzione di rango ROW_NUMBER che restituisce il numero sequenziale di una riga all’interno di una partizione di un set di risultati, a partire da 1 per la prima riga di ogni partizione.

SELECT row_number() OVER (
        PARTITION BY Foo ORDER BY Foo
        ) AS Riga_1
    , *
FROM #temp_table_1

ed analogamente anche sulla seconda tabella.
I risultati che ottengo sono i seguenti:

Riga_1FooBar
12183098142230
22183098NULL
32183098NULL
Riga_2FooBar
12183098142228
22183098142229
32183098142230
42183098142231
52183098142232
62183098142233

Il PARTITION BY Foo è superfluo in questo semplice esempio, in quanto ho già precedentemente filtrato per Foo=2183098, ma in un caso reale così non è.

4.3.3 Associazione dei dati

Una volta fatto ciò, l’associazinoe risulta immediata con un JOIN in questo modo:

SELECT T1.Foo
, T1.Bar AS [Bar tabella 1]
, T2.Bar AS [Bar tabella 2]
FROM (
... prima query
) AS T1
INNER JOIN (
... seconda query
) AS T2 ON T1.Riga_1 = T2.riga_2

Che mi permette di ottenere quanto voluto.

FooBar tabella 1Bar tabella 2
2183098142230142228
2183098NULL142229
2183098NULL142230

4.4 INSERT INTO tabella senza identity

Se stiamo lavorando su tabelle legacy o non architettate al meglio, può capitare di avere una colonna chiave primaria senza identity, conseguentemente quando si ha la necessità di eseguire insert massivi devo utilizzare un trucco per associare ad ogni record inserito il valore della chiave primaria incrementale.
Per poter eseguire questa cosa, vengono ancora una volta in aiuto le window function.
Prendiamo come esempio la tabella TableNoIdentity ha IDTable chiave primaria ma senza alcuna identity impostata su di essa.
Per prima cosa salvo in una variabile il valore massimo della chiave primaria inserito nella tabella.

DECLARE @maxIdTable int;
SELECT @maxIdTable = max(IDTable) FROM TableNoIdentity

Per l’inserimento con la INSERT INTO SELECT utilizzo, come prima colonna della SELECT, un OVER (ORDER BY ( SELECT 1 ) ), che mi fornisce un numero crescente per ogni riga e vi vado a sommare il valore massimo trovato prima.

INSERT INTO TableNoIdentity (...)
SELECT @maxIdTable + row_number() OVER (ORDER BY ( SELECT 1 ) ), foo_field, ...
FROM foo_table

In questo modo ottengo un inserimento con id crescente coerente con la chiave primaria.

5. Funzioni di rango

Le funzioni di rango restituiscono un valore di rango per ogni riga di una partizione. In base alla funzione utilizzata, è possibile che venga assegnato lo stesso valore a più righe.

Row NumberRankDense RankQuartile
1111
2111
3112
4112
5113
6623
7624

5.1 RANK

Restituisce il rango di ogni riga all’interno della partizione di un set di risultati. Il valore di rango di una riga è uno più il numero di ranghi che precedono la riga in questione. Se due o più righe hanno un valore equivalente per un rango, ogni riga equivalente riceve lo stesso rango. La funzione RANK non restituirà sempre valori interi consecutivi.

5.2 DENSE_RANK

Restituisce il rango delle righe nella partizione di un set dei risultati, senza vuoti tra i ranghi. Se due o più righe hanno un valore equivalente per un rango nella stessa partizione, alle righe con valori equivalenti viene assegnato lo stesso rango. I numeri restituiti dalla funzione DENSE_RANK sono pertanto sempre consecutivi e senza gap.

5.3 ROW_NUMBER

Restituisce il numero sequenziale di una riga all’interno di una partizione di un set di risultati, a partire da 1 per la prima riga di ogni partizione.

5.4 NTILE

Distribuisce le righe di una partizione ordinata in un numero specificato di gruppi. Utile per suddividere le righe ottenute da una SELECT in n gruppi in base ad un determinato parametro (Consumo, fattura, data…).
Per esempio la seguente query divide le letture (IDConsumoMisuratori) per un determinato IDgasMisuratori in base al Consumo.

SELECT IDConsumoMisuratori, Consumo
, NTILE(4) OVER (
ORDER BY Consumo DESC
) AS Quartile
FROM ConsumoMisuratori AS gl
WHERE IDMisuaratore = 100002

IDConsumoMisuratoriConsumoQuartile
3255713.0001
114095.0001
1243817.0001
2402990.0002
15922976.0002
42972.0002
2622691.0003
15942604.0003
15952498.0003
31907.0004
1581722.0004
16021722.0004

Indice

Share
Ultimi articoli
Join

Newsletter

Nessuno spam, solo articoli interessanti ;)

Focus

Post correlati

sharding

Cosa è il database sharding?

Introduzione Ogni applicazione che utilizza un database deve pensare a come gestire un aumento delle richieste e dell’utilizzo di questa ultima senza comprometterne l’usabilità. Rendere

WITH – Common Table Expression

Le Common Table Expression forniscono una sintassi alternativa per evitare l’accumularsi di query innestate: permettono di creare delle tabelle temporanee basandosi sui risultati di una

Utilizzare la clausola ROLLUP

Talvolta voglio eseguire una query che mi fornisca dei risultati aggregati di media, min, max… ma anche dei risultati sull’intero rowset senza aggregazione. Per avere

Codice Pragmatico

Contatti

Per informazioni, dubbi o consulenze non esitate a contattarmi.

Lascia un messaggio

Ricevi le ultime news

Iscrivi alla newsletter

Solo articoli interessanti, promesso ;)