La clause OVER a été ajoutée à SQL Server «de retour» dans SQL Server 2005, et elle a été développée dans SQL Serveur 2012. Il est principalement utilisé avec les « Fonctions Fenêtre »; la seule exception étant la fonction de séquence NEXT VALUE FOR. La clause OVER est utilisée pour déterminer quelles lignes de la requête sont appliquées à la fonction, dans quel ordre elles sont évaluées par cette fonction et quand les calculs de la fonction doivent redémarrer. Comme il est utilisé en conjonction avec d’autres fonctions, et que cet article ne concerne spécifiquement que la clause OVER, ces fonctions ne seront abordées que dans la mesure où elle se rapporte à la clause OVER dans les exemples donnés.

La syntaxe de la clause OVER est:

<function> OVER ( )

En regardant la syntaxe, il apparaît que toutes les sous-clauses sont facultatives. En fait, chaque fonction qui peut utiliser la clause OVER détermine laquelle des sous-clauses sont autorisées et lesquelles sont obligatoires. Selon la fonction utilisée, la clause OVER elle-même peut être facultative. Il y a un graphique à la fin de cet article qui montre quelles fonctions autorisent / nécessitent quelles parties de la clause OVER.

La clause PARTITION BY est utilisée pour diviser l’ensemble de résultats de la requête en sous-ensembles de données, ou partitions. Si la clause PARTITION BY n’est pas utilisée, l’ensemble des résultats de la requête est la partition qui sera utilisée. La fonction de fenêtre utilisée est appliquée à chaque partition séparément et le calcul effectué par la fonction est redémarré pour chaque partition. Vous définissez un ensemble de valeurs qui déterminent la ou les partitions dans lesquelles diviser la requête. Ces valeurs peuvent être des colonnes, des fonctions scalaires, des sous-requêtes scalaires ou des variables.

Par exemple, examinons la requête suivante:

SELECT COUNT(*)FROM .sys.indexes;

Cette requête renvoie l’ensemble de résultats suivant:

Il s’agit simplement du nombre de lignes renvoyées par la requête – dans ce cas, le nombre d’index dans la base de données msdb. Ajoutons maintenant la clause OVER à cette requête:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

Les résultats abrégés sont:

Cette requête renvoie les object_id et index_id pour chaque index, ainsi que le nombre total d’index dans l’ensemble de résultats. Puisqu’une clause PARTITION BY n’a pas été utilisée, l’ensemble du jeu de résultats a été traité comme une seule partition. Il est maintenant temps d’ajouter la clause PARTITION BY et de voir comment cela change les résultats:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

Les résultats abrégés sont:

Cette requête retourne une ligne pour chaque index, mais maintenant la requête spécifie une clause PARTITION BY de la colonne object_id, donc la fonction count renvoie le nombre d’index sur cet object_id particulier. La clause ORDER BY contrôle l’ordre dans lequel les lignes sont évaluées par la fonction. Cela sera démontré sous peu. La clause ROWS ou RANGE détermine le sous-ensemble de lignes dans la partition qui doit être appliqué à la fonction. Lorsque vous utilisez ROWS ou RANGE, vous spécifiez le point de début et de fin de la fenêtre. Les valeurs autorisées sont:

Il existe deux syntaxes pour spécifier la fenêtre:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Si seule l’image de début est spécifiée, l’image de fin par défaut est CURRENT ROW.

Le mot clé UNBOUNDED spécifie le début de la partition (pour PRECEDING), ou la fin de la partition (pour SUIVANT). CURRENT ROW spécifie que la ligne actuelle est soit le début de la fenêtre, soit la fin de la fenêtre, selon la position du cadre de la fenêtre dans laquelle elle est utilisée. « N » spécifie un nombre de lignes avant la ligne actuelle (pour PRECEDING ), ou après la ligne courante (pour FOLLOWING) à utiliser pour le cadre de la fenêtre.

Voici les spécifications de fenêtre valides:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Pour utiliser la clause ROWS ou RANGE, vous devez également spécifier la clause ORDER BY. Inversement, si vous utilisez la clause ORDER BY et que vous ne spécifiez pas de clause ROWS ou RANGE, la valeur par défaut RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW est utilisé.

Pour illustrer les clauses ORDER BY et ROWS ou RANGE, créons des données de test: deux comptes, quatre dates par compte et un montant pour chaque date. La requête affichera les deux clauses étant utilisées de différentes manières:

Cette requête renvoie l’ensemble de résultats suivant:

Le  » La colonne RowNbr « est usin g la fonction COUNT pour renvoyer le nombre de lignes de la partition. La partition est triée par TranDate, et nous spécifions un cadre de fenêtre de toutes les lignes depuis le début de la partition jusqu’à la ligne actuelle. Pour la première ligne, il n’y a qu’une seule ligne dans le cadre de la fenêtre, donc la valeur « 1 » est renvoyée. Pour la deuxième ligne, il y a maintenant deux lignes dans le cadre de la fenêtre, donc la valeur « 2 » est renvoyée. Et ainsi de suite dans le reste des lignes de ce compte.

Étant donné que la clause PARTITION BY spécifie le compte, lorsque le compte change, les calculs de la fonction sont réinitialisés, ce qui peut être vu en examinant les lignes du deuxième compte dans l’ensemble de résultats. Voici un exemple d’agrégation « en cours », où l’agrégation s’appuie sur des calculs antérieurs. Un exemple d’utilisation de cette agrégation serait le cas échéant lors du calcul du solde de votre compte bancaire après chaque transaction (également appelé total cumulé).

La colonne « DateCount » effectue un décompte du nombre de lignes, partitionné par la date. Dans cet exemple, chacun des comptes a une transaction à chacune des quatre mêmes dates, donc chaque date a deux transactions (une pour chaque compte). La valeur « 2 » est renvoyée pour chaque ligne. Cela revient à effectuer un comptage qui utilise GROUP BY pour la date, la différence étant que le total est renvoyé pour chaque ligne au lieu d’une seule fois pour chaque date. Un exemple Le moment où vous utiliseriez cette méthode serait d’afficher une « Ligne X de Y », ou de calculer un pourcentage de la ligne actuelle par rapport au total.

La colonne « Last2Count » effectue un décompte des lignes dans la partition, pour la ligne actuelle et la ligne qui la précède immédiatement. Pour la première ligne de chaque compte, puisqu’il n’y a pas de ligne la précédant, la valeur « 1 » est renvoyée. Pour les lignes restantes de chaque compte, la valeur « 2 » est renvoyée. Il s’agit d’un exemple d’agrégation « mobile » ou « glissante ». Un exemple d’utilisation de cette méthode serait de calculer un bonus en fonction de ventes des deux derniers mois.

À ce stade, je n’ai montré que la clause ROWS. La clause RANGE fonctionne de la même manière, mais au lieu de traiter les lignes de manière positionnelle, elle traite de la valeurs renvoyées par cette ligne. C’est parce qu’elle n’est pas positionnelle que les N clauses PRECEDING / FOLLOWING ne peuvent pas être utilisées. Examinons rapidement la différence entre ROWS et RANGE en utilisant les deux dans la même requête. liste des personnes (appelons-les DBA) et leurs tarifs horaires. Notez que les lignes avec RowIDs 4 & 5 et 12 & 13 ont le même taux. La requête résumera les taux deux fois, une fois en utilisant ROWS et l’autre en utilisant RANGE:

Cette requête produit l’ensemble de résultats suivant:

Dans les colonnes SumByRows et SumByRange, la clause OVER est identique à l’exception de la clause ROWS / RANGE. Notez également que puisque la plage de fin n’a pas été spécifiée, la valeur par défaut est d’utiliser CURRENT ROW. Étant donné que nous additionnons le salaire depuis le début de l’ensemble de résultats jusqu’à la ligne actuelle, ce que nous calculons réellement est un total cumulé de la colonne Salaire. Dans la colonne SumByRows, la valeur est calculée à l’aide de la clause ROWS, et nous pouvons voir que la somme de la ligne actuelle est le salaire de la ligne actuelle plus le total de la ligne précédente. Cependant, la clause RANGE fonctionne à partir de la valeur de la colonne Salary, donc elle résume toutes les lignes avec le même salaire ou un salaire inférieur. Il en résulte que la valeur SumByRange est la même valeur pour toutes les lignes avec le même salaire.

Une remarque importante: la clause ORDER BY dans la clause OVER contrôle uniquement l’ordre dans lequel les lignes de la partition seront utilisées par la fonction fenêtre. Il ne contrôle pas l’ordre du jeu de résultats final. Sans une clause ORDER BY sur la requête elle-même, l’ordre des lignes n’est pas garanti. Vous pouvez remarquer que votre requête peut être renvoyée dans l’ordre de la dernière clause OVER spécifiée – cela est dû à la façon dont cela est actuellement implémenté dans SQL Server. Si l’équipe SQL Server de Microsoft modifie son fonctionnement, il se peut qu’elle ne classe plus vos résultats de la manière que vous observez actuellement. Si vous avez besoin d’un ordre spécifique pour l’ensemble de résultats, vous devez fournir une clause ORDER BY par rapport à la requête elle-même.

Enfin, voici un tableau des différentes fonctions qui peuvent utiliser la clause OVER, ainsi que quelles parties de la clause sont autorisées / obligatoires / facultatives.

R-obligatoire, O-facultatif, X-non autorisé