Clause OVER (Transact-SQL)

Syntaxe

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

Arguments

• PARTITION BY
  Divise l'ensemble de résultats en partitions. La fonction de fenêtre est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition.

• value_expression
  Spécifie la colonne par laquelle l'ensemble de lignes produit par la clause FROM correspondante est partitionné. value_expression peut faire référence uniquement à des colonnes rendues disponibles par la clause FROM. value_expression ne peut pas faire référence à des expressions ou des alias dans la liste de sélection. value_expression peut être une expression de colonne, une sous-requête scalaire, une fonction scalaire ou une variable définie par l'utilisateur.

• <Clause ORDER BY>
  Spécifie l'ordre d'application de la fonction de classement de fenêtre. Pour plus d'informations, consultez Clause ORDER BY (Transact-SQL).

  Important

  Lorsque la clause <ORDER BY> est utilisée dans le contexte d'une fonction de classement de fenêtre, elle ne peut faire référence qu'à des colonnes rendues disponibles par la clause FROM. Un entier ne peut pas être spécifié pour représenter la position du nom ou de l'alias d'une colonne dans la liste de sélection. La clause <ORDER BY> ne peut pas être utilisée avec des fonctions d'agrégation de fenêtre.

Notes

Les fonctions de fenêtre sont définies dans la norme ISO SQL. SQL Server fournit des fonctions de classement et d'agrégation. Une fenêtre est un groupe de lignes spécifié par l'utilisateur. Une fonction de fenêtre calcule une valeur pour chaque ligne d'un ensemble de résultats dérivé de la fenêtre.

Il est possible d'utiliser plusieurs fonctions de classement ou d'aggrégation de fenêtre dans une requête avec une seule clause FROM. Toutefois, la clause OVER de chaque fonction peut être différente en termes de partitionnement et de tri. La clause OVER ne peut pas être utilisée avec la fonction d'agrégation CHECKSUM.

Exemples

A. Utilisation de la clause OVER avec la fonction 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. Utilisation de la clause OVER avec des fonctions d'agrégation

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

Voir aussi

Référence