Quand dois-je utiliser les fonctions de fenêtre SQL ?

Avez-vous entendu un de vos collègues se vanter d'utiliser les fonctions de fenêtre SQL ? Vous connaissez les bases du SQL, mais peu, voire pas du tout, les fonctions de fenêtre ? Si vous voulez en savoir plus sur les fonctions de fenêtre et comment les utiliser dans un contexte professionnel, vous êtes au bon endroit !

Cela arrive souvent. Vous atteignez un certain niveau de connaissances, et vous vous sentez comme le roi (ou la reine) du monde. Puis, vous entendez vos collègues parler de quelque chose dont vous n'avez jamais entendu parler (dans ce cas, les fonctions de fenêtre). Vous vous sentez immédiatement un peu honteux de ne pas connaître les fonctions de fenêtre. Ce sentiment vous est-il familier ?

Ce sentiment n'est pas agréable. Mais il peut être positif s'il vous pousse à demander ce que sont les fonctions de fenêtre. Vous effectuez donc une recherche sur Google et trouvez des articles utiles (comme celui-ci). Soudain, l'embarras se transforme en pouvoir lorsque vous réalisez que les fonctions de fenêtre ne sont pas mystérieuses ou inapprenables. À nouveau, vous vous sentez comme le roi du monde. Ou de reine. Peut-être même les deux.

Allons vers ce sentiment royal ! Je vais essayer de ne pas vous bombarder de SQL. Au lieu de cela, je vais prendre le chemin inverse. Je vais vous donner plusieurs exemples concrets d'entreprises qui vous montreront l'utilisation pratique des fonctions de fenêtre.

Que sont les Fonctions de fenêtrage?

Une fonction fenêtre est, en termes simples, une fonction qui effectue des calculs sur un ensemble de lignes de table. Le nom vient du fait que l'ensemble des lignes est appelé fenêtre ou cadre de fenêtre.

Voici un exemple de ce à quoi ressemble une fenêtre. Dans le tableau ci-dessous, les fenêtres sont marquées par des couleurs différentes. La somme cumulée est calculée pour chaque région, donc dans ce cas, les fenêtres sont définies par région.

La syntaxe de la fonction fenêtre est la suivante :

window_function ([ALL] expression)
OVER ([PARTITION BY partition_list] [ORDER BY order_list] [window_frame_clause])

La partie de la syntaxe relative à la fonction de fenêtre peut être une fonction d'agrégation, telle que SUM() ou AVG(). Elle peut également être un autre type de fonction, telle qu'une fonction analytique ou de classement.

La fenêtre, ou le nombre de lignes sur lesquelles certains calculs seront effectués, est définie par la clause OVER().

PARTITION BY est une clause facultative dans les fonctions de fenêtre. Elle définit une partition sur laquelle la fonction fenêtre effectuera l'opération. Si PARTITION BY n'est pas défini, la fonction fenêtre effectuera l'opération sur la table entière. Vous pouvez en savoir plus dans cet article.

ORDER BY triera les lignes de chaque partition dans l'ordre souhaité. Si cette clause n'est pas définie, alors la clause utilisera la table entière.

La clause de cadre de fenêtre définit le nombre de lignes sur lesquelles la fonction de fenêtre opérera à l'aide de deux mots-clés. L'un est ROWS, utilisé pour limiter le nombre de lignes en spécifiant le nombre de lignes précédant ou suivant la ligne actuelle. L'autre est RANGE, utilisé pour limiter le nombre de lignes en spécifiant une plage de valeurs par rapport à la valeur de la ligne actuelle. C'est pourquoi la clause de cadre de fenêtre est également appelée clause ROW ou RANGE.

Pour apprendre à utiliser cette syntaxe et la mettre en pratique, le cours sur les fonctions de fenêtre ( LearnSQL.fr ) est l'endroit idéal. En outre, si vous souhaitez obtenir des explications supplémentaires sur les fonctions de fenêtre, vous les trouverez ici, accompagnées de quelques exemples.

Passons maintenant à la partie amusante, la résolution de problèmes !

Exemple 1 : Calculer le salaire moyen et comparer le salaire de Individuel à la moyenne

Voici un exemple simple d'un problème courant dans le monde des affaires. Disons qu'il existe un tableau employeequi contient des données sur les salaires des employés. Elle se compose des lignes suivantes :

• id - identifiant unique
• first_name - Prénom de l'employé
• last_name - nom de famille de l'employé
• department - département de l'employé
• salary - salaire mensuel de l'employé

À partir de ces données, vous devez d'abord calculer le salaire moyen de l'ensemble de l'entreprise. Ensuite, vous devez calculer de combien le salaire de chaque employé est supérieur ou inférieur au salaire moyen. Le code suivant permet de le faire rapidement :

SELECT	first_name,
		last_name,
		department,
		salary,
		AVG(salary) OVER() AS avg_salary,
		salary - AVG(salary) OVER() AS diff_salary
