Clausola OVER (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 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).

  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

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

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

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

Vedere anche

Riferimento