Agréger correctement

Leçon 5
45 min
Module 2 : L'essentiel de SQL

Leçon 5 sur 18 · SQL et préparation des données pour l'IAAgréger correctement

Aperçu du cours

1. Sources et structure des données

2. L'essentiel de SQL

3. Des requêtes d'analyse utiles

4. Nettoyer et transformer les données

5. Préparer la modélisation sans risque

6. Contrats de données et passation

7. Études de cas

Ce que vous saurez faire

Transformez plusieurs lignes en quelques nombres qui répondent à une question, comme les billets de soutien par équipe ou la valeur moyenne des commandes. Sachez ce que chaque nombre compte vraiment. Compter les lignes ne revient pas toujours à compter les billets. Une moyenne peut aussi inclure des valeurs incorrectes.

Carnet JupyterFaites cette leçon dans un carnet JupyterColab · Kaggle · GitHub · en anglais

Le carnet (notebook) Jupyter contient toutes les requêtes, tâches et vérifications de cette leçon, avec les consignes. Sa première cellule télécharge les données d’exercice et crée la base de données. Il fonctionne donc sans le projet local du module 1. Lisez les explications ici et exécutez le code là-bas.

Ouvrir dans Colab Ouvrir dans Kaggle Voir sur GitHub

Le carnet est en anglais. Colab et Kaggle ouvrent votre propre copie. Il faut un compte Google ou Kaggle gratuit. Vous êtes bloqué ? Consultez le carnet de solution.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 45 minutes

Ce que vous devez savoir

SELECT, WHERE, ORDER BY et IS NULL, présentés dans Sélectionner et filtrer.
Une comparaison avec NULL donne un résultat inconnu. WHERE exclut donc la ligne, comme expliqué dans Sélectionner et filtrer : ce que WHERE garde.

Ce qu’il vous faut sur votre ordinateur (seulement pour travailler localement; le carnet et les encadrés de cette page n’en ont pas besoin)

Le dossier ~/projects/ticket-data avec larkfield.db et q.py. Vous l’avez créé dans Préparer une base de données d’exercice : créer la base de données.
Si le dossier ou la base de données manque ou a changé, reprenez cette section. Vous pouvez aussi réinitialiser la base de données avec python3 get_data.py --reset.
Un terminal ouvert dans ce dossier. Tapez python3 sur macOS et Linux, et python sur Windows, comme expliqué dans Quelle commande Python saisir.
Enregistrez les requêtes de ce module dans queries/, avec une numérotation commençant par 21-. Voir Sélectionner et filtrer : exercice guidé. Cette leçon ajoute 23-. Si vous avez sauté cette leçon, vous n’avez pas besoin de ses fichiers ici.

Testé sur macOS avec Python 3.14.6 (SQLite 3.50.4). Les encadrés du navigateur utilisent SQLite 3.49.1. Le carnet a été testé dans Jupyter.

Six commandes, une moyenne

Une agrégation transforme plusieurs lignes en une valeur : un compte, un total, une moyenne, un minimum ou un maximum. SQL a une fonction pour chacune : COUNT, SUM, AVG, MIN et MAX.

Voici six billets avec la valeur, en dollars, de la commande concernée :

ticket_id	team	order_value	Ce que la valeur représente vraiment
T-1	delivery	40	une commande de 40 $
T-2	delivery	60	une commande de 60 $
T-3	returns	50	une commande de 50 $
T-4	account	NULL	aucune commande : une question sur le compte
T-5	returns	−1	« inconnu » dans le système de commandes
T-6	delivery	5000	une commande de 50 $, transmise en cents

Les agrégations de order_value sur les six lignes :

Expression	Résultat	Pourquoi
`COUNT(*)`	6	compte les lignes
`COUNT(order_value)`	5	compte les lignes où la colonne n’est pas NULL
`SUM(order_value)`	5149	40 + 60 + 50 − 1 + 5000; NULL est exclu
`AVG(order_value)`	1029.8	5149 ÷ 5 : la somme divisée par le nombre de valeurs non NULL
`MIN(order_value)`	−1	la plus petite valeur non NULL
`MAX(order_value)`	5000	la plus grande valeur

