SELECT – OVER Clause (Transact-SQL) (Italiano)

• 08/11/2017
• 17 minuti per la lettura
• • V
  • L
  • c
  • j
  • M
  • +11

Si applica a: SQL Server (tutti versioni supportate) Database SQL di Azure Istanza gestita SQL di Azure Azure Synapse Analytics Parallel Data Warehouse

Determina il partizionamento e lordinamento di un set di righe prima dellapplicazione della funzione finestra associata. Ovvero, la clausola OVER definisce una finestra o un set di righe specificato dallutente allinterno di un set di risultati di query. Una funzione finestra calcola quindi un valore per ogni riga nella finestra. È possibile utilizzare la clausola OVER con le funzioni per calcolare valori aggregati come medie mobili, aggregati cumulativi, totali parziali o i primi N risultati per gruppo.

• Funzioni di classificazione

• Funzioni aggregate

• Funzioni analitiche

• VALORE SUCCESSIVO PER funzione

Convenzioni della sintassi Transact-SQL

Sintassi

Argomenti

Le funzioni di finestra potrebbero avere i seguenti argomenti nella loro OVER clausola:

• PARTITION BY che divide il set di risultati della query in partizioni.
• ORDER BY che definisce lordine logico delle righe allinterno di ciascuna partizione del set di risultati.
• ROWS / RANGE che limita le righe allinterno della partizione specificando i punti di inizio e di fine allinterno della partizione. Richiede largomento ORDER BY e il valore predefinito è dallinizio della partizione allelemento corrente se è specificato largomento ORDER BY.

Se non specifichi alcun argomento, le funzioni della finestra verranno applicate allintero set di risultati.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTIZIONE PER

Divide il set di risultati della query in partizioni. La funzione finestra viene applicata a ciascuna partizione separatamente e il calcolo viene riavviato per ogni partizione ion.

Se PARTITION BY non è specificato, la funzione tratta tutte le righe del set di risultati della query come una singola partizione. La funzione verrà applicata a tutte le righe nella partizione se non si specifica la clausola ORDER BY.

PARTITION BY espressione_valore

Specifica la colonna in base alla quale il set di righe è partizionato. value_expression può fare riferimento solo alle colonne rese disponibili dalla clausola FROM. value_expression non può fare riferimento a espressioni o alias nellelenco di selezione. value_expression può essere unespressione di colonna, una sottoquery scalare, una funzione scalare o una variabile definita dallutente.

ORDER BY

Definisce lordine logico delle righe allinterno di ciascuna partizione del set di risultati. Cioè, specifica lordine logico in cui viene eseguito il calcolo della funzione finestra.

• Se non è specificato, lordine predefinito è ASC e la funzione window utilizzerà tutte le righe nella partizione.
• Se è specificato e non è specificato un ROWS / RANGE, il valore predefinito RANGE UNBOUNDED PRECEDING AND CURRENT ROW viene utilizzato come predefinito per la finestra frame dalle funzioni che possono accettare la specifica opzionale ROWS / RANGE (ad esempio min o max).

order_by_expression
Specifica una colonna o unespressione su cui ordinare. order_by_expression può fare riferimento solo alle colonne rese disponibili dalla clausola FROM. Non è possibile specificare un numero intero per rappresentare un nome di colonna o un alias.

COLLATE collation_name
Specifica che loperazione ORDER BY deve essere eseguita in base alle regole di confronto specificate in collation_name. collation_name può essere un nome di regole di confronto di Windows o un nome di regole di confronto SQL. Per ulteriori informazioni, vedere regole di confronto e supporto Unicode. COLLATE è applicabile solo per colonne di tipo char, varchar, nchar e nvarchar.

ASC | DESC
Specifica che i valori nella colonna specificata devono essere ordinati in ordine crescente o decrescente. ASC è lordinamento predefinito.I valori nulli vengono trattati come i valori più bassi possibili.

ROWS o RANGE

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Ulteriori limiti le righe allinterno della partizione specificando i punti iniziale e finale allinterno della partizione. Ciò viene fatto specificando un intervallo di righe rispetto alla riga corrente tramite associazione logica o associazione fisica. Lassociazione fisica si ottiene utilizzando la clausola ROWS.

