Vue d'ensemble des fonctions de fenêtre de MySQL

Les fonctions de fenêtre de MySQL sont très utiles lorsque vous souhaitez créer des rapports significatifs avec SQL. Dans cet article, nous allons présenter les fonctions de fenêtre les plus courantes de MySQL et expliquer comment les utiliser.

MySQL 8.0 a introduit une nouvelle fonctionnalité : les fonctions de fenêtre. Ces fonctions sont très utiles aux analystes de données et à tous ceux qui créent des rapports à l'aide de MySQL. Grâce à elles, vous pouvez facilement calculer des moyennes mobiles, des sommes cumulées et d'autres calculs sur des sous-ensembles spécifiques de vos données. Et vous pouvez le faire sans créer de requêtes SQL complexes ou de tables temporaires.

Si vous êtes un utilisateur régulier de SQL, vous avez peut-être déjà rencontré des fonctions de fenêtre dans le cadre de votre travail. Alors que de nombreuses bases de données disposent de ces fonctions depuis un certain temps, MySQL est resté à la traîne jusqu'en 2018. Avec MySQL 8, la plateforme est désormais à la hauteur !

Dans cet article, nous allons explorer les avantages des fonctions de fenêtre dans MySQL et la façon dont elles peuvent améliorer votre analyse de données. Si vous souhaitez vous exercer à l'utilisation des fonctions de fenêtre, consultez notre cours interactif Fonctions de fenêtrage in MySQL 8. Il propose plus de 200 exercices interactifs sur les fonctions de fenêtre de MySQL.

Qu'est-ce que Fonctions de fenêtrage dans MySQL ?

Une fonction SQL window effectue des calculs sur un ensemble de lignes de la table qui sont liées à la ligne courante. Cet ensemble de lignes est appelé une fenêtre ou un cadre de fenêtre - c'est de là que vient le terme "fonctions de fenêtre".

Commençons par un exemple simple. Imaginez que vous souhaitiez calculer la somme des valeurs de toutes les lignes, mais que vous vouliez que le résultat soit affiché sur chaque ligne. Vous pourriez avoir besoin de cette information pour comparer les valeurs individuelles avec le total lors de l'analyse des données.

C'est un jeu d'enfant si vous savez comment utiliser les fonctions de fenêtre ! Le résultat de votre requête ressemblerait à ceci :

Vous avez peut-être remarqué que les fonctions de fenêtre sont similaires aux fonctions d'agrégation. Elles calculent toutes deux une valeur agrégée pour un certain groupe de lignes. Cependant, contrairement à la clause GROUP BY, les fonctions de fenêtre en SQL ne réduisent pas les lignes. Au lieu de cela, le tableau résultant affiche à la fois les valeurs individuelles et les valeurs agrégées. Cela peut être pratique dans les rapports où vous devez travailler avec les valeurs agrégées et non agrégées en même temps.

Comment définir une fonction de fenêtre : La clause OVER()

Les fonctions de fenêtre sont définies à l'aide de la clause OVER():

SELECT …,
   <window_function> OVER(...),
   …
FROM …

La clause OVER() indique à la base de données d'utiliser une fonction de fenêtre. La forme la plus simple du cadre de la fenêtre est celle où les parenthèses sont vides, comme dans l'exemple suivant : OVER(). Cela signifie que la fenêtre est constituée de toutes les lignes de la table.

Des clauses supplémentaires peuvent être incluses dans la clause OVER() pour définir plus précisément la fenêtre. Dans cet article, nous nous concentrerons sur les clauses PARTITION BY et ORDER BY. D'autres clauses peuvent être utilisées dans OVER(), mais nous ne les aborderons pas dans cet article. Si vous souhaitez aller encore plus loin, consultez notre cours Fonctions de fenêtrage in MySQL 8. Vous pouvez également consulter cet article sur les fonctions de fenêtre de MySQL , qui fournit de très bons exemples sur la façon de les utiliser dans vos requêtes.

Exemples de requêtes avec des fonctions de fenêtre dans MySQL

Voyons quelques exemples de requêtes pour mieux comprendre où et comment vous pouvez utiliser les fonctions de fenêtre.

Dans notre exemple, nous avons un site web qui permet aux utilisateurs de participer à des quiz. Il existe plusieurs catégories de quiz et le nombre maximum de points que les participants peuvent obtenir est de 100.

Pour stocker les scores des participants, ce site web utilise le tableau participant table. Il comporte les colonnes suivantes :

• id - L'ID du participant, qui est également la clé primaire (PK) de la table.
• name - Le nom du participant.
• quiz_score - Le score du participant.
• quiz_date - La date à laquelle le quiz a été tenté.
• quiz_category - La catégorie du quiz.

Voici quelques lignes du tableau :

Maintenant que les données vous sont familières, passons à l'utilisation des fonctions de la fenêtre ! Vous trouverez peut-être cette feuille de contrôle SQL Fonctions de fenêtrage utile en tant que guide de référence rapide pendant que nous parcourons les exemples.

Exemple 1 : Clause OVER() vide - Calcul d'une statistique pour toutes les lignes

Supposons que nous souhaitions obtenir le score de chaque participant, la catégorie du quiz auquel il a participé et le score le plus élevé jamais atteint dans tous les quiz. Nous pouvons le faire en utilisant une clause OVER() vide. Ainsi, notre fenêtre inclura toutes les lignes de la requête.

Voici la requête que nous exécuterons :

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER() AS max_score
FROM participant;

Le résultat sera le suivant :

La fonction MAX() a été appliquée à toutes les lignes de notre requête. Vous pouvez voir que le score le plus élevé est 100 et qu'il est affiché pour toutes les lignes avec les scores individuels.

Vous pouvez utiliser la clause vide OVER() avec d'autres fonctions, telles que COUNT(), SUM(), AVG(), et d'autres. Cela vous permet de calculer une statistique globale pour toutes les lignes de la requête ; vous pouvez comparer cette statistique globale à la valeur de chaque ligne individuelle.

Pour en savoir plus sur l'utilisation de la clause OVER() dans MySQL, consultez notre article Qu'est-ce que la clause OVER de MySQL ?

Exemple 2 : OVER() avec ORDER BY - Calculer un classement

Lorsqu'elle est utilisée dans la clause OVER(), ORDER BY détermine l'ordre dans lequel les lignes sont classées dans le cadre de la fenêtre. Voyons un exemple :

Nous pouvons utiliser cette requête pour créer un classement des résultats de quiz :

SELECT 
  name, 
  quiz_score,
  quiz_category,
  RANK() OVER(ORDER BY quiz_score DESC) AS rank
FROM participant;

La fonction de fenêtre RANK() attribue un rang à chaque ligne d'une partition ; ce rang est basé sur la valeur d'une expression spécifiée. La première ligne obtient le rang 1, la deuxième ligne le rang 2, etc.

Plus précisément, la fonction RANK() attribue un rang unique à chaque valeur distincte de l'expression dans la partition. Les lignes ayant la même valeur auront le même rang, et le rang suivant sera ignoré. Par exemple, si deux lignes ont la même valeur et reçoivent un rang de 1, le rang suivant attribué sera 3, en sautant le rang 2. Pour en savoir plus sur les fonctions de fenêtre de classement en SQL, consultez notre blog.

Ici, nous utilisons la fonction RANK() pour calculer le classement du score de chaque participant au quiz. La clause OVER() avec la clause ORDER BY détermine l'ordre dans lequel la fonction RANK() est appliquée. Dans ce cas, la clause ORDER BY est définie sur quiz_score DESC, ce qui signifie que les scores du quiz sont classés par ordre décroissant (du plus élevé au plus bas) avant que le classement ne soit calculé. La première ligne (avec la valeur la plus élevée) obtient le rang 1, la deuxième ligne le rang 2, etc.

Voici ce que le code renvoie :

Bien joué ! Nous avons pu attribuer un rang à chaque participant.

Utilisez la clause OVER (ORDER BY) dans MySQL lorsque vous souhaitez appliquer une fonction aux lignes dans un ordre spécifique. Cela peut s'avérer utile pour calculer des totaux courants, des moyennes mobiles et créer divers classements.

Exemple 3 : OVER() avec PARTITION BY - Calcul d'une statistique pour chaque partition

Utilisons davantage la colonne de catégorie. Vous vous souvenez du premier exemple de requête que nous avons parcouru ? Pour chaque participant, nous avons affiché son score, la catégorie du quiz auquel il a participé et le score le plus élevé jamais atteint dans tous les quiz. Cette fois-ci, nous aimerions faire quelque chose de similaire. Cependant, au lieu d'afficher le score le plus élevé jamais atteint dans tous les quiz, nous afficherons le score le plus élevé jamais atteint dans la catégorie de ce quiz.

Pour ce faire, nous aurons besoin de la clause OVER() avec PARTITION BY. Partitionner des données en SQL signifie diviser un ensemble de lignes en groupes plus petits sur la base d'une ou de plusieurs colonnes spécifiées. C'est un peu similaire à la clause GROUP BY, mais les fonctions de fenêtre ne réduisent pas les lignes.

Nous pouvons utiliser cette requête :

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER(PARTITION BY quiz_category) 
    AS max_score_in_category