La vraie moyenne des quatre commandes connues est (40 + 60 + 50 + 50) ÷ 4 = 50 $. SQL donne 1 029,8. Rien n’a échoué : SQL a fait un calcul correct sur des valeurs incorrectes.

Chaque ligne enseigne une règle :

NULL est exclu de COUNT(column), SUM, AVG, MIN et MAX. Ici, c’est correct : T-4 n’a aucune commande. Il ne doit donc pas compter comme une commande de 0 $.
−1 est une valeur sentinelle : une valeur d’apparence normale qui représente autre chose, ici « inconnu ». SQL ne peut pas le savoir. Il additionne donc −1 comme tout autre nombre.
5000 utilise la mauvaise unité. Un ancien formulaire transmettait des cents, pas des dollars. Une valeur 100 fois trop grande déplace la moyenne bien plus que toutes les autres.

MIN et MAX révèlent les deux problèmes en même temps. Voilà pourquoi vous les affichez à côté de chaque moyenne.

Exécutez du SQL dans votre navigateur

Essayez : six commandes

Cet encadré contient seulement les six billets ci-dessus. Exécutez la requête et comparez chaque valeur au tableau. Vous devriez voir 6, 5, 5 149, 1 029,8, −1 et 5 000. Ajoutez ensuite WHERE order_value <> -1 après FROM tickets. Prédisez combien de lignes COUNT(*) donne maintenant, et pourquoi ce n’est pas 5.

Requête SQL (vous pouvez la modifier)

SELECT
    COUNT(*)           AS all_rows,
    COUNT(order_value) AS with_value,
    SUM(order_value)   AS total,
    AVG(order_value)   AS mean_value,
    MIN(order_value)   AS smallest,
    MAX(order_value)   AS largest
FROM tickets;

Avec WHERE order_value <> -1, COUNT(*) donne 4, pas 5. Le filtre exclut T-5, mais aussi T-4, car NULL <> -1 donne un résultat inconnu. C’est la règle de la dernière leçon, appliquée à une agrégation.

Une valeur par groupe : GROUP BY

GROUP BY team rassemble les lignes d’une même équipe dans un groupe. Les agrégations donnent ensuite une valeur par groupe. Le résultat contient donc une ligne par équipe :

SELECT team, COUNT(*) AS ticket_rows, AVG(order_value) AS mean_value
FROM tickets
GROUP BY team;

Groupe	Lignes du groupe	`ticket_rows`	`mean_value`
account	T-4	1	NULL (aucune valeur pour calculer une moyenne)
delivery	T-1, T-2, T-6	3	(40 + 60 + 5000) ÷ 3 = 1700
returns	T-3, T-5	2	(50 − 1) ÷ 2 = 24.5

Le résultat contient 3 lignes, et 1 + 3 + 2 = 6 lignes sont entrées dans le calcul. C’est la vérification à faire pour tout GROUP BY : la somme des comptes des groupes égale le nombre de lignes entrées. Si une colonne de regroupement contient des valeurs NULL, elles forment toutes un seul groupe, nommé NULL.

Chaque colonne dans SELECT doit figurer dans GROUP BY ou dans une agrégation. SELECT team, ticket_id … GROUP BY team demande un seul ticket_id pour un groupe de trois billets. PostgreSQL s’arrête avec une erreur. SQLite répond et choisit l’un des trois sans avertissement. N’écrivez donc pas cette requête.

Trois façons de compter

COUNT a trois formes. Sur la vraie table tickets, elles donnent trois réponses différentes :

Expression	Ce qui est compté	Résultat
`COUNT(*)`	les lignes	1417
`COUNT(order_value)`	les lignes où `order_value` n’est pas NULL	1229
`COUNT(DISTINCT ticket_id)`	les valeurs différentes de `ticket_id`, sans NULL	1403

COUNT(*) compte deux fois les 14 lignes dupliquées lors de l’exportation. COUNT(DISTINCT ticket_id) compte chaque billet une seule fois. Quand quelqu’un demande « combien de billets? », la réponse est 1 403. COUNT(order_value) donne 188 de moins que COUNT(*) : les 188 billets concernant un compte n’ont aucune commande.

COUNT(DISTINCT customer_id) donne 198. Les 12 billets d’invités ont NULL dans cette colonne. COUNT exclut NULL, donc les invités ne comptent pas comme « un client ».

Exécutez du SQL dans votre navigateur

Essayez : trois façons de compter

Prédisez with_channel avant d’exécuter la requête : 38 lignes n’ont aucun canal. Vous devriez voir 1 417, 1 229, 1 403, 1 379 et 198.

Requête SQL (vous pouvez la modifier)

SELECT
    COUNT(*)                    AS all_rows,
    COUNT(order_value)          AS with_order_value,
    COUNT(DISTINCT ticket_id)   AS tickets,
    COUNT(channel)              AS with_channel,
    COUNT(DISTINCT customer_id) AS customers
FROM tickets;

Exemple détaillé : la valeur moyenne des commandes par équipe

Priya, la scientifique des données, prévoit d’utiliser la variable order_value dans le modèle. Elle demande à Mei : « Quelle est la valeur habituelle d’une commande pour chaque équipe? » Mei produit une ligne par équipe, avec les comptes à côté de la moyenne :

-- One row per team: rows, distinct tickets and order values.
SELECT
    team,
    COUNT(*)                  AS ticket_rows,
    COUNT(DISTINCT ticket_id) AS tickets,
    COUNT(order_value)        AS with_value,
    ROUND(AVG(order_value), 2) AS avg_value,
    MIN(order_value)          AS min_value,
    MAX(order_value)          AS max_value
FROM tickets
GROUP BY team
ORDER BY team;

ROUND(x, 2) arrondit à deux décimales. Cela change seulement la présentation du résultat.

team      ticket_rows  tickets  with_value  avg_value  min_value  max_value
--------  -----------  -------  ----------  ---------  ---------  ---------
account   188          188      0           NULL       NULL       NULL
delivery  452          449      452         264.45     -1.0       17319.0
payment   241          240      241         213.79     -1.0       8554.0
returns   327          321      327         262.24     -1.0       12276.0
warranty  209          205      209         535.11     -1.0       34518.0

5 rows

Mei lit de gauche à droite et vérifie chaque colonne :

Lignes : 188 + 452 + 241 + 327 + 209 = 1 417. Chaque ligne appartient à exactement un groupe.
Billets : 188 + 449 + 240 + 321 + 205 = 1 403. Les lignes dupliquées se trouvent dans quatre équipes.
Avec une valeur : l’équipe account a 0. Grace confirme : « Les questions sur un compte n’ont aucune commande. » Ici, une valeur manquante a un sens. Elle ne signifie pas « inconnu ».
Minimum : −1 dans quatre équipes. Dans le système de commandes, −1 signifie « inconnu ». C’est une sentinelle, pas un remboursement. La table contient 11 lignes de ce type.
Maximum : jusqu’à 34 518 dollars, pour une boutique de jardinage. Mei consulte les lignes les plus élevées avec ORDER BY order_value DESC. Les 25 lignes au-dessus de 2 000 proviennent de l’ancien formulaire Web (web_form ou webform). Celui-ci transmettait des cents jusqu’en février 2026.

La moyenne de 535,11 pour warranty ne représente donc pas une commande habituelle. Sur toute la table, AVG(order_value) donne 299,95. Pour une vérification rapide, Mei exclut les 11 lignes à −1 et les 25 lignes au-dessus de 2 000. La moyenne des 1 193 autres valeurs est 123,93.

Cette vérification n’est pas la correction. Elle exclut 25 vraies commandes seulement parce que leur unité est incorrecte. Et la limite de 2 000 est une estimation qui fonctionne seulement pour cette exportation. Le module 4 convertit les lignes en cents et enregistre la sentinelle comme inconnue. Il ajoute un indicateur à chaque ligne modifiée. Pour l’instant, Mei écrit à Priya : « N’utilisez pas order_value avant le nettoyage. La moyenne de 299,95 inclut 11 valeurs sentinelles et d’anciennes lignes en cents. »

