SQL Server 2012: Novità nella clausola Over

SQL server 2012 ha introdotto molte nuove funzioni T-SQL e miglioramenti vari.
La clausola OVER() è una di quelle funzioni/clausole che hanno subito interessanti implementazioni.

Questa clausola consente di determinare il partizionamento e l'ordinamento dei dati all'interno di un resultset prima che la funzione associata venga eseguita. Può essere utilizzata per pilotare le funzioni di ranking o le funzioni di aggregazione o le funzioni analitiche.

Vediamo come funziona.

Per creare l'ambiente di demo utilizzo lo script di preparazione che trovate nel post relativo alle funzioni di ranking

Immaginiamo che si voglia ottenere per un ipotetico venditore le informazioni di dettaglio di ogni singola fattura emessa, seguite da:
1. Totale della singola riga
2. Totale della fattura
3. Totale di tutte le fatture emesse dal venditore
4. Importo medio righe in fattura


Grazie alla clausola over associata alla corretta funzione d'aggregazione possiamo ottenere il risultato desiderato

Select  IntVend.id
 ,V.Nome
 ,P.CodProd
 ,DV.Qta
 ,P.Prezzo                                           [Prezzo Unitario]
 ,DV.Qta*P.Prezzo                                    [Importo Riga]
 ,Sum(DV.Qta*P.Prezzo)Over(Partition by IntVend.id)  [Totale Singola Fattura]
 ,Sum(DV.Qta*P.Prezzo)Over(Partition by V.id)        [Totale Fatture]
 ,Avg(DV.Qta*P.Prezzo)Over(Partition by IntVend.id)  [Importo Medio Righe in fattura]
from tabIntestazioneVendite IntVend
 inner join tabDettaglioVendite DV On DV.idVendita = IntVend.id
 inner join tabProdotti P on P.id = DV.idProdotto
 inner join tabClienti C on C.id = IntVend.idCliente
 inner join tabVenditori V on V.id = IntVend.idVenditore
Where V.Nome = 'Venditore_A'

L'importo [Totale Singola Fattura] viene ottenuto mediante Sum(DV.Qta*P.Prezzo)Over(Partition by IntVend.id).
Il calcolo della Sum(DV.Qta*P.Prezzo) viene effettuato sulle righe interessate dalla Over(Partition by IntVend.id). All'interno del totale di tutte le righe di dettaglio di ogni fattura emessa dal venditore, Partition by crea un partizionamento contenente le righe di una specifica fattura e su di esse applica la Sum.

Il prezzo medio dei prodotti nella singola fattura viene calcolato mediante Avg(DV.Qta*P.Prezzo)Over(Partition by IntVend.id). Come potete vedere i due calcoli utilizzano partizionamenti differenti all'interno della stess query. Questa caratteristica ci consente di scrivere query semplici ma che elaborano più partizionamenti di dati senza dover ricorrere a Group by e query nidificate.

Nulla di nuovo nell'utilizzo che abbiamo appena visto, ma grazie ai nuovi ROWS/RANGE è possibile limitare ulteriormente le righe all'interno del partizionamento su cui si vogliono applicare le funzioni.

Immaginiamo di voler ottenere anche l'importo della fattura incrementandolo riga per riga per visualizzare quindi un importo incrementale. Grazie alla Over(Partition by IntVend.id Order by IntVend.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) otteniamo il totale di ogni riga sommato al totale della precedente all'interno dello stesso raggruppamento.

Select  IntVend.id
 ,V.Nome
 ,P.CodProd
 ,DV.Qta
 ,P.Prezzo
 ,DV.Qta*P.Prezzo                                                     [Importo Riga]
 ,Sum(DV.Qta*P.Prezzo)Over(Partition by IntVend.id)                   [Totale Singola Fattura]
 ,Sum(DV.Qta*P.Prezzo)Over(Partition by IntVend.id Order by IntVend.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) [Totale Incrementale Fattura]
 ,Sum(DV.Qta*P.Prezzo)Over(Partition by V.id)                         [Totale Fatture]
 ,Avg(DV.Qta*P.Prezzo)Over(Partition by IntVend.id)                   [Importo Medio Righe in fattura]
from tabIntestazioneVendite IntVend
 inner join tabDettaglioVendite DV On DV.idVendita = IntVend.id
 inner join tabProdotti P on P.id = DV.idProdotto
 inner join tabClienti C on C.id = IntVend.idCliente
 inner join tabVenditori V on V.id = IntVend.idVenditore
Where V.Nome = 'Venditore_A'

Immaginiamo di voler ottenere anche il totale Incrementale di tutte le fatture emesse dal venditore. Grazie alla Over(Order by IntVend.id RANGE UNBOUNDED PRECEDING) possiamo visualizzare l'importo totale di tutte le fatture sommando il totale della fattura attuale alla precedente.

Select  IntVend.id
 ,V.Nome
 ,P.CodProd
 ,DV.Qta
 ,P.Prezzo
 ,DV.Qta*P.Prezzo                                                      [Importo Riga]
 ,Sum(DV.Qta*P.Prezzo)Over(Partition by IntVend.id)                    [Totale Singola Fattura]
 ,Sum(DV.Qta*P.Prezzo)Over(Partition by IntVend.id Order by IntVend.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) [Totale Incrementale Fattura]
 ,Sum(DV.Qta*P.Prezzo)Over(Order by IntVend.id RANGE UNBOUNDED PRECEDING)[Importo Totale Incrementale Fatture]
 ,Avg(DV.Qta*P.Prezzo)Over(Partition by IntVend.id)                    [Importo Medio Prodotti in fattura]
from tabIntestazioneVendite IntVend
 inner join tabDettaglioVendite DV On DV.idVendita = IntVend.id
 inner join tabProdotti P on P.id = DV.idProdotto
 inner join tabClienti C on C.id = IntVend.idCliente
 inner join tabVenditori V on V.id = IntVend.idVenditore
Where V.Nome = 'Venditore_A'

Ciao

Luca