Comment définir un cadre de fenêtre dans les fonctions de fenêtre SQL

La fonction fenêtre est une ressource super puissante du langage SQL. Au cœur de toute fonction fenêtre, il y a un ensemble d'enregistrements, appelé le cadre de la fenêtre, défini à l'aide d'une clause OVER. Il est essentiel de savoir quels enregistrements se trouvent dans le cadre de la fenêtre, comment ils sont ordonnés et quelles sont leurs limites supérieure et inférieure pour comprendre le fonctionnement des fonctions de fenêtre. Dans cet article, nous analyserons et expliquerons à l'aide d'exemples comment vous pouvez définir différents types de cadres de fenêtre. Lisez la suite pour franchir une étape importante dans le développement de vos compétences SQL !

Utilisation de PARTITION BY pour définir un cadre de fenêtre

Les fonctions de fenêtre SQL effectuent des calculs sur la base d'un ensemble d'enregistrements. Par exemple, vous pouvez vouloir calculer le salaire moyen d'un groupe spécifique d'enregistrements d'employés. Ce groupe d'enregistrements s'appelle le cadre de fenêtre, et sa définition est essentielle pour comprendre le fonctionnement des fonctions de fenêtre et la façon dont nous pouvons en tirer parti.

Le cadre de la fenêtre est un ensemble de lignes liées à la ligne actuelle où la fonction de fenêtre est utilisée pour le calcul. Le cadre de la fenêtre peut être un ensemble de lignes différent pour la ligne suivante dans le résultat de la requête, puisqu'il dépend de la ligne actuelle en cours de traitement. Chaque ligne du jeu de résultats de la requête possède son propre cadre de fenêtre.

Dans la suite de cet article, nous allons montrer des exemples de requêtes basées sur la base de données d'un groupe de concessionnaires automobiles. Le groupe stocke les informations sur les ventes groupées par mois dans une table appelée monthly_car_sales. Le tableau ci-dessous contient des exemples de données :

monthly_car_sales

Une façon simple de créer un cadre de fenêtre est d'utiliser une clause OVER avec une sous-clause PARTITION BY. Dans l'exemple SQL suivant, nous générons un rapport sur les revenus par marque de voiture pour l'année 2021.

SELECT make,
       SUM(revenue) OVER (PARTITION BY make) AS total_revenue
FROM   monthly_car_sales
WHERE  year = 2021

Ci-dessous, les cadres de fenêtre générés par la requête précédente sont affichés en différentes couleurs (rouge pour Ford et bleu pour Renault). Tous les enregistrements ayant la même valeur dans la colonne make (les lignes avec le code couleur ci-dessous) appartiennent à la même fenêtre. Puisque nous avons seulement deux valeurs différentes dans la colonne make, nous avons deux cadres de fenêtre.

Le résultat de la requête est le suivant :

Je voudrais vous suggérer 2 articles où vous pouvez trouver beaucoup d'informations introductives sur les fonctions de fenêtre SQL : "Cours SQL du mois - Fonctions de fenêtrage " et "Quand dois-je utiliser SQL Fonctions de fenêtrage?".

Ordonner les lignes dans un cadre de fenêtre avec ORDER BY

En plus de PARTITION BY, nous pouvons utiliser une sous-clause ORDER BY pour ordonner les lignes dans un cadre de fenêtre. Le fait d'ordonner le cadre de la fenêtre selon certains critères nous permet d'utiliser des fonctions analytiques de fenêtre telles que LEAD(), LAG() et FIRST_VALUE(), entre autres.

Par exemple, si nous voulons obtenir la différence de revenus entre des mois consécutifs, nous pouvons ordonner le cadre de la fenêtre par mois. Ensuite, étant donné n'importe quelle ligne actuelle, la fonction de fenêtre LAG() peut renvoyer n'importe quelle colonne du mois précédent. Voyons un exemple qui permet d'obtenir la différence de revenus entre chaque paire de mois consécutifs.

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER ( ORDER BY month) AS previous_month_revenue,
	 revenue - LAG (revenue) OVER (ORDER BY month) AS delta_revenue
