Agrégation

Agrégation

L'idée d'agrégation est de pouvoir calculer des synthèses à partir des données récupérées d'une requête SQL.

Par exemple, je veux calculer la somme de valeurs extraites d'une requête, ou bien compter le nombre de lignes retournées.

Il y a plusieurs opérations d'agrégation possibles :

• Compter : count

• La somme : sum

• La moyenne : avg

• Le minimum : min

• Le maximum : max

À noter, si j'utilise un opérateur d'agrégation, le résultat sortant ne contiendra que la valeur d'agrégation, et pas toutes les lignes demandées par la requête select !

Par exemple, je veux connaître le nombre d'interventions et le budget moyen qui ont eu lieu dans l'Eure :

select count(*) as 'Intervention', avg(budget) as 'Budget moyen'
from action 
where 
  address_french_departmentId = '27';

Le résultat est :

Intervention	Budget moyen
123	339.390244

À noter, qu'un plan d'action implicite a été créé par notre SGBDR :

• Une table temporaire contenant les lignes qui remplissent notre clause where est construit

• Cette table est traversée, en calculant les valeurs synthèses

Exemple : count et distinct

Je veux savoir le nombre total d'intervenants qui sont intervenus en novembre 2022.

Pour cela, il y a plusieurs jointures à faire :

• on commence par récupérer tous les événements de cette période (la table action_event)

• pour chaque événement, on peut récupérer les intervenants, en faisant un produit cartésien avec event_animator (avec la contrainte que le eventId soit identique)

Je fais un premier teste de construction d'une requête SQL :

select count(*)
from 
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId
;

Résultat : 3397 lignes

ALERTE ! ALERTE ! ALERTE !

Ici il y a eu une mauvaise interprétation des données, ou bien de la question !

Je veux connaître le nombre d'intervenants, alors cette requête me donne le nombre d'interventions par chaque intervenant !

Vous ne voyez pas trop la différence ? Dans ce cas, je conseille toujours de visualiser les données avant de procéder avec l'agrégation.

Commençons juste par visualiser l'ID de l'intervenant et les dates :

select ea.animatorId, e.startUtc
from 
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId
;

Qui retourne :

animatorId	startUtc
5ff8c7cfbf559e0012e3123c	2022-11-18 09:00:00
5ff8c7cfbf559e0012e3123c	2022-11-25 09:00:00
5ff8b8489b4af50013c919cd	2022-11-10 10:00:00
5ff8b8489b4af50013c919cd	2022-11-17 10:00:00
5ff8b8489b4af50013c919cd	2022-11-24 10:00:00
60cb4a6500c00100129f6de2	2022-11-09 16:00:00
60cb4a6500c00100129f6de2	2022-11-16 16:00:00
60cb4a6500c00100129f6de2	2022-11-23 16:00:00
60cb4a6500c00100129f6de2	2022-11-30 16:00:00
5ff8c5bf4875ad00136fbeb7	2022-11-07 14:15:00
61fd46f0f3cd6f0019f27645	2022-11-08 14:00:00
61fd46f0f3cd6f0019f27645	2022-11-15 14:00:00
5ff8b88c4875ad00136fbce3	2022-11-22 14:00:00
61fd46f0f3cd6f0019f27645	2022-11-29 14:00:00
...	...

Ah oui ! On voit toute suite que l'intervenant 5ff8c7cfbf559e0012e3123c intervient dans plusieurs événements pendant la période. Et, il sera compté 5 fois dans notre agrégation !

On peut utiliser distinct pour corriger l'erreur :

select count(distinct ea.animatorId) as Intervenants
from 
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId
;

Le résultat : 772 intervenants

Attention aux détails :

• On utilise l'opérateur distinct uniquement sur l'attribut animatorId, car distinct agit sur l'ensemble des attributs. Si on avait gardé startUtc, distinct n'aurait pas changé le résultat

• Ensuite, on compte le nombre de résultats avec count

Groupes

Je sais maintenant qu'il y a eu 772 intervenants qui ont travaillé le mois de novembre 2022. En revanche, maintenant, je veux construire un rapport qui détaille le nombre total d'événements de chaque intervenant.

Dans ce cas, il serait utile de grouper les résultats de la requête précédente, et compter le nombre de lignes par groupe. On utilise la clause group by :

select ea.animatorId, count(*) as 'Evénements'
from 
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId
group by 
  ea.animatorId
;

Le résultat :

animatorId	Evénements
5f1fd5776ecbce00107beb84	4
5f3a82f408d0f10010d8eafe	4
5f3b9e0f6ecbce00107bec30	26
5f3d14af08d0f10010d8eb2a	19
5f3d154f6ecbce00107bec8d	15
...	...

Attention aux détails :

• la clause group by peut agir sur plusieurs attributs (comme distinct), et va créer des groupes, des tuples identiques

• j'enlève donc distinct de la requête

Explorer un peu plus group by !

• Enlever la clause count(*) de la requête (en laissant group by). Vous remarquez des différences ?

• Ajoutez startUtc à la clause select (uniquement). Vous arrivez à expliquer le résultat ?

• Ensuite, ajoutez startUtc à la clause group by. Vous arrivez à expliquer le résultat ?

Bien sûr, notre résultat n'est pas très intéressant pour notre rapport. Idéalement, je récupérerai les noms/prénoms des intervenants pour mon rapport, qui demande d'autres jointures :

select ea.animatorId, u.givenName, u.familyName, count(*) as 'Evénements'
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
group by 
  ea.animatorId
;

Exercice avec groupes

Je veux générer un rapport qui me sort les salaires à payer pour chaque intervenant pour le mois de novembre 2022. Le rapport doit lister :

• Le prénom et nom de l'intervenant

• Le nombre d'heures travaillé

• Le total à lui payer

L'intervenant est payé par heure. Donc, pour un intervenant payé à 25 € l'heure, pour événement de 2 heures, l'intervenant sera payé 50 €.

Astuces et feuille de route :

• Commencez par analyser le schéma :

  • Quel(s) attribut(s) décrive(nt) la durée d'un événement ?

  • On récupère où son tarif horaire ?

• Essayez d’abord construire une requête qui indique le début et la fin de chaque événement de chaque intervenant, en affichant son tarif horaire

• Ensuite, vous arrivez à transformer le début et la fin de chaque événement en nombre d'heures ? Astuce : regardez les fonctions timediff et time_to_sec

• Ensuite, vous arrivez à calculer la somme à payer pour chaque événement ?

• Ensuite, essayez de calculer la synthèse (agrégation) pour chaque intervenant

Solution

On commence par construire une requête simple qui affiche le début, la fin et le tarif horaire :

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  ea.fixedHourlyRate, 
  e.startUtc, 
  e.endUtc
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
limit 10
;

On voit, par exemple, Aubry Aloïs a 8 événements pendant la période. Quelques événements durent 3 heures, d'autres 2 heures.

On peut utiliser la fonction timediff afin de calculer la différence des deux datetime:

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  ea.fixedHourlyRate, 
  e.startUtc, 
  e.endUtc,
  timediff(e.endUtc,e.startUtc)
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
limit 10
;

Cela nous ajoute une colonne en format TIME, par exemple :

03:00:00   

Pas très pratique. Idéalement, on aura une seule valeur numérique qu'on peut utiliser pour multiplier avec le tarif horaire. La fonction hour nous aidera potentiellement, mais en fait elle nous retourne uniquement un entier. Si jamais une personne travaille une heure et demie, cette fonction retournera une valeur de 2.

On va devoir convertir la valeur de TIME en secondes avec time_to_sec, puis diviser par 60 (pour le nombre de minutes) puis diviser par 60 pour avoir le nombre d'heures :

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  ea.fixedHourlyRate, 
  e.startUtc, 
  e.endUtc,
  time_to_sec(timediff(e.endUtc,e.startUtc)) / 3600
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
limit 10
;

On a maintenant le nombre d'heures.

On veut utiliser ce résultat pour calculer le salaire, mais aussi l'afficher comme une colonne qui s'appelle "hours".

Je vous présente la notion de variables dans SQL ! On peut affecter le résultat d'une fonction dans une variable (qui commence par @ et avec le symbole d'affectation :=), puis utilise cette variable pour une colonne suivante :

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  ea.fixedHourlyRate, 
  e.startUtc, 
  e.endUtc,
  @hours := time_to_sec(timediff(e.endUtc,e.startUtc)) / 3600 as hours,
  @hours * ea.fixedHourlyRate as salary
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
limit 10
;

Enfin, on peut calculer la synthèse :

