Que sont les fonctions SQL Window ?

Vous avez besoin d'améliorer votre analyse de données ? Apprenez ces fonctions SQL window ici et vous ferez passer vos compétences d'analyse au niveau supérieur. Exercices pratiques et explications détaillées inclus !

Lorsque j'ai entendu parler pour la première fois des fonctions SQL window, j'ai pensé qu'il s'agissait d'un mariage étrange entre SQL et Windows. Je me trompais. Les fonctions SQL window n'ont rien à voir avec le célèbre système d'exploitation. Il s'agit de fonctions SQL qui effectuent des calculs sur l'ensemble des lignes liées à la ligne courante. Cet ensemble de lignes est appelé fenêtre ou cadre de fenêtre, d'où le nom de la fonction.

Vous pouvez également entendre parler de fonctions de fenêtrage en SQL, de fonctions analytiques ou de fonctions OVER(). Il ne s'agit là que de noms alternatifs pour les fonctions de fenêtrage SQL, un ensemble d'outils extrêmement utiles pour l'analyse des données.

Dans cet article, nous allons vous montrer ce que vous pouvez faire avec les fonctions de fenêtrage et comment. Je commencerai par un bref historique des fonctions SQL window et j'expliquerai pourquoi elles sont appelées ainsi. Ensuite, je vous guiderai à travers la syntaxe et vous montrerai comment elle fonctionne à l'aide de plusieurs exemples. Après avoir pratiqué la syntaxe, nous serons prêts pour des exemples concrets de fonctions window dans la vie d'un analyste de données. Et voici la partie la plus intéressante : après chaque exemple, il y a un exercice à résoudre et à apprendre par le codage.

Cependant, la source principale de vos connaissances sur ce sujet devrait être notre Fonctions de fenêtrage cours. Ses 218 exercices interactifs couvrent en détail les fonctions de la fenêtre SQL. En d'autres termes, vous apprendrez à connaître les cadres de fenêtres et les clauses OVER(), PARTITION BY, et ORDER BY. Tout cela est nécessaire pour agréger, classer et analyser les données à l'aide des fonctions de fenêtre.

Brève histoire de SQL Fonctions de fenêtrage

Les fonctions de fenêtre ont été introduites pour la première fois dans la base de données Oracle8i, publiée en 1998. Cependant, elles ont été incluses dans la norme SQL cinq ans plus tard avec SQL:2003.

Microsoft les a ensuite incluses dans SQL Server 2005. D'autres systèmes de gestion de bases de données (SGBD) ont suivi ; PostgreSQL les prend en charge depuis la sortie de PostgreSQL 8.4 en 2009 ; MariaDB les a incluses dans la version 10.2 (2016), et MySQL les a ajoutées à la version 8 en 2018.

Les fonctions de fenêtre sont une fonctionnalité relativement nouvelle de SQL. C'est pourquoi elles ne font pas partie du programme d'études habituel de SQL. En les apprenant, vous aurez une longueur d'avance par rapport à de nombreux utilisateurs de SQL.

Qu'est-ce qu'une fenêtre ?

Un ensemble de lignes liées à la ligne courante est appelé une fenêtre ou un cadre de fenêtre. D'où le nom de ces fonctions : leur résultat est basé sur un cadre de fenêtre coulissante.

Par exemple, vous pouvez calculer une somme cumulative comme indiqué ci-dessous :

La fenêtre pour la somme cumulée 2023-10-04 est surlignée en vert. Elle comprend la ligne actuelle (pour 2023-10-04) et toutes les lignes précédentes. La somme cumulée est donc calculée comme la somme de toutes les ventes précédentes et actuelles : 4.241 + 2.389 + 1.580 + 3.395 = 11.605 (la ligne délimitée par des points rouges n'est pas incluse dans la fenêtre ni dans la somme).

Lorsque nous passons à la ligne suivante, la fenêtre se déplace également : elle inclut désormais toutes les lignes précédentes (en vert) et la ligne actuelle (en pointillés rouges). La somme cumulée est maintenant de 4 241 + 2 389 + 1 580 + 3 395 + 1 265 = 12 870.

La fenêtre est donc l'ensemble des lignes liées à la ligne actuelle qui sont utilisées dans les calculs pour cette ligne. La fenêtre change (glisse) au fur et à mesure que l'on passe d'une ligne à l'autre ; c'est grâce à ces images d'une fenêtre qui glisse que l'on obtient le nom de ces fonctions.

Syntaxe des fonctions de fenêtre SQL

La syntaxe des fonctions de fenêtre est la suivante :

SELECT column_1,
       column_2,
	 <window_function> OVER(PARTITION BY … ORDER BY … ) AS column_alias
FROM table;

Voici ce que fait chaque partie :

• <window_function> - Spécifie la fonction à appliquer à cette fenêtre.
• OVER() - Définit la fenêtre (ensemble de lignes) et indique qu'il s'agit d'une fonction de fenêtre ; sans cette clause, il ne s'agit pas d'une fonction de fenêtre.
• <window_frame> - Définit la taille du cadre de la fenêtre (facultatif).
• PARTITION BY - Divise la fenêtre en groupes plus petits appelés partitions (facultatif) ; en cas d'omission, l'ensemble des résultats constitue une seule partition.
• ORDER BY - Trie les lignes dans le cadre de la fenêtre (facultatif), c'est-à-dire qu'il décide de l'ordre dans lequel l'opération de fenêtrage sera effectuée ; s'il est omis, l'ordre des lignes à l'intérieur de la partition est arbitraire.

Des clauses supplémentaires peuvent définir plus précisément la fenêtre. Leur syntaxe est la suivante :

[<ROWS or RANGE clause> BETWEEN <lower_bound> AND <upper_bound>]

La clause ROWS définit la fenêtre en termes de nombre fixe de lignes par rapport à la ligne actuelle.

La clause RANGE fait de même. Mais elle prend également en compte toutes les lignes dont les valeurs dans les colonnes spécifiées dans la clause ORDER BY sont identiques à celles de la ligne actuelle.

Les limites de la fenêtre peuvent être définies comme suit

• UNBOUNDED PRECEDING - Toutes les lignes précédant la ligne actuelle.
• n PRECEDING - Un nombre défini de lignes avant la ligne actuelle.
• CURRENT ROW - Inclut la ligne actuelle.
• n FOLLOWING - Un nombre défini de lignes après la ligne actuelle.
• UNBOUNDED FOLLOWING - Toutes les lignes après la ligne actuelle.

Voyons maintenant comment cela fonctionne en pratique.

Exemples de données et de syntaxe

Nous utiliserons le tableau album_catalogue dans tous ces exemples. Vous pouvez le créer vous-même à l'aide de ce script. Un aperçu des données est présenté ci-dessous :

L'ensemble de données est une liste d'albums avec leur durée, leur genre, leur artiste et les données relatives aux ventes, y compris le nombre d'exemplaires vendus et la période (trimestres). Les données vont jusqu'au troisième trimestre 2023.

Je vais d'abord vous montrer plusieurs exemples, en expliquant au passage chaque partie cruciale de la syntaxe des fonctions de fenêtrage.

Exemple de syntaxe n° 1 : OVER ()

Vous pouvez utiliser la fonction de fenêtrage SUM() avec la seule clause OVER() pour obtenir le total des ventes au quatrième trimestre 2022 :

SELECT sales_period,
 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER() AS sold_in_4Q_2022
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Je veux la somme des exemplaires vendus, je spécifie donc cette colonne dans SUM(). La clause OVER() est obligatoire. Si vous souhaitez utiliser OVER() sans aucune des clauses facultatives, laissez simplement les parenthèses vides.

J'utilise WHERE pour ne sortir que les données du trimestre souhaité.

Lorsque vous écrivez une requête comme celle-ci - avec un OVER() vide - l'ensemble des résultats (colonnes sélectionnées, filtres appliqués, etc.) est pris en compte lors de l'exécution des calculs de la fonction de fenêtre. Ici, le résultat indique les ventes individuelles de chaque album vendu au cours du quatrième trimestre 2022. Il indique également les ventes totales de tous les albums vendus au cours de cette période.

Avec l'aide de SUM() et OVER(), je suis en mesure d'afficher les ventes de chaque album et le total trimestriel.

Exemple de syntaxe n° 2 : OVER (ORDER BY)

Vous pouvez ajouter des clauses supplémentaires à l'intérieur de la clause OVER() pour modifier la définition du cadre de la fenêtre. L'une de ces clauses est ORDER BY. La clause ORDER BY définit le tri des lignes à l'intérieur d'un cadre de fenêtre : les lignes peuvent être traitées par la fonction de fenêtre dans un ordre donné.

Voyons un exemple. Vous pouvez calculer la somme cumulée en ajoutant ORDER BY à la requête précédente. Dans cet exemple, je souhaite connaître les ventes de l'album "In the Right Place" au fil du temps et le nombre cumulé d'albums vendus jusqu'à une période donnée. Voici la requête :

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(ORDER BY sales_period ASC) AS cumulative_sum
FROM album_catalogue
WHERE album_title = 'In the Right Place';

La même colonne copies_sold se trouve dans SUM(). Cette fois, OVER() contient la clause ORDER BY. Vous souhaitez afficher les ventes cumulées du premier au dernier trimestre. C'est pourquoi vous avez besoin de sales_period et ASC dans ORDER BY.

Dans chaque ligne, vous pouvez voir les ventes pour chaque trimestre et la somme cumulée, c'est-à-dire la somme du trimestre en cours et de tous les trimestres précédents. Par exemple, l'album s'est vendu à 94 exemplaires au troisième trimestre 2022. Le total des ventes en 2022 jusqu'à cette date (ou en trois trimestres) est le suivant : 222 + 208 + 94 = 524.

Exemple de syntaxe n° 3 : OVER (PARTITION BY)

Une autre clause que vous pouvez utiliser dans OVER() est PARTITION BY. PARTITION BY est utilisée pour diviser la fenêtre en segments plus petits sur la base de certains critères. Par exemple, vous pouvez dresser la liste des albums, de leurs ventes pour le quatrième trimestre 2022 et des ventes par genre pour ce trimestre :

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM (copies_sold) OVER(PARTITION BY album_genre) AS sales_by_genre
FROM album_catalogue
WHERE sales_period = '2022_4Q';

Nous utilisons à nouveau la même fonction de fenêtre SUM(). Cette fois-ci, nous utilisons PARTITION BY pour diviser la fenêtre en segments plus petits basés sur le genre de l'album. Tout le reste est identique.

La requête renvoie le résultat ci-dessous. Il s'agit d'une analyse des données relatives aux ventes d'albums par genre pour le dernier trimestre 2022.

Par exemple, la somme cumulée pour les albums folk est de 809 + 246 + 400 + 214 + 412 = 2 081.

Exemple de syntaxe n° 4 : OVER (ORDER BY PARTITION BY)

Vous pouvez également utiliser PARTITION BY et ORDER BY dans OVER(). Les lignes sont divisées en segments avec PARTITION BY et traitées dans un ordre donné par ORDER BY.

En utilisant la requête ci-dessous, je peux afficher toutes les données analytiques des albums et calculer la somme cumulée pour chaque album séparément :

SELECT sales_period,
	 album_title,
	 artist,
	 copies_sold,
	 SUM (copies_sold) OVER(PARTITION BY album_title ORDER BY sales_period ASC) AS cumulative_sum_by_album
FROM album_catalogue;

Je calcule cette somme à l'aide de la fonction de fenêtre SUM(), comme je l'ai fait précédemment. J'ai partitionné la fenêtre par album. Cela signifie que la somme sera cumulée jusqu'à ce que la fonction atteigne la dernière ligne d'un album particulier. Lorsqu'elle atteint un autre album, elle se réinitialise et recommence à accumuler la somme depuis le début.

J'utilise également ORDER BY pour demander à la fonction de cumuler la somme du premier au dernier trimestre.

Vous pouvez voir que la somme cumulée pour "A Whole New Thing" est de 3 654. L'album suivant ("Blue") commence à la ligne suivante, de sorte que le cumul est réinitialisé : la somme cumulée est la même que les ventes individuelles de l'album au cours du premier trimestre 2022. Ensuite, elle s'accumule jusqu'à l'album suivant. Le résultat va jusqu'au dernier album, qui est "Wednesday Morning, 3 A.M." dans notre cas.

Je vous ai montré les façons les plus courantes de définir des cadres de fenêtres avec ces exemples. Mais ce ne sont pas les seules. Vous pouvez également utiliser les clauses ROW ou RANGE avec la syntaxe et les limites que nous avons expliquées précédemment. Ne vous inquiétez pas. Vous verrez l'utilisation pratique de ces clauses dans des exemples concrets.

Je n'ai utilisé qu'une seule fonction, SUM(), tout au long de ces exemples. Il s'agit d'une des nombreuses fonctions de fenêtre ; passons rapidement en revue quelques-unes des autres.

Quelles sont les fonctions les plus courantes de Fonctions de fenêtrage?

Les fonctions de fenêtre les plus courantes peuvent être classées en trois catégories :

1. Agrégat Fonctions de fenêtrage:

• COUNT() - Compte le nombre de lignes dans une fenêtre.
• SUM() - Totalise des valeurs données dans une fenêtre.
• AVG() - Calcule la moyenne de valeurs données dans une fenêtre.
• MIN() - Trouve la plus petite valeur dans une fenêtre.
• MAX() - Trouve la plus grande valeur dans une fenêtre.

2. Classement Fonctions de fenêtrage:

• ROW_NUMBER() - Classe les valeurs séquentiellement, avec des rangs différents pour les valeurs ex æquo.
• RANK() - Classe les valeurs en utilisant le même rang pour les valeurs ex æquo ; saute le rang suivant après les ex æquo (par exemple, 1, 2, 2, 4).
• DENSE_RANK() - Classe les valeurs en utilisant le même rang pour les valeurs liées ; ne saute pas le rang suivant après les égalités (par exemple, 1,2,2,3,4).

3. Analytique Fonctions de fenêtrage:

• LEAD() - Obtient des données à partir d'un décalage défini (c.-à-d. un nombre donné de lignes) après la ligne actuelle.
• LAG() - Permet d'obtenir des données à partir d'un décalage défini (c'est-à-dire un nombre donné de lignes) avant la ligne actuelle.

Il existe d'autres fonctions de fenêtre qui pourraient vous être utiles. Consultez-les dans notre feuille de contrôle gratuite SQL Fonctions de fenêtrage.

Exemples réels de fonctions de fenêtre SQL

Jusqu'à présent, je me suis concentré sur la syntaxe des fonctions SQL window. Je vais maintenant vous montrer les utilisations pratiques les plus courantes des fonctions de fenêtre et comment elles peuvent aider les analystes de données dans leur travail.

Ces exemples utilisent le même ensemble de données que précédemment.

Exemple n° 1 : Pourcentage du total

Montrons des informations sur chaque album et ses ventes au cours du premier trimestre 2023. En outre, nous montrerons les ventes trimestrielles par genre. Calculons ensuite la part de chaque album (en pourcentage) dans les ventes par genre.

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 SUM(copies_sold) OVER (PARTITION BY album_genre) AS sales_by_genre,
	 (copies_sold*1.0/ SUM(copies_sold) OVER (PARTITION BY album_genre))*100.0 AS percent_of_genre_sales
FROM album_catalogue
WHERE sales_period = '2023_1Q'
ORDER BY album_genre, copies_sold DESC;

Pour obtenir les ventes par genre, j'utilise à nouveau la fonction de fenêtre SUM(). Dans la clause OVER(), je n'utilise que PARTITION BY. De cette façon, je peux diviser la fenêtre en fonction du genre de l'album.

Dans la ligne de code suivante, je divise les exemplaires vendus (de chaque album) et je les divise par les ventes par genre. Pour ce faire, il suffit de copier le calcul de la ligne précédente. Ensuite, il faut multiplier le quotient par 100 pour obtenir le pourcentage. Vous remarquerez que j'ai également multiplié copies_sold par 1,0. Cela permet de convertir les nombres entiers en valeurs décimales.

Filtrez le trimestre souhaité à l'aide de WHERE. Enfin, classez les résultats par ordre alphabétique de genre, puis par ordre décroissant d'exemplaires vendus.

Voici le résultat :

Vérifions le calcul pour la première ligne. L'album de Simon & Garfunkel s'est vendu à 809 exemplaires. Le total des ventes d'albums folk pour ce trimestre est de 2 081. Le pourcentage des ventes individuelles par rapport aux ventes totales du genre est donc de 809/2 081*100 = 38,88 %.

La somme des pourcentages pour chaque genre devrait être de 100 %. Vérifions-le pour un genre folklorique : 38,88 % + 19,80 % + 19,22 % + 11,82 % + 10,28 % = 100 %.

Fonction de fenêtre vs. fonction d'agrégation vs. GROUP BY

J'utilise à nouveau la fonction d'agrégation comme fonction de fenêtre. J'aurais pu utiliser une simple fonction agrégée SUM() avec GROUP BY pour obtenir les ventes par genre pour le trimestre spécifié. Quelle est alors la différence ?

Une fonction de fenêtre vous permet d'afficher à la fois des données analytiques et des données agrégées (les ventes individuelles avec les ventes par genre et le quotient de ces valeurs), tandis qu'une fonction agrégée utilisée avec GROUP BY réduirait la ligne individuelle et n'afficherait que la valeur agrégée (la somme des ventes pour le trimestre).

Résoudre cet exercice pour s'entraîner

À l'aide des fonctions de fenêtre, réécrivez la requête ci-dessus de manière à afficher les ventes moyennes par genre. Indiquez également dans quelle mesure les ventes de chaque album sont supérieures ou inférieures à la moyenne par genre (en pourcentage). N'affichez que les ventes du troisième trimestre 2023. Afficher le titre de l'album, l'artiste, le nombre d'exemplaires vendus et le genre de l'album. Trier les résultats par ordre croissant en fonction du genre et des ventes de chaque album.

Solution :

SELECT album_title,
	 artist,
	 copies_sold,
	 album_genre,
	 AVG(copies_sold) OVER (PARTITION BY album_genre) AS average_sales_by_genre,
	   ((copies_sold/AVG(copies_sold) OVER (PARTITION BY album_genre))-1)*100 AS pct_from_average	   
FROM album_catalogue
WHERE sales_period = '2023_3Q'
ORDER BY album_genre, copies_sold;

Résultat :

Exemple n° 2 : classement des données

Dans cet exemple, j'utiliserai une fonction de fenêtre pour classer des données. Je souhaite afficher chaque titre d'album distinct et sa durée, puis les classer par ordre de longueur. L'album le plus long sera classé en premier.

SELECT *, 
	 RANK() OVER (ORDER BY album_length DESC) AS album_length_rank
FROM (SELECT DISTINCT album_title,
	       album_length
	FROM album_catalogue) AS distinct_album;

Commençons par expliquer la sous-requête : nous l'utilisons pour sélectionner des albums distincts et leur longueur.

Ensuite, nous utilisons la requête principale pour sélectionner toutes les données de la sous-requête. Utilisez maintenant la fonction de fenêtre RANK() pour classer les albums. Vous pouvez également utiliser d'autres fonctions de classement, en fonction de vos données et de vos tâches.

Pour que le classement fonctionne comme vous le souhaitez, utilisez la clause ORDER BY dans OVER(). Spécifiez la colonne par laquelle vous voulez classer et dans quel ordre. Dans ce cas, il s'agit d'un classement décroissant par longueur.

Voici le classement :

Résoudre cet exercice pour s'entraîner

Classez chaque album unique en fonction de ses ventes dans son genre. Ne montrez que les données du premier trimestre 2023. Indiquez le titre de l'album, ses ventes, son genre et son rang. Si certains albums ont le même nombre de ventes, classez-les de la même manière et ne sautez pas le rang suivant.

Solution :

SELECT *, 
	 DENSE_RANK() OVER (PARTITION BY album_genre ORDER BY copies_sold DESC) AS album_sales_rank
FROM (SELECT DISTINCT album_title,
	       copies_sold,
	       album_genre
	FROM album_catalogue
	WHERE sales_period = '2023_1Q') AS distinct_album;

Sortie :

Exemple n° 3 : Total courant

Dans cet exemple, j'indiquerai la période de vente d'un album particulier, son titre, son artiste et le nombre d'exemplaires vendus. J'ajouterai également un total courant des exemplaires vendus qui comprendra trois lignes : la ligne actuelle et les deux lignes précédentes. La somme doit être calculée du premier au dernier trimestre.

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'In a Silent Way';

J'utilise à nouveau la fonction de fenêtre SUM(). Ensuite, il y a un ORDER BY dans OVER() pour ordonner les ventes afin que nous puissions les additionner de manière ascendante.

Ensuite, je dois définir le cadre de la fenêtre mobile. Le total courant doit inclure la ligne actuelle et les deux lignes précédentes. Il s'agit des limites inférieure et supérieure spécifiées dans la clause ROWS. La limite inférieure correspond aux deux lignes précédentes, c'est-à-dire BETWEEN 2 PRECEDING. La limite supérieure est CURRENT ROW. Les deux bornes sont assemblées dans un cadre de fenêtre à l'aide du mot-clé AND.

Je veux afficher le calcul pour l'album "In a Silent Way" de Miles Davis, je filtre donc les données à l'aide du mot-clé WHERE.

Voici les totaux en cours :

Vérifions le résultat et expliquons ce qu'est un total courant.

Un total courant est similaire à un total cumulatif (ou somme), mais ce n'est pas la même chose. Le total cumulé vous donne la somme de la ligne actuelle et de toutes les lignes précédentes, c'est-à-dire que le cadre de la fenêtre augmente avec chaque ligne. Un total courant est une somme à l'intérieur d'une fenêtre définie qui reste de la même taille mais qui se déplace avec chaque ligne. Dans notre cas, la fenêtre est définie comme étant la ligne actuelle et les deux lignes précédentes.

Examinez les valeurs en surbrillance. Le total courant pour le premier trimestre 2022 est de 428, soit le même que pour la vente individuelle. Comme il n'y a pas de lignes précédentes, le total courant ne comprend que la ligne actuelle.

Le total courant suivant est 428 + 1053 = 1481. Il additionne la ligne actuelle et la ligne précédente, puisqu'il n'y a qu'une seule ligne précédente.

Le total courant pour le troisième trimestre 2022 est 428 + 1053 + 19 = 1500. C'est la première fois que vous obtenez la fenêtre entière, c'est-à-dire la ligne actuelle et les deux lignes précédentes.

Lorsque vous passez à la ligne suivante, la fenêtre se déplace mais sa taille reste la même. Le total courant pour le trimestre suivant est de 1053 + 19 + 65 = 1137. Il implique à nouveau la ligne actuelle et les deux lignes précédentes - mais des lignes différentes par rapport au trimestre.

Résolvez cet exercice pour vous entraîner

Réécrivez la requête ci-dessus pour qu'elle calcule le total courant de l'album "The New Folk Sound of Terry Callier". Le total cumulé doit être calculé du premier au dernier trimestre. Il doit inclure quatre trimestres : les deux précédents, le trimestre en cours et le suivant. Indiquez également la période de vente, le titre de l'album, l'artiste et le nombre d'exemplaires vendus.

Solution :

SELECT sales_period, 
	 album_title,
	 artist,
	 copies_sold,
	 SUM(copies_sold) OVER (ORDER BY sales_period ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sales_running_total
FROM album_catalogue
WHERE album_title = 'The New Folk Sound of Terry Callier';

Sortie :

Exemple n° 4 : Différence d'un trimestre à l'autre

Dans ce dernier exemple, je vais montrer comment utiliser les fonctions de la fenêtre pour calculer la différence de ventes entre les trimestres :

SELECT *,
	 LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS previous_quarter_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold) OVER (ORDER BY sales_period) AS quarterly_sales_difference
FROM (SELECT sales_period,
	SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

J'écris d'abord une sous-requête qui calcule les ventes totales pour chaque trimestre. J'utilise la fonction d'agrégation SUM() et je regroupe les résultats par période de vente.

Ensuite, je sélectionne toutes les données de la sous-requête dans la requête principale.

Je dois maintenant obtenir les ventes du trimestre précédent. Je vais écrire la fonction de fenêtre LAG(), qui est utilisée pour accéder aux valeurs des lignes précédentes. La valeur à laquelle je souhaite accéder est spécifiée dans la fonction. Dans ce cas, il s'agit des exemplaires trimestriels vendus à partir de la sous-requête. En définissant l'argument offset, la fonction me permet de définir jusqu'où je veux remonter. Comme je ne l'ai pas défini, le décalage par défaut est de un. En d'autres termes, la fonction récupère les données de la ligne/du trimestre précédent(e). Mais si vous souhaitez remonter de deux lignes/trimestres, vous devez écrire LAG(quarterly_copies_sold, 2).

J'utilise également ORDER BY dans OVER() pour m'assurer que les valeurs du cadre sont triées du trimestre le plus ancien au plus récent.

Cette utilisation de la fonction window permet de clarifier ce que je vais faire dans la ligne de code suivante. C'est ici que s'effectue le calcul proprement dit de la comparaison des ventes du trimestre en cours et du trimestre précédent. C'est simple maintenant : soustrayez la fonction window définie ci-dessus de la colonne quarterly_copies_sold.

Voici le résultat :

Il n'y a pas de valeurs précédentes pour 2022_1Q, car il n'y a pas de trimestre précédent. Les ventes trimestrielles pour 2022_2Q sont de 7 581. Les ventes du trimestre précédent étaient de 9 519. Le calcul montre que les ventes actuelles sont inférieures de 1 938 exemplaires (7 581 - 9 519) aux ventes du trimestre précédent.

Vous pouvez analyser le reste des résultats de la même manière.

Résoudre cet exercice pour s'entraîner

Réécrivez la requête ci-dessus pour qu'elle indique la différence entre les ventes trimestrielles d'une année sur l'autre - par exemple, comparez le premier trimestre 2023 avec le premier trimestre 2022. Indiquez la période de vente, les exemplaires vendus au cours du trimestre, les ventes pour le même trimestre de l'année précédente et la différence d'une année sur l'autre entre les trimestres.

Solution :

SELECT *,
	 LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_sales,
	 quarterly_copies_sold - LAG(quarterly_copies_sold, 4) OVER (ORDER BY sales_period) AS year_over_year_difference
FROM (SELECT sales_period,
	   SUM(copies_sold) AS quarterly_copies_sold
FROM album_catalogue
GROUP BY sales_period) AS quarterly_sales; 

Sortie :

Si vous en voulez plus, voici d'autres exemples de fonctions de fenêtre. Pour vous entraîner, consultez ces 11 exercices sur les fonctions de fenêtre SQL.

SQL Fonctions de fenêtrage: une fenêtre pour une meilleure analyse des données

Cet article s'est avéré très complet sur les fonctions de fenêtre SQL. Vous avez appris à connaître les fonctions de fenêtre et le fonctionnement de chaque partie cruciale de leur syntaxe.

Vous savez également qu'il existe plusieurs catégories de fonctions de fenêtre. Les plus couramment utilisées sont les fonctions fenêtre agrégées, de classement et analytiques. Les exemples pratiques vous ont montré comment les fonctions fenêtre peuvent être utilisées dans des tâches courantes d'analyse de données.

J'espère que vous n'avez pas sauté les exercices de l'article. Si c'est le cas, je vous recommande à nouveau de les résoudre. Ce n'est qu'en pratiquant que vous pourrez vraiment comprendre ce que sont les fonctions SQL window.

La ressource la plus riche pour l'apprentissage et la pratique est notre cours de Fonctions de fenêtrage est la ressource la plus riche pour apprendre et s'entraîner. Il s'agit d'un cours interactif qui comporte plus de 200 exercices pratiques et couvre la syntaxe complète des fonctions de fenêtre. Si vous avez des entretiens d'embauche en vue, n'oubliez pas de répondre à ces questions d'entretien sur les fonctions de fenêtre SQL. Bonne chance et continuez à apprendre le langage SQL !