Clausola OVER (Transact-SQL)
Determina il partizionamento e l'ordinamento del set di righe prima dell'applicazione della funzione finestra associata.
Si applica a:
Funzioni finestra di rango
Funzioni finestra di aggregazione. Per ulteriori informazioni, vedere Funzioni di aggregazione (Transact-SQL).
.gif "Icona di collegamento a un argomento")
Sintassi
Ranking Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
<ORDER BY_Clause> )
Aggregate Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
Argomenti
PARTITION BY
Suddivide il set di risultati in partizioni. La funzione finestra viene applicata a ogni singola partizione e il calcolo viene riavviato per ogni partizione.value_expression
Specifica la colonna di partizionamento del set di righe generato dalla clausola FROM corrispondente. value_expression può fare riferimento solo a colonne rese disponibili dalla clausola FROM e non a espressioni o alias nell'elenco di selezione. value_expression può essere unìespressione di colonna, una sottoquery scalare, una funzione scalare o una varibile definita dall'utente.<ORDER BY Clause>
Specifica l'ordine di applicazione della funzione finestra di rango. Per ulteriori informazioni, vedere Clausola ORDER BY (Transact-SQL)..gif "Nota importante")
ImportanteQuando viene utilizzato nel contesto di una funzione finestra di rango, l'argomento <ORDER BY Clause> può fare riferimento solo a colonne rese disponibili dalla clausola FROM. Non è possibile specificare un valore integer per rappresentare la posizione del nome o l'alias di una colonna nell'elenco di selezione. <ORDER BY Clause> non può essere utilizzato con funzioni finestra di aggregazione.
Osservazioni
Le funzioni finestra sono definite nello standard SQL ISO. SQL Server offre funzioni finestra di aggregazione e di rango. Una finestra è un set di righe specificato dall'utente e una funzione finestra calcola un valore per ogni riga in un set di risultati derivato dalla finestra.
È possibile utilizzare più funzioni finestra di rango o aggregazione in una singola query con una singola clausola FROM. La clausola OVER di ogni funzione, tuttavia, può specificare un partizionamento e un ordinamento diversi. Non è possibile utilizzare la clausola OVER con la funzione di aggregazione CHECKSUM.
Esempi
A. Utilizzo della clausola OVER con la funzione ROW_NUMBER
Ogni funzione di rango, ad esempio ROW_NUMBER, DENSE_RANK, RANK o NTILE, utilizza la clausola OVER. Nell'esempio seguente viene illustrato l'utilizzo della clausola OVER con ROW_NUMBER.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
B. Utilizzo della clausola OVER con funzioni di aggregazione
Nell'esempio seguente viene illustrato l'utilizzo della clausola OVER con funzioni di aggregazione. Nell'esempio l'utilizzo della clausola OVER è più efficace rispetto all'utilizzo di sottoquery.
USE AdventureWorks;
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
Set di risultati:
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 AdventureWorks;
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
Set di risultati: Si noti che le aggregazioni vengono calcolate in base a SalesOrderID, mentre Percent by ProductID viene calcolato 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.43 |
43664 |
773 |
1 |
14 |
7.14 |
43664 |
778 |
1 |
14 |
7.14 |