Fonctions de fenêtre SQL vs. GROUP BY : Quelle est la différence ?

Une idée fausse très répandue parmi les utilisateurs de SQL est qu'il n'y a pas beaucoup de différence entre les fonctions de fenêtre SQL et les fonctions d'agrégation ou la clause GROUP BY. Or, les différences sont très importantes.

Le seul point commun entre GROUP BY et les fonctions de fenêtre est peut-être que toutes deux vous permettent d'exécuter une fonction (telle que AVG, MAX, MIN ou COUNT) sur un groupe d'enregistrements. Je dirais que le pouvoir spécial des fonctions fenêtre est qu'elles nous permettent d'obtenir des résultats qui seraient autrement presque impossibles à atteindre.

Dans cet article, nous examinerons l'utilisation des fonctions fenêtre par rapport à GROUP BY et des fonctions fenêtre par rapport aux fonctions agrégées.

Examen rapide de GROUP BY

La clause GROUP BY nous permet de regrouper un ensemble d'enregistrements sur la base de certains critères et d'appliquer une fonction (par exemple AVG ou MAX) à chaque groupe, obtenant ainsi un résultat pour chaque groupe d'enregistrements. Voyons un exemple.

Nous disposons d'une table appelée employee avec un total de cinq employés et trois départements :

fig1 : la table des employés

Supposons que nous voulions obtenir le salaire moyen par département et le salaire le plus élevé pour chaque département. Nous devons utiliser la requête suivante :

SELECT Department, 
       avg(salary) as average,
       max(salary) as top_salary
FROM employee
GROUP BY department

L'image ci-dessous montre le résultat :

GROUP BY vs Fonctions de fenêtrage

Lorsque vous comparez les fonctions de fenêtre et GROUP BY, il est essentiel de se rappeler que GROUP BY regroupe les enregistrements individuels en groupes ; après avoir utilisé GROUP BY, vous ne pouvez pas vous référer à un champ individuel car il est regroupé. Nous reviendrons plus tard sur ce sujet. Pour l'instant, nous nous contenterons de mentionner que les fonctions de fenêtre ne réduisent pas les enregistrements individuels.

Ainsi, si vous souhaitez créer un rapport contenant le nom d'un employé, son salaire et le salaire le plus élevé de son département, vous ne pouvez pas le faire avec GROUP BY. Les enregistrements individuels de chaque employé sont comprimés par la clause GROUP BY department. Pour ce type de rapport, vous devez utiliser les fonctions de fenêtre, ce qui est le sujet de la section suivante.

Si vous souhaitez approfondir les nuances de SQL GROUP BY et des rapports, nous vous recommandons notre cours interactif Création de rapports basiques en SQL.

La puissance de Fonctions de fenêtrage

Les fonctions de fenêtre sont une fonctionnalité puissante de SQL. Elles nous permettent d'appliquer des fonctions comme AVG, COUNT, MAX et MIN à un groupe d'enregistrements tout en laissant les enregistrements individuels accessibles. Comme les enregistrements individuels ne sont pas réduits, nous pouvons créer des requêtes affichant les données de l'enregistrement individuel ainsi que le résultat de la fonction fenêtre. C'est ce qui rend les fonctions de fenêtre si puissantes.

Supposons que nous voulions obtenir une liste de noms d'employés, de salaires et du salaire le plus élevé dans leur département.

SELECT 	employee_name,
department,
salary,
max(salary) OVER (PARTITION BY department) as top_salary
FROM		employee

L'image suivante montre le résultat :

Dans la requête précédente, nous avons utilisé une fonction fenêtre :

max(salary) OVER (PARTITION BY department) as top_salary

La fonction fenêtre est MAX() et nous l'avons appliquée à l'ensemble des enregistrements définis par la clause OVER (PARTITION BY department)qui sont les enregistrements ayant la même valeur dans le champ département. Enfin, nous avons renommé la colonne top_salary. Dans le résultat de la requête, nous avons des lignes pour les employés individuels. Si nous avions utilisé GROUP BY au lieu des fonctions de fenêtre, nous aurions obtenu des lignes pour chaque département.

Les fonctions de fenêtre ont une syntaxe plutôt verbeuse ; si vous voulez entrer dans les détails, je vous suggère le cours "Fonctions de fenêtrage", qui est un tutoriel étape par étape qui vous fait découvrir les fonctions de fenêtre SQL à l'aide d'exemples et d'exercices.

Fonctions de fenêtrage vs Fonctions agrégées

Lorsque l'on compare les fonctions fenêtres et les fonctions agrégées, on remarque une fonctionnalité super puissante du côté des fonctions fenêtres : les fonctions positionnelles. Elles nous permettent d'obtenir la valeur d'une colonne à partir d'autres enregistrements dans la même fenêtre. Il s'agit d'une capacité vraiment étonnante, qui permet aux utilisateurs de SQL de créer des rapports complexes en quelques lignes seulement. Examinons brièvement deux de ces fonctions : LEAD() et LAG().

La fonction LAG() renvoie la valeur de la colonne de l'enregistrement précédent dans la fenêtre, tandis que LEAD() renvoie la valeur de la colonne de l'enregistrement suivant dans la fenêtre. Il est très important que la fenêtre soit ordonnée par la colonne de droite si vous voulez utiliser ces fonctions.

Voyons un exemple de la façon dont nous pouvons utiliser ces fonctions. Supposons que nous disposions d'une table qui stocke les actions d'une société avec leur valeur de marché à un moment donné. Le tableau pourrait ressembler à ceci :

fig2 : le tableau des actions

Supposons que nous voulions un rapport montrant la valeur de chaque action avec sa valeur précédente et le pourcentage de variation par rapport à la valeur précédente. Nous pouvons le faire en utilisant la fonction LEAD() pour obtenir la valeur précédente de l'action. Notez que nous utilisons ORDER BY timestamp pour définir la partition (c'est-à-dire la fenêtre d'enregistrements). Nous reviendrons sur ce point plus tard.

SELECT 	
  share_symbol,
  timestamp,
  value,
  LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp ) AS previous_value, 
  TRUNC(((value - (LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp )))*100)/value,2) AS percentage_variation
FROM share

Remarquez que les colonnes previous_value et percentage_variation sont des colonnes calculées. Elles utilisent des valeurs provenant de différents enregistrements de la même table.

Lorsque nous utilisons des fonctions positionnelles, il est très important de placer une clause ORDER BY en même temps que la clause PARTITION (comme nous l'avons fait dans la requête précédente). Si nous n'utilisons pas la bonne clause ORDER BY, les résultats peuvent être erronés. Pourquoi ? Parce que les fonctions positionnelles fonctionnent en fonction de l'ordre des enregistrements dans la fenêtre.

Examinons cela un peu plus en détail. La fonction FIRST_VALUE() renvoie la valeur d'une colonne du premier enregistrement de la fenêtre. LAG() Comme nous le savons, la fonction de positionnement renvoie la valeur de la colonne de l'enregistrement précédent dans la fenêtre. Il est essentiel de respecter l'ordre des fenêtres ; imaginez ce que vous obtiendriez de ces fonctions dans le cas contraire ! Dans notre exemple, nous voulons connaître la valeur de marché chronologique précédente pour une action spécifique. Nous avons donc utilisé ORDER BY timestamp. Si nous omettons le ORDER BY ou si nous classons par une autre colonne, le résultat sera erroné.

Dans certains cas spécifiques, les fonctions positionnelles peuvent renvoyer des valeurs erronées en raison d'une fenêtre partiellement remplie. Et il existe d'autres fonctions de fenêtre, comme RANK(), NTH_VALUE() et LAST_VALUE(). Nous n'avons pas la place de couvrir tout cela ici, mais je vous suggère de consulter cet article expliquant les fonctions de fenêtre et ces exemples de fonctions de fenêtre pour en savoir plus.

Fermeture de la fenêtre

Dans cet article, nous avons exploré les différences entre les fonctions de fenêtre et GROUP BY. Nous avons examiné des exemples avec plusieurs fonctions agrégées et de fenêtre. Nous avons également parlé d'une limitation importante de la clause GROUP BY, à savoir l'" effondrement des enregistrements ". Cette limitation n'est pas présente sur les fonctions fenêtre, ce qui permet aux développeurs SQL de combiner des données au niveau des enregistrements avec les résultats des fonctions fenêtre dans la même requête.

Un autre avantage des fonctions de fenêtre est leur capacité à combiner les valeurs de requête de différents enregistrements (de la même fenêtre) dans la même ligne du jeu de résultats.

Si vous souhaitez en savoir plus sur les fonctions de fenêtre, je vous suggère le cours Fonctions de fenêtrage où vous pourrez apprendre les fonctions de fenêtre SQL à l'aide d'exercices interactifs et d'explications détaillées.