SELECT – Clause OVER (Transact-SQL)

• 11/08/2017
• 17 minutes de lecture
• • V
  • L
  • c
  • j
  • M
  • +11

Sapplique à: SQL Server (tous versions prises en charge) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

Détermine le partitionnement et lordre dun ensemble de lignes avant que la fonction de fenêtre associée ne soit appliquée. Autrement dit, la clause OVER définit une fenêtre ou un ensemble de lignes spécifié par lutilisateur dans un ensemble de résultats de requête. Une fonction de fenêtre calcule ensuite une valeur pour chaque ligne de la fenêtre. Vous pouvez utiliser la clause OVER avec des fonctions pour calculer des valeurs agrégées telles que des moyennes mobiles, des agrégats cumulés, des totaux en cours dexécution ou un N supérieur par résultats de groupe.

• Fonctions de classement

• Fonctions dagrégation

• Fonctions analytiques

• Fonction VALEUR SUIVANTE POUR

Conventions de syntaxe Transact-SQL

Syntaxe

Arguments

Les fonctions de fenêtre peuvent avoir les arguments suivants dans leur clause OVER:

• PARTITION BY qui divise lensemble de résultats de la requête en partitions.
• ORDER BY qui définit lordre logique des lignes dans chaque partition du jeu de résultats.
• ROWS / RANGE qui limite les lignes dans la partition en spécifiant les points de début et de fin dans la partition. Il nécessite largument ORDER BY et la valeur par défaut va du début de la partition à lélément actuel si largument ORDER BY est spécifié.

Si vous « ne spécifiez aucun argument, les fonctions de la fenêtre seront appliquées à lensemble du jeu de résultats.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Divise lensemble 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 partie ion.

Si PARTITION BY nest pas spécifié, la fonction traite toutes les lignes du jeu de résultats de la requête comme une seule partition. La fonction sera appliquée sur toutes les lignes dans la partition si vous ne spécifiez pas la clause ORDER BY.

PARTITION BY value_expression

Spécifie la colonne par laquelle lensemble de lignes est partitionné. value_expression ne peut faire référence quaux colonnes rendues disponibles par la clause FROM. value_expression ne peut pas faire référence à des expressions ou des alias dans la liste de sélection. value_expression peut être une expression de colonne, une sous-requête scalaire, une fonction scalaire ou une variable définie par lutilisateur.

ORDER BY

Définit lordre logique des lignes dans chaque partition du jeu de résultats. Autrement dit, il spécifie lordre logique dans lequel le calcul de la fonction de fenêtre est effectué.

• Sil nest pas spécifié, lordre par défaut est ASC et La fonction window utilisera toutes les lignes de la partition.
• Sil est spécifié et quaucun ROWS / RANGE nest spécifié, alors la valeur par défaut RANGE UNBOUNDED PRECEDING AND CURRENT ROW est utilisée par défaut pour window encadrer par les fonctions qui peuvent accepter la spécification optionnelle ROWS / RANGE (par exemple min ou max).

order_by_expression
Spécifie une colonne ou une expression sur laquelle trier. order_by_expression ne peut faire référence quaux colonnes rendues disponibles par la clause FROM. Un entier ne peut pas être spécifié pour représenter un nom de colonne ou un alias.

COLLATE collation_name
Spécifie que lopération ORDER BY doit être effectuée selon le classement spécifié dans collation_name. nom_collation peut être un nom de classement Windows ou un nom de classement SQL. Pour plus dinformations, consultez Collation et prise en charge Unicode. COLLATE ne sapplique quaux colonnes de type char, varchar, nchar et nvarchar.

ASC | DESC
Spécifie que les valeurs de la colonne spécifiée doivent être triées par ordre croissant ou décroissant. ASC est lordre de tri par défaut.Les valeurs nulles sont traitées comme les valeurs les plus basses possibles.

ROWS ou RANGE

Sapplique à: SQL Server 2012 (11.x) et versions ultérieures.

Autres limites les lignes dans la partition en spécifiant les points de début et de fin dans la partition. Cela se fait en spécifiant une plage de lignes par rapport à la ligne actuelle soit par association logique soit par association physique. Lassociation physique est réalisée en utilisant la clause ROWS.

