Clause OVER (Transact-SQL)

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

Icône Lien de rubriqueConventions 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 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

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.

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

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.

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