Conditions sur les groupes : HAVING

WHERE filtre les lignes, avant le regroupement. HAVING filtre les groupes, après l’agrégation. HAVING peut donc tester une agrégation, mais pas WHERE.

Grace demande : « Quelles équipes ont eu au moins 40 billets urgents? »

SELECT team, COUNT(*) AS urgent_rows
FROM tickets
WHERE priority = 1          -- 1. keep the urgent rows (186 rows)
GROUP BY team               -- 2. make one group per team (5 groups)
HAVING COUNT(*) >= 40       -- 3. keep the groups with 40 or more (2 groups)
ORDER BY urgent_rows DESC;

La réponse est payment (59) et delivery (49). Les autres équipes ont returns 39, warranty 21 et account 18; le total est 186. Si vous écrivez WHERE COUNT(*) >= 40, SQL s’arrête avec une erreur : WHERE s’exécute avant que les groupes existent.

Compter avec une condition : CASE

Parfois, une question exige plusieurs comptes sur les mêmes lignes, chacun avec sa propre condition. CASE donne une valeur qui dépend d’un test :

CASE WHEN status = 'escalated' THEN ticket_id END

Cela donne ticket_id quand le statut est escalated (transféré au niveau supérieur), et NULL dans toutes les autres lignes. COUNT exclut NULL. Ainsi, COUNT(DISTINCT CASE WHEN status = 'escalated' THEN ticket_id END) compte les billets différents ayant un événement de transfert au niveau supérieur.

Dans la table outcomes, une ligne représente un événement de résultat dans la vie d’un billet :

Exécutez du SQL dans votre navigateur

Essayez : événements et billets

Exécutez la requête. Vous devriez voir 1 874 événements pour 1 399 billets, 228 événements de transfert et 225 billets transférés. L’écart de 3 vient de trois événements de transfert enregistrés deux fois, comme vous le verrez dans la prochaine leçon. Remplacez les deux 'escalated' par 'resolved' (résolu) et exécutez de nouveau : 1 533 événements, 1 396 billets.

Requête SQL (vous pouvez la modifier)

SELECT
    COUNT(*) AS events,
    COUNT(DISTINCT ticket_id) AS tickets_with_events,
    COUNT(CASE WHEN status = 'escalated' THEN outcome_id END) AS escalated_events,
    COUNT(DISTINCT CASE WHEN status = 'escalated' THEN ticket_id END) AS escalated_tickets
FROM outcomes;

Un billet peut avoir plusieurs événements. Compter les événements ne revient donc pas à compter les billets. Pour « combien de billets ont été transférés au niveau supérieur? », comptez les valeurs distinctes de ticket_id.

Exercice guidé : le résumé par équipe

Ouvrez un terminal dans ~/projects/ticket-data.
Créez le fichier queries/23-team-summary.sql avec la requête de Mei dans l’exemple détaillé.
Exécutez-le :
- macOS
- Windows
Fenêtre de terminal
python3 q.py queries/23-team-summary.sql
Fenêtre de terminal
python q.py queries/23-team-summary.sql
Vérification : vous voyez 5 lignes, et la ligne account a 0 dans with_value.
Additionnez la colonne ticket_rows. Vérification : la somme est 1 417.
Additionnez la colonne tickets. Vérification : la somme est 1 403.
Ajoutez la ligne WHERE order_value <> -1 entre FROM tickets et GROUP BY team. Avant l’exécution, prédisez le ticket_rows de l’équipe account.
Exécutez le fichier de nouveau. Vérification : la ligne account a disparu, et min_value n’est plus −1.
Supprimez la ligne de l’étape 6. Enregistrez le fichier.

Afficher l’explication de l’étape 7

