Fonctions d'agrégation — COUNT / SUM / AVG / MIN / MAX

Les données qu'on va utiliser — la table score

Dans cet article, on travaille avec les fonctions d'agrégation (des fonctions qui replient plusieurs lignes en une seule valeur). Combien de lignes y a-t-il, quel est le total, la moyenne, le minimum, le maximum — tout cela compresse de nombreuses lignes en un seul nombre. Les cinq bases sont COUNT / SUM / AVG / MIN / MAX, et presque tous les chiffres récapitulatifs d'un rapport peuvent se construire rien qu'avec celles-ci.

Le jeu de données est la table score (30 lignes = 10 personnes × les 3 matières Math / English / Science). En ciblant les points de la colonne score, tu vas d'abord traiter les agrégations sur toute la table, puis les agrégations après avoir restreint les lignes avec WHERE. Les agrégations par groupe (comme la moyenne par matière) sont traitées dans l'article suivant, donc ici on se concentre sur le fait de replier toute la table — ou toute la plage restreinte par WHERE — en une seule valeur.

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

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

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

Les fonctions d'agrégation : « plusieurs lignes → une valeur »

Les fonctions d'agrégation se placent à la position d'une colonne dans un SELECT.

Le point à surveiller ici, c'est de ne pas mélanger une colonne ordinaire et une fonction d'agrégation dans le même `SELECT`. Une colonne ordinaire comme name a une valeur différente par ligne, tandis qu'une fonction d'agrégation comme COUNT(*) replie toute la table (ou tout l'ensemble restreint par `WHERE`) en une seule valeur. Si tu écris les deux, comme dans SELECT name, COUNT(*) FROM score;, le côté agrégat est une valeur unique pour tout l'ensemble alors que name n'a pas de réponse définie à « la valeur de quelle ligne dois-je afficher ? ». Dans la plupart des bases de données, c'est une erreur.

-- Récupérer ensemble le nombre, le nombre distinct, le total et la moyenne
SELECT
  COUNT(*)             AS row_count,
  COUNT(DISTINCT name) AS name_kinds,
  SUM(score)           AS total,
  AVG(score)           AS avg_raw
FROM score
WHERE subject = 'English';

La différence entre COUNT(*) et COUNT(colonne)

COUNT(*) compte le nombre de lignes lui-même. COUNT(colonne) compte uniquement les lignes où cette colonne n'est pas NULL. La table score n'a pas de NULL, donc les deux donnent le même 30, mais pour une colonne contenant des NULL tu verras une différence ici (on le confirmera plus tard avec une table contenant des NULL). En plus, écrire COUNT(DISTINCT colonne) compte le nombre de valeurs distinctes après suppression des doublons (utilise-le sur subject et tu obtiens 3, le nombre de matières distinctes).

Astuce — les fonctions d'agrégation et NULL

SUM / AVG / MIN / MAX n'incluent pas les lignes NULL dans le calcul (ils les ignorent). En particulier, AVG est calculé comme la somme des valeurs non NULL ÷ le nombre de valeurs non NULL. Donc pour une colonne contenant des NULL, SUM(colonne) / COUNT(*) (dénominateur = toutes les lignes) et AVG(colonne) (dénominateur = nombre de non-NULL) donnent des résultats différents. Quand tu veux faire la moyenne en traitant NULL comme 0, écris-le explicitement avec quelque chose comme AVG(COALESCE(colonne, 0)).

Imagine une exigence : « Je veux afficher l'échelle globale des données de notes sur un tableau de bord en une seule ligne. » (Exécute-la correctement et l'explication apparaîtra.)

① À partir de la table score, utilise les cinq fonctions d'agrégation pour extraire les 5 colonnes suivantes en une seule ligne.

② Donne comme alias row_count au nombre de toutes les lignes, total à la somme des points, avg_score à la moyenne des points, min_score au point le plus bas et max_score au point le plus haut, dans cet ordre.

③ Comme la moyenne s'affiche avec une longue décimale, arrondis-la à 2 décimales et donne-lui l'alias avg_score.

Restreindre la cible avec WHERE avant d'agréger

Ajouter WHERE fait que seules les lignes qui satisfont la condition deviennent l'entrée des fonctions d'agrégation. L'ordre d'exécution est « FROM lit toutes les lignes → WHERE restreint les lignes → les lignes restantes sont repliées par les fonctions d'agrégation ». Autrement dit, les fonctions d'agrégation ne voient que les lignes ayant passé WHERE, et la somme ou la moyenne est calculée après le filtrage. Ajoute WHERE subject = 'Math' et seules les 10 lignes de Math sont visées, donc la moyenne devient la moyenne de Math uniquement.

-- Nombre / moyenne / meilleure note pour les 10 lignes Science uniquement
SELECT
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_science,
  MAX(score)           AS top_science
FROM score
WHERE subject = 'Science';

Imagine une exigence : « Je veux découper uniquement les notes de Math et produire la moyenne, le max et le min. »

① À partir de la table score, vise uniquement les lignes où subject est Math.

② Extrais, en une seule ligne, le nombre de lignes visées avec l'alias math_count, la note moyenne avec avg_math, le point le plus haut avec max_math et le point le plus bas avec min_math, dans cet ordre.

③ Arrondis la moyenne à 2 décimales.

COUNT(colonne) et NULL, et l'arrondi de AVG

La table score jusqu'ici n'avait pas de NULL. Confirmons comment l'agrégation change selon que des NULL sont présents ou non, en utilisant la table customer qui a des trous (8 personnes, avec des NULL dans age / email / country). COUNT(*) est le nombre de lignes, donc il reste 8, mais COUNT(email) ne compte que les lignes où email est rempli, donc c'est 4. Exécuter COUNT(colonne) sur une colonne contenant des NULL donne un résultat plus petit que COUNT(*) comme ceci.

Aussi, comme AVG est une division, il produit des décimales comme 31.5. Dans un rapport, c'est plus facile à manipuler si tu arrondis avec ROUND(expression, chiffres), comme dans ROUND(AVG(age), 2).

-- Comment COUNT et AVG changent avec des NULL présents
SELECT
  COUNT(*)            AS rows_all,
  COUNT(country)      AS with_country,
  ROUND(AVG(age), 2)  AS avg_age
FROM customer;

Imagine une exigence : « Les données des membres ont des champs non remplis (NULL). Je veux comparer le nombre total au nombre où une valeur est réellement présente. »

① À partir de la table customer, extrais, en une seule ligne, le nombre total de lignes avec l'alias all_rows, le nombre où email est rempli avec with_email, le nombre où age est rempli avec with_age, et le nombre de valeurs distinctes de country avec country_kinds, dans cet ordre.

② Confirme qu'exécuter COUNT(colonne) sur une colonne avec des NULL donne un résultat plus petit que COUNT(*).

Imagine une exigence : « Je veux extraire uniquement les notes qui battent la moyenne globale et voir leur nombre et leur moyenne. » La moyenne globale est 77.93… (tu l'as confirmée dans l'exercice précédent).

① À partir de la table score, vise uniquement les lignes où score est de 78 ou plus.

② Extrais, en une seule ligne, le nombre de lignes visées avec l'alias high_count, leur note moyenne avec avg_high, et le point le plus haut avec max_high, dans cet ordre.

③ Arrondis la moyenne à 2 décimales.