SELECT - Clause OVER (Transact-SQL)SELECT - OVER Clause (Transact-SQL)

Cette rubrique s’applique à : OuiSQL Server (à partir de 2008)Ouibase de données SQL AzureOuiAzure SQL Data Warehouse Oui Parallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Détermine le partitionnement et l'ordre d'un ensemble de lignes avant l'application de la fonction de fenêtre associée.Determines the partitioning and ordering of a rowset before the associated window function is applied. Autrement dit, la clause OVER définit une fenêtre ou un ensemble de lignes spécifié par l'utilisateur dans un jeu de résultats de requête.That is, the OVER clause defines a window or user-specified set of rows within a query result set. Une fonction de fenêtre calcule ensuite une valeur pour chaque ligne dans la fenêtre.A window function then computes a value for each row in the window. Vous pouvez utiliser la clause OVER avec des fonctions pour calculer des valeurs agrégées telles que les moyennes mobiles, les agrégats cumulatifs, des cumuls ou les N premières lignes par groupe de résultats.You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

SyntaxeSyntax

-- Syntax for SQL Server, Azure SQL Database, and Azure SQL Data Warehouse  

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> }  
-- Syntax for Parallel Data Warehouse  

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )  

ArgumentsArguments

PARTITION BYPARTITION BY
Divise le jeu de résultats de la requête en partitions.Divides the query result set into partitions. La fonction de fenêtre est appliquée à chaque partition séparément et le calcul redémarre pour chaque partition.The window function is applied to each partition separately and computation restarts for each partition.

value_expressionvalue_expression
Spécifie la colonne par laquelle l'ensemble de lignes est partitionné.Specifies the column by which the rowset is partitioned. value_expression peut uniquement référencer des colonnes mises à disposition par la clause FROM.value_expression can only refer to columns made available by the FROM clause. value_expression ne peut pas référencer des expressions ou des alias dans la liste de sélection.value_expression cannot refer to expressions or aliases in the select list. value_expression peut être une expression de colonne, une sous-requête scalaire, une fonction scalaire ou une variable définie par l’utilisateur.value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<ORDER BY clause><ORDER BY clause>
Définit l'ordre logique des lignes dans chaque partition du jeu de résultats.Defines the logical order of the rows within each partition of the result set. Autrement dit, il spécifie l'ordre logique dans lequel le calcul de la fonction de la fenêtre est effectué.That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expressionorder_by_expression
Spécifie une colonne ou une expression dans lesquelles trier.Specifies a column or expression on which to sort. order_by_expression peut uniquement référencer des colonnes mises à disposition par la clause FROM.order_by_expression can only refer to columns made available by the FROM clause. Un entier ne peut pas être spécifié pour représenter un nom de colonne ou un alias.An integer cannot be specified to represent a column name or alias.

COLLATE collation_nameCOLLATE collation_name
Spécifie que l’opération ORDER BY doit être exécutée selon le classement spécifié dans collation_name.Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. collation_name peut être un nom de classement Windows ou SQL.collation_name can be either a Windows collation name or a SQL collation name. Pour plus d’informations, consultez Prise en charge d’Unicode et du classement.For more information, see Collation and Unicode Support. COLLATE est applicable uniquement aux colonnes de types char, varchar, nchar et nvarchar.COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESCASC | DESC
Spécifie que les valeurs dans la colonne spécifiée doivent être triées par ordre croissant ou décroissant.Specifies that the values in the specified column should be sorted in ascending or descending order. ASC correspond à l'ordre de tri par défaut.ASC is the default sort order. Les valeurs NULL sont traitées comme les plus petites valeurs possibles.Null values are treated as the lowest possible values.

ROWS | RANGEROWS | RANGE
S'applique à: SQL Server 2012 (11.x)SQL Server 2012 (11.x) jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Limite davantage les lignes dans la partition en spécifiant les points de départ et de terminaison dans la partition.Further limits the rows within the partition by specifying start and end points within the partition. Cette opération s'effectue en spécifiant une plage de lignes par rapport à la ligne actuelle par association logique ou association physique.This is done by specifying a range of rows with respect to the current row either by logical association or physical association. L'association physique est réalisée en utilisant la clause ROWS.Physical association is achieved by using the ROWS clause.

La clause ROWS limite les lignes dans une partition en spécifiant un nombre fixe de lignes précédant ou suivant la ligne actuelle.The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Également, la clause RANGE limite logiquement les lignes dans une partition en spécifiant une plage de valeurs par rapport à la valeur de la ligne actuelle.Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. Les lignes précédentes et suivantes sont définies en fonction de l'organisation dans la clause ORDER BY.Preceding and following rows are defined based on the ordering in the ORDER BY clause. Le frame de fenêtre « RANGE …The window frame “RANGE … CURRENT ROW… »CURRENT ROW …” inclut toutes les lignes qui ont les mêmes valeurs dans l’expression ORDER BY que la ligne actuelle.includes all rows that have the same values in the ORDER BY expression as the current row. Par exemple, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW signifie que la fenêtre de lignes traitées par la fonction comprend trois lignes, en commençant par les deux lignes qui précèdent la ligne actuelle (ligne actuelle comprise).For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.

Note

ROWS ou RANGE requièrent que la clause ORDER BY soit spécifiée.ROWS or RANGE requires that the ORDER BY clause be specified. Si ORDER BY contient plusieurs expressions d'ordre, CURRENT ROW FOR RANGE prend en compte toutes les colonnes dans la liste ORDER BY lors de la détermination de la ligne actuelle.If ORDER BY contains multiple order expressions, CURRENT ROW FOR RANGE considers all columns in the ORDER BY list when determining the current row.

UNBOUNDED PRECEDINGUNBOUNDED PRECEDING
S'applique à: SQL Server 2012 (11.x)SQL Server 2012 (11.x) jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Spécifie que la fenêtre commence à la première ligne de la partition.Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING peut être spécifié comme point de départ de la fenêtre.UNBOUNDED PRECEDING can only be specified as window starting point.

<unsigned value specification> PRECEDING<unsigned value specification> PRECEDING
Spécifié avec <unsigned value specification> pour indiquer le nombre de lignes ou de valeurs qui précèdent la ligne actuelle.Specified with <unsigned value specification>to indicate the number of rows or values to precede the current row. Cette spécification n'est pas autorisée pour RANGE.This specification is not allowed for RANGE.

CURRENT ROWCURRENT ROW
S'applique à: SQL Server 2012 (11.x)SQL Server 2012 (11.x) jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Spécifie que la fenêtre commence ou se termine à la ligne actuelle en cas d'utilisation avec ROWS ou à la valeur actuelle en cas de utilisation avec RANGE.Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW peut être spécifié comme point de départ et de fin.CURRENT ROW can be specified as both a starting and ending point.

BETWEEN <window frame bound > AND <window frame bound >BETWEEN <window frame bound > AND <window frame bound >
S'applique à: SQL Server 2012 (11.x)SQL Server 2012 (11.x) jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Utilisé avec ROWS ou RANGE pour spécifier les points limite inférieurs (départ) et supérieurs (fin) de la fenêtre.Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. <window frame bound> définit le point de départ limite et <window frame bound> définit le point de fin limite.<window frame bound> defines the boundary starting point and <window frame bound> defines the boundary end point. La limite supérieure ne peut pas être inférieure à la limite inférieure.The upper bound cannot be smaller than the lower bound.

UNBOUNDED FOLLOWINGUNBOUNDED FOLLOWING
S'applique à: SQL Server 2012 (11.x)SQL Server 2012 (11.x) jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Spécifie que la fenêtre se termine à la dernière ligne de la partition.Specifies that the window ends at the last row of the partition. FOLLOWING UNBOUNDED peut être spécifié comme point de fin de fenêtre.UNBOUNDED FOLLOWING can only be specified as a window end point. Par exemple RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING définit une fenêtre qui commence par la ligne actuelle et se termine à la dernière ligne de la partition.For example RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING defines a window that starts with the current row and ends with the last row of the partition.

<unsigned value specification> FOLLOWING<unsigned value specification> FOLLOWING
Spécifié avec <unsigned value specification> pour indiquer le nombre de lignes ou de valeurs qui suivent la ligne actuelle.Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. Quand <unsigned value specification> FOLLOWING est spécifié comme point de départ de la fenêtre, le point de fin doit être <unsigned value specification> FOLLOWING.When <unsigned value specification> FOLLOWING is specified as the window starting point, the ending point must be <unsigned value specification>FOLLOWING. Par exemple, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING définit une fenêtre qui commence avec la deuxième ligne qui suit la ligne actuelle et se termine par la dixième ligne qui suit la ligne actuelle.For example, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING defines a window that starts with the second row that follows the current row and ends with the tenth row that follows the current row. Cette spécification n'est pas autorisée pour RANGE.This specification is not allowed for RANGE.

