Fonctions d'agrégation dans MySQL

Les fonctions d'agrégation concernent

  • 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 d'agrégation à savoir :

1) COMPTER
2) SOMME
3) AVG
4) MINIMUM
5) MAXIMUM

Pourquoi utiliser les fonctions d'agrégation

D'un point de vue commercial, différents niveaux d'organisation ont des besoins en informations différents. Les managers de haut niveau souhaitent généralement connaître les chiffres complets et non nécessairement les 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 exiger les rapports suivants

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

Nous produisons facilement les rapports ci-dessus à l’aide de fonctions d’agrégation.

Examinons les fonctions d'agrégation en détail.

Fonction COUNT

La fonction COUNT renvoie le nombre total de valeurs dans le champ spécifié. Il fonctionne sur les types de données numériques et non numériques. Toutes les fonctions d'agrégation excluent par défaut les valeurs nulles avant de travailler sur les données.

COUNT (*) est une implémentation spéciale de la fonction COUNT qui renvoie le nombre de toutes les lignes d'une table spécifiée. COUNT (*) considère également Nuls et les doublons.

Le tableau ci-dessous montre les données du tableau de location de films

numéro de réference Date de la transaction date de retour numéro de membre id_film film_ revenu
11 20-06-2012 NULL 1 1 0
12 22-06-2012 25-06-2012 1 2 0
13 22-06-2012 25-06-2012 3 2 0
14 21-06-2012 24-06-2012 2 2 0
15 23-06-2012 NULL 3 3 0

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

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

Exécution de la requête ci-dessus dans MySQL établi contre myflixdb nous donne les résultats suivants.

COUNT('movie_id')
3

Mot-clé DISTINCT

Mot-clé DISTINCT

Le mot-clé DISTINCT qui nous permet d'omettre les doublons de nos résultats. Ceci est réalisé en regroupant des valeurs similaires.

Pour apprécier le concept de Distinct, exécutons une requête simple

SELECT `movie_id` FROM `movierentals`;

movie_id
1
2
2
2
3

Exécutons maintenant la même requête avec le mot-clé distinct-

SELECT DISTINCT `movie_id` FROM `movierentals`;

Comme indiqué ci-dessous, distinct omet les enregistrements en double des résultats.

movie_id
1
2
3

Fonction MIN

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

A titre d'exemple, supposons que nous voulions connaître l'année de sortie du film le plus ancien de notre bibliothèque, nous pouvons utiliser MySQLde la fonction MIN pour obtenir les informations souhaitées.

La requête suivante nous aide à y parvenir

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

Exécution de la requête ci-dessus dans MySQL workbench contre myflixdb nous donne les résultats suivants.

MIN('year_released')
2005

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 obtenir l'année de sortie du dernier film de notre base de données. Nous pouvons facilement utiliser la fonction MAX pour y parvenir.

L'exemple suivant renvoie la dernière année de sortie du film.

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

Exécution de la requête ci-dessus dans MySQL L'établi utilisant myflixdb nous donne les résultats suivants.

MAX('year_released')
2012

Fonction SUM

Supposons que nous souhaitions un rapport indiquant le montant total des paiements effectués jusqu'à présent. Nous pouvons utiliser le MySQL SUM fonction qui renvoie la somme de toutes les valeurs de la colonne spécifiée. SUM fonctionne uniquement sur les champs numériques. Les valeurs nulles sont exclues du résultat renvoyé.

Le tableau suivant montre les données du tableau des paiements.

identifiant_de paiement numéro de membre date de paiement la description le montant payé numéro_de_référence_externe
1 1 23-07-2012 Paiement de la location du film 2500 11
2 1 25-07-2012 Paiement de la location du film 2000 12
3 3 30-07-2012 Paiement de la location du film 6000 NULL

La requête ci-dessous récupère tous les paiements effectués et les résume pour renvoyer un résultat unique.

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

Exécution de la requête ci-dessus dans MySQL L'établi contre myflixdb donne les résultats suivants.

SUM('amount_paid')
10500

AVG fonction

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 voulions trouver le montant moyen payé. Nous pouvons utiliser la requête suivante –

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

Exécution de la requête ci-dessus dans MySQL établi, nous donne les résultats suivants.

AVG('amount_paid')
3500

Casse-tête

Vous pensez que les fonctions d'agrégation sont faciles. Essaye ça!

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

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 L'établi nous donne les résultats suivants.

AVG fonction

Résumé

  • MySQL prend en charge les cinq (5) fonctions d'agrégation standard ISO COUNT, SUM, AVG, MIN et MAX.
  • SOMME et AVG les fonctions ne fonctionnent que sur des données numériques.
  • Si vous souhaitez exclure les valeurs en double des résultats de la fonction d'agrégation, utilisez le mot clé DISTINCT. Le mot clé ALL inclut même les doublons. Si rien n’est spécifié, ALL est considéré comme la valeur par défaut.
  • Les fonctions d'agrégation peuvent être utilisées conjointement avec d'autres clauses SQL telles que PAR GROUPE