Clausola OVER (Transact-SQL)
Data aggiornamento: 17 luglio 2006
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")
Convenzioni della sintassi Transact-SQL
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 Non può riferirsi a espressioni o alias nell'elenco di selezione. <value_expression> può essere un'espressione della colonna, una subquery scalare, una funzione scalare o una variabile 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 "ms189461.note(it-it,SQL.90).gif")
Importante:Quando 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 subquery.
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 |
Vedere anche
Riferimento
Funzioni di rango (Transact-SQL)
Funzioni di aggregazione (Transact-SQL)
Guida in linea e informazioni
Cronologia modifiche
| Versione | Cronologia |
|---|---|
17 luglio 2006 |
|