Une introduction aux fonctions de fenêtre SQL

La fonctionnalité très puissante que vous aimez détester (mais que vous devez connaître)

Les fonctions de fenêtre SQL fournissent des fonctionnalités extrêmement puissantes et utiles. Mais pour beaucoup, comme ils sont si étrangers au SQL standard, ils sont difficiles à apprendre et à comprendre, ont une syntaxe étrange et sont très souvent évités.

Les fonctions de fenêtre peuvent être simplement expliquées comme des fonctions de calcul similaires à l'agrégation, mais là où l'agrégation normale via la clause GROUP BY se combine puis masque les lignes individuelles en cours d'agrégation, les fonctions de fenêtre ont accès à des lignes individuelles et peuvent ajouter certains des attributs de ces lignes dans le ensemble de résultats.

Dans ce didacticiel sur les fonctions de fenêtre SQL, je vais vous familiariser avec les fonctions de fenêtre, expliquer les avantages et quand vous les utiliseriez, et vous donner des exemples réels pour vous aider avec les concepts.

Une fenêtre sur vos données

L'une des fonctionnalités les plus utilisées et les plus importantes de SQL est la possibilité d'agréger ou de regrouper des lignes de données de manière particulière. Dans certains cas, cependant, le regroupement peut devenir extrêmement complexe, selon ce qui est requis.

Avez-vous déjà voulu parcourir les résultats de votre requête pour obtenir un classement, une liste des x premiers ou similaire ? Avez-vous eu des projets d'analyse pour lesquels vous vouliez préparer vos données parfaitement pour un outil de visualisation, mais avez trouvé cela presque impossible ou si complexe que cela n'en valait pas la peine ?

Les fonctions de fenêtre peuvent faciliter les choses. Après avoir obtenu le résultat de votre requête, c'est-à-dire après la clause WHERE et toute agrégation standard, les fonctions de fenêtre agiront sur les lignes restantes (la fenêtre de données) et vous obtiendront ce que vous voulez.

Certaines des fonctions de fenêtre que nous allons examiner incluent :

• OVER
• COUNT()
• SUM()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LEAD()
• LAG()

Trop facile

La clause OVER est ce qui spécifie une fonction de fenêtre et doit toujours être incluse dans l'instruction. La valeur par défaut dans une clause OVER est l'ensemble de lignes entier. À titre d'exemple, examinons une table d'employés dans une base de données d'entreprise et affichons le nombre total d'employés sur chaque ligne, ainsi que les informations de chaque employé, y compris quand ils ont commencé dans l'entreprise.

 SELECT COUNT(*) OVER() As NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Ce qui précède, comme de nombreuses fonctions de fenêtre, peut également être écrit d'une manière non fenêtrée plus familière, ce qui, dans cet exemple simple, n'est pas trop mal :

 SELECT (SELECT COUNT(*) FROM Employee) as NumEmployees, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Mais maintenant, disons que nous souhaitons montrer le nombre d'employés qui ont commencé le même mois que l'employé de la rangée. Nous devrons réduire ou limiter le nombre à ce mois pour chaque ligne. Comment est-ce fait? Nous utilisons la clause PARTITION de la fenêtre, comme ceci :

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started),YEAR(date_started)) As NumPerMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname FROM Employee ORDER BY date_started;

Les partitions vous permettent de filtrer la fenêtre en sections par une certaine valeur ou des valeurs. Chaque section est souvent appelée le cadre de la fenêtre.

Pour aller plus loin, disons que nous voulions non seulement savoir combien d'employés ont commencé le même mois, mais nous voulons montrer dans quel ordre ils ont commencé ce mois-là. Pour cela, nous pouvons utiliser la clause familière ORDER BY . Cependant, dans une fonction de fenêtre, ORDER BY agit un peu différemment qu'à la fin d'une requête.

 SELECT COUNT(*) OVER (PARTITION BY MONTH(date_started), YEAR(date_started) ORDER BY date_started) As NumThisMonth, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Dans ce cas, ORDER BY modifie la fenêtre pour qu'elle aille du début de la partition (ici le mois et l'année de début du salarié) à la ligne courante. Ainsi, le comptage redémarre à chaque partition.

Classez-le

Les fonctions de fenêtre peuvent être très utiles à des fins de classement. Nous avons vu précédemment que l'utilisation de la fonction d'agrégation COUNT permettait de voir dans quel ordre les Employés rejoignaient l'entreprise. Nous aurions également pu utiliser des fonctions de classement de fenêtre, telles que ROW_NUMBER() , RANK() et DENSE_RANK() .

Les différences peuvent être observées après avoir ajouté un nouvel employé le mois suivant et supprimé la partition :

 SELECT ROW_NUMBER() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As StartingRank, RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As EmployeeRank, DENSE_RANK() OVER (ORDER BY YEAR(date_started),MONTH(date_started)) As DenseRank, DATENAME(month,date_started)+' '+DATENAME(year,date_started) As TheMonth, firstname, lastname, date_started FROM Employee ORDER BY date_started;

