# 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 :

```sql
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 :

```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

{% hint style="warning" %}
**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* !
{% endhint %}

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 :

```sql
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 :

```sql
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` :

```sql
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

{% hint style="info" %}
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 ?
  {% endhint %}

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 :

```sql
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

<details>

<summary>Solution</summary>

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

```sql
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`:

```sql
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`](https://mariadb.com/kb/en/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`](https://mariadb.com/kb/en/time_to_sec/), puis diviser par 60 (pour le nombre de minutes) puis diviser par 60 pour avoir le nombre d'heures :

```sql
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 :

```sql
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`

```sql
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 :

```sql
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
;
```

</details>

### 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 :

```sql
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` :

```sql
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.](https://dev.glassworks.tech:18081/courses/sgbdr/sgbdr-supports/-/tree/main/src/samples/sakila)

### 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 »

<details>

<summary>Solution</summary>

121 lignes.

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

</details>

### Question 2

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

<details>

<summary>Solution</summary>

55 lignes.

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

</details>

### Question 3

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

<details>

<summary>Solution</summary>

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

```sql
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;
```

</details>

### Question 4

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

<details>

<summary>Solution</summary>

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

```sql
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;
```

</details>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.glassworks.tech/sgbdr/interrogation/015-agregation.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.