Le funzioni finestra (o Windowing) sono un ottimo modo per ottenere diverse prospettive su un insieme di dati senza dover fare chiamate ripetute al server per quei dati. Per esempio, possiamo raccogliere la somma di una colonna e visualizzarla fianco a fianco con i dati di dettaglio, così che “SalesAmount” e “SUM(SalesAmount)” possono apparire nella stessa riga. Possiamo anche fare funzioni analitiche come PERCENT_RANK e funzioni di classificazione come ROW_NUMBER, il tutto senza alterare la granularità del set di risultati o fare ulteriori viaggi per ottenere gli stessi dati sorgente ancora e ancora.
“Guarda come riesco a bilanciare senza sforzo due calcoli renali di Val Kilmer. Abbastanza prepotente!”
Le funzioni finestra usano tutte la clausola OVER(), che è usata per definire come viene valutata la funzione. La clausola OVER() accetta tre diversi argomenti:
- PARTITION BY: Azzera il suo contatore ogni volta che la colonna o le colonne indicate cambiano valore.
- ORDER BY: Ordina le righe che la funzione valuterà. Questo non ordina l’intero set di risultati, solo il modo in cui la funzione procede attraverso le righe.
- ROWS BETWEEN: specifica come limitare ulteriormente le righe valutate dalla funzione.
Facciamo finta di guardare dati semplificati di una gara di sollevamento pesi. Ecco alcuni esempi di codice (li faremo tutti in una dichiarazione SELECT perché aggiungere/rimuovere le funzioni della finestra non cambia in alcun modo il numero di righe che otteniamo):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SELECT
LiftID
, LiftDate
, LiftPersonID
, LiftWeight
/* ROW_NUMBER elencherà il numero della riga, ordinato per LiftID.
Il contatore si azzera ad ogni nuova combinazione di LiftDate e LiftPersonID */
, ROW_NUMBER() OVER (PARTITION BY LiftDate, LiftPersonID ORDER BY LiftID) AS LiftNumForToday
/* SUM somma i pesi sollevati.
La prima SOMMA mostrerà il totale generale per l’intero set di risultati.
La seconda SOMMA mostrerà il peso totale sollevato per la data di sollevamento di quella riga.
La terza SOMMA mostrerà il peso totale del sollevamento per la data del sollevamento di quella riga e la persona. */
, SUM(LiftWeight) OVER () AS WeightGrandTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate) AS WeightTotal
, SUM(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonWeightTotal
/* AVG mostra il peso medio sollevato.
Il primo AVG mostrerà il peso medio sollevato per la data di sollevamento di quella riga.
Il secondo AVG mostra il peso medio sollevato per la data di sollevamento di quella riga e la persona. */
, AVG(LiftWeight) OVER (PARTITION BY LiftDate) AS PersonWeightAvg
, AVG(LiftWeight) OVER (PARTITION BY LiftDate, LiftPersonID) AS PersonDayWeightAvg
/* LAG e LEAD permettono alla riga corrente di riportare i dati nelle righe precedenti o successive.
Questa funzione LAG restituirà il LiftWeight da 1 riga dietro di essa (in ordine di LiftID) e se non viene trovato alcun valore, restituirà 0 invece di NULL.
La funzione LEAD otterrà il LiftWeight da 3 righe avanti. Poiché non abbiamo specificato il valore di default opzionale (come lo “0” che abbiamo dato alla funzione LAG, restituirà NULL se non c’è una riga 3 righe più avanti. */
, LAG(LiftWeight, 1, 0) OVER (ORDER BY LiftID) AS PrevLift
, LEAD(LiftWeight, 3) OVER (ORDER BY LiftID) AS NextLift
/* FIRST_VALUE AND LAST_VALUE restituirà il primo e l’ultimo valore della colonna specificata nel set di risultati.
Questa funzione FIRST_VALUE restituisce il primo LiftWeight nell’insieme dei risultati.
Questa funzione LAST_VALUE restituisce l’ultimo LiftWeight nell’insieme dei risultati.
***AVVERTENZA: senza le ROWS BETWEEN nel LAST_VALUE, si possono ottenere risultati inaspettati.***
*/
, FIRST_VALUE(LiftWeight) OVER (ORDER BY LiftDate) AS FirstLift
, LAST_VALUE(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastLift
/* SUM usando ROWS BETWEEN restringerà il campo valutato dalla funzione finestra.
La funzione inizierà e finirà dove specificato da ROWS BETWEEN.
La prima SOMMA aggiungerà tutti i valori di LiftWeight nelle righe fino alla riga corrente compresa.
La seconda SOMMA aggiungerà tutti i valori di LiftWeight nelle righe tra la riga corrente e le 3 righe precedenti.
*/
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WeightRunningTotal
, SUM(LiftWeight) OVER (ORDER BY LiftDate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS WeightSumLast4
FROM dbo.Lifts
|
Considerazioni per le funzioni finestra
Se non avete SQL Server 2012 o successivo, il vostro armadio delle funzioni finestra è piuttosto spoglio; SQL Server dal 2005 al 2008 R2 permetteva solo PARTITION BY nella clausola OVER di una funzione aggregata, e avete RANK() e ROW_NUMBER(). Questo era tutto. Se siete uno sviluppatore ancora su una di queste versioni precedenti, questo è un caso convincente per passare alla 2012 o successive. Pensate a quanto tempo potreste risparmiare non scrivendo più CTE e a quanto più veloci saranno le vostre query.
Parlando di velocità…
Evitando i giri al server per gli stessi dati, riduciamo l’I/O su quelle tabelle. Se stiamo colpendo gli indici, possiamo davvero ridurre le letture coinvolte. C’è un compromesso, ma di solito è molto favorevole. Le funzioni finestra richiedono che SQL Server costruisca la finestra e calcoli la funzione (indicata come compiti come Window Spool, Segment, Sequence Project e Compute Scalar). Nel fare ciò, aggiunge letture alla tabella di lavoro. Tuttavia, questo è generalmente meno costoso che tornare indietro per ottenere i dati sorgente più volte, aggregarli se necessario, e unirli tutti insieme. Inoltre, Worktable esiste in tempdb, che – idealmente – è sul tuo livello di archiviazione più veloce.
Infine, ricorda che le limitazioni che metti su una funzione finestra – PARTITION BY, ORDER BY, o ROWS BETWEEN – sono lì per applicare il contesto alla funzione finestra e in nessun modo si applicano all’insieme dei risultati nel suo complesso. In altre parole, la vostra istruzione SELECT non sarà influenzata da qualsiasi cosa diciate a una funzione finestra di fare.