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.
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 |
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
Version | Historique |
---|---|
17 juillet 2006 |
|