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).

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).

  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:

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.

Vedere anche

Riferimento

Funzioni di rango (Transact-SQL)
Funzioni di aggregazione (Transact-SQL)

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche