1 4 5 3 agregats
SQL, récapitulatif ¾ : agrégats¶
Supports complémentaires
Les requêtes d'agrégation en SQL consistent à effectuer des
regroupements de nuplets en fonction des valeurs d'une ou plusieurs
expressions. Ce regroupement est spécifié par la clause group by
. On
obtient une structure qui n'est pas une table relationnelle puisqu'il
s'agit d'un ensemble de groupes de nuplets. On doit ensuite ramener
cette structure à une table en appliquant des fonctions de groupes qui
déterminent des valeurs agrégées calculées pour chaque groupe.
Enfin, il est possible d'exprimer des conditions sur les valeurs
agrégées pour ne conserver qu'un ou plusieurs des groupes constitués.
Ces conditions portent sur des groupes de nuplets et ne peuvent donc
être obtenues avec where
. On utilise alors la clause having
.
Les agrégats s'effectuent toujours sur le résultat d'une requête
classique select - from
. On peut donc les voir comme une extension de
SQL consistant à partitionner un résultat en groupes selon certains
critères, puis à exprimer des conditions sur ces groupes, et enfin à
appliquer des fonctions d'agrégation.
Il existe un groupe par défaut : c'est la table toute entière. Sans même
utiliser group by
, on peut appliquer les fonctions d'agrégation au
contenu entier de la table comme le montre l'exemple suivant.
select count(*) as nbPersonnes, count(prénom) as nbPrénoms, count(nom) as nbNoms
from Personne
Ce qui donne :
nbPersonnes | nbPrénoms | nbNoms |
---|---|---|
7 | 6 | 7 |
On obtient 7 pour le nombre de nuplets, 6 pour le nombre de prénoms, et
7 pour le nombre de noms. En effet, l'attribut prénom
est à null
pour la première personne et n'est en conséquence pas pris en compte
par la fonction d'agrégation. Pour compter tous les nuplets, on doit
utiliser count(*)
ou un attribut déclaré comme not null
. On peut
aussi compter le nombre de valeurs distinctes dans un groupe avec
count(distinct <expression>)
.
La clause group by
¶
Le rôle du group by
est de partitionner le résultat d'un bloc
select from where
en fonction d'un critère (un ou plusieurs
attributs, ou plus généralement une expression sur des attributs). Pour
bien analyser ce qui se passe pendant une requête avec group by
on
peut décomposer l'exécution d'une requête en deux étapes. Prenons
l'exemple de celle permettant de vérifier que la somme des quotes-parts
des propriétaires est bien égale à 100 pour tous les appartements.
select idAppart, sum(quotePart) as totalQP
from Propriétaire
group by idAppart
idAppart | totalQP |
---|---|
100 | 100 |
101 | 100 |
102 | 100 |
103 | 100 |
104 | 100 |
201 | 100 |
202 | 100 |
Dans une première étape le système va constituer les groupes. On peut
les représenter avec un tableau comprenant, pour chaque nuplet, d'une
part la (ou les) valeur(s) du (ou des) attribut(s) de partitionnement
(ici idAppart
), d'autre part l'ensemble de nuplets dans lesquelles
on trouve cette valeur. Ces nuplets "imbriqués" sont séparés par des
points-virgules dans la représentation ci-dessous.
idAppart | Groupe | count |
---|---|---|
100 | (idPersonne=1 quotePart=33 ; idPersonne=5 quotePart=67) | 2 |
101 | (idPersonne=1 quotePart=100) | 1 |
102 | (idPersonne=5 quotePart=100) | 1 |
103 | (idPersonne=2 quotePart=100) | 1 |
104 | (idPersonne=2 quotePart=100) | 1 |
201 | (idPersonne=5 quotePart=100) | 1 |
202 | (idPersonne=1 quotePart=100) | 1 |
Le groupe associé à l'appartement 100 est constitué de deux copropriétaires. Le tableau ci-dessus n'est donc pas une table relationnelle dans laquelle chaque cellule ne peut contenir qu'une seule valeur.
Pour se ramener à une table relationnelle, on transforme durant la
deuxième étape chaque groupe de nuplets en une valeur par application
d'une fonction d'agrégation. La fonction count()
compte le nombre de
nuplets dans chaque groupe, max()
donne la valeur maximale d'un
attribut parmi l'ensemble des nuplets du groupe, etc. La liste des
fonctions d'agrégation est donnée ci-dessous :
count(expression)
, Compte le nombre de nuplets pour lesquelsexpression
estnot null
.avg(expression
), Calcule la moyenne deexpression
.min(expression
), Calcule la valeur minimale deexpression
.max(expression
), Calcule la valeur maximale deexpression
.sum(expression
), Calcule la somme deexpression
.std(expression
), Calcule l'écart-type deexpression
.
Dans la norme SQL l'utilisation de fonctions d'agrégation pour les
attributs qui n'apparaissent pas dans le group by
est obligatoire.
Une requête comme :
select id, surface, max(niveau) as niveauMax
from Appart
group by surface
sera rejetée parce que le groupe associé à une même surface contient
deux appartements différents (et donc deux valeurs différentes pour
id
), et qu'il n'y a pas de raison d'afficher l'un plutôt que
l'autre.
Quelques exemples¶
Note Vous pouvez exécuter ces requêtes sur le site des TP http://deptfod.cnam.fr/bd/tp
Calculons la surface totale des appartements, groupés par immeuble. Décomposons : nous avons d'abord besoin du bloc "select - from - where" avec les identifants d'immeubles et les surfaces d'appartement.
select idImmeuble, surface
from Appart
On ajoute à cette requête la clause group by
pour grouper par
immeuble. On obtient alors (en phase intermédiaire) deux groupes
d'appartements, un pour chaque immeuble. Il reste à appliquer une
fonction d'agrégation pour ramener ces groupes à une valeur atomique.
select idImmeuble, sum(surface) as totalSurface
from Appart
group by idImmeuble
On pourrait aussi appliquer d'autres fonctions d'agrégation :
select idImmeuble, min(niveau) as minEtage, max(niveau) as maxEtage,
sum(surface) as totalSurface
from Appart
group by idImmeuble
Revenons un moment à nos voyageurs et à leurs séjours. La requête ci-dessous doit être claire. Exécutez-la sur le site : on constate qu'un voyageur a effectué plusieurs séjours et qu'un logement a reçu plusieurs voyageurs.
select v.nom as nomVoyageur, l.nom as nomLogement
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
En ajoutant une clause group by
on produit des statistiques sur le
résultat de cette requête. Par exemple, en groupant sur les voyageurs
select v.nom as nomVoyageur, count(*) as 'nbSéjours'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by v.idVoyageur
Ou en groupant sur les logements
select l.nom as nomLogement, count(*) as 'nbVoyageurs'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by l.code
On peut aussi regrouper sur plusieurs attributs. Pour obtenir par exemple le nombre de séjours effectués par un voyageur dans un même logement.
select l.nom as nomLogement, v.nom as 'nomVoyageur', count(*) as 'nbSéjours'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by l.code, v.idVoyageur
Moralité : à partir d'une requête SQL select - from -- where
, aussi
complexe que nécessaire, vous produisez un résultat (une table). Le
group by
permet d'effectuer des regroupements et des agrégations
(simples) sur ce résultat. Il s'agit vraiment d'un complément au SQL
que nous avons étudié en long et en large.
La clause having
¶
Finalement, on peut faire porter des conditions sur les groupes, ou plus
précisément sur le résultat de fonctions d'agrégation appliquées à des
groupes avec la clause having
. Par exemple, on peut sélectionner les
appartements pour lesquels on connaît au moins deux copropriétaires.
select idAppart, count(*) as nbProprios
from Propriétaire
group by idAppart
having count(*) >= 2
On voit que la condition porte ici sur une propriété de l'ensemble
des nuplets du groupe et pas de chaque nuplet pris individuellement. La
clause having
est donc toujours exprimée sur le résultat de fonctions
d'agrégation, par opposition avec la clause where
qui ne peut
exprimer des conditions que sur les nuplets pris un à un.
Important La requête ci-dessus pourrait s'exprimer en utilisant l'alias pour éviter d'avoir à répeter deux fois le
count(*)
(et pour la rendre plus claire).
select idAppart, count(*) as nbProprios
from Propriétaire
group by idAppart
having nbProprios >= 2
Il n'est malheureusement pas sûr que l'utilisation de l'alias dans le
group by
soit acceptée dans tous les systèmes.
Quelques exemples pour conclure. Toujours sur la base des voyageurs : quels voyageurs ont effectué au moins deux séjours ?
select v.nom as nomVoyageur, count(*) as 'nbSéjours'
from Voyageur as v, Séjour as s, Logement as l
where v.idVoyageur = s.idVoyageur
and l.code = s.codeLogement
group by v.idVoyageur
having count(*) > 1
Quels logements proposent moins de deux activités ?
select l.nom
from Logement as l, Activité as a
where l.code = a.codeLogement
group by l.code
having count(*) < 2
Voici enfin une requête un peu complexe (sur la base des immeubles) sélectionnant la surface possédée par chaque copropriétaire pour l'immeuble 1. La surface possédée est la somme des surfaces d'appartements possédés par un propriétaire, pondérées par leur quote-part. On regroupe par propriétaire et on trie sur la surface possédée.
select prénom nom,
sum(quotePart * surface / 100) as 'surfacePossédée'
from Personne as p1, Propriétaire as p2, Appart as a
where p1.id=p2.idPersonne
and a.id=p2.idAppart
and idImmeuble = 1
group by p1.id
order by sum(quotePart * surface / 100)
On obtient le résultat suivant.
nom | surfacePossédée |
---|---|
null | 99.5000 |
Alice | 125.0000 |
Alphonsine | 300.5000 |