Clause OVER (Transact-SQL)

Mis à jour : 17 juillet 2006

Détermine le partitionnement et l'ordre de l'ensemble de lignes avant l'application de la fonction de fenêtre associée.

S'applique aux types de fonctions suivantes :

Les fonctions de classement de fenêtre

Les fonctions d'aggrégation de fenêtre Pour plus d'informations, consultez Fonctions d'agrégation (Transact-SQL).

Conventions de la syntaxe de 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 en fonction de laquelle l'ensemble de lignes produit par la clause FROM correspondante est partitionnée. <value_expression> ne peut faire référence qu'à des colonnes rendues disponibles par la clause FROM. Ce paramètre ne peut pas faire référence à des expressions ou à des alias de 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

Chaque fonction de classement (ROW_NUMBER, DENSE_RANK, RANK, NTILE) utilise la clause OVER. L'exemple suivant illustre l'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

Les exemples ci-dessous illustrent l'utilisation de la clause OVER avec des fonctions d'agrégation. Dans cet exemple, l'utilisation de la clause OVER s'avère plus efficace que d'utiliser des sous-requêtes.

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

Voici l'ensemble des résultats.

L'exemple suivant illustre l'utilisation de la clause OVER avec une fonction d'agrégation dans une valeur calculée.

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

Voici l'ensemble des résultats. Notez que les agrégations sont calculées par SalesOrderID et que le pourcentage Percent by ProductID est calculé pour chaque ligne de SalesOrderID.

Voir aussi

Référence

Fonctions de classement (Transact-SQL)
Fonctions d'agrégation (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications