Qu'est-ce que la clause OVER de MySQL ?

Si vous voulez apprendre les fonctions de fenêtre dans MySQL, vous devez comprendre la clause OVER. Dans cet article, nous utilisons des exemples du monde réel pour expliquer ce qu'est la clause OVER de MySQL, comment elle fonctionne et pourquoi elle est si géniale.

En 2018, MySQL a introduit une nouvelle fonctionnalité : les fonctions de fenêtre, auxquelles on accède via la clause OVER. Les fonctions de fenêtre sont une ressource super puissante disponible dans presque toutes les bases de données SQL. Elles effectuent un calcul spécifique (par exemple, une somme, un compte, une moyenne, etc.) sur un ensemble de lignes ; cet ensemble de lignes est appelé " fenêtre " et est défini par la clause MySQL OVER.

Dans cet article, nous allons expliquer comment utiliser la clause MySQL OVER dans différents scénarios. Nous vous présenterons également plusieurs fonctions de fenêtre. J'espère qu'après avoir lu cet article, vous serez convaincu de l'intérêt d'apprendre les fonctions de fenêtre. Vous saurez également comment les appliquer dans vos requêtes !

Fonctions de fenêtrage: Une fonctionnalité très demandée

Les fonctions de fenêtre sont disponibles dans la plupart des grandes bases de données depuis un certain temps, mais jusqu'en 2018, elles n'étaient pas disponibles dans MySQL. Pour maintenir MySQL à jour, les fonctions de fenêtre ont été introduites dans MySQL 8.02. Si vous envisagez de travailler avec la version 8 de MySQL, il vaut la peine d'apprendre les fonctions de fenêtre et la clause OVER, car elles sont très puissantes.

Quand utiliserez-vous les fonctions de fenêtre ? Elles sont souvent utiles, par exemple pour calculer le salaire moyen d'un certain groupe d'employés. Dans ce cas, la définition du groupe est le point central ; vous ne voulez pas la moyenne des salaires de tous les employés, et si vous vous trompez de groupe, le résultat sera faux. La définition d'un groupe d'enregistrements est la raison d'être de la clause OVER : elle dicte où la fonction fenêtre va travailler.

Au fil de cet article, nous allons créer quelques exemples de requêtes basées sur une base de données contenant les enregistrements d'un groupe d'agriculteurs qui produisent des oranges. Les agriculteurs partagent leurs données de production, qui sont stockées dans la table orange_production tableau que vous voyez ci-dessous :

Le cas d'utilisation le plus simple est une clause OVER vide ; cela signifie que la fenêtre d'enregistrements correspond à l'ensemble des enregistrements renvoyés par la requête. Par exemple, si nos agriculteurs veulent avoir un rapport de chaque enregistrement d'agriculteur à côté du total de la production d'orange en 2017, nous écrirons cette requête :

SELECT farmer_name, 
       kilos_produced, 
       SUM(kilos_produced) OVER() total_produced
 FROM  orange_production 
WHERE  crop_year = 2017

Ici, la clause OVER construit une fenêtre qui inclut tous les enregistrements renvoyés par la requête - en d'autres termes, tous les enregistrements de l'année 2017. Le résultat est le suivant :

Comment fonctionne un cadre de fenêtre coulissante

C'était un exemple très simple de la clause MySQL OVER. La fenêtre d'enregistrements était statique (la fenêtre était la même pour toutes les lignes retournées par la requête). Cependant, l'un des points forts de la clause OVER est la possibilité de créer une fenêtre dynamique d'enregistrements (également appelée fenêtre coulissante). Les fenêtres coulissantes ou dynamiques signifient que la fenêtre d'enregistrements peut être différente pour chaque ligne renvoyée par la requête. En outre, la fenêtre est créée sur la base de la ligne actuelle de la requête, de sorte que les lignes de la fenêtre peuvent changer lorsque la ligne actuelle change.

Prenons un exemple de fenêtre coulissante. Supposons que nos agriculteurs souhaitent voir leur propre production ainsi que la production totale de la même variété d'orange.

SELECT farmer_name, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety) AS total_same_variety
FROM orange_production 

La clause OVER(PARTITION BY orange_variety) crée des fenêtres en regroupant tous les enregistrements ayant la même valeur dans la colonne orange_variety. Cela nous donne deux fenêtres : 'Golden' et 'SuperSun'. Dans le tableau ci-dessous, chaque fenêtre est représentée par une couleur différente :

Vous pouvez maintenant voir le résultat de la requête :

Remarquez que la colonne Total Same Variety (à l'extrême droite) comprend la production pour toutes les années. Peut-être chaque agriculteur préfère-t-il comparer sa production à la production totale de la même variété pour la même année. Cette comparaison leur permet de voir leur part du taux de production. Pour ce faire, nous devons ajouter la colonne crop_year à la clause PARTITION BY. La requête sera la suivante :

SELECT farmer, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety, crop_year)                                                                                                                    AS total_same_variety_year
   FROM orange_production 

La clause OVER(PARTITION BY orange_variety, crop_year) crée des fenêtres en regroupant tous les enregistrements ayant la même valeur dans les colonnes orange_variety et crop_year. Ci-dessous, nous avons à nouveau utilisé différentes couleurs pour montrer les fenêtres de lignes créées par cette clause OVER:

Et voici les résultats de la requête :

Si vous souhaitez approfondir les fonctions de fenêtre MySQL, je vous suggère le cours LearnSQL.fr'Fonctions de fenêtrage , où vous trouverez une description complète de ce sujet et plusieurs exemples. Si vous avez l'intention d'utiliser fréquemment les fonctions de fenêtre, cette aide-mémoire - un guide rapide super complet des fonctions de fenêtre - est très utile.

Mais commençons par notre propre exploration de OVER et des fonctions de fenêtre dans MySQL.

Apprendre la clause OVER de MySQL par l'exemple

Dans cette section, nous allons explorer plusieurs exemples de requêtes qui illustrent différentes utilisations de la clause OVER dans MySQL.

Tout d'abord, nous allons utiliser la sous-clause ORDER BY dans la clause OVER. ORDER BY va générer une fenêtre avec les enregistrements classés selon un critère défini. Certaines fonctions (comme SUM(), LAG(), LEAD(), et NTH_VALUE()) peuvent renvoyer des résultats différents selon l'ordre des lignes dans la fenêtre. Supposons que l'agriculteur Pierre souhaite connaître sa production cumulée au fil des ans :

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(ORDER BY crop_year) cumulative_previous_years
 FROM  orange_production
WHERE  farmer = ‘Pierre’

La fonction fenêtre SUM(kilos_produced) OVER(ORDER BY crop_year) travaille sur une fenêtre ordonnée. Et elle ne prend en compte que les lignes actuelles et précédentes (c'est-à-dire les valeurs actuelles et précédentes de crop_year ). Nous pouvons voir le résultat de ce cumul SUM() dans le tableau de résultats :

Dans l'exemple suivant, nous allons combiner deux sous-clauses (PARTITION BY et ORDER BY) dans la clause OVER. Supposons que les agriculteurs veulent un rapport montrant le total produit par chaque agriculteur chaque année et le total des années précédentes. Il faut alors partitionner par la colonne farmer et ordonner par crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) cumulative_kilos_produced
   FROM orange_production

L'image suivante montre les fenêtres partitionnées par farmer_name en différentes couleurs ; notez qu'à l'intérieur de chaque fenêtre, les lignes sont ordonnées par crop_year.

L'expression SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) ordonne les lignes de la partition (qui est basée sur la valeur farmer ) en utilisant les valeurs crop_year. Vous pouvez le constater dans le tableau des résultats :

Pour plus d'informations sur la combinaison des clauses PARTITION BY et ORDER BY, consultez l'article SQL Fonctions de fenêtrage par explication.

Utilisation des fonctions positionnelles sur les fenêtres ordonnées

Ainsi, vous pouvez maintenant voir l'importance de pouvoir ordonner les lignes dans une fenêtre. En fait, certaines fonctions de fenêtre MySQL ne fonctionnent que sur des fenêtres ordonnées. Par exemple, la fonction LAG() nous permet d'obtenir une valeur de colonne à partir de la ligne précédente (liée à la ligne actuelle) dans une fenêtre ordonnée.

Imaginons que nous voulions un rapport montrant la production de l'année précédente comparée à celle de l'année en cours. Pour ce faire, nous utilisons la fonction LAG() sur une fenêtre ordonnée par crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced AS current_year_production, 
       LAG(kilos_produced) OVER(PARTITION BY farmer 
ORDER BY crop_year)AS previous_year_production
  FROM orange_production

La fonction LAG() peut renvoyer n'importe quelle valeur de colonne de la ligne précédente à la ligne actuelle, comme le montre le résultat de la requête suivante :

La fonction LEAD() renvoie n'importe quelle valeur de colonne de la ligne qui suit la ligne actuelle. De même, la fonction NTH_VALUE() renvoie la ligne à n'importe quelle position spécifiée dans une fenêtre ordonnée (par exemple, première, deuxième, dernière). Ces fonctions de fenêtre MySQL vous permettent de créer facilement des rapports complexes.

Essayons une opération mathématique à l'aide des fonctions de fenêtre MySQL. Supposons que les agriculteurs souhaitent un rapport contenant le delta de production par rapport à l'année précédente. Nous pouvons utiliser la fonction LAG() pour calculer la différence entre les productions de l'année précédente et de l'année en cours :

SELECT farmer, 
       crop_year,
       kilos_produced current_year_production, 
       kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer 
                         ORDER BY crop_year) AS production_delta
   FROM orange_production

L'expression kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) calcule le delta de production entre l'année précédente et l'année en cours, comme nous pouvons le voir dans les résultats :

Pour les lecteurs qui souhaitent approfondir les fonctions positionnelles, je suggère l'article Common SQL Fonctions de fenêtrage: Positional Functions, qui contient plusieurs exemples de ce type intéressant de fonction fenêtre.

En savoir plus sur MySQL OVER et Fonctions de fenêtrage

Dans cet article, nous avons couvert plusieurs exemples d'utilisation de la clause MySQL OVER, des utilisations très simples aux plus complexes. Il est essentiel de savoir comment fonctionne OVER et quelles lignes il inclut dans la fenêtre pour pouvoir utiliser les fonctions de fenêtre.

Il existe de nombreuses fonctions de fenêtre MySQL que vous pouvez expérimenter : AVG(), MIN(), MAX(), LAG(), LEAD() et NTH_VALUE(). Elles utilisent toutes la clause OVER de la même manière que nous venons de l'expliquer.

Enfin, pour les lecteurs qui souhaitent en savoir plus sur les fonctions de fenêtre MySQL, je recommande le cours interactif LearnSQL Fonctions de fenêtrage. Vous pouvez en savoir plus à ce sujet dans notre article intitulé Le cours SQL du mois - Fonctions de fenêtrage. Vous trouverez dans ce cours des explications détaillées et de nombreux exemples d'utilisation des différentes fonctions de fenêtre. Et n'oubliez pas : lorsque vous améliorez vos compétences MySQL, vous augmentez vos actifs !