La clausola ROWS limita le righe allinterno di una partizione specificando un numero fisso di righe che precedono o seguono la riga corrente. In alternativa, la clausola RANGE limita logicamente le righe allinterno di una partizione specificando un intervallo di valori rispetto al valore nella riga corrente. Le righe precedenti e successive vengono definite in base allordine nella clausola ORDER BY. La cornice della finestra “RANGE … CURRENT ROW …” include tutte le righe che hanno gli stessi valori nellespressione ORDER BY della riga corrente. Ad esempio, RIGHE TRA 2 RIGHE PRECEDENTI E CORRENTE significa che la finestra di righe su cui opera la funzione ha una dimensione di tre righe, a partire da 2 righe precedenti fino alla riga corrente inclusa.

UNBOUNDED PRECEDING

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Specifica che la finestra viene avviata dalla prima riga della partizione. UNBOUNDED PRECEDING può essere specificato solo come punto di inizio della finestra.

< specifica del valore senza segno > PRECEDING
Specificato con < specifica di valore senza segno > per indicare il numero di righe o valori che precedono la riga corrente. Questa specifica non è consentita per RANGE.

CURRENT ROW

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Specifica che la finestra viene avviata o termina alla riga corrente se utilizzato con ROWS o al valore corrente se utilizzato con RANGE. CURRENT ROW può essere specificato sia come punto iniziale che come punto finale.

BETWEEN AND

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Utilizzato con ROWS o RANGE per specificare i punti limite inferiore (iniziale) e superiore (finale) della finestra. < window frame bound > definisce il punto di partenza del confine e < finestra frame bound > definisce il punto finale del confine. Il limite superiore non può essere inferiore al limite inferiore.

UNBOUNDED FOLLOWING

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Specifica che la finestra termina nellultima riga della partizione. UNBOUNDED FOLLOWING può essere specificato solo come punto finale della finestra. Ad esempio RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definisce una finestra che inizia con la riga corrente e termina con lultima riga della partizione.

< specifica del valore senza segno > SEGUENTE
Specificato con < specifica del valore senza segno > per indicare il numero di righe o valori per seguire la riga corrente. Quando < specifica del valore senza segno > FOLLOWING viene specificato come punto iniziale della finestra, il punto finale deve essere < specifica del valore senza segno > SEGUE. Ad esempio, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING definisce una finestra che inizia con la seconda riga che segue la riga corrente e termina con la decima riga che segue la riga corrente. Questa specifica non è consentita per RANGE.

letterale intero senza segno
Si applica a: SQL Server 2012 (11.x) e versioni successive.

È un valore letterale intero positivo (incluso 0 ) che specifica il numero di righe o valori che devono precedere o seguire la riga o il valore corrente. Questa specifica è valida solo per ROWS.

Osservazioni generali

È possibile utilizzare più di una funzione finestra in una singola query con una singola clausola FROM. La clausola OVER per ciascuna funzione può differire in partizionamento e ordinamento.

Se PARTITION BY non è specificato, la funzione tratta tutte le righe del set di risultati della query come un singolo gruppo.

Importante !

Se ROWS / RANGE è specificato e < frame della finestra che precede > viene utilizzato per < estensione del telaio della finestra > (sintassi breve), questa specifica viene utilizzata per il punto iniziale del confine del telaio della finestra e RIGA CORRENTE viene utilizzata per il punto finale del confine. Ad esempio “ROWS 5 PRECEDING” è uguale a “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.

Limitazioni e restrizioni

La clausola OVER non può essere utilizzata con la funzione di aggregazione CHECKSUM.

RANGE non può essere utilizzata con < specifica del valore senza segno > PRECEDING o < specifica del valore senza segno > FOLLOWING.

A seconda del ranking, dellaggregato o dellanalitica funzione utilizzata con la clausola OVER, < clausola ORDER BY > e / o < ROWS e la clausola RANGE > potrebbero non essere supportate.

Esempi

A. Utilizzo della clausola OVER con la funzione ROW_NUMBER

Lesempio seguente mostra lutilizzo della clausola OVER con la funzione ROW_NUMBER per visualizzare un numero di riga per ogni riga allinterno di una partizione. La clausola ORDER BY specificata nella clausola OVER ordina le righe in ogni partizione in base alla colonna SalesYTD. La clausola ORDER BY nellistruzione SELECT determina lordine in cui viene restituito lintero set di risultati della query.

Ecco il set di risultati.

B. Utilizzo della clausola OVER con funzioni aggregate

Lesempio seguente utilizza la clausola OVER con funzioni aggregate su tutte le righe restituite dalla query. In questo esempio, lutilizzo della clausola OVER è più efficiente rispetto allutilizzo di sottoquery per derivare i valori aggregati.

Ecco il set di risultati.

Il seguente esempio mostra lutilizzo della clausola OVER con una funzione di aggregazione in un valore calcolato.

Ecco il set di risultati. Tieni presente che gli aggregati vengono calcolati da SalesOrderID e Percent by ProductID viene calcolato per ogni riga di ogni SalesOrderID.

C. Produzione di una media mobile e di un totale cumulativo

Lesempio seguente utilizza le funzioni AVG e SUM con la clausola OVER per fornire una media mobile e un totale cumulativo delle vendite annuali per ogni territorio nel Sales.SalesPerson tabella. I dati vengono partizionati da TerritoryID e ordinati logicamente da SalesYTD. Ciò significa che la funzione AVG viene calcolata per ogni territorio in base allanno di vendita. Si noti che per TerritoryID 1, ci sono due righe per lanno di vendita 2005 che rappresentano i due addetti alle vendite con vendite quellanno. Viene calcolata la media delle vendite per queste due righe, quindi la terza riga che rappresenta le vendite per lanno 2006 viene inclusa nel calcolo.

Ecco il set di risultati.

In questo esempio, la clausola OVER non include PARTITION BY. Ciò significa che la funzione verrà applicata a tutte le righe restituite dalla query. La clausola ORDER BY specificata nella clausola OVER determina lordine logico a cui viene applicata la funzione AVG. La query restituisce una media mobile delle vendite per anno per tutti i territori di vendita specificati nella clausola WHERE. La clausola ORDER BY specificata nellistruzione SELECT determina lordine in cui vengono visualizzate le righe della query.

Ecco il set di risultati.

D. Specifica della clausola ROWS

Si applica a: SQL Server 2012 (11.x) e versioni successive.

Lesempio seguente utilizza la clausola ROWS per definire una finestra su cui le righe vengono calcolate come la riga corrente e il numero N di righe che seguono (1 riga in questo esempio).

Ecco il set di risultati.

Nellesempio seguente, la clausola ROWS è specificata con UNBOUNDED PRECEDING. Il risultato è che la finestra inizia dalla prima riga della partizione.

Ecco il set di risultati.

Esempi: Parallel Data Warehouse

E. Utilizzo della clausola OVER con la funzione ROW_NUMBER

Lesempio seguente restituisce ROW_NUMBER per i rappresentanti di vendita in base alla quota di vendita assegnata.

Di seguito è riportato un set di risultati parziale.

F. Utilizzo della clausola OVER con funzioni aggregate

I seguenti esempi mostrano lutilizzo della clausola OVER con funzioni aggregate. In questo esempio, lutilizzo della clausola OVER è più efficiente rispetto allutilizzo di sottoquery.

Ecco il set di risultati.

Lesempio seguente mostra lutilizzo della clausola OVER con una funzione di aggregazione in un valore calcolato. Tieni presente che gli aggregati vengono calcolati da SalesOrderNumber e la percentuale dellordine di vendita totale viene calcolata per ciascuna riga di ogni SalesOrderNumber.

Il primo inizio di questo set di risultati è:

Vedere anche

Funzioni aggregate (Transact-SQL)
Funzioni analitiche (Transact-SQL)
Eccellente post sul blog sulle funzioni delle finestre e OVER, su sqlmag.com, di Itzik Ben-Gan