FROM employee;

Comme vous êtes familier avec SQL, vous reconnaissez probablement au moins une partie de ce code. Il sélectionne first_name, last_name, department, et salary dans la table employee. Rien de nouveau ici.

La ligne AVG(salary) OVER() AS avg_salary calcule le salaire moyen dans la fenêtre définie par OVER(). Le résultat sera affiché dans la colonne avg_salary.

La ligne salary - AVG(salary) OVER() AS diff_salary calcule la différence entre le salaire de chaque employé et le salaire moyen, Le résultat sera affiché dans la colonne diff_salary.

L'exécution de ce code donnera un tableau utile. Vous pouvez en voir un extrait ci-dessous :

Exemple 2 : Calcul du nombre moyen de produits vendus par date et par région

Imaginez que vous travaillez dans une entreprise qui opère dans trois régions. La direction veut connaître le nombre moyen de produits vendus dans chaque région. De même, elle veut connaître le nombre moyen de produits vendus globalement pour chaque date. Les fonctions de la fenêtre vous permettront de le faire facilement.

Dans cet exemple, les données sont stockées dans la table sales qui comporte trois colonnes :

• date - date de la vente du produit
• region - nom de la région
• products_sold - nombre de produits vendus

Voici le code nécessaire à la création de ce rapport :

SELECT	date,
		region,
		products_sold,
		AVG(products_sold) OVER(PARTITION BY date) AS avg_date,
		AVG(products_sold) OVER(PARTITION BY region) AS avg_region
FROM sales
ORDER BY region, date;

Ce code sélectionne les colonnes date, region, et products_sold. Ensuite, il calcule le nombre moyen de produits vendus à chaque date. Ceci est défini par la clause PARTITION BY(). Le résultat sera affiché dans la colonne avg_date.

La ligne suivante calcule également le nombre moyen de produits vendus, cette fois dans chaque région. Le résultat sera affiché dans la colonne avg_region. Ensuite, le résultat est ordonné par région et par date à l'aide de la clause ORDER BY.

Voir les résultats ci-dessous :

Exemple 3 : Calculer la somme cumulée des produits vendus par région

La direction était satisfaite de votre rapport précédent ! Elle souhaite maintenant que vous calculiez la somme cumulée (ou le total courant) des produits vendus dans chaque région. Les fonctions de fenêtre sont utiles pour effectuer de tels calculs.

Le calcul sera effectué sur la table sales utilisée dans l'exemple 2. Ce code vous permettra de fournir rapidement les chiffres requis :

SELECT	date,
		region,
		products_sold,
		SUM(products_sold) OVER(PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_region
FROM sales
ORDER BY region, date;

La première partie du code sélectionne les mêmes colonnes que le code de l'exemple 2. Ensuite, elle calcule la somme des produits par région. Ceci est défini par la clause PARTITION BY(). Bien sûr, vous avez besoin de la somme cumulée, pas de la somme totale. C'est pourquoi la fenêtre est ordonnée par la date à l'aide de la commande ORDER BY.

Maintenant que tout est défini, vous devez dire à SQL d'ajouter la valeur de la ligne actuelle à la somme des lignes précédentes dans la fenêtre. Cette opération est effectuée par la commande ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

La fonction de fenêtre additionnera toutes les lignes avant la ligne actuelle (c'est pourquoi UNBOUNDED PRECEDING) et aucune ligne après la ligne actuelle (c'est pourquoi CURRENT ROW) dans la fenêtre spécifiée. Le résultat sera affiché dans la colonne cumulative_region.

Le résultat sera ordonné par la région et la date, comme dans l'exemple précédent.

Après avoir exécuté le code, vous obtiendrez le tableau ci-dessous :

Exemple 4 : Comparer les prix des actions aux prix minimum et maximum du marché

Votre entreprise surveille les cours des actions sur le NASDAQ et le NYSE. La direction souhaite que vous calculiez les prix minimum et maximum des actions sur les deux marchés au cours des 365 derniers jours. Ils veulent également voir comment le prix de chaque action négociée en 2020 diffère du prix minimum et maximum sur le marché.

Le tableau stockprice contient des données du 15/03/2019 au 14/03/2020 et se compose des colonnes suivantes :

• date - date de la transaction
• stock_name - nom de l'action
• stock_price - prix de l'action
• stock_market - marché sur lequel l'action est négociée
• max_price - cours maximum sur le marché au cours des 365 derniers jours
• min_price - cours minimum sur le marché au cours des 365 derniers jours
• diff_max - cours de l'action moins le cours maximum sur le marché au cours des 365 derniers jours
• diff_min - cours de l'action moins le cours minimum sur le marché au cours des 365 derniers jours.

Le code qui va créer rapidement le rapport demandé ressemble à ceci :

SELECT	date,
		stock_name,
		stock_price,
		stock_market,
		MAX(stock_price) OVER(PARTITION BY stock_market) as max_price,
		MIN(stock_price) OVER(PARTITION BY stock_market) as min_price,
		stock_price - MAX(stock_price) OVER(PARTITION BY stock_market) AS diff_max,
		stock_price - MIN(stock_price) OVER(PARTITION BY stock_market) AS diff_min
FROM stockprice
WHERE date > '2019-12-31'
ORDER BY date;

La première partie du code sélectionne les colonnes originales du tableau : date, stock_name, stock_price, et stock_market. Ensuite, la fonction fenêtre MAX() avec la clause PARTITION BY calcule le prix maximum pour chaque marché, NASDAQ et NYSE, séparément. Le résultat sera affiché dans la colonne max_price.

La ligne de code suivante fonctionne de la même manière, sauf qu'elle calcule maintenant le prix minimum. Le résultat sera affiché dans la colonne min_price.

Les lignes de code suivantes calculent la différence entre le prix de l'action et le prix maximum et minimum, respectivement, pour chaque marché. Les résultats seront affichés dans les colonnes diff_max et diff_min.

Étant donné que le rapport ne doit afficher que les données de 2020, j'ai utilisé la clause WHERE. Enfin, le tableau résultant est ordonné par date, ce qui est logique pour un tel rapport.

Voici à quoi ressemblent les premières lignes du rapport :

Exemple 5 : Calculer le pourcentage de changement de prix et les moyennes mobiles.

La direction sait maintenant que vous pouvez fournir des rapports avec une grande précision et rapidité. Ils sont impressionnés ! Ils ne se rendent pas compte que vous avez appris les fonctions de la fenêtre SQL et que ce qu'ils ont demandé est facile pour vous.

Ensuite, on vous demande quelque chose qui devrait vous prendre beaucoup plus de temps. Mais ce ne sera pas le cas ! Vous avez un tableau de prix qui contient toutes les price changements d'une action en 2020. Parfois, il n'y a qu'un seul changement par jour, parfois il y en a plusieurs. Le tableau se compose des colonnes suivantes :

• date - date du cours
• stock_price - prix de l'action

La direction vous a demandé de leur envoyer un rapport qui prendra chaque prix et le comparera au prix précédent. De plus, elle vous a demandé de calculer la moyenne mobile du cours de l'action. C'est ainsi que vous allez le faire :

SELECT	date,
		stock_price,
		(stock_price/LAG(stock_price) OVER(ORDER BY date)) - 1 AS percent_change,
AVG(stock_price) OVER(ORDER BY date ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING) AS moving_avg
FROM price;

Alors, que fait ce code ? Tout d'abord, il sélectionne les colonnes originales du tableau : date et stock_price.

La ligne suivante introduit une nouvelle fonction de fenêtre, LAG(). Cette fonction accède aux données de la ligne précédente, ce qui est idéal pour cette tâche. L'adresse stock_price est divisée par le prix précédent (d'où la fonction LAG() ). Ensuite, on soustrait 1 du résultat pour obtenir un pourcentage. Le résultat sera affiché dans la colonne percent_change.

La ligne suivante calcule la moyenne mobile. Elle utilise la fonction fenêtre AVG(), que vous connaissez déjà. Dans la clause OVER(), les données sont ordonnées par la date.

La direction n'a pas précisé comment elle voulait que la moyenne mobile soit calculée. J'ai donc décidé de la calculer en utilisant cinq changements de prix, ce qui est spécifié dans ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING. Lors du calcul de la moyenne mobile, le code prendra en compte la ligne actuelle et les quatre lignes suivantes, soit cinq au total.

Vous pouvez jouer avec cette partie du code car il n'y a pas de nombre défini de données à prendre en compte lors du calcul des moyennes mobiles. Cela dépend de la quantité et du type de données, ainsi que des préférences individuelles. En modifiant les chiffres qui précèdent PRECEDING et FOLLOWING, vous pouvez facilement et rapidement modifier le calcul, en fonction de la méthodologie que vous décidez d'utiliser.

Voir les résultats ci-dessous :

Pensez-vous que l'apprentissage de Fonctions de fenêtrage peut vous aider dans votre travail ?

J'ai donné des exemples concrets d'entreprises comme point de départ au lieu de l'analyse pure du code des fonctions de fenêtre. Les cinq exemples correspondent à des situations que j'ai rencontrées dans ma carrière.

C'est de cette façon que j'ai appris SQL. D'abord, j'ai eu un problème à résoudre. Ensuite, j'ai essayé de comprendre comment le faire en utilisant SQL. Si vous pensez que les fonctions de fenêtre seront utiles dans votre travail, ce cours LearnSQL.fr est un excellent moyen d'en savoir plus.

Si vous avez trouvé ces exemples intéressants ou si vous souhaitez partager certains de vos exemples concrets, n'hésitez pas à commenter ci-dessous !