FROM monthly_car_sales
WHERE year = 2021
  AND model = 'Mustang'

Le résultat de la requête précédente est le suivant :

La première ligne du résultat comporte des valeurs NULL dans les colonnes previous_month_revenue et delta_revenue. Cela s'explique par le fait qu'il n'y a pas de mois précédent pour janvier. La colonne delta_revenu est calculée entre le revenu du mois en cours et le revenu du mois précédent, ce dernier étant obtenu avec la fonction fenêtre LAG().

Lorsque nous utilisons ORDER BY dans une clause OVER, il y a un nouvel élément à prendre en compte : les limites du cadre de la fenêtre. Si nous ne spécifions aucune clause ORDER BY, la partition entière devient le cadre de la fenêtre. Cependant, lorsque nous utilisons une sous-clause ORDER BY, la ligne actuelle devient la limite supérieure du cadre de la fenêtre. En d'autres termes, les lignes qui suivent la ligne actuelle (selon les critères de ORDER BY ) ne sont pas incluses dans le cadre de la fenêtre. Dans la section suivante, nous aborderons en détail le concept de limites de cadre de fenêtre.

Définition des limites du cadre de la fenêtre avec ROWS

Une caractéristique très intéressante de la clause OVER est la possibilité de spécifier les limites supérieure et inférieure d'un cadre de fenêtre. Ces limites peuvent être spécifiées en utilisant l'une des deux sous-clauses de la clause OVER: ROWS ou RANGE. Dans cette section, nous expliquerons comment utiliser la sous-clause ROWS de la clause OVER.

Le cadre de la fenêtre est un ensemble de lignes qui sont liées d'une manière ou d'une autre à la ligne actuelle. Leurs limites peuvent être définies pour chaque ligne du résultat de la requête avec une sous-clause ROWS, dont la syntaxe est la suivante :

ROWS BETWEEN lower_bound AND upper_bound

Comme nous l'avons déjà mentionné dans la section précédente, il est important de savoir quelles sont les limites par défaut du cadre de la fenêtre. Lorsque nous spécifions une sous-clause ORDER BY, la ligne actuelle est la limite supérieure du cadre de la fenêtre par défaut. Cependant, dans certains cas, nous devons modifier cette limite supérieure (ou la limite inférieure), comme nous le verrons ci-dessous.

Prenons un exemple où nous devons spécifier les limites d'un cadre de fenêtre. Supposons que nous voulions un rapport contenant les ventes totales pour le mois en cours, les ventes totales pour le mois précédent et les ventes maximales pour chaque mois de l'année, le tout par marque et modèle. La requête pour obtenir un tel rapport est la suivante :

