Par Kathi Kellenberger
J’utilisais ROW_NUMBER dans mon code T-SQL depuis des années sans réaliser qu’il fait partie d’un groupe de fonctions incroyablement utiles appelées fonctions de fenêtre. Ce n’est qu’en préparant une présentation sur les nouvelles fonctionnalités T-SQL de la version SQL Server 2012 que j’ai réalisé qu’il y avait tellement de choses à dire que j’ai créé une présentation séparée uniquement sur les nouvelles fonctions de fenêtre. Finalement, cette présentation s’est transformée en deux présentations, en un livre publié par Apress et en un cours Pluralsight. J’ai parlé à qui voulait bien l’entendre de ces formidables fonctions, et je me retrouve à les utiliser quotidiennement au travail.
Les fonctions de fenêtre vous permettent d’effectuer des calculs sur un ensemble de lignes. Leur fonctionnement est similaire à celui du regroupement avec les fonctions agrégées, mais elles permettent une différence importante : Les requêtes agrégées renvoient une ligne par groupe, et les détails sont éliminés dans les résultats. Les requêtes impliquant des fonctions de fenêtre renvoient toutes les lignes de détail ainsi que le résultat de l’expression de la fonction de fenêtre. Vous avez le détail, et obtenez l’agrégat aussi.
Lorsque je parle des fonctions de fenêtre lors d’événements SQL Server et développeurs, généralement environ la moitié de la salle est familière avec ROW_NUMBER. Une poignée en connaîtra plus que cela. C’est décevant car ces fonctions utiles sont disponibles depuis des années.
Ma fonction fenêtre préférée s’appelle LAG. LAG vous permet de saisir n’importe quelle colonne d’une autre ligne du jeu de résultats. Vous pouvez accomplir la même chose avec d’autres méthodes, mais les performances sont bien moins bonnes. Voici deux requêtes effectuées sur la base de données AdventureWorks. Les deux requêtes donnent le même résultat. La première le fait par le biais d’une jointure externe, tandis que la seconde tire parti de LAG.
–Lister les clients avec les dates de commande et les jours depuis la commande précédente
SELECT CustomerID, OrderDate,
DATEDIFF(day,OAPPLY.PrevOrder, OrderDate) AS DaysSincePrevOrder
FROM Sales.SalesOrderHeader AS SOH
OUTER APPLY (
SELECT TOP(1) OrderDate AS PrevOrder
FROM Sales.SalesOrderHeader AS OA
WHERE OA.CustomerID = SOH.CustomerID
AND OA.OrderDate < SOH.OrderDate
ORDER BY OrderDate) AS OAPPLY
ORDER BY CustomerID, OrderDate;
–Produire les mêmes résultats en utilisant LAG
SELECT CustomerID, OrderDate,
DATEDIFF(day,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate),OrderDate) AS DaysSinceLastOrder
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
La deuxième requête utilisant la fonction LAG est plus simple à écrire et a de meilleures performances. Le plan d’exécution réel montre que la première requête prend 99% des ressources du lot. La deuxième requête ne prend que 1% des ressources. Voici les deux plans d’exécution, et vous pouvez voir par vous-même:
Si vous n’êtes pas familier avec la syntaxe des fonctions de fenêtre, la requête LAG peut être un peu déroutante. Toutes les fonctions de fenêtre auront une clause OVER qui définit la fenêtre, ou l’ensemble des lignes, pour le calcul. Je ne veux pas comparer les dates de commande entre les clients, j’ai donc utilisé l’option PARTITION BY pour diviser les lignes en groupes par CustomerID:
LAG(OrderDate) OVER(PARTITION BY CustomerID …
La clause OVER possède également une option ORDER BY. Ceci est nécessaire dans le cas de LAG, afin que les lignes soient alignées dans le bon ordre pour que le moteur puisse saisir les valeurs respectives de leurs bonnes lignes. Mon invocation finale de LAG ressemble à ceci:
LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderDate)
LAG n’est qu’une fonction parmi de nombreuses fonctions de fenêtre. Ces fonctions ne vous permettent pas réellement de faire quelque chose d’impossible sans elles, mais elles facilitent définitivement le codage. Vous constaterez également que plusieurs fois vos requêtes s’exécutent plus rapidement lorsqu’elles sont formulées à l’aide de ces nouvelles fonctions.
Je finis par utiliser les fonctions de fenêtre sur une base quotidienne. Elles me facilitent la vie et améliorent les performances de mes requêtes. J’adore les utiliser, et vous devriez le faire aussi.
À propos de l’auteur
Kathi Kellenberger est consultante chez Linchpin People. Elle aime écrire et parler sur des sujets liés à SQL Server, ayant écrit plus de deux douzaines d’articles, contribué à quatre livres et fait des présentations lors de nombreux événements liés à SQL Server. Pendant son temps libre, Kathi aime passer du temps avec sa famille et ses amis, chanter et faire du vélo.
Apprenez-en encore plus sur les fonctions de fenêtre et leur puissance pour résoudre les problèmes de requête difficiles grâce au livre de Kathi Kellenberger, Expert T-SQL Window Functions in SQL Server (ISBN 9781484211045). Parmi les autres ouvrages de Kathi, citons Beginning SQL Server Reporting Services (2016) et Beginning T-SQL (2014).
Laisser un commentaire