SELECT - CLAUSE OVER (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison d’analytique SQL dans Microsoft FabricEntrepôt dans Microsoft FabricBase 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.

• Fonctions de classement
• Fonctions d’agrégation
• Fonctions analytiques
• NEXT VALUE FOR

Conventions de la syntaxe Transact-SQL

Syntax

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 uniquement pour Analytics Platform System (PDW) :

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Arguments

Les fonctions fenêtre peuvent inclure les arguments suivants dans leur OVER proposition :

• PARTITION BY divise l’ensemble de résultats de requête en partitions.

• ORDER BY définit l’ordre logique des lignes dans chaque partition de l’ensemble des résultats.

• ROWS ou RANGE limite les lignes à l’intérieur de la partition en spécifiant les points de début et de fin dans la partition. Cela nécessite une ORDER BY dispute. Si vous spécifiez ORDER BY, la valeur par défaut va du début de la partition à l’élément courant.

Si vous ne spécifiez aucun argument, les fonctions fenêtre s’appliquent à l’ensemble des résultats.

SELECT object_id,
       MIN(object_id) OVER () AS [min],
       MAX(object_id) OVER () AS [max]
FROM sys.objects;

PARTITION PAR

Divise le jeu de résultats de la requête en partitions. La fonction fenêtre s’applique à chaque partition séparément, et le calcul redémarre pour chaque partition.

PARTITION BY <value_expression>

Si vous ne spécifiez PARTITION BYpas, la fonction traite toutes les lignes du jeu de résultats de la requête en tant que partition unique.

Si vous ne spécifiez pas de ORDER BY clause, la fonction s’applique à toutes les lignes de la partition.

PARTITION PAR 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;

ORDONNER PAR

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. Il spécifie l’ordre logique dans lequel le calcul de la fonction fenêtre est effectué.

• Si vous ne spécifiez pas d’ordre, l’ordre par défaut est ASC et la fonction de fenêtre utilise toutes les lignes de la partition.

• Si vous spécifiez un ordre, mais que vous ne spécifiez ROWS pas ou RANGEque les fonctions qui peuvent accepter une spécification facultative ROWSRANGE (par exemple, MIN ou MAX) sont utilisées RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW comme cadre de fenêtre par défaut.

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;

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. Vous ne pouvez pas spécifier d’entier 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. NULL les valeurs sont les valeurs possibles les plus basses.

ROWS ou RANGE

S’applique à : SQL Server 2012 (11.x) et versions ultérieures.

Ces options limitent davantage les lignes de la partition en spécifiant les points de début et de fin dans la partition. Vous spécifiez une plage de lignes par rapport à la ligne actuelle par association logique ou association physique. Vous obtenez une association physique à 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 lesquelles la fonction fonctionne est de trois lignes de taille, en commençant par deux 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;

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.

PRÉCÉDÉ ILLIMITÉ

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. Vous ne pouvez spécifier UNBOUNDED PRECEDING que le point de départ de la fenêtre.

<spécification> de valeur non signée PRÉCÉDENT

Spécifiez avec <unsigned value specification> pour indiquer le nombre de lignes ou de valeurs à précéder de la ligne active. Cette spécification n’est pas autorisée pour RANGE.

RANGÉE ACTUELLE

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. Vous pouvez spécifier CURRENT ROW comme point de départ et de fin.

ENTRE ET

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.

SUIVI ILLIMITÉ

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. Vous ne pouvez spécifier UNBOUNDED FOLLOWING qu’un 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.

<spécification de> valeur non signée SUIVANT

Spécifiez avec <unsigned value specification> pour indiquer le nombre de lignes ou de valeurs à suivre. Lorsque vous spécifiez <unsigned value specification> FOLLOWING 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.

Remarks

Vous pouvez utiliser plusieurs fonctions de fenêtre dans une seule requête avec une clause unique FROM . La OVER clause de chaque fonction peut différer dans le partitionnement et l’ordre.

Si vous ne spécifiez PARTITION BYpas, la fonction traite toutes les lignes du jeu de résultats de la requête en tant que groupe unique.

Si vous ne spécifiez ORDER BYpas, la partition entière est utilisée pour un cadre de fenêtre. Cette règle s’applique uniquement aux fonctions qui ne nécessitent pas de ORDER BY clause. Si vous ne spécifiez pas ou ROWS si vous le spécifiez RANGEORDER BY, RANGE UNBOUNDED PRECEDING AND CURRENT ROW est utilisé comme valeur par défaut pour le cadre de fenêtre. Cette règle s’applique uniquement aux fonctions qui peuvent accepter une spécification ou ROWS facultativeRANGE. Par exemple, les fonctions de classement ne peuvent pas accepter ROWS ou RANGE, de sorte que ce cadre de fenêtre n’est pas appliqué même s’il ORDER BY est présent et ROWS ou RANGE n’est pas.

Limitations

Vous ne pouvez pas utiliser la OVER clause avec les DISTINCT agrégations.

Vous ne pouvez pas utiliser RANGE avec <unsigned value specification> PRECEDING ou <unsigned value specification> FOLLOWING.

La prise en charge de ORDER BY la clause et des ROWSRANGE clauses dépend du classement, de l’agrégation ou de la fonction analytique que vous utilisez avec la OVER clause.

Considérations relatives aux performances

Avec les fonctions de fenêtre, le SQL Moteur de base de données est souvent chargé de partitionner et de trier de grands ensembles de données pour des requêtes analytiques complexes. Utilisez les techniques suivantes pour rendre les requêtes avec des fonctions fenêtre efficaces.

Fournir un indice de soutien

Pour les requêtes à forte valeur ajoutée ou fréquemment exécutées utilisant des fonctions fenêtre, envisagez de créer un nouvel index non regroupé. Pour bénéficier d’une requête utilisant une fonction fenêtre, la position des colonnes clés dans le nouvel index non regroupé doit correspondre aux PARTITION BY colonnes qui suivent les ORDER BY colonnes, le cas échéant. Si une ORDER BY clause est présente, l’ordre des colonnes clés d’index doit également correspondre à l’ordre spécifié dans la ORDER BY proposition. Par exemple:

SELECT CustomerID,
       OrderDate,
       SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM Sales.SalesOrderHeader;

Cette requête pourrait bénéficier de cet index non clusteré de rowstore :

CREATE INDEX IX_SalesOrderHeader_Customer_OrderDate
    ON Sales.SalesOrderHeader (CustomerID, OrderDate)
    INCLUDE (TotalDue);

Bénéfice de l’exécution en mode batch

Les opérateurs d’agrégat de fenêtres peuvent fonctionner plus rapidement en mode batch qu’en mode ligne. Avec le traitement en mode batch, les opérateurs de requête travaillent sur des lots de lignes au lieu d’une ligne à la fois. Le mode batch est possible dans les cas suivants :

• La requête fait référence à un tableau qui possède un index de colonnal. Pour plus d’informations, consultez les index Columnstore - Performances des requêtes.
• La requête s’exécute sur des tables rowstore (tas ou arbre B+) avec un niveau de compatibilité de base de données 150 ou supérieur, à partir de SQL Server 2019 (15.x).

Vous ne pouvez pas forcer une requête à utiliser le mode batch. Le SQL Moteur de base de données l’utilise lorsque c’est possible et jugé bénéfique. Un ensemble d’opérateurs de plan d’exécution peut utiliser le mode batch pour les objets rowstore et columnstore. Pour confirmer que le mode batch est utilisé, cherchez l’opérateur dans le plan d’exécution réel, comme Window Aggregate, et vérifiez que la propriété de Actual Execution Mode l’opérateur est Batch. Pour plus d’informations sur les opérateurs de plans d’exécution, voir Référence des opérateurs de plans de présentation logiques et physiques.

Évitez les déversements de tri

Une cardinalité sous-estimée peut faire en sorte que l’opération de tri nécessite plus de mémoire au moment de l’exécution. Ce processus peut augmenter le coût des requêtes. Pour atténuer les déversements :

• Assurez-vous que les statistiques couvrent les colonnes de partitionnement et d’ordre. Assurez-vous que ces statistiques sont à jour. Pour plus d’informations, consultez Statistiques.
• Assurez-vous que le retour d’information de la Mémoire est activé. Le retour d’information sur la reconnaissance de mémoire aide l’optimiseur de requêtes à ajuster les accords de mémoire lors des exécutions ultérieures. Pour garantir que vos charges de travail sont automatiquement éligibles aux retours de la subvention de mémoire, utilisez le niveau de compatibilité de base de données 140 ou supérieur. Quand il est activé, ce paramètre apparaît comme étant activé dans sys.database_scoped_configurations.
• Réduisez le nombre de lignes d’entrée avec WHERE des filtres.

Examples

Les exemples de code de cet article utilisent l’exemple de base de données AdventureWorks2025 ou AdventureWorksDW2025, que vous pouvez télécharger à partir de la page d’accueil Microsoft SQL Server Samples and Community Projects.

A. Utiliser la clause OVER avec la fonction ROW_NUMBER

L’exemple suivant montre comment utiliser la OVER clause avec la ROW_NUMBER fonction pour afficher un numéro de ligne pour chaque ligne d’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 AdventureWorks2025;
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 AdventureWorks2025;
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 montre comment utiliser la OVER clause avec une fonction d’agrégation dans une valeur calculée.

USE AdventureWorks2025;
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 AVG les SUM 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. La requête partitionne les données par TerritoryID et les trie logiquement par SalesYTD. Cette utilisation de OVER signifie que la AVG fonction est calculée pour chaque territoire en fonction de l’année de vente. Pour TerritoryID , 1deux lignes existent pour l’année 2022des ventes , représentant les deux vendeurs avec 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 2023 est incluse dans le calcul.

USE AdventureWorks2025;
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        2021        559,697.56           559,697.56           559,697.56
287              NULL        2023        519,905.93           539,801.75           1,079,603.50
285              NULL        2024        172,524.45           417,375.98           1,252,127.95
283              1           2022        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2022        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2023        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2022        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2022        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2022        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2022        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        2021        559,697.56           559,697.56           559,697.56
275              2           2022        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2022        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2022        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2022        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2022        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2022        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2023        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2023        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2024        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           2021        1,079,603.50
287              NULL        519,905.93           2023        692,430.38
285              NULL        172,524.45           2024        172,524.45
283              1           1,573,012.94         2022        2,925,590.07
280              1           1,352,577.13         2022        2,929,139.33
284              1           1,576,562.20         2023        1,576,562.20
275              2           3,763,178.18         2022        3,763,178.18
277              3           3,189,418.37         2022        3,189,418.37
276              4           4,251,368.55         2022        6,709,904.17
281              4           2,458,535.62         2022        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           2021        559,697.56
287              NULL        519,905.93           2023        1,079,603.50
285              NULL        172,524.45           2024        1,252,127.95
283              1           1,573,012.94         2022        1,573,012.94
280              1           1,352,577.13         2022        2,925,590.07
284              1           1,576,562.20         2023        4,502,152.27
275              2           3,763,178.18         2022        3,763,178.18
277              3           3,189,418.37         2022        3,189,418.37
276              4           4,251,368.55         2022        4,251,368.55
281              4           2,458,535.62         2022        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

Contenu connexe

• Fonctions d'agrégation (Transact-SQL)
• Fonctions analytiques (Transact-SQL)
• Excellent billet de blog sur les fonctions de fenêtre et OVER publié sur sqlmag.com par Itzik Ben-Gan