GROUP BY et HAVING — Agrégats par groupe

Les données utilisées dans cet article — la table score

Dans l'article précédent, tu as réduit une table entière à une seule valeur. Le `GROUP BY` de cet article (regroupement — réunir dans un même groupe les lignes qui partagent la même valeur) exécute les agrégats par groupe et renvoie une ligne d'agrégat par groupe. En plus de cela, `HAVING` te permet de filtrer les résultats d'agrégat, par exemple « uniquement les groupes dont la moyenne est supérieure ou égale à 80 ».

Le sujet est la même table score que la dernière fois (30 lignes = 10 personnes × 3 matières). Regrouper par name donne 10 groupes, et regrouper par subject donne 3 groupes.

Avant de te lancer dans les exercices, jette un œil aux définitions des colonnes et à un échantillon des données de la table score.

① Utilise PRAGMA table_info(score); pour vérifier les noms de colonnes, les types et la clé primaire.

② Utilise SELECT * FROM score LIMIT 5; pour prévisualiser les 5 premières lignes de données.

GROUP BY — agréger par groupe

Quand tu écris GROUP BY colonne, les lignes qui partagent la même valeur dans cette colonne sont réunies dans un même groupe, et les fonctions d'agrégat sont calculées une fois par groupe. Avec SELECT name, AVG(score) FROM score GROUP BY name;, les 3 lignes ayant le même nom (une par matière) forment un groupe, et tu obtiens une ligne de moyenne par nom. Les seules « colonnes brutes » que tu peux mettre dans SELECT sont les colonnes nommées dans `GROUP BY` ; toute autre colonne doit être enveloppée dans une fonction d'agrégat (parce que sa valeur n'est pas unique au sein d'un groupe).

-- Nombre / moyenne / meilleure note par matière (3 groupes par subject)
SELECT
  subject,
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_score,
  MAX(score)           AS top_score
FROM score
GROUP BY subject;

Imagine le besoin « je veux une liste de la moyenne de chaque élève ». (Exécute-la correctement et l'explication apparaîtra.)

① Regroupe la table score par nom.

② Pour chaque groupe, extrais le nom comme name, le nombre de matières passées comme subjects et la moyenne comme avg_score, dans cet ordre.

③ Arrondis la moyenne à 2 décimales. L'ordre n'est pas spécifié.

HAVING — filtrer les résultats d'agrégat

Après un agrégat de groupe, tu veux parfois filtrer sur la valeur d'agrégat, comme « n'affiche que les groupes dont la moyenne est supérieure ou égale à 80 ». Tu ne peux pas faire cela avec WHERE. WHERE est une clause qui juge les lignes individuelles avant le regroupement, donc un agrégat de groupe comme AVG(score) n'a pas encore été calculé. C'est là qu'intervient `HAVING`. HAVING s'écrit après GROUP BY et juge sa condition sur les résultats d'agrégat des groupes. Écrire HAVING AVG(score) >= 80 ne garde que les groupes dont la moyenne est supérieure ou égale à 80.

-- Seulement les noms dont la moyenne dépasse 85 (HAVING juge la valeur d'agrégat)
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_score
FROM score
GROUP BY name
HAVING AVG(score) > 85;

Imagine le besoin « je veux lister uniquement les meilleurs élèves dont la moyenne est de 80 ou plus ».

① Regroupe la table score par nom.

② Pour chaque groupe, extrais le nom comme name et la moyenne comme avg_score (arrondie à 2 décimales).

③ Ne garde que les groupes dont la moyenne est de 80 ou plus. L'ordre n'est pas spécifié.

WHERE vs. HAVING — filtrer des lignes, ou filtrer des groupes

WHERE et HAVING sont tous deux des filtres, mais ils agissent à des moments différents. WHERE juge les lignes individuelles et les écarte avant le regroupement. HAVING juge par groupe et les écarte après le regroupement et l'agrégation. L'ordre d'exécution est FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Tu peux aussi utiliser les deux à la fois. Dans ce cas, le déroulement est « restreindre les lignes ciblées avec WHERE, puis regrouper, puis filtrer les résultats d'agrégat avec HAVING ». Par exemple, pour obtenir « les personnes dont la moyenne par nom est de 80 ou plus, en ne considérant que Math et English », tu restreins les lignes avec WHERE subject IN ('Math','English') et tu restreins les groupes avec HAVING AVG(score) >= 80.

-- Utiliser WHERE et HAVING ensemble
-- Exclure Science, ne garder que les noms dont la moyenne est de 80 ou plus
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_two
FROM score
WHERE subject <> 'Science'
GROUP BY name
HAVING AVG(score) >= 80;

Imagine le besoin « en ne considérant que les deux matières Math et English, je veux les personnes dont la moyenne par nom est de 85 ou plus ».

① Dans la table score, ne garde que les lignes où subject est Math ou English (Science est hors champ).

② Regroupe les lignes restantes par nom et extrais le nom comme name et la moyenne des deux matières comme avg_two (arrondie à 2 décimales).

③ Ensuite, ne garde que les groupes dont la moyenne est de 85 ou plus. L'ordre n'est pas spécifié.

Imagine le besoin « je veux afficher les moyennes par matière comme un classement, la plus haute en premier ».

① Regroupe la table score par matière.

② Pour chaque groupe, extrais la matière comme subject, le nombre d'élèves qui l'ont passée comme students et la moyenne comme avg_score (arrondie à 2 décimales), dans cet ordre.

③ Trie-les de la moyenne la plus haute à la plus basse.