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.
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
IDConsumoMisuratori IDMisuaratore Consumo 1 999 NULL 2 999 31.0 3 999 18.0 4 999 NULL 5 999 NULL
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
IDMisuaratore ConsumoMedio 999 24.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
IDMisuaratore ConsumoMedio 999 24.500000 999 24.500000 999 24.500000 999 24.500000 999 24.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.
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
IDMisuaratore ConsumoMedio 999 24.500000 999 24.500000 999 24.500000 999 24.500000 999 24.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
IDMisuaratore ConsumoMedio 999 -289.5 999 -289.5 999 -289.5 ... ...
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:
IDConsumoMisuratori IDMisuaratore Consumo Metodo eseguito 1 999 NULL > AVG(NULL) 2 999 31.0 > AVG(NULL, 31) 3 999 24.50 > AVG(NULL, 31, 18) 4 999 24.50 > AVG(NULL, 31, 18, NULL) 5 999 24.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
IDMisuaratore ConsumoMedio 111 NULL 999 24.500000 999 24.500000 333 58.600000 100030 55.375000 ... ...
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
IDMisuaratore ConsumoMassimo 111 NULL 999 31.0 333 156.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
IDMisuaratore ConsumoMassimo Pos 111 NULL 1 999 31.0 1 999 18.0 2 999 NULL 3 ... ... ...
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
IDMisuaratore ConsumoMassimo 111 NULL 999 31.0 333 156.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 < 3 e ottengo, per ogni IDMisuaratore, i primi due valori per il Consumo (se esistono)
IDMisuaratore ConsumoMassimo 111 NULL 999 31.0 999 18.0 333 156.0 333 66.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;
Foo Bar 2183098 142230 2183098 NULL 2183098 NULL
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;
Foo Bar 2183098 142228 2183098 142229 2183098 142230 2183098 142231 2183098 142232 2183098 142233
Il mio obiettivo è associare le due tabelle (come se ci fosse una specie di JOIN) per ottenere il seugente risultato
Foo Bar tabella 1 Bar tabella 2 2183098 142230 142228 2183098 NULL 142229 2183098 NULL 142230
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_1 Foo Bar 1 2183098 142230 2 2183098 NULL 3 2183098 NULL
Riga_2 Foo Bar 1 2183098 142228 2 2183098 142229 3 2183098 142230 4 2183098 142231 5 2183098 142232 6 2183098 142233
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.
Foo Bar tabella 1 Bar tabella 2 2183098 142230 142228 2183098 NULL 142229 2183098 NULL 142230
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.
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 Number Rank Dense Rank Quartile 1 1 1 1 2 1 1 1 3 1 1 2 4 1 1 2 5 1 1 3 6 6 2 3 7 6 2 4
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.
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.
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.
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
IDConsumoMisuratori Consumo Quartile 325 5713.000 1 11 4095.000 1 124 3817.000 1 240 2990.000 2 1592 2976.000 2 4 2972.000 2 262 2691.000 3 1594 2604.000 3 1595 2498.000 3 3 1907.000 4 158 1722.000 4 1602 1722.000 4
