Clausola SELECT - OVER (Transact-SQL)
Si applica a:
SQL Server database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft Fabric Warehouse nel database SQL di Microsoft Fabric in Microsoft Fabric
La OVER clausola determina il partizionamento e l'ordinamento di un set di righe prima dell'applicazione della funzione finestra associata. Ovvero, la OVER clausola definisce una finestra o un set specificato dall'utente di righe all'interno di un set di risultati della query. Una funzione finestra calcola quindi un valore per ogni riga della finestra. È possibile usare la OVER clausola con funzioni per calcolare valori aggregati, ad esempio medie mobili, aggregazioni cumulative, totali in esecuzione o primi N per gruppo risultati.
Convenzioni relative alla sintassi Transact-SQL
Sintassi
Sintassi per SQL Server, database SQL di Azure e Azure Synapse Analytics.
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ , ...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{ <window frame preceding>
| <window frame between>
}
<window frame between> ::=
BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{ <window frame preceding>
| <window frame following>
}
<window frame preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification> PRECEDING
| CURRENT ROW
}
<window frame following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification> FOLLOWING
| CURRENT ROW
}
<unsigned value specification> ::=
{ <unsigned integer literal> }
Sintassi per Parallel Data Warehouse.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Argomenti
Nella clausola OVER delle funzioni finestra possono essere presenti gli argomenti seguenti:
PARTITION BY, che suddivide il set dei risultati della query in partizioni.
ORDER BY, che definisce l'ordine logico delle righe all'interno di ogni partizione del set di risultati.
ROWS o RANGE che limita le righe all'interno della partizione specificando i punti iniziale e finale all'interno della partizione. Richiede l’uso dell’argomento
ORDER BYe il valore predefinito è dall'inizio della partizione fino all'elemento corrente se viene specificato l'argomentoORDER BY.
Se non si specifica alcun argomento, le funzioni della finestra vengono applicate all'intero set di risultati.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | min | max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTITION BY
Suddivide il set di risultati della query in partizioni. La funzione finestra viene applicata a ogni singola partizione e il calcolo viene riavviato per ogni partizione.
PARTITION BY <value_expression>
Se PARTITION BY non viene specificato, la funzione considera tutte le righe del set di risultati della query come una singola partizione.
La funzione viene applicata a tutte le righe della partizione se non si specifica ORDER BY la clausola .
PARTITION BY value_expression
Specifica la colonna in base alla quale viene partizionato il set di righe. value_expression può fare riferimento solo alle colonne rese disponibili dalla FROM clausola . value_expression non può fare riferimento a espressioni o alias nell'elenco di selezione. value_expression può essere un'espressione di colonna, una sottoquery scalare, una funzione scalare o una variabile definita dall'utente.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type) AS [min],
MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
| object_id | type | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Definisce l'ordine logico delle righe all'interno di ogni partizione del set di risultati. In altre parole, specifica l'ordine logico in cui viene eseguito il calcolo della funzione finestra.
Se non specificato, l'ordine predefinito è
ASCe la funzione window usa tutte le righe nella partizione.Se specificato e un
ROWSoRANGEnon viene specificato, il valore predefinito viene usato come predefinitoRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWper il frame della finestra, dalle funzioni che possono accettare una specifica oRANGEfacoltativaROWS, ad esempiominomax.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
| object_id | type | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
Specifica una colonna o un'espressione in base alla quale eseguire l'ordinamento. order_by_expression può fare riferimento solo alle colonne rese disponibili dalla FROM clausola . Non è possibile specificare un numero intero per rappresentare un nome di colonna o un alias.
COLLATE collation_name
Specifica che l'operazione ORDER BY deve essere eseguita in base alle regole di confronto specificate in collation_name. In collation_name è possibile usare nomi di regole di confronto di Windows o SQL. Per ulteriori informazioni, vedere Supporto per Unicode e regole di confronto. COLLATEè applicabile solo per le 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 è l'ordinamento predefinito. I valori Null vengono considerati i valori in assoluto più piccoli.
ROWS o RANGE
Si applica a: SQL Server 2012 (11.x) e versioni successive.
Limita ulteriormente le righe all'interno della partizione specificando i punti iniziali e finali. Specifica un intervallo di righe rispetto alla riga corrente in base all'associazione logica o all'associazione fisica. L'associazione fisica viene ottenuta usando la ROWS clausola .
La ROWS clausola limita le righe all'interno di una partizione specificando un numero fisso di righe precedenti o successive alla riga corrente. In alternativa, la RANGE clausola limita logicamente le righe all'interno di una partizione specificando un intervallo di valori rispetto al valore nella riga corrente. Le righe precedenti e seguenti vengono definite in base all'ordinamento nella ORDER BY clausola . La cornice RANGE ... CURRENT ROW ... della finestra include tutte le righe con gli stessi valori nell'espressione ORDER BY della riga corrente. Ad esempio, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa che la finestra delle righe su cui opera la funzione è composta da tre righe, a partire da 2 righe precedenti fino a e includendo la riga corrente.
SELECT object_id,
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
| object_id | preceding | central | following |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
ROWS oppure RANGE richiede di specificare la ORDER BY clausola . Se ORDER BY contiene più espressioni di ordine, CURRENT ROW FOR RANGE considera tutte le colonne nell'elenco ORDER BY quando si determina la riga corrente.
UNBOUNDED PRECEDING
Si applica a: SQL Server 2012 (11.x) e versioni successive.
Specifica che la finestra inizia in corrispondenza della prima riga della partizione. UNBOUNDED PRECEDING può essere specificato solo come punto iniziale della finestra.
<unsigned value specification> PRECEDING
Specificato con <unsigned value specification> per indicare il numero di righe o valori da precedere 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 inizia o termina alla riga corrente quando viene utilizzata con ROWS o il valore corrente quando viene utilizzato con RANGE. CURRENT ROW può essere specificato sia come punto iniziale che finale.
BETWEEN AND
Si applica a: SQL Server 2012 (11.x) e versioni successive.
BETWEEN <window frame bound> AND <window frame bound>
Usato con ROWS o RANGE per specificare i punti limite inferiore (iniziale) e superiore (finale) della finestra. <window frame bound> definisce il punto iniziale del limite e <window frame bound> definisce l'endpoint limite. 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 in corrispondenza dell'ultima riga della partizione. UNBOUNDED FOLLOWING può essere specificato solo come endpoint della finestra. Ad esempio, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definisce una finestra che inizia con la riga corrente e termina con l'ultima riga della partizione.
<unsigned value specification> FOLLOWING
Specificato con <unsigned value specification> per indicare il numero di righe o valori da seguire per la riga corrente. Quando <unsigned value specification> FOLLOWING viene specificato come punto iniziale della finestra, il punto finale deve essere <unsigned value specification> FOLLOWING. 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.
<valore letterale integer senza segno>
Si applica a: SQL Server 2012 (11.x) e versioni successive.
Valore letterale integer positivo (incluso 0) che specifica il numero di righe o valori da precedere o seguire la riga o il valore corrente. Questa specifica è valida solo per ROWS.
Osservazioni:
In una singola query è possibile usare più funzioni finestra con una singola FROM clausola. La OVER clausola per ogni funzione può differire nel partizionamento e nell'ordinamento.
Se PARTITION BY non viene specificato, la funzione considera tutte le righe del set di risultati della query come un singolo gruppo.
Importante
Se ROWS o RANGE viene specificato e <window frame preceding> viene usato per <window frame extent> (sintassi breve), questa specifica viene usata per il punto iniziale del limite della cornice della finestra e CURRENT ROW viene usato per il punto finale del limite. Ad esempio, ROWS 5 PRECEDING è uguale a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Se ORDER BY non viene specificato, l'intera partizione viene usata per un frame di finestra. Questo vale solo per le funzioni che non richiedono ORDER BY la clausola . Se ROWS o RANGE non è specificato ma ORDER BY viene specificato, RANGE UNBOUNDED PRECEDING AND CURRENT ROW viene usato come predefinito per la cornice della finestra. Questo vale solo per le funzioni che possono accettare facoltative ROWS o RANGE specifiche. Ad esempio, le funzioni di classificazione non possono accettare ROWS o RANGE, pertanto questo frame di finestra non viene applicato anche se ORDER BY è presente e ROWS non RANGE lo è.
Limiti
La OVER clausola non può essere usata con le DISTINCT aggregazioni.
RANGE non può essere usato con <unsigned value specification> PRECEDING o <unsigned value specification> FOLLOWING.
A seconda della funzione di classificazione, aggregazione o analitica usata con la OVER clausola <ORDER BY clause> e/o l'oggetto <ROWS and RANGE clause> potrebbe non essere supportato.
Esempi
Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.
R. Usare la clausola OVER con la funzione ROW_NUMBER
Nell'esempio seguente viene illustrato l'uso della clausola con ROW_NUMBER la OVER funzione per visualizzare un numero di riga per ogni riga all'interno di una partizione. La clausola ORDER BY specificata nella clausola OVER ordina le righe in ogni partizione in base alla colonna SalesYTD. La ORDER BY clausola nell'istruzione SELECT determina l'ordine in cui viene restituito l'intero set di risultati della query.
USE AdventureWorks2022;
GO
SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName,
s.SalesYTD,
a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
GO
Il set di risultati è il seguente.
Row Number LastName SalesYTD PostalCode
--------------- ----------------------- --------------------- ----------
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
B. Usare la clausola OVER con funzioni di aggregazione
Nell'esempio seguente viene utilizzata la clausola OVER con funzioni di aggregazione su tutte le righe restituite dalla query. Nell'esempio l'utilizzo della clausola OVER è più efficace rispetto all'utilizzo di sottoquery per derivare i valori di aggregazione.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg",
COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Count",
MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min",
MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Il set di risultati è il seguente.
SalesOrderID ProductID OrderQty Total Avg Count Min Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659 776 1 26 2 12 1 6
43659 777 3 26 2 12 1 6
43659 778 1 26 2 12 1 6
43659 771 1 26 2 12 1 6
43659 772 1 26 2 12 1 6
43659 773 2 26 2 12 1 6
43659 774 1 26 2 12 1 6
43659 714 3 26 2 12 1 6
43659 716 1 26 2 12 1 6
43659 709 6 26 2 12 1 6
43659 712 2 26 2 12 1 6
43659 711 4 26 2 12 1 6
43664 772 1 14 1 8 1 4
43664 775 4 14 1 8 1 4
43664 714 1 14 1 8 1 4
43664 716 1 14 1 8 1 4
43664 777 2 14 1 8 1 4
43664 771 3 14 1 8 1 4
43664 773 1 14 1 8 1 4
43664 778 1 14 1 8 1 4
Nell'esempio seguente viene illustrato l'utilizzo della clausola OVER con una funzione di aggregazione in un valore calcolato.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
CAST (1. * OrderQty / SUM(OrderQty) OVER (PARTITION BY SalesOrderID) * 100 AS DECIMAL (5, 2)) AS [Percent by ProductID]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Il set di risultati è il seguente. Le aggregazioni vengono calcolate in SalesOrderID base a e vengono Percent by ProductID calcolate per ogni riga di ogni SalesOrderID.
SalesOrderID ProductID OrderQty Total Percent by ProductID
------------ ----------- -------- ----------- ---------------------------------------
43659 776 1 26 3.85
43659 777 3 26 11.54
43659 778 1 26 3.85
43659 771 1 26 3.85
43659 772 1 26 3.85
43659 773 2 26 7.69
43659 774 1 26 3.85
43659 714 3 26 11.54
43659 716 1 26 3.85
43659 709 6 26 23.08
43659 712 2 26 7.69
43659 711 4 26 15.38
43664 772 1 14 7.14
43664 775 4 14 28.57
43664 714 1 14 7.14
43664 716 1 14 7.14
43664 777 2 14 14.29
43664 771 3 14 21.4
43664 773 1 14 7.14
43664 778 1 14 7.14
C. Produrre una media mobile e un totale cumulativo
Nell'esempio seguente vengono utilizzate le AVG funzioni e SUM con la OVER clausola per fornire una media mobile e un totale cumulativo delle vendite annuali per ogni territorio nella Sales.SalesPerson tabella. I dati vengono partizionati in base a TerritoryID e ordinati logicamente in base a SalesYTD. Ciò significa che la AVG funzione viene calcolata per ogni territorio in base all'anno di vendita. Per TerritoryID 1, sono presenti due righe per l'anno 2005 di vendita che rappresentano le due persone di vendita con vendite quell'anno. Le vendite medie per queste due righe vengono calcolate e la terza riga che rappresenta le vendite per l'anno 2006 viene inclusa nel calcolo.
USE AdventureWorks2022;
GO
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear;
Il set di risultati è il seguente.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
In questo esempio la OVER clausola non include PARTITION BY. Ciò significa che la funzione viene applicata a tutte le righe restituite dalla query. La ORDER BY clausola specificata nella OVER clausola determina l'ordine logico a cui viene applicata la AVG funzione. La query restituisce una media mobile delle vendite per anno per tutti i territori di vendita specificati nella WHERE clausola . La ORDER BY clausola specificata nell'istruzione determina l'ordine SELECT in cui vengono visualizzate le righe della query.
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY SalesYear;
Il set di risultati è il seguente.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93
D. Specificare la clausola ROWS
Si applica a: SQL Server 2012 (11.x) e versioni successive.
Nell'esempio seguente viene utilizzata la ROWS clausola per definire una finestra su cui vengono calcolate le righe come riga corrente e il numero N di righe che seguono (una riga in questo esempio).
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Il set di risultati è il seguente.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62
Nell'esempio seguente la ROWS clausola viene specificata con UNBOUNDED PRECEDING. Come risultato si ottiene che la finestra inizia in corrispondenza della prima riga della partizione.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS UNBOUNDED PRECEDING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Il set di risultati è il seguente.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 559,697.56
287 NULL 519,905.93 2006 1,079,603.50
285 NULL 172,524.45 2007 1,252,127.95
283 1 1,573,012.94 2005 1,573,012.94
280 1 1,352,577.13 2005 2,925,590.07
284 1 1,576,562.20 2006 4,502,152.27
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 4,251,368.55
281 4 2,458,535.62 2005 6,709,904.17
Esempi: Piattaforma di strumenti analitici (PDW)
E. Usare la clausola OVER con la funzione ROW_NUMBER
L'esempio seguente restituisce il valore ROW_NUMBER per i venditori in base alle rispettive quote di vendita assegnate.
SELECT ROW_NUMBER() OVER (ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName,
LastName,
CONVERT (VARCHAR (13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Di seguito è riportato un set di risultati parziale.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. Usare la clausola OVER con funzioni di aggregazione
Negli esempi seguenti viene illustrato l'uso della OVER clausola con funzioni di aggregazione. In questo esempio, l'uso della clausola è più efficiente rispetto all'uso OVER di sottoquery.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
AVG(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS AVG,
COUNT(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS COUNT,
MIN(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MIN,
MAX(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MAX
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Il set di risultati è il seguente.
OrderNumber Product Qty Total Avg Count Min Max
----------- ------- --- ----- --- ----- --- ---
SO43659 218 6 16 3 5 1 6
SO43659 220 4 16 3 5 1 6
SO43659 223 2 16 3 5 1 6
SO43659 229 3 16 3 5 1 6
SO43659 235 1 16 3 5 1 6
SO43664 229 1 2 1 2 1 1
SO43664 235 1 2 1 2 1 1
Nell'esempio seguente viene illustrato l'utilizzo della clausola OVER con una funzione di aggregazione in un valore calcolato. Le aggregazioni vengono calcolate in base SalesOrderNumber a e la percentuale dell'ordine di vendita totale viene calcolata per ogni riga di ogni SalesOrderNumber.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey AS Product,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
CAST (1. * OrderQuantity / SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) * 100 AS DECIMAL (5, 2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Il primo inizio di questo set di risultati è il seguente:
OrderNumber Product Qty Total PctByProduct
----------- ------- --- ----- ------------
SO43659 218 6 16 37.50
SO43659 220 4 16 25.00
SO43659 223 2 16 12.50
SO43659 229 2 16 18.75