4th Jul 2022 7 minutes de lecture Fonctions de fenêtre SQL vs. GROUP BY : Quelle est la différence ? Ignacio L. Bisso sql apprendre sql fonctions de fenêtrage Table des matières Examen rapide de GROUP BY GROUP BY vs Fonctions de fenêtrage La puissance de Fonctions de fenêtrage Fonctions de fenêtrage vs Fonctions agrégées Fermeture de la fenêtre 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 : Employee_NameDepartmentSalary John RobertsFinance2300 Peter HudsonMarketing1800 Sue GibsonFinance2000 Melinda BishopMarketing1500 Nancy HudsonIT1950 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 : Departmentaveragetop_salary Marketing16501800 Finance21502300 IT19501950 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 : Employee_NameDepartmentsalarytop_salary John RobertsFinance23002300 Peter HudsonMarketing18001800 Sue GibsonFinance20002300 Melinda BishopMarketing15001800 Nancy HudsonIT19501950 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 : share_symboltimestampvalue OILBEST2020-03-05 10:00120 OILBEST2020-03-05 12:00123 OILBEST2020-03-05 15:00122 BANKWEB2020-03-05 10:0091 BANKWEB2020-03-05 12:0087 BANKWEB2020-03-05 15:0099 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. share_symboltimestampvalueprevious_valuepercentage_variation OILBEST2020-03-05 10:00120 OILBEST2020-03-05 12:001231202.43 OILBEST2020-03-05 15:00122123-0.81 BANKWEB2020-03-05 10:0091 BANKWEB2020-03-05 12:008791-4.59 BANKWEB2020-03-05 15:00998712.12 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. Tags: sql apprendre sql fonctions de fenêtrage