La clause ROWS limite les lignes dune partition en spécifiant un nombre fixe de lignes précédant ou suivant la ligne courante. Sinon, la clause RANGE limite logiquement les lignes dune partition en spécifiant une plage de valeurs par rapport à la valeur de la ligne actuelle. Les lignes précédentes et suivantes sont définies en fonction de lordre dans la clause ORDER BY. Le cadre de fenêtre « RANGE … CURRENT ROW … » inclut toutes les lignes qui ont les mêmes valeurs dans lexpression ORDER BY que la ligne actuelle. Par exemple, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW signifie que la fenêtre de lignes sur laquelle la fonction opère a une taille de trois lignes, commençant par 2 lignes précédant et incluant la ligne actuelle.

UNBOUNDED PRECEDING

Sapplique à: 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 la fenêtre.

< spécification de valeur non signée > PRECEDING
Spécifié avec < spécification de valeur non signée > pour indiquer le nombre de lignes ou de valeurs devant précéder la ligne actuelle. Cette spécification nest pas autorisée pour RANGE.

CURRENT ROW

Sapplique à: SQL Server 2012 (11.x) et versions ultérieures.

Spécifie que la fenêtre démarre ou se termine à la ligne actuelle lorsquelle est utilisée avec ROWS ou à la valeur actuelle lorsquelle est utilisée avec RANGE. CURRENT ROW peut être spécifié comme point de départ et de fin.

BETWEEN AND

Sapplique à: SQL Server 2012 (11.x) et versions ultérieures.

Utilisé avec ROWS ou RANGE pour spécifier les points de limite inférieur (début) et supérieur (fin) de la fenêtre. < cadre de fenêtre lié > définit le point de départ de la limite et < cadre de fenêtre lié > définit le point final de la limite. La limite supérieure ne peut pas être inférieure à la limite inférieure.

UNBOUNDED FOLLOWING

Sapplique à: 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é que comme point de fin de fenêtre. Par exemple, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING définit une fenêtre qui commence par la ligne actuelle et se termine par la dernière ligne de la partition.

< spécification de valeur non signée > FOLLOWING
Spécifié avec < spécification de valeur non signée > pour indiquer le nombre de lignes ou valeurs pour suivre la ligne actuelle. Lorsque < spécification de valeur non signée > FOLLOWING est spécifié comme point de départ de la fenêtre, le point de fin doit être < spécification de valeur non signée > SUIVANT. 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 actuelle et se termine par la dixième ligne qui suit la ligne actuelle. Cette spécification nest pas autorisée pour RANGE.

Entier littéral non signé
Sapplique à: SQL Server 2012 (11.x) et versions ultérieures.

Est un littéral entier positif (y compris 0 ) qui spécifie le nombre de lignes ou de valeurs devant précéder ou suivre la ligne ou la valeur actuelle. Cette spécification nest valide que pour ROWS.

Remarques générales

Plus dune fonction de fenêtre peut être utilisée dans une seule requête avec une seule clause FROM. La clause OVER pour chaque fonction peut différer dans le partitionnement et lordre.

Si PARTITION BY nest pas spécifié, la fonction traite toutes les lignes du jeu de résultats de la requête comme un seul groupe.

Important !

Si ROWS / RANGE est spécifié et que < le cadre de fenêtre précédant > est utilisé pour < extension du cadre de la fenêtre > (syntaxe courte) alors cette spécification est utilisée pour le point de départ de la limite du cadre de la fenêtre et la LIGNE COURANTE est utilisée pour le point de fin de la limite. Par exemple « ROWS 5 PRECEDING » est égal à « ROWS BETWEEN 5 PRECEDING AND ACURRENT ROW ».

Limitations et restrictions

La clause OVER ne peut pas être utilisée avec la fonction dagrégation CHECKSUM.

RANGE ne peut pas être utilisée avec < spécification de valeur non signée > PRECEDING ou < spécification de valeur non signée > FOLLOWING.

En fonction du classement, de lagrégat ou de lanalyse fonction utilisée avec la clause OVER, < clause ORDER BY > et / ou < Les clauses ROWS et RANGE > peuvent ne pas être prises en charge.