SELECT make,
       model,
       month,
       revenue AS current_month_revenue,
       LAG(revenue) OVER (PARTITION BY make, model ORDER BY month) AS    
                                                   prev_month,
	 MAX(revenue) OVER (PARTITION BY make, model ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_year_revenue
FROM monthly_car_sales
WHERE year = 2021

Si nous avions omis la sous-clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING dans la fonction fenêtre MAX() de la requête précédente, nous aurions obtenu le maximum entre le premier mois et le mois en cours. C'est une erreur, puisque nous voulons obtenir le revenu mensuel maximum en considérant l'année entière (y compris les mois suivant le mois en cours). Nous devons donc inclure tous les mois disponibles dans le tableau. Pour ce faire, nous ajoutons la sous-clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING à la clause OVER.

L'image suivante montre toutes les limites possibles que nous pouvons spécifier pour définir les limites inférieure et supérieure d'un cadre de fenêtre :

Les options pour les limites inférieures et supérieures dans la clause OVER sont les suivantes :

• UNBOUNDED PRECEDING
• N PRECEDING
• CURRENT ROW
• N FOLLOWING
• UNBOUNDED FOLLOWING

Nous devons nous assurer que la limite inférieure est inférieure à la limite supérieure.

Enfin, je vous suggère l'article Pourquoi apprendre SQL Fonctions de fenêtrage en 2021 ? où vous pourrez apprendre à utiliser les fonctions de fenêtre.

Définition des limites du cadre de la fenêtre avec RANGE

Dans la section précédente, nous avons défini les limites d'un cadre de fenêtre en termes de ROWS. Dans cette section, nous allons expliquer comment utiliser la sous-clause RANGE pour spécifier les limites d'un cadre de fenêtre dans des plages de lignes. La syntaxe de la sous-clause RANGE est la suivante :

RANGE BETWEEN lower_bound AND upper_bound

Une plage est un ensemble de lignes ayant la même valeur pour le critère PARTITION BY. Par exemple, si nous avons un mois PARTITION BY, nous pouvons voir la différence dans l'image suivante lorsque nous utilisons ROWS ou RANGE pour définir un cadre de fenêtre :

OVER ( PARTITION BY …... 

             ORDER BY month 

             ROWS BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

OVER ( PARTITION BY ….. 

             ORDER BY month 

             RANGE BETWEEN 1 PRECEDING 

             AND 1 FOLLOWING )

Si nous voulons un rapport sur les recettes par marque pour le mois en cours et pour chacun des trois derniers mois, nous pouvons utiliser la requête suivante :

SELECT make,
       model,
       month,
	 revenue AS model_revenue_current_month,
       SUM(revenue) OVER ( PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 0 PRECEDING AND CURRENT ROW
                         ) AS make_current_month,
	 SUM(revenue) OVER (PARTITION BY make
                           ORDER BY month
                           RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                        ) AS make_last_2_months,
	   SUM(revenue) OVER (PARTITION BY make
                            ORDER BY month
                            RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                        ) AS make_last_3_months
FROM monthly_car_sales
WHERE year = 2021
ORDER BY 1,3,2

La requête précédente utilise le sous-clause RANGE pour spécifier un cadre de fenêtre avec tous les enregistrements de la marque actuelle pour une plage de N mois. Par exemple :

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
                  ) AS make_last_2_months

Le sous-clause précédent RANGE BETWEEN 1 PRECEDING AND CURRENT ROW spécifie un cadre de fenêtre qui inclut le mois précédent et le mois en cours. La fonction SUM() renverra alors le revenu total des deux derniers mois.

De même, nous pouvons utiliser la clause suivante OVER pour obtenir le revenu total des trois derniers mois.

SUM(revenue) OVER (
                   PARTITION BY make
                   ORDER BY month
                   RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
                  ) AS make_last_3_months

Il existe plusieurs abréviations pour faciliter la syntaxe de ces clauses limitatives :

Enfin, voici mon aide-mémoire préféré sur les fonctions de fenêtre avec de nombreux détails syntaxiques : Aide-mémoire sur les fonctions de fenêtre SQL.

Tirez parti de Fonctions de fenêtrage!

Une idée centrale de la fonction fenêtre est le cadre de la fenêtre, c'est-à-dire le groupe d'enregistrements sur lequel la fonction fenêtre travaille. Dans cet article, nous avons expliqué que le cadre de la fenêtre dépend de la ligne courante et est défini par la clause OVER. Nous avons également présenté plusieurs exemples permettant de définir les enregistrements à inclure dans le cadre de la fenêtre, d'ordonner les lignes à l'intérieur de celui-ci et de définir ses limites.

Pour ceux qui veulent aller plus loin, je suggère le cours en ligne Fonctions de fenêtrage SQL, qui contient de nombreux exemples utilisant différentes fonctions de fenêtre. Développez vos compétences et augmentez vos atouts !