MySQL Fonctions d'agrégation : SOMME, NB, AVG & MAX

Que sont les fonctions agrégées dans MySQL?

An fonction d'agrégation Cette fonction lit plusieurs lignes d'une même colonne et les regroupe en une seule valeur. Les fonctions d'agrégation servent principalement à :

• Effectuer des calculs sur plusieurs lignes
• D'une seule colonne d'un tableau
• Et renvoyer une seule valeur.

La norme ISO définit cinq (5) fonctions agrégées, à savoir :

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

Une seule règle s'applique aux cinq : Les fonctions d'agrégation ignorent les valeurs NULL. COUNT(*) est la seule exception, et nous allons voir pourquoi ci-dessous.

Pourquoi utiliser les fonctions d'agrégation

Les besoins en information varient selon le niveau hiérarchique. Les cadres supérieurs s'intéressent généralement aux chiffres globaux, et non aux détails individuels.

Les fonctions d'agrégation nous permettent de produire facilement des données résumées à partir de notre base de données.

Par exemple, à partir de notre base de données Myflix, la direction peut avoir besoin des rapports suivants :

• Films les moins loués.
• Films les plus loués.
• Nombre moyen de locations de chaque film par mois.

Tous les rapports ci-dessus proviennent de fonctions d'agrégation. Examinons-les en détail.

Fonction COUNT

La fonction COUNT renvoie le nombre total de valeurs dans le champ spécifié, pour les types de données numériques et non numériques. Comme toute fonction d'agrégation, COUNT(colonne) exclut les valeurs NULL.

COUNT(*) est une forme spéciale qui renvoie le nombre total de lignes d'un tableau. Elle compte également les éléments manquants. NULL et les doublons, car il compte les lignes plutôt que les valeurs.

La table movierentals contient ces données :

Supposons que nous voulions obtenir le nombre de fois où le film ayant l'identifiant 2 a été loué.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Exécuter ceci dans MySQL Workbench La requête contre myflixdb renvoie 3, car trois lignes contiennent movie_id 2.

Mot-clé DISTINCT

COUNT répond « combien ». La question suivante est généralement « combien ? » différent « ceux-là », et c’est à cela que sert DISTINCT.

Le mot-clé DISTINCT permet d'exclure les doublons de nos résultats par groupe.ping Des valeurs identiques regroupées, exactement comme le suggère l'illustration ci-dessus.

Commençons par exécuter une requête simple.

SELECT `movie_id` FROM `movierentals`;

Voici maintenant la même requête avec le mot-clé DISTINCT :

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT omet les enregistrements en double :

COUNT vs COUNT(*) vs COUNT(DISTINCT) : lequel utiliser ?

DISTINCT peut également être placé à l'intérieur une fonction d'agrégation, et c'est là que la plupart des débutants échouent. track lignes sont effectivement comptabilisées. Les quatre formulaires ci-dessous utilisent tous le même tableau de location de films à cinq lignes présenté précédemment, mais ils ne renvoient pas tous le même nombre. La différence tient à deux questions : le formulaire compte-t-il les lignes ou les valeurs, et conserve-t-il les doublons ?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

Astuce : Utilisez COUNT(*) pour compter les lignes, COUNT(colonne) lorsqu'une valeur NULL doit signifier « ne s'applique pas », et COUNT(DISTINCT colonne) pour les valeurs uniques. L'inverse de DISTINCT est ALL ; c'est le comportement par défaut, et donc rarement écrit en toutes lettres.

Fonction MIN

La fonction MIN renvoie la plus petite valeur dans le champ de table spécifié.

Supposons que nous voulions connaître l'année de sortie du film le plus ancien de notre bibliothèque. MySQLLa fonction MIN de nous donne cela.

SELECT MIN(`year_released`) FROM `movies`;

Résultat:

Fonction MAX

Comme son nom l'indique, la fonction MAX est l'opposé de la fonction MIN. Il renvoie la plus grande valeur du champ de table spécifié.

Supposons que nous souhaitions connaître l'année de sortie du dernier film de notre base de données. L'exemple suivant permet de la retrouver.

SELECT MAX(`year_released`) FROM `movies`;

Résultat:

Fonction SUM

MIN et MAX sélectionnent une valeur existante dans une colonne. SUM et AVG calculer un nouveau nombre à partir de toute la colonne.

Supposons que nous voulions connaître le montant total des paiements effectués jusqu'à présent. MySQL SUM fonction renvoie la somme de toutes les valeurs de la colonne spécifiée. SUM fonctionne uniquement sur les champs numériques et Les valeurs NULL sont exclues du résultat.

Le tableau suivant présente les données du tableau des paiements.

La requête ci-dessous récupère tous les paiements effectués et les additionne en un seul résultat : 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Résultat:

AVG fonction

Le MySQL AVG fonction renvoie la moyenne des valeurs dans une colonne spécifiée. Tout comme la fonction SUM, elle fonctionne uniquement sur les types de données numériques.

Supposons que nous souhaitions calculer le montant moyen payé. Nous pouvons utiliser la requête suivante, qui divise le total de 10 500 par les trois lignes de paiement non nulles.

SELECT AVG(`amount_paid`) FROM `payments`;

Résultat:

⚠️ Attention : AVG La division se fait par le nombre de lignes non NULL, et non par le nombre total de lignes du tableau. Les valeurs NULL sont ignorées au lieu d'être comptabilisées comme zéro, ce qui augmente discrètement la moyenne. AVG(IFNULL(`amount_paid`, 0)) lorsqu'une valeur manquante signifie zéro.

Exemple pratique : Combinaison de fonctions d’agrégation avec GROUP BY

Chaque fonction ci-dessus a renvoyé une valeur pour l'ensemble du tableau. L'ajout d'un PAR GROUPE La clause renvoie une figure par groupe En revanche, c'est ainsi que se construisent les vrais rapports.

L'exemple suivant regroupe les membres par nom, puis compte le nombre total de paiements, le montant moyen des paiements et le total général des montants des paiements pour chaque membre.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

Exécution de l'exemple ci-dessus dans MySQL Workbench nous donne les résultats suivants.

La requête joint les deux tables dans la clause WHERE — l'ancienne méthode de jointure par virgule. Le code moderne écrit la même logique de manière explicite. Jonction intérieure… surNotez également que chaque colonne non agrégée de la liste SELECT doit apparaître dans GROUP BY, ou MySQL 5.7 et versions ultérieures rejettent la requête sous ONLY_FULL_GROUP_BY. Voir le officiel MySQL référence de fonction agrégée.

FAQ