Skip to content

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 lesquels expression est not null.
  • avg(expression), Calcule la moyenne de expression.
  • min(expression), Calcule la valeur minimale de expression.
  • max(expression), Calcule la valeur maximale de expression.
  • sum(expression), Calcule la somme de expression.
  • std(expression), Calcule l'écart-type de expression.

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