• On enlève les attributs qui n'ont pas de sens dans la synthèse (comme date de début et fin, tarif horaire, etc)

• On ajoute des opérateurs de synthèse : ici sum pour additionner les colonnes

• On ajoute notre critère de groupement avec group by

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  sum(@hours := time_to_sec(timediff(e.endUtc,e.startUtc)) / 3600) as hours,
  sum(@hours * ea.fixedHourlyRate) as salary
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
group by ea.animatorId
limit 10
;

On peut aussi arrondir à deux points de décimal :

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  round(sum(@hours := time_to_sec(timediff(e.endUtc,e.startUtc)) / 3600), 2) as hours,
  round(sum(@hours * ea.fixedHourlyRate), 2) as salary
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
group by ea.animatorId
limit 10
;

La clause having

Parfois, on aimerait appliquer un deuxième phase de filtrage sur le résultat d'une synthèse.

Par exemple, imaginons qu'il y a une règle qui interdit le travail de plus que 35 heures par mois. On aimerait sortir un rapport de tous les intervenants qui ont travaillé plus que 35 heures pour la période de novembre 2022.

On peut d'abord s'inspirer de l'exercice précédent pour sortir le nombre d'heures travaillées par chaque intervenant :

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  round(sum(time_to_sec(timediff(e.endUtc,e.startUtc)) / 3600), 2) as hours
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
group by ea.animatorId
limit 10
;

animatorId	givenName	familyName	hours
5f1fd5776ecbce00107beb84	Antoinette	Mercier	4.00
5f3a82f408d0f10010d8eafe	Amarande	Paul	4.00
5f3b9e0f6ecbce00107bec30	Landry	Fernandez	71.00
5f3d14af08d0f10010d8eb2a	Alcime	Dubois	36.00
5f3d154f6ecbce00107bec8d	Herluin	Royer	51.00

On peut ensuite appliquer un deuxième filtre sur ce résultat avec la clause having :

select 
  ea.animatorId, 
  u.givenName, 
  u.familyName, 
  round(sum(time_to_sec(timediff(e.endUtc,e.startUtc)) / 3600), 2) as hours
from 
  user as u,
  animator as a,
  action_event as e,
  event_animator as ea
where 
  year(e.startUtc) = 2022 and
  month(e.startUtc) = 11 and
  e.eventId = ea.eventId and
  a.animatorId = ea.animatorId and
  a.userId = u.userId
group by ea.animatorId
having hours > 35
limit 10
;

animatorId	givenName	familyName	hours
5f3b9e0f6ecbce00107bec30	Landry	Fernandez	71.00
5f3d14af08d0f10010d8eb2a	Alcime	Dubois	36.00
5f3d154f6ecbce00107bec8d	Herluin	Royer	51.00
..	..	..	..

Les valeurs null et l'agrégation

Attention ! Toutes les fonctions sauf count ignorent les valeurs null.

Exercices

Les exercices suivants sont basés sur la base de données SAKILA, une base de données de test fourni par MySQL.

Récupérez la base SAKILA ici.

Question 1

Affichez la liste des noms de famille des acteurs, et comptez le nombre d’acteurs qui partagent le même nom de famille. Appelez cette deuxième colonne « shared_surname »

Solution

121 lignes.

select last_name, count(*) as shared_surname
from actor
group by last_name;

Question 2

Affichez uniquement la liste des acteurs qui partagent le même nom de famille.

Solution

55 lignes.

select last_name, count(*) as shared_surname
from actor
group by last_name
having shared_surname > 1;

Question 3

Calculez le prix de location moyen pour chaque genre de film. Quel est le prix moyen pour le genre Drama ?

Solution

16 lignes. Le prix moyen pour le genre Drama est 3.022258.

select 
  category.name, 
  avg(rental_rate)
from film, film_category, category
where 
  film_category.film_id = film.film_id and 
  category.category_id = film_category.category_id
group by 
  film_category.category_id, 
  category.name;

Question 4

Combien de genres ont un prix moyen supérieur à 3€ ?

Solution

9 lignes. Le prix moyen pour le genre Drama est 3.022258.

select 
  category.name, 
  avg(rental_rate) as avg_price
from film, film_category, category
where 
  film_category.film_id = film.film_id and 
  category.category_id = film_category.category_id
group by film_category.category_id, category.name
having avg_price > 3;