L’équipe account a NULL dans chaque ligne. NULL <> -1 donne un résultat inconnu. WHERE exclut donc les 188 lignes, et le groupe n’existe plus. Le résultat contient 4 lignes : 1 417 − 188 − 11 = 1 218 lignes sont entrées dans le calcul. Un filtre sur une colonne peut supprimer un groupe entier sans message. Comptez les groupes avant et après.

Exécutez du SQL dans votre navigateur

À vous : les lignes par canal

La requête de départ donne 9 groupes, car chaque graphie forme son propre groupe. Modifiez-la pour que Email, EMAIL et email (courriel) forment un seul groupe, et que chat rejoigne chat (clavardage). Triez le résultat du plus grand groupe au plus petit. Quand tout est correct, vous voyez 6 lignes. La première est email avec 568 lignes, et la vérification réussit.

Requête SQL (vous pouvez la modifier)

-- One row per channel, written in a standard form.
-- Show the channel and the number of rows, largest first.
SELECT channel, COUNT(*) AS ticket_rows
FROM tickets
GROUP BY channel;

Afficher l’exemple de réponse

SELECT LOWER(TRIM(channel)) AS channel_for_report, COUNT(*) AS ticket_rows
FROM tickets
GROUP BY LOWER(TRIM(channel))
ORDER BY ticket_rows DESC;

channel_for_report	ticket_rows
email	568
chat	421
web_form	200
phone	185
NULL	38
webform	5

La somme des groupes est 1 417. Il reste six groupes pour quatre vrais canaux : webform et web_form représentent le même canal, et NULL signifie « non enregistré ». LOWER(TRIM()) ne corrige pas une graphie différente. Le module 4 détermine quoi faire avec ces deux cas.

Variante autonome : un long message

Grace dit : « Les billets urgents sont longs. Les clients écrivent plus quand c’est grave. » Vérifiez son affirmation avec la longueur du premier message, word_count.

Exécutez du SQL dans votre navigateur

À vous : les mots par priorité

Produisez une ligne par priorité, triée par priorité. Donnez quatre colonnes : la priorité, le nombre de lignes, la moyenne de word_count arrondie à une décimale et le plus grand word_count. Décidez ensuite : Grace a-t-elle raison?

Requête SQL (vous pouvez la modifier)

-- For each priority: the number of rows, the mean word count
-- (rounded to 1 decimal place) and the largest word count.
-- Write your query here.

Afficher l’exemple de réponse

SELECT
    priority,
    COUNT(*) AS ticket_rows,
    ROUND(AVG(word_count), 1) AS mean_words,
    MAX(word_count) AS max_words
FROM tickets
GROUP BY priority
ORDER BY priority;

priority	ticket_rows	mean_words	max_words
1	186	371.0	48210
2	526	109.1	435
3	705	106.4	553

La priorité 1 a une moyenne de 371 mots, plus de trois fois celle des autres. Mais MAX révèle un message de 48 210 mots : un fichier de journaux (logs) collé dans T-10361. Ajoutez WHERE word_count < 10000. La moyenne de la priorité 1 tombe à 112,4, près des autres. Grace n’a donc pas raison : une seule ligne a fait la différence. Une moyenne doit être accompagnée du compte, du minimum et du maximum.

Erreur courante

La valeur moyenne des commandes inclut des sentinelles et des cents

Symptôme : Tomás inscrit « valeur moyenne des commandes : 299,95 $ » dans une diapositive sur les caractéristiques du modèle. Omar, le gestionnaire de produit, dit qu’une commande habituelle de Larkfield vaut environ 100 $. Il demande d’où vient ce nombre.

Cause : AVG(order_value) a utilisé toutes les valeurs non NULL. 11 lignes contiennent −1, une sentinelle pour « inconnu ». 25 lignes de l’ancien formulaire Web sont en cents, jusqu’à 34 518. SQL n’a donné aucune erreur, car le calcul est correct. Les valeurs sont incorrectes.

Diagnostic : affichez COUNT(column), MIN et MAX à côté de chaque AVG. Un minimum de −1 pour un prix révèle immédiatement le problème. C’est aussi le cas d’un maximum de 34 518 pour une commande de jardinage. Triez ensuite les lignes selon la colonne et lisez les valeurs extrêmes.