littéral entier non signéunsigned integer literal
S'applique à: SQL Server 2012 (11.x)SQL Server 2012 (11.x) jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Est un littéral entier positif (comprenant 0) qui spécifie le nombre de lignes ou de valeurs qui précèdent ou suivent la ligne ou la valeur actuelle.Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. Cette condition est valide uniquement pour ROWS.This specification is valid only for ROWS.

Remarques d'ordre généralGeneral Remarks

Vous pouvez utiliser plusieurs fonctions de fenêtre dans une seule requête avec une seule clause FROM.More than one window function can be used in a single query with a single FROM clause. La clause OVER de chaque fonction peut être différente en termes de partitionnement et de tri.The OVER clause for each function can differ in partitioning and ordering.

Si PARTITION BY n'est pas spécifié, la fonction gère toutes les lignes du jeu de résultats de la requête en un seul groupe.If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

Important !Important!

Si ROWS/RANGE est spécifié et que <window frame preceding> est utilisé pour <window frame extent> (syntaxe courte), cette spécification est utilisée comme point de départ limite et CURRENT ROW comme point de fin limite du frame de la fenêtre.If ROWS/RANGE is specified and <window frame preceding> is used for <window frame extent> (short syntax) then this specification is used for the window frame boundary starting point and CURRENT ROW is used for the boundary ending point. Par exemple « ROWS 5 PRECEDING » est égal à « ROWS BETWEEN 5 PRECEDING AND CURRENT ROW ».For example “ROWS 5 PRECEDING” is equal to “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.

Note

Si ORDER BY n'est pas spécifié, la partition entière est utilisée pour un cadre de fenêtre.If ORDER BY is not specified entire partition is used for a window frame. Cela s'applique uniquement aux fonctions qui ne nécessitent pas la clause ORDER BY.This applies only to functions that do not require ORDER BY clause. Si ROWS/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.If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. Cela s'applique uniquement aux fonctions qui acceptent la spécification facultative ROWS/RANGE.This applies only to functions that have can accept optional ROWS/RANGE specification. Par exemple, les fonctions de classement n'acceptent pas ROWS/RANGE, par conséquent ce cadre de fenêtre n'est pas appliqué même si ORDER BY est présent et ROWS/RANGE ne l'est pas.For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.

Limitations et restrictionsLimitations and Restrictions

La clause OVER ne peut pas être utilisée avec la fonction d'agrégation CHECKSUM.The OVER clause cannot be used with the CHECKSUM aggregate function.

RANGE ne peut pas être utilisé avec <unsigned value specification> PRECEDING ou <unsigned value specification> FOLLOWING.RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.

Selon la fonction de classement, d’agrégation ou analytique utilisée avec la clause OVER, <ORDER BY clause> et/ou <ROWS et RANGE clause> peuvent ne pas être pris en charge.Depending on the ranking, aggregate, or analytic function used with the OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not be supported.

ExemplesExamples

A.A. Utilisation de la clause OVER avec la fonction ROW_NUMBERUsing the OVER clause with the ROW_NUMBER function

L'exemple suivant montre comment utiliser la clause OVER avec la fonction ROW_NUMBER pour afficher un numéro de ligne pour chaque ligne dans une partition.The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. La clause ORDER BY spécifiée dans la clause OVER trie les lignes dans chaque partition par la colonne SalesYTD.The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. La clause ORDER BY dans l'instruction SELECT détermine l'ordre dans lequel la totalité du jeu de résultats de la requête est retournée.The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned.

USE AdventureWorks2012;  
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 des résultats.Here is the result set.

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.B. Utilisation de la clause OVER avec des fonctions d'agrégationUsing the OVER clause with aggregate functions

L'exemple suivant utilise la clause OVER avec des fonctions d'agrégation sur toutes les lignes retournées par la requête.The following example uses the OVER clause with aggregate functions over all rows returned by the query. 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.In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.

USE AdventureWorks2012;  
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 des résultats.Here is the result set.

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.The following example shows using the OVER clause with an aggregate function in a calculated value.

USE AdventureWorks2012;  
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 des résultats.Here is the result set. Notez que les agrégations sont calculées par SalesOrderID et que le pourcentage Percent by ProductID est calculé pour chaque ligne de SalesOrderID. Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each 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.4  
43664        773         1        14          7.14  
43664        778         1        14          7.14  

 (20 row(s) affected)  