FROM participant;

La clause PARTITION BY avec la clause OVER() détermine la partition des données sur laquelle la fonction MAX() est appliquée. Dans ce cas, la clause PARTITION BY est définie sur quiz_category, ce qui signifie que les données sont divisées en partitions basées sur la catégorie du quiz. C'est exactement ce que nous voulions !

Voici le résultat :

Pour chaque participant, nous avons affiché à la fois son score individuel et le score le plus élevé de sa catégorie. Cela semble juste, car le quiz sur la musique aurait pu être plus difficile que celui sur les sciences !

OVER (PARTITION BY) dans MySQL est utile pour calculer des sommes cumulées ou des valeurs moyennes, créer des classements au sein de groupes, identifier les meilleurs ou les moins bons résultats, et bien d'autres choses encore. Cette clause apporte de la flexibilité et des fonctionnalités avancées aux requêtes SQL, permettant des analyses et des manipulations de données puissantes au sein de sous-ensembles de données. Pour en savoir plus sur l'utilisation de SQL PARTITION BY avec OVER, consultez notre blog.

C'était facile, non ? Essayons quelque chose de plus compliqué !

Exemple 4 : OVER() avec PARTITION BY et ORDER BY - Calculer une statistique pour chaque partition dans un ordre spécifique

Pouvons-nous utiliser PARTITION BY et ORDER BY en même temps ? Bien sûr ! Cette combinaison est utile dans de nombreuses situations.

Dans MySQL, l'utilisation de la clause OVER() avec PARTITION BY et ORDER BY vous permet d'effectuer des calculs et des analyses sur des partitions spécifiques de données tout en contrôlant l'ordre dans lequel les calculs sont appliqués au sein de chaque partition.

La clause PARTITION BY divise l'ensemble des résultats en partitions distinctes basées sur des colonnes ou des expressions spécifiées. Chaque partition est traitée séparément pour le calcul ou l'analyse.

La clause ORDER BY, lorsqu'elle est utilisée avec OVER(), détermine l'ordre dans lequel les données sont traitées dans chaque partition. Elle spécifie la colonne ou l'expression par laquelle les données doivent être triées.

Voyons ce duo en action. Dans cet exemple, nous allons calculer la moyenne cumulée des notes de quiz par catégorie. Une moyenne cumulative est la moyenne d'un ensemble de valeurs jusqu'à un certain point.

Voici la requête que nous allons utiliser :

SELECT
  name,
  quiz_date,
  quiz_score,
  quiz_category,
  ROUND(
    AVG(quiz_score) 
OVER(PARTITION BY quiz_category ORDER BY quiz_date)
  ) AS cumulative_avg
FROM participant;

Nous souhaitons connaître la moyenne cumulée des notes de quiz par catégorie. Pour ce faire, nous avons utilisé PARTITION BY quiz_category, comme nous l'avons fait la dernière fois. De plus, il est logique que la moyenne cumulée soit calculée de la date la plus ancienne à la date la plus récente, nous avons donc utilisé ORDER BY quiz_date. Cela signifie que les données de chaque partition sont triées par date de quiz en ordre croissant (du plus grand au plus petit) avant que la fonction AVG() ne soit appliquée.

Voici à quoi ressemble le résultat de la requête :

En utilisant PARTITION BY et ORDER BY ensemble dans la clause OVER(), le calcul de la moyenne est appliqué séparément pour chaque catégorie de quiz. Au sein de chaque catégorie, la moyenne est calculée dans l'ordre des dates de l'épreuve. Cela signifie que pour chaque ligne de l'ensemble de résultats, le calcul de la moyenne ne prend en compte que les lignes appartenant à la même catégorie de quiz et les classe par date. La colonne cumulative_avg reflétera le score moyen jusqu'à la ligne actuelle pour chaque catégorie de quiz, en tenant compte de l'ordre des dates de quiz.

Prêt à pratiquer MySQL Fonctions de fenêtrage?

Comme vous pouvez le constater, les fonctions de fenêtre dans MySQL sont un outil très puissant qui peut vous aider à créer des rapports complexes. Vous pouvez utiliser les fonctions de fenêtre de MySQL pour créer des classements et calculer des mesures d'année en année, des moyennes mobiles, et bien plus encore !

Dans cet article, nous n'avons fait qu'effleurer toutes les possibilités d'utilisation des fonctions de fenêtre. Si vous souhaitez approfondir vos connaissances et explorer d'autres exemples d'utilisation, consultez ces articles sur les fonctions SQL window et la clause OVER() de MySQL.

Et pour plus de pratique, n'oubliez pas de consulter notre cours Fonctions de fenêtrage in MySQL 8!