Correction : ne présentez pas la moyenne avant de nettoyer la colonne. Le module 4 convertit les lignes en cents, marque −1 comme inconnu et conserve les valeurs d’origine. D’ici là, signalez le problème avec les comptes correspondants.

Conséquence pour le travail en IA : un modèle qui utilise order_value apprend à partir de la mauvaise échelle. Certaines étapes de préparation remplacent les valeurs manquantes par la moyenne. D’autres mettent les valeurs à l’échelle avec la moyenne et l’écart-type. Elles propagent l’erreur à chaque ligne.

Vérifiez votre compréhension

La table tickets contient 1 417 lignes. COUNT(*) donne 1 417, COUNT(order_value) donne 1 229 et COUNT(DISTINCT ticket_id) donne 1 403. Expliquez chaque différence.

Afficher la réponse

COUNT(*) compte chaque ligne. COUNT(order_value) exclut les 188 lignes où order_value est NULL : les billets concernant un compte, sans commande. COUNT(DISTINCT ticket_id) compte chaque billet une seule fois. 14 billets apparaissent deux fois sous forme de lignes dupliquées lors de l’exportation, donc 1 417 − 14 = 1 403. Pour répondre à « combien de billets? », utilisez le compte distinct.

Vérifiez votre compréhension

Prédisez : dans la table des six commandes, que donne AVG(COALESCE(order_value, 0))? (COALESCE(x, 0) donne 0 quand x est NULL.) Est-ce une meilleure moyenne?

Afficher la réponse

COALESCE transforme le NULL de T-4 en 0. Il y a donc 6 valeurs avec la même somme, 5 149 : 5 149 ÷ 6 = 858,17. Ce n’est pas mieux. T-4 est une question sur un compte, sans commande. La requête le compte maintenant comme une commande de 0 $. Sur la vraie table, la même erreur fait passer la moyenne de 299,95 à 260,16. Ici, l’absence a un sens. Ne la remplacez pas par un nombre avant de comprendre ce sens.

Vérifiez votre compréhension

Quelle est la différence entre WHERE et HAVING? Écrivez une requête qui liste les clients ayant au moins 30 billets distincts.

Afficher la réponse

WHERE filtre les lignes avant leur regroupement. HAVING filtre les groupes après l’agrégation. Il peut donc utiliser COUNT, SUM et les autres fonctions.

SELECT customer_id, COUNT(DISTINCT ticket_id) AS tickets
FROM tickets
GROUP BY customer_id
HAVING COUNT(DISTINCT ticket_id) >= 30
ORDER BY tickets DESC;

La requête renvoie 11 clients. Le premier est C-0226 avec 46 billets (47 lignes, car l’un de ses billets est dupliqué).

Récapitulatif

Une agrégation transforme plusieurs lignes en une valeur : COUNT, SUM, AVG, MIN, MAX. Avec GROUP BY, vous obtenez une valeur par groupe. La somme des comptes des groupes doit égaler le nombre de lignes entrées dans le calcul.
COUNT(*) compte les lignes, COUNT(column) compte les valeurs non NULL, et COUNT(DISTINCT column) compte les valeurs différentes. « Combien de billets? » correspond à COUNT(DISTINCT ticket_id) : 1 403.
Les agrégations excluent NULL. C’est correct quand NULL signifie « aucune valeur », comme pour les billets concernant un compte sans commande.
SQL calcule correctement sur des valeurs incorrectes. La sentinelle −1 et les lignes en cents font que AVG(order_value) donne 299,95. Affichez MIN et MAX à côté de chaque moyenne.
WHERE filtre les lignes avant le regroupement; HAVING filtre les groupes après. CASE donne une valeur par ligne, ce qui permet à une requête de compter selon plusieurs conditions.

Ensuite, vous combinez deux tables avec une jointure. Vous vérifiez que la jointure n’a ni ajouté ni perdu de lignes.

Leçon suivante : Joindre des tables