Exemples

A. Utilisation de la clause OVER avec la fonction ROW_NUMBER

Lexemple suivant montre lutilisation de la clause OVER avec la fonction ROW_NUMBER pour afficher un numéro de ligne pour chaque ligne dune partition. La clause ORDER BY spécifiée dans la clause OVER classe les lignes de chaque partition selon la colonne SalesYTD. La clause ORDER BY dans linstruction SELECT détermine lordre dans lequel lensemble de résultats de la requête est renvoyé.

Voici lensemble de résultats.

B. Utilisation de la clause OVER avec des fonctions dagrégation

Lexemple suivant utilise la clause OVER avec des fonctions dagrégation sur toutes les lignes renvoyées par la requête. Dans cet exemple, lutilisation de la clause OVER est plus efficace que lutilisation de sous-requêtes pour dériver les valeurs agrégées.

Voici lensemble de résultats.

Lexemple suivant montre lutilisation de la clause OVER avec une fonction dagrégation dans une valeur calculée.

Voici le jeu de résultats. Notez que les agrégats sont calculés par SalesOrderID et que Percent by ProductID est calculé pour chaque ligne de chaque SalesOrderID.

C. Production dune moyenne mobile et dun total cumulé

Lexemple 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 territoire dans le Sales.SalesPerson. Les données sont partitionnées par TerritoryID et classées logiquement par SalesYTD. Cela signifie que la fonction AVG est calculée pour chaque territoire en fonction de lannée de vente. Notez que pour TerritoryID 1, il y a deux lignes pour lannée de vente 2005 représentant les deux commerciaux avec des ventes cette année-là. Les ventes moyennes pour ces deux lignes sont calculées, puis la troisième ligne représentant les ventes pour lannée 2006 est incluse dans le calcul.

Voici lensemble de résultats.

Dans cet exemple, la clause OVER ninclut pas PARTITION BY. Cela signifie que la fonction sera appliquée à toutes les lignes renvoyées par la requête. La clause ORDER BY spécifiée dans la clause OVER détermine lordre logique auquel la fonction AVG est appliquée. La requête renvoie une moyenne mobile des ventes par année pour tous les territoires de vente spécifiés dans la clause WHERE. La clause ORDER BY spécifiée dans linstruction SELECT détermine lordre dans lequel les lignes de la requête sont affichées.

Voici le jeu de résultats.

D. Spécification de la clause ROWS

Sapplique à: SQL Server 2012 (11.x) et versions ultérieures.

Lexemple suivant utilise la clause ROWS pour définir une fenêtre sur laquelle les lignes sont calculées comme la ligne actuelle et le nombre N de lignes qui suivent (1 ligne dans cet exemple).

Voici lensemble de résultats.

Dans lexemple suivant, la clause ROWS est spécifiée avec UNBOUNDED PRECEDING. Le résultat est que la fenêtre commence à la première ligne de la partition.

Voici le jeu de résultats.

Exemples: Parallel Data Warehouse

E. Utilisation de la clause OVER avec la fonction ROW_NUMBER

Lexemple suivant renvoie le ROW_NUMBER pour les commerciaux en fonction de leur quota de vente attribué.

Voici un ensemble de résultats partiel.

F. Utilisation de la clause OVER avec des fonctions dagrégation

Les exemples suivants illustrent lutilisation de la clause OVER avec des fonctions dagrégation. Dans cet exemple, lutilisation de la clause OVER est plus efficace que lutilisation de sous-requêtes.

Voici lensemble de résultats.

Lexemple suivant montre lutilisation de la clause OVER avec une fonction dagrégation dans une valeur calculée. Notez que les agrégats sont calculés par SalesOrderNumber et le pourcentage du total de la commande client est calculé pour chaque ligne de chaque SalesOrderNumber.

Le premier début de cet ensemble de résultats est:

Voir aussi

Fonctions dagrégation (Transact-SQL)
Fonctions analytiques (Transact-SQL)
Excellent article de blog sur les fonctions des fenêtres et OVER, sur sqlmag.com, par Itzik Ben-Gan