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).
.gif "Icône Lien de rubrique")
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 |