#Agrégations : résumer des ensembles de lignesCopier le lien

Les agrégations condensent des ensembles de lignes en une valeur : COUNT, SUM, AVG, MIN, MAX. GROUP BY forme les groupes, HAVING filtre après agrégation. La différence WHERE/HAVING et le traitement des NULL expliquent la majorité des résultats surprenants.

#Prérequis et objectifsCopier le lien

• Prérequis : SELECT (ordre logique d'évaluation, NULL), JOIN (cardinalités du 1 vers N).
• Écrire des agrégats globaux et par groupe, avec le bon filtre au bon endroit.
• Prévoir l'effet des NULL sur chaque fonction d'agrégation.
• Distinguer GROUP BY (une ligne par groupe) des fonctions de fenêtre (une ligne par ligne d'entrée).

#Le pipeline d'agrégationCopier le lien

#Jeu de données fil rougeCopier le lien

1create table users(id integer primary key, name text not null);2create table orders(3  id integer primary key,4  user_id integer not null references users(id),5  amount real not null,6  order_date text not null7);8 9insert into users values (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');10insert into orders values11  (1, 1, 40.0,  '2026-01-05'),12  (2, 1, 60.0,  '2026-01-12'),13  (3, 2, 15.5,  '2026-01-08'),14  (4, 2, 90.0,  '2026-02-01'),

Agrégat global, puis par groupe :

1select count(*) as nb_commandes, sum(amount) as chiffre,2       avg(amount) as panier_moyen, min(amount) as mini, max(amount) as maxi3from orders;4-- Attendu : 1 ligne ; 5, 217.5, 43.5, 12.0, 90.05 6select user_id, count(*) as n, sum(amount) as total7from orders8group by user_id9having sum(amount) > 10010order by total desc;11-- Attendu : 1 ligne ; Alice (1) : 2 commandes, 100.012-- Bob totalise 117.5... vérifiez : 15.5 + 90.0 + 12.0 = 117.5, donc Bob passe aussi

Correction du commentaire ci-dessus : les deux groupes dépassent 100, le résultat attendu est donc 2 lignes, Alice 100.0 et Bob 117.5, Bob en tête. Leçon : faites l'addition vous-même avant d'exécuter ; c'est exactement le genre d'erreur d'inattention qu'un having mal calibré révèle.

#WHERE contre HAVINGCopier le lien

WHERE filtre les lignes avant agrégation, HAVING filtre les groupes après calcul des agrégats. Conséquence pratique : un prédicat sur une colonne brute va dans WHERE (moins de lignes à agréger), un prédicat sur un agrégat va nécessairement dans HAVING.

1-- WHERE élimine d'abord les petites commandes, puis on agrège2select user_id, sum(amount) as total3from orders4where amount >= 205group by user_id;6-- Attendu : 2 lignes ; Alice 100.0 (40+60), Bob 90.0 (seule la commande de 90 survit)7 8-- HAVING filtre les groupes après calcul9select user_id, sum(amount) as total10from orders11group by user_id12having sum(amount) >= 100;13-- Attendu : 2 lignes ; Alice 100.0, Bob 117.5

#Un agrégat ne se filtre pas dans WHERECopier le lien

Erreur la plus fréquente en TD, et elle vient d'une confusion naturelle : on veut « les étudiants au-dessus de la moyenne » et on écrit une comparaison à AVG(...) dans le WHERE.

1-- FAUX : un agrégat n'existe pas encore au moment du WHERE2select etudiant from EtudiantUE3where uniteValeur = 'SL2IBD' and noteExam > avg(noteExam);

WHERE s'évalue avant GROUP BY, donc avant tout calcul d'agrégat : la fonction AVG n'a rien à agréger à ce stade, et le moteur rejette la requête (ou, pire, l'accepte avec une sémantique surprenante selon le dialecte). Deux écritures correctes :

1-- Solution 1 : sous-requête scalaire non corrélée (la moyenne est une constante)2select etudiant from EtudiantUE3where uniteValeur = 'SL2IBD'4  and noteExam > (select avg(noteExam) from EtudiantUE where uniteValeur = 'SL2IBD');5 6-- Solution 2 : sous-requête corrélée sur l'UE (une moyenne par UE)7select e.etudiant from EtudiantUE e8where e.noteExam > (select avg(x.noteExam) from EtudiantUE x where x.uniteValeur = e.uniteValeur);

La règle générale : un prédicat sur un agrégat va dans HAVING (filtre de groupe), un prédicat sur une valeur scalaire calculée par une sous-requête va dans WHERE (filtre de ligne). Confondre les deux est la faute de syntaxe la plus coûteuse en examen.

#NULL et agrégatsCopier le lien

COUNT(*) compte toutes les lignes ; COUNT(col) ignore les NULL de col ; SUM et AVG les ignorent aussi. Un groupe dont toutes les valeurs agrégées sont NULL donne SUM NULL, pas 0 : distinguez « total nul » et « aucune donnée » avec COUNT.

1select2  count(*) as toutes,3  count(amount) as montants_renseignes4from orders;5-- Attendu : 1 ligne ; 5, 5 (aucun amount NULL dans notre jeu)

#Fonctions de fenêtre : agréger sans regrouperCopier le lien

GROUP BY condense : une ligne par groupe. Les fonctions de fenêtre calculent un agrégat par ligne, sur une fenêtre définie par PARTITION BY et ORDER BY. Trois usages classiques : cumul, classement, comparaison à la moyenne du groupe.

1-- Total cumulé des commandes d'Alice, chronologiquement2select id, order_date, amount,3       sum(amount) over (partition by user_id order by order_date) as cumul4from orders5where user_id = 16order by order_date;7-- Attendu : 2 lignes ; (1, 40.0, 40.0) puis (2, 60.0, 100.0)8 9-- Classement des montants, toutes commandes confondues10select id, amount,11       rank() over (order by amount desc) as rang,12       row_number() over (order by amount desc) as numero13from orders14order by rang;

rank() laisse les ex æquo partager un rang et saute les suivants ; row_number() attribue un numéro unique. La clause rows between 2 preceding and current row borne la fenêtre aux trois dernières lignes, utile pour les moyennes mobiles.

#PlaygroundCopier le lien

#Exercice : moyennes mobiles et classementsCopier le lien

Sur le jeu fil rouge, écrivez :

1. la moyenne mobile des montants sur les 3 dernières commandes (fenêtre ordonnée par id, rows between 2 preceding and current row) ;
2. le classement (rank) des commandes de Bob par montant décroissant ;
3. pour chaque commande, l'écart entre son montant et le panier moyen de son utilisateur.

#Exercice : sept requêtes d'agrégation (type TD noté)Copier le lien

Sur le schéma officiel Etudiant(numero, nom, prenom, adresse), UE(code, libelle, nbHeures, responsable), EtudiantUE(etudiant, uniteValeur, noteCC, noteExam) avec les tuples du cours (cf. la section SELECT) :

1. la somme des heures associées aux UE « SL2IBD » et « SL2IPI » ;
2. le nombre d'étudiants ayant suivi l'UE « SL2IPI » ;
3. le nombre de prénoms d'étudiants différents ;
4. le nom et le prénom des étudiants qui suivent « SL2IBD » mais pas « SL2IAL » ;
5. le numéro des étudiants qui ont, dans « SL2IBD », une note d'examen supérieure à la moyenne des notes d'examen de cette UE ;
6. le libellé des UE dont la moyenne de contrôle continu est supérieure à 10 ;
7. le nom des étudiants qui n'ont pas la moins bonne note dans l'UE « SL2IBD ».

1-- 1. Somme des heures (attention : BETWEEN ne convient pas, il faudrait une plage continue)2select sum(nbHeures) from UE where code = 'SL2IBD' or code = 'SL2IPI';3-- Attendu : 1 ligne ; 60 (24 + 36)4 5-- 2. Combien d'étudiants ont suivi SL2IPI6select count(*) from EtudiantUE where uniteValeur = 'SL2IPI';7-- Attendu : 1 ligne ; 38 9-- 3. Prénoms distincts10select count(distinct prenom) from Etudiant;11-- Attendu : 1 ligne ; 312 13-- 4. SL2IBD sans SL2IAL : différence ensembliste, pas agrégation14select e.nom, e.prenom

#QuizCopier le lien