Vous pouvez voir les différences. ROW_NUMBER() donne un décompte séquentiel dans une partition donnée (mais en l'absence de partition, il parcourt toutes les lignes). RANK() donne le rang de chaque ligne en fonction de la clause ORDER BY . Il montre les égalités, puis saute le classement suivant. DENSE_RANK affiche également les égalités, mais continue ensuite avec la valeur consécutive suivante comme s'il n'y avait pas d'égalité.

Les autres fonctions de classement incluent :

• CUME_DIST - Calcule le rang relatif de la ligne actuelle dans une partition
• NTILE - Divise les lignes pour chaque partition de fenêtre aussi équitablement que possible
• PERCENT_RANK – Rang en pourcentage de la ligne actuelle

Notez également dans cet exemple que vous pouvez avoir plusieurs fonctions Window dans une seule requête, et que la partition et l'ordre peuvent être différents dans chacune !

Lignes et plages et cadres, Oh My

Pour définir ou limiter davantage votre cadre de fenêtre dans la clause OVER() , vous pouvez utiliser ROWS et RANGE . Avec la clause ROWS , vous pouvez spécifier les lignes incluses dans votre partition comme celles précédant ou suivant la ligne actuelle.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

Dans cet exemple, le cadre de la fenêtre va de la première ligne à la ligne actuelle moins 1, et la taille de la fenêtre continue d'augmenter pour chaque ligne.

La gamme fonctionne un peu différemment et nous pouvons obtenir un résultat différent.

 SELECT OrderYear, OrderMonth, TotalDue, SUM(TotalDue) OVER(ORDER BY OrderYear, OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS 'LaggingRunningTotal' FROM sales_products;

La plage inclura les lignes du cadre de la fenêtre qui ont les mêmes valeurs ORDER BY que la ligne actuelle. Ainsi, il est possible que vous obteniez des doublons avec RANGE si ORDER BY n'est pas unique.

Certains décrivent ROWS comme un opérateur physique tandis que RANGE est un opérateur logique. Dans tous les cas, les valeurs par défaut pour ROWS et RANGE sont toujours UNBOUNDED PRECEDING AND CURRENT ROW .

Quoi d'autre?

La plupart des fonctions d'agrégation standard fonctionnent avec les fonctions de fenêtre. Nous avons déjà vu COUNT dans les exemples. D'autres incluent SUM , AVG , MIN , MAX , etc.

Avec les fonctions de fenêtre, vous pouvez également accéder aux enregistrements précédents et suivants en utilisant LAG et LEAD , et FIRST_VALUE et LAST_VALUE . Par exemple, supposons que vous souhaitiez afficher sur chaque ligne un chiffre d'affaires pour le mois en cours et la différence entre le chiffre d'affaires du mois dernier. Vous pourriez faire quelque chose comme ceci :

 SELECT id, OrderMonth, OrderYear, product, sales, sales - LAG(sales,1) OVER (PARTITION BY product ORDER BY OrderYear, OrderMonth) As sales_change FROM sales_products WHERE sale_year = 2019;

Fondamentalement, les fonctions de fenêtre SQL sont très puissantes

Bien qu'il s'agisse d'une introduction rapide aux fonctions de fenêtre SQL, nous espérons que cela suscitera votre intérêt de voir tout ce qu'elles peuvent faire. Nous avons appris que les fonctions de fenêtre effectuent des calculs similaires à ceux des fonctions d'agrégation, mais avec l'avantage supplémentaire qu'elles ont accès aux données dans les lignes individuelles, ce qui les rend assez puissantes. Ils contiennent toujours la clause OVER et peuvent contenir PARTITION BY , ORDER BY et un hôte de fonctions d'agrégation ( SUM , COUNT , etc.) et d'autres fonctions positionnelles ( LEAD , LAG ). Nous avons également découvert les cadres de fenêtre et comment ils encapsulent des sections de données.

Notez que différentes versions de SQL peuvent implémenter les fonctions de fenêtre différemment, et certaines peuvent ne pas avoir implémenté toutes les fonctions ou clauses de fenêtre. Assurez-vous de consulter la documentation de la plate-forme que vous utilisez.

Si, en tant que développeur SQL, vous souhaitez optimiser les performances de votre base de données SQL, consultez Optimisation des performances de la base de données SQL pour les développeurs .

Bon fenêtrage !

Pour plus d'informations sur des implémentations spécifiques, voir :

• Documentation des fonctions de fenêtre de PostgreSQL pour une implémentation de PostgreSQL.
• SELECT - OVER Clause (Transact-SQL) docs par Microsoft.
• Fonctions de fenêtre dans SQL Server pour un excellent aperçu des implémentations de SQL Server et de sa partie 2.