C.C. Production d'une moyenne mobile et d'un total cumuléProducing a moving average and cumulative total

L'exemple suivant utilise les fonctions AVG et SUM avec la clause OVER pour fournir une moyenne mobile et un total cumulé des ventes annuelles pour chaque secteur dans la table Sales.SalesPerson.The following example uses the AVG and SUM functions with the OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the Sales.SalesPerson table. Les données sont partitionnées par TerritoryID et classées logiquement par SalesYTD.The data is partitioned by TerritoryID and logically ordered by SalesYTD. Cela signifie que la fonction AVG est calculée pour chaque secteur selon l'année de vente.This means that the AVG function is computed for each territory based on the sales year. Notez que pour TerritoryID 1, il y a deux lignes pour l'année 2005 représentant les deux vendeurs avec leurs ventes de l'année.Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. Les ventes moyennes pour ces deux lignes sont calculées et la troisième ligne représentant les ventes de l'année 2006 est incluse dans le calcul.The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.

USE AdventureWorks2012;  
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 des résultats.Here is the result set.

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  

(10 row(s) affected)  

Dans cet exemple, la clause OVER n'inclut pas PARTITION BY.In this example, the OVER clause does not include PARTITION BY. Cela signifie que la fonction sera appliquée à toutes les lignes retournées par la requête.This means that the function will be applied to all rows returned by the query. La clause ORDER BY spécifiée dans la clause OVER détermine l'ordre logique selon lequel la fonction AVG est appliquée.The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. La requête retourne une moyenne mobile des ventes par année pour tous les secteurs de vente spécifiés dans la clause WHERE.The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. La clause ORDER BY spécifiée dans l'instruction SELECT détermine l'ordre dans lequel les lignes de la requête sont affichées.The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.

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 des résultats.Here is the result set.

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  
(10 row(s) affected)  

D.D. Spécification de la clause ROWSSpecifying the ROWS clause

S'applique à: SQL Server 2012 (11.x)SQL Server 2012 (11.x) jusqu'à SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

L’exemple suivant utilise la clause ROWS pour définir une fenêtre de calcul des lignes comprenant la ligne actuelle et les N lignes qui suivent (une seule ligne dans cet exemple).The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).

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 des résultats.Here is the result set.

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 clause ROWS est spécifiée avec UNBOUNDED PRECEDING.In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. Le résultat est que la fenêtre commence à la première ligne de la partition.The result is that the window starts at the first row of the 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 des résultats.Here is the result set.

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 : Parallel Data WarehouseParallel Data WarehouseExamples: Parallel Data WarehouseParallel Data Warehouse

E.E. Utilisation de la clause OVER avec la fonction ROW_NUMBERUsing the OVER clause with the ROW_NUMBER function

L’exemple suivant retourne le ROW_NUMBER des représentants commerciaux en fonction de leur quota de ventes assigné.The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

-- Uses AdventureWorks  

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.Here is a partial result set.

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.F. Utilisation de la clause OVER avec des fonctions d'agrégationUsing the OVER clause with aggregate functions

Les exemples ci-dessous illustrent l’utilisation de la clause OVER avec des fonctions d’agrégation.The following examples show using the OVER clause with aggregate functions. Dans cet exemple, l’utilisation de la clause OVER s’avère plus efficace que d’utiliser des sous-requêtes.In this example, using the OVER clause is more efficient than using subqueries.

-- Uses AdventureWorks  

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 des résultats.Here is the result set.

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.The following example shows using the OVER clause with an aggregate function in a calculated value. Notez que les agrégations sont calculées par SalesOrderNumber et que le pourcentage des commandes totales est calculé pour chaque ligne de chaque SalesOrderNumber.Notice that the aggregates are calculated by SalesOrderNumber and the percentage of the total sales order is calculated for each line of each SalesOrderNumber.

-- Uses AdventureWorks  

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 début de ce jeu de résultats est le suivant :The first start of this result set is:

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  

Voir aussiSee Also

Fonctions d’agrégation (Transact-SQL) Aggregate Functions (Transact-SQL)
Fonctions analytiques (Transact-SQL) Analytic Functions (Transact-SQL)
Excellent billet de blog sur les fonctions de fenêtre et OVER publié sur sqlmag.com par Itzik Ben-GanExcellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan