SQL - requêtesOpérations d'agrégation

Vous savez maintenant faire absolument toutes les requêtes qui traitent des attributs en SQL! Mais cela ne veut évidemment pas dire que vous savez faire toutes les requêtes. Il reste encore une catégorie que nous n'avons pas vu, les requêtes d'agrégation.
Elles permettent de répondre aux questions du type:

Quel est le nombre d'étudiants inscrits dans chaque département?

Quel est l'âge moyen des étudiants?

Elles donnent la possibilité de calculer des fonctions sur une colonne du résultat, ce qui implique que la requête retourne une colonne et une ligne.
Elles prennent en compte la plupart des opérations arithmétiques classiques: sum, avg, min, count, ...

mysql> # nombre de lignes dans la table gène
mysql> select count(*) from gene;
mysql> # nombre de lignes dans la table gène et renommage
mysql> select count(*) as nb from gene;
mysql> # position moyenne de début d'un gène
mysql> select avg(debut) from gene;

Voici la liste des opérations que vous pouvez utiliser:

count(*): nombre de lignes renvoyées par la requête

count(distinct attr): nombre de valeurs distinctes trouvées pour l'attribut attr

max(attr): valeur maximale de toutes les valeurs de attr

min(attr): valeur minimale de toutes les valeurs de attr

avg(attr): valeur moyenne de toutes les valeurs de attr

stddev(attr): écart-type de toutes les valeurs de attr

sum(attr): somme de toutes les valeurs de attr

variance(attr): variane de toutes les valeurs de attr

Voyons quelques exemples:

mysql> # nombre de lignes dans la table tab1
mysql> select count(*) from tab1;
mysql> # nombre de numofficiel différents dans la table tab1
mysql> select count(distinct numofficiel) from tab1;
mysql> # sommme des valeurs dans la table tab1
mysql> select sum(valeur) from tab1;
mysql> # moyenne des valeurs dans la table tab1
mysql> select avg(valeur) from tab1;
mysql> # écart-type des valeurs dans la table tab1
mysql> select stddev(valeur) from tab1;
mysql> # valeur la plus haute dans la table tab1
mysql> select max(valeur) from tab1;
mysql> # valeur la plus basse dans la table tab1
mysql> select min(valeur) from tab1;

Regroupement des valeurs

Il peut être fréquent d'utiliser ces fonctions d'agrégation, mais telles quelles, ça ne servira pas très souvent. Ce qui est beaucoup plus fréquent, c'est de les regrouper. On utilisera alors la clause group by.
Cette clause permet de généraliser les fonctions précédentes: le calcul des agrégations est alors possible sur des éléments d'une partition. Elle permet d'utiliser les fonctions d'agrégation sur des sous-ensembles de valeurs.

Voilà la syntaxe:
Select <liste d'attributs>
from <listes relations>
where <conditions sur les lignes>
group by <liste d'attributs>
having <conditions sur les éléments de la partition>;

La partition de la table résultat se fait sur les attributs spécifiés après le group by. Le résultat de la requête comporte autant de lignes que d'éléments dans la partition.

Soit achat(id, client, date, prix) une table de notre base de données. Le prix moyen des achats par client sera trouvé pas la requête suivante:

mysql> select client, avg(client); from achat group by client;

L'utilisation de la clause group by impose des restrictions sur la liste des attributs de la clause select:

soit un attribut agrégé, c'est-à-dire avec SUM, AVG ...
soit un attribut qui apparaît déjà dans la clause group by

Conditions de regroupement sur les regroupements

Il est évidemment possible, tout comme pour les attributs, de mettre en place des conditions sur les fonctions d'agrégation.
La clause where permettait de définir des sélections sur les lignes du résultat d'une requête. La clause having va permettre de définir des sélections sur les éléments d'une partition.

Le prix moyen des achats par client si ils ont fait au moins deux achats sera trouvé pas la requête suivante:

mysql> select client, avg(client); from achat group by client having count(*)>=2;

Attention!!! Je sais que beaucoup d'entre vous feront l'erreur d'utiliser where pour les fonctions d'agrégation. Donc je le rappelle where count(*)... vous enverra vers une erreur!