SELECT - CLAUSE OVER (Transact-SQL)
S’applique à : 
SQL Server Base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Point de terminaison d'analyse SQL dans Microsoft Fabric Entrepôt dans Microsoft Fabric Base de données SQL dans Microsoft Fabric
La OVER clause détermine le partitionnement et l’ordre d’un ensemble de lignes avant l’application de la fonction de fenêtre associée. Autrement dit, la OVER clause définit une fenêtre ou un ensemble de lignes spécifié par l’utilisateur dans un jeu de résultats de requête. Une fonction de fenêtre calcule ensuite une valeur pour chaque ligne dans la fenêtre. Vous pouvez utiliser la OVER clause avec des fonctions pour calculer des valeurs agrégées telles que les moyennes mobiles, les agrégats cumulés, les totaux en cours d’exécution ou les résultats du groupe supérieur par groupe.
Conventions de la syntaxe Transact-SQL
Syntaxe
Syntaxe pour SQL Server, Azure SQL Database et Azure Synapse Analytics.
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ , ...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{ <window frame preceding>
| <window frame between>
}
<window frame between> ::=
BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{ <window frame preceding>
| <window frame following>
}
<window frame preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification> PRECEDING
| CURRENT ROW
}
<window frame following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification> FOLLOWING
| CURRENT ROW
}
<unsigned value specification> ::=
{ <unsigned integer literal> }
Syntaxe pour Parallel Data Warehouse.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Arguments
Les fonctions de fenêtre peuvent avoir les arguments suivants dans leur clause OVER :
PARTITION BY qui divise le jeu de résultats de la requête en partitions.
ORDER BY qui définit l’ordre logique des lignes dans chaque partition du jeu de résultats.
ROWS ou RANGE qui limite les lignes de la partition en spécifiant les points de début et de fin dans la partition. Elle requiert l’argument
ORDER BYet la valeur par défaut est du début de la partition à l’élément actuel si l’argumentORDER BYest spécifié.
Si vous ne spécifiez aucun argument, les fonctions de fenêtre sont appliquées sur l’ensemble du jeu de résultats.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | minute(s) | max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTITION BY
Divise le jeu de résultats de la requête en partitions. La fonction de fenêtre est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition.
PARTITION BY <value_expression>
Si PARTITION BY elle n’est pas spécifiée, la fonction traite toutes les lignes du jeu de résultats de la requête en tant que partition unique.
La fonction est appliquée à toutes les lignes de la partition si vous ne spécifiez ORDER BY pas de clause.
PARTITION BY value_expression
Spécifie la colonne par laquelle l'ensemble de lignes est partitionné. value_expression ne peut faire référence qu’aux colonnes rendues disponibles par la FROM clause. 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.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type) AS [min],
MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
| object_id | type | minute(s) | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Définit l'ordre logique des lignes dans chaque partition du jeu de résultats. Autrement dit, il spécifie l’ordre logique dans lequel le calcul de la fonction de la fenêtre est effectué.
Si elle n’est pas spécifiée, l’ordre par défaut est
ASCet la fonction de fenêtre utilise toutes les lignes de la partition.Si elle est spécifiée et qu’elle
ROWSn’estRANGEpas spécifiée, la valeur par défaut est utilisée comme valeur parRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWdéfaut pour le cadre de fenêtre, par les fonctions qui peuvent accepter une spécification ouRANGEROWSfacultative (par exemple,minoumax).
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
| object_id | type | minute(s) | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
Spécifie une colonne ou une expression dans lesquelles trier. order_by_expression ne peut faire référence qu’aux colonnes rendues disponibles par la FROM clause. Un entier ne peut pas être spécifié pour représenter un nom de colonne ou un alias.
COLLATE collation_name
Spécifie que l’opération ORDER BY doit être effectuée en fonction du classement spécifié dans collation_name. collation_name peut être un nom de classement Windows ou SQL. Pour plus d’informations, voir Classement et prise en charge d’Unicode. COLLATEs’applique uniquement aux colonnes de type char, varchar, nchar et nvarchar.
ASC | DESC
Spécifie que les valeurs dans la colonne spécifiée doivent être triées par ordre croissant ou décroissant. ASC correspond à l’ordre de tri par défaut. Les valeurs NULL sont traitées comme les plus petites valeurs possibles.
ROWS ou RANGE
S’applique à : SQL Server 2012 (11.x) et versions ultérieures.
Limite davantage les lignes dans la partition en spécifiant les points de départ et de terminaison dans la partition. Il spécifie une plage de lignes par rapport à la ligne actuelle par association logique ou par association physique. L’association physique est obtenue à l’aide de la ROWS clause.
La ROWS clause limite les lignes d’une partition en spécifiant un nombre fixe de lignes précédant ou suivant la ligne actuelle. Sinon, la RANGE clause limite logiquement les lignes d’une partition en spécifiant une plage de valeurs par rapport à la valeur de la ligne active. Les lignes précédentes et suivantes sont définies en fonction de l’ordre dans la ORDER BY clause. Le cadre RANGE ... CURRENT ROW ... de fenêtre inclut toutes les lignes qui ont les mêmes valeurs dans l’expression ORDER BY que la ligne active. Par exemple, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW signifie que la fenêtre des lignes sur laquelle la fonction fonctionne est de trois lignes de taille, en commençant par 2 lignes précédant jusqu’à ce que la ligne active soit comprise.
SELECT object_id,
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
| object_id | précédent | central | suivant |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
ROWS ou RANGE exige que vous spécifiiez la ORDER BY clause. Si ORDER BY elle contient plusieurs expressions d’ordre, CURRENT ROW FOR RANGE prend en compte toutes les colonnes de la ORDER BY liste lors de la détermination de la ligne active.
UNBOUNDED PRECEDING
S’applique à : SQL Server 2012 (11.x) et versions ultérieures.
Spécifie que la fenêtre commence à la première ligne de la partition. UNBOUNDED PRECEDING ne peut être spécifié que comme point de départ de fenêtre.
<unsigned value specification> PRECEDING
Spécifié avec <unsigned value specification> pour indiquer le nombre de lignes ou de valeurs à précéder de la ligne actuelle. Cette spécification n’est pas autorisée pour RANGE.
CURRENT ROW
S’applique à : SQL Server 2012 (11.x) et versions ultérieures.
Spécifie que la fenêtre démarre ou se termine à la ligne actuelle lorsqu’elle est utilisée avec ROWS ou avec la valeur actuelle lorsqu’elle est utilisée avec RANGE. CURRENT ROW peut être spécifié comme point de départ et de fin.
BETWEEN AND
S’applique à : SQL Server 2012 (11.x) et versions ultérieures.
BETWEEN <window frame bound> AND <window frame bound>
Utilisé avec l’une ou l’autre ROWS pour RANGE spécifier les points de limite inférieurs (de départ) et supérieur (fin) de la fenêtre. <window frame bound> définit le point de départ de la limite et <window frame bound> définit le point de terminaison de limite. La limite supérieure ne peut pas être inférieure à la limite inférieure.
UNBOUNDED FOLLOWING
S’applique à : SQL Server 2012 (11.x) et versions ultérieures.
Spécifie que la fenêtre se termine à la dernière ligne de la partition. UNBOUNDED FOLLOWING ne peut être spécifié qu’en tant que point de terminaison de fenêtre. Par exemple, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING définit une fenêtre qui commence par la ligne active et se termine par la dernière ligne de la partition.
<unsigned value specification> FOLLOWING
Spécifié avec <unsigned value specification> pour indiquer le nombre de lignes ou de valeurs qui suivent la ligne actuelle. Quand <unsigned value specification> FOLLOWING elle est spécifiée comme point de départ de la fenêtre, le point de terminaison doit être <unsigned value specification> FOLLOWING. Par exemple, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING définit une fenêtre qui commence par la deuxième ligne qui suit la ligne active et se termine par la dixième ligne qui suit la ligne actuelle. Cette spécification n’est pas autorisée pour RANGE.
<littéral entier non signé>
S’applique à : SQL Server 2012 (11.x) et versions ultérieures.
Littéral entier positif (y compris 0) qui spécifie le nombre de lignes ou de valeurs à précéder ou à suivre la ligne ou la valeur actuelle. Cette spécification est valide uniquement pour ROWS.
Notes
Plusieurs fonctions de fenêtre peuvent être utilisées dans une requête unique avec une clause unique FROM . La OVER clause de chaque fonction peut différer dans le partitionnement et l’ordre.
Si PARTITION BY elle n’est pas spécifiée, la fonction traite toutes les lignes du jeu de résultats de la requête en tant que groupe unique.
Important
Si ROWS ou RANGE est spécifié et <window frame preceding> utilisé pour <window frame extent> (syntaxe courte), cette spécification est utilisée pour le point de départ du cadre de fenêtre et CURRENT ROW est utilisée pour le point de terminaison de limite de limite. Par exemple, ROWS 5 PRECEDING est égal à ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Si ORDER BY elle n’est pas spécifiée, la partition entière est utilisée pour un cadre de fenêtre. Cela s’applique uniquement aux fonctions qui ne nécessitent ORDER BY pas de clause. Si ROWS ou RANGE n’est pas spécifié, mais ORDER BY est spécifié, RANGE UNBOUNDED PRECEDING AND CURRENT ROW est utilisé comme valeur par défaut pour le cadre de fenêtre. Cela s’applique uniquement aux fonctions qui peuvent accepter des spécifications ou RANGE facultativesROWS. Par exemple, les fonctions de classement ne peuvent pas accepter ROWS ou RANGE, par conséquent, ce cadre de fenêtre n’est pas appliqué même s’il ORDER BY est présent et ROWS ou RANGE non.
Limites
La OVER clause ne peut pas être utilisée avec les DISTINCT agrégations.
Impossible d'utiliser RANGE avec <unsigned value specification> PRECEDING ou <unsigned value specification> FOLLOWING.
Selon le classement, l’agrégation ou la fonction analytique utilisée avec la OVER clause, <ORDER BY clause> et/ou la <ROWS and RANGE clause> fonction peut ne pas être prise en charge.
Exemples
Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.
R : Utiliser la clause OVER avec la fonction ROW_NUMBER
L’exemple suivant montre l’utilisation de la OVER clause avec ROW_NUMBER la fonction pour afficher un numéro de ligne pour chaque ligne dans une partition. La clause ORDER BY spécifiée dans la clause OVER classe les lignes de chaque partition par la colonne SalesYTD. La ORDER BY clause de l’instruction SELECT détermine l’ordre dans lequel l’ensemble du jeu de résultats de la requête est retourné.
USE AdventureWorks2022;
GO
SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName,
s.SalesYTD,
a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
GO
Voici le jeu de résultats.
Row Number LastName SalesYTD PostalCode
--------------- ----------------------- --------------------- ----------
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
B. Utiliser la clause OVER avec des fonctions d’agrégation
L'exemple suivant utilise la clause OVER avec des fonctions d'agrégation sur toutes les lignes retournées par la requête. Dans cet exemple, l'utilisation de la clause OVER est plus efficace que l'utilisation de sous-requêtes pour dériver les valeurs d'agrégation.
USE AdventureWorks2022;
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 le jeu de 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 AdventureWorks2022;
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 le jeu de résultats. Les agrégats sont calculés par SalesOrderID et sont Percent by ProductID calculés pour chaque ligne de chaque SalesOrderIDligne .
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.4
43664 773 1 14 7.14
43664 778 1 14 7.14
C. Produire une moyenne mobile et un total cumulé
L’exemple suivant utilise les fonctions et SUM les AVG fonctions avec la OVER clause pour fournir une moyenne mobile et un total cumulé de ventes annuelles pour chaque territoire de la Sales.SalesPerson table. Les données sont partitionnées par TerritoryID et classées logiquement par SalesYTD. Cela signifie que la AVG fonction est calculée pour chaque territoire en fonction de l’année des ventes. Pour TerritoryID 1, il existe deux lignes pour l’année 2005 de vente représentant les deux vendeurs ayant des ventes cette année. Les ventes moyennes de ces deux lignes sont calculées, puis la troisième ligne représentant les ventes de l’année 2006 est incluse dans le calcul.
USE AdventureWorks2022;
GO
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear;
Voici le jeu de résultats.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
Dans cet exemple, la OVER clause n’inclut PARTITION BYpas . Cela signifie que la fonction est appliquée à toutes les lignes retournées par la requête. La ORDER BY clause spécifiée dans la OVER clause détermine l’ordre logique auquel la AVG fonction est appliquée. La requête retourne une moyenne mobile des ventes par année pour tous les territoires de vente spécifiés dans la WHERE clause. La ORDER BY clause spécifiée dans l’instruction SELECT détermine l’ordre dans lequel les lignes de la requête sont affichées.
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY SalesYear;
Voici le jeu de résultats.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93
D. Spécifier la clause ROWS
S’applique à : SQL Server 2012 (11.x) et versions ultérieures.
L’exemple suivant utilise la ROWS clause pour définir une fenêtre sur laquelle les lignes sont calculées comme ligne actuelle et le nombre N de lignes qui suivent (une ligne dans cet exemple).
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Voici le jeu de résultats.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62
Dans l’exemple suivant, la ROWS clause est spécifiée avec UNBOUNDED PRECEDING. Le résultat est que la fenêtre commence à la première ligne de la partition.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS UNBOUNDED PRECEDING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Voici le jeu de résultats.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 559,697.56
287 NULL 519,905.93 2006 1,079,603.50
285 NULL 172,524.45 2007 1,252,127.95
283 1 1,573,012.94 2005 1,573,012.94
280 1 1,352,577.13 2005 2,925,590.07
284 1 1,576,562.20 2006 4,502,152.27
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 4,251,368.55
281 4 2,458,535.62 2005 6,709,904.17
Exemples : Analytics Platform System (PDW)
E. Utiliser la clause OVER avec la fonction ROW_NUMBER
L’exemple suivant retourne la valeur ROW_NUMBER des représentants commerciaux en fonction de leur quota de ventes assigné.
SELECT ROW_NUMBER() OVER (ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName,
LastName,
CONVERT (VARCHAR (13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Voici un jeu de résultats partiel.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. Utiliser la clause OVER avec des fonctions d’agrégation
Les exemples suivants montrent l’utilisation de la OVER clause avec des fonctions d’agrégation. Dans cet exemple, l’utilisation de la OVER clause est plus efficace que l’utilisation de sous-requêtes.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
AVG(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS AVG,
COUNT(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS COUNT,
MIN(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MIN,
MAX(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MAX
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Voici le jeu de résultats.
OrderNumber Product Qty Total Avg Count Min Max
----------- ------- --- ----- --- ----- --- ---
SO43659 218 6 16 3 5 1 6
SO43659 220 4 16 3 5 1 6
SO43659 223 2 16 3 5 1 6
SO43659 229 3 16 3 5 1 6
SO43659 235 1 16 3 5 1 6
SO43664 229 1 2 1 2 1 1
SO43664 235 1 2 1 2 1 1
L'exemple suivant illustre l'utilisation de la clause OVER avec une fonction d'agrégation dans une valeur calculée. Les agrégats sont calculés par SalesOrderNumber et le pourcentage de la commande client totale est calculé pour chaque ligne de chaque SalesOrderNumberligne .
SELECT SalesOrderNumber AS OrderNumber,
ProductKey AS Product,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
CAST (1. * OrderQuantity / SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) * 100 AS DECIMAL (5, 2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Le premier début de ce jeu de résultats est le suivant :
OrderNumber Product Qty Total PctByProduct
----------- ------- --- ----- ------------
SO43659 218 6 16 37.50
SO43659 220 4 16 25.00
SO43659 223 2 16 12.50
SO43659 229 2 16 18.75