La clausola OVER è stata aggiunta a SQL Server “nel passato” in SQL Server 2005 ed è stata ampliata in SQL Server 2012. Viene utilizzato prevalentemente con le “Funzioni finestra”; l’unica eccezione è la funzione di sequenza NEXT VALUE FOR. La clausola OVER viene utilizzata per determinare quali righe della query vengono applicate alla funzione, in quale ordine vengono valutate da quella funzione e quando i calcoli della funzione devono essere riavviati. Poiché viene utilizzato insieme ad altre funzioni, e questo articolo riguarda specificamente solo la clausola OVER, di queste funzioni si parlerà solo in quanto si riferisce alla clausola OVER negli esempi forniti.

La sintassi di la clausola OVER è:

<function> OVER ( )

Osservando la sintassi, sembra che tutte le sottoclausole siano opzionali. Infatti, ogni funzione che può utilizzare la clausola OVER determina quali sotto-clausole sono consentite e quali sono obbligatorie. A seconda della funzione utilizzata, la clausola OVER stessa potrebbe essere facoltativa. C’è un grafico alla fine di questo articolo che mostra quali funzioni consentono / richiedono quali parti della clausola OVER.

La clausola PARTITION BY viene utilizzata per dividere il set di risultati dalla query in sottoinsiemi di dati, oppure partizioni. Se la clausola PARTITION BY non viene utilizzata, l’intero set di risultati della query è la partizione che verrà utilizzata. La funzione finestra utilizzata viene applicata a ciascuna partizione separatamente e il calcolo eseguito dalla funzione viene riavviato per ciascuna partizione. Si definisce un insieme di valori che determinano le partizioni in cui dividere la query. Questi valori possono essere colonne, funzioni scalari, sottoquery scalari o variabili.

Ad esempio, esaminiamo la seguente query:

SELECT COUNT(*)FROM .sys.indexes;

Questa query restituisce il seguente set di risultati:

Questo è semplicemente il numero di righe restituite dalla query – in questo caso, il numero di indici nel database msdb. Ora aggiungiamo la clausola OVER a questa query:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

I risultati ridotti sono:

Questa query restituisce object_id e index_id per ogni indice e il numero totale di indici nel set di risultati. Poiché una clausola PARTITION BY non è stata utilizzata, l’intero set di risultati è stato trattato come una singola partizione. È ora il momento di aggiungere la clausola PARTITION BY e vedere come questo cambia i risultati:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

I risultati ridotti sono:

Questa query restituisce una riga per ogni indice, ma ora la query specifica una clausola PARTITION BY della colonna object_id, quindi la funzione count restituisce il numero di indici su quel particolare object_id. La clausola ORDER BY controlla l’ordine in cui le righe vengono valutate dalla funzione. Questo sarà dimostrato a breve. La clausola ROWS o RANGE determina il sottoinsieme di righe all’interno della partizione che devono essere applicate alla funzione. Quando si utilizza ROWS o RANGE, si specifica il punto di inizio e di fine della finestra. I valori consentiti sono:

Esistono due sintassi per specificare la finestra:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Se viene specificato solo il frame iniziale, il frame finale predefinito è CURRENT ROW.

La parola chiave UNBOUNDED specifica l’inizio della partizione (per PRECEDING) o la fine di la partizione (per FOLLOWING). CURRENT ROW specifica che la riga corrente è l’inizio o la fine della finestra, a seconda della posizione del frame della finestra in cui viene utilizzata. “N” specifica un numero di righe prima della riga corrente (per PRECEDING ) o dopo la riga corrente (per FOLLOWING) da utilizzare per il frame della finestra.

Le seguenti sono specifiche della finestra valide:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Per poter utilizzare la clausola ROWS o RANGE, è necessario specificare anche la clausola ORDER BY. Al contrario, se si utilizza la clausola ORDER BY e non si specifica una clausola ROWS o RANGE, il valore predefinito RANGE BETWEEN UNBOUNDED PRECEDING E CURRENT Viene utilizzato ROW.

Per dimostrare le clausole ORDER BY e ROWS o RANGE, creiamo alcuni dati di prova: due account, quattro date per account e un importo per ogni data. La query mostrerà entrambi clausole utilizzate in modi diversi:

Questa query restituisce il seguente set di risultati:

Il ” RowNbr “è in uso g la funzione COUNT per restituire quante righe ci sono nella partizione. La partizione è ordinata da TranDate e stiamo specificando un frame della finestra di tutte le righe dall’inizio della partizione fino alla riga corrente. Per la prima riga, c’è solo una riga nel frame della finestra, quindi viene restituito il valore “1”. Per la seconda riga, ora ci sono due righe nel frame della finestra quindi viene restituito il valore “2”. E così via per il resto delle righe in questo account.

Poiché la clausola PARTITION BY specifica l’account, quando l’account cambia, vengono ripristinati i calcoli della funzione, che possono essere visualizzati esaminando le righe per il secondo account nel set di risultati. Questo è un esempio di aggregazione “corrente”, in cui l’aggregazione si basa su calcoli precedenti. Un esempio di quando utilizzeresti questo sarebbe quando calcoli il saldo del tuo conto bancario dopo ogni transazione (altrimenti noto come totale parziale).

La colonna “DateCount” sta eseguendo un conteggio del numero di righe, partizionate in base alla data. In questo esempio, ciascuno dei conti ha una transazione in ciascuna delle stesse quattro date, quindi ogni data ha due transazioni (una per ogni conto). In questo modo viene restituito il valore “2” per ogni riga. Ciò è simile all’esecuzione di un conteggio che utilizza GROUP BY per la data; la differenza è che il totale viene restituito per ogni riga anziché una sola volta per ogni data. Un esempio di quando utilizzeresti questo metodo sarebbe visualizzare una “Riga X di Y” o calcolare una percentuale della riga corrente sul totale.

La colonna “Last2Count” esegue un conteggio delle righe all’interno della partizione, per la riga corrente e per quella immediatamente precedente, per la prima riga di ogni account, non essendoci righe precedenti, viene restituito il valore “1”. Per le righe rimanenti in ogni account, viene restituito un valore di “2”. Questo è un esempio di aggregazione “mobile” o “scorrevole”. Un esempio di quando utilizzeresti questo metodo sarebbe calcolare un bonus in base al vendite degli ultimi due mesi.

A questo punto, ho mostrato solo la clausola ROWS. La clausola RANGE funziona in modo simile, ma invece di trattare le righe in modo posizionale, si occupa della valori restituiti da quella riga. È perché non è posizionale che le clausole N PRECEDING / FOLLOWING non possono essere utilizzate. Diamo una rapida occhiata alla differenza tra ROWS e RANGE utilizzando entrambi nella stessa query. Qui abbiamo un elenco di persone (chiamiamole DBA) e le loro tariffe orarie. Tieni presente che le righe con RowIDs 4 & 5 e 12 & 13 hanno la stessa velocità. La query sommerà le tariffe due volte, una volta utilizzando ROWS e l’altra utilizzando RANGE:

Questa query produce il seguente set di risultati:

In entrambe le colonne SumByRows e SumByRange la clausola OVER è identica ad eccezione della clausola ROWS / RANGE. Si noti inoltre che poiché l’intervallo finale non è stato specificato, l’impostazione predefinita è utilizzare CURRENT ROW. Poiché stiamo sommando lo stipendio dall’inizio del risultato impostato attraverso la riga corrente, ciò che stiamo realmente calcolando è un totale parziale della colonna Salario. Nella colonna SumByRows, il valore viene calcolato utilizzando la clausola ROWS e possiamo vedere che la somma della riga corrente è lo stipendio della riga corrente più il totale della riga precedente. Tuttavia, la clausola RANGE funziona al di fuori del valore della colonna Stipendio, quindi riassume tutte le righe con stipendio uguale o inferiore. Ciò fa sì che il valore SumByRange sia lo stesso valore per tutte le righe con lo stesso salario.

Una nota importante: la clausola ORDER BY nella clausola OVER controlla solo l’ordine in cui verranno utilizzate le righe nella partizione dalla funzione finestra. Non controlla l’ordine del set di risultati finali. Senza una clausola ORDER BY sulla query stessa, l’ordine delle righe non è garantito. È possibile notare che la query potrebbe essere restituita nell’ordine dell’ultima clausola OVER specificata, ciò è dovuto al modo in cui è attualmente implementato in SQL Server. Se il team di SQL Server in Microsoft cambia il modo in cui funziona, potrebbe non ordinare più i risultati nel modo in cui stai attualmente osservando. Se è necessario un ordine specifico per il set di risultati, è necessario fornire una clausola ORDER BY rispetto alla query stessa.

Infine, ecco un grafico delle varie funzioni che possono utilizzare la clausola OVER, nonché quali parti della clausola sono consentite / obbligatorie / facoltative.

R-Obbligatorio, O-Facoltativo, X-Non consentito