Joindre des tables

Ce que vous saurez faire

Combinez les lignes de deux tables, par exemple chaque billet de soutien avec le segment de son client. Prouvez ensuite que la jointure n’a ni perdu ni répété de lignes. Une jointure qui ajoute ou supprime des lignes ne signale aucune erreur. Tous les totaux calculés après cette jointure sont alors faux.

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 instructions. Sa première cellule télécharge les données d’exercice et construit la base de données. Il fonctionne donc sans le projet local du module 1. Lisez les explications ici et exécutez le code dans le carnet.

Ouvrir dans ColabOuvrir dans KaggleVoir 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 55 minutes

Ce que vous devez savoir

• GROUP BY, COUNT(DISTINCT …) et CASE, présentés dans Agréger correctement.
• Les clés qui relient les tables, présentées dans Les concepts relationnels : tickets.customer_id pointe vers customers.customer_id, et outcomes.ticket_id pointe vers tickets.ticket_id. Un billet peut avoir plusieurs événements de résultat.

Ce qu’il vous faut sur votre ordinateur (seulement pour le travail local; 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 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 des numéros commençant à 21-, comme dans Sélectionner et filtrer. Cette leçon ajoute les numéros 24- à 27-.

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.

Quatre billets et cinq événements

Une jointure combine deux tables en un seul résultat. Pour chaque ligne de la première table, elle trouve les lignes de la deuxième qui respectent une condition. Elle place chaque paire correspondante côte à côte dans une ligne. La condition suit ON. Elle indique généralement qu’une clé étrangère est égale à une clé primaire : ON o.ticket_id = t.ticket_id.

Voici deux petites tables. Les données sont les mêmes que dans les encadrés et l’explorateur ci-dessous.

tickets (la table de gauche, 4 lignes) :

ticket_id	customer_id	team
T-1	C-1	delivery
T-2	C-1	returns
T-3	C-2	payment
T-4	NULL	account

outcomes (la table de droite, 5 lignes) :

outcome_id	ticket_id	status
O-1	T-1	escalated
O-2	T-1	resolved
O-3	T-2	resolved
O-4	T-3	resolved
O-5	T-9	resolved

Trois éléments de ces tables comptent pour une jointure. T-1 a deux événements. T-4 n’a aucun événement. Et O-5 appartient à T-9, un billet absent de tickets : une ligne orpheline, dont la clé ne pointe vers rien.

Deux jointures sont courantes :

• Une jointure interne (INNER JOIN, ou simplement JOIN) garde seulement les paires qui correspondent. Une ligne sans correspondance ne figure pas dans le résultat.
• Une jointure gauche (LEFT JOIN) garde toutes les lignes de la table de gauche. Une ligne de gauche sans correspondance produit une ligne de résultat, avec NULL dans chaque colonne de la table de droite.

Voici les deux résultats, avec le sort de chaque ligne :

ticket_id	team	outcome_id	status	INNER JOIN	LEFT JOIN
T-1	delivery	O-1	escalated	conservée, répétée (T-1 correspond à 2 événements)	conservée, répétée
T-1	delivery	O-2	resolved	conservée, répétée	conservée, répétée
T-2	returns	O-3	resolved	conservée	conservée
T-3	payment	O-4	resolved	conservée	conservée
T-4	account	NULL	NULL	supprimée (aucun événement)	conservée, valeurs NULL ajoutées
Lignes				4	5

O-5 ne figure dans aucun résultat : aucun billet ne correspond à T-9. Une jointure gauche garde seulement les lignes sans correspondance de la table de gauche.

Les nombres de lignes expliquent ce qui se passe. La table de gauche a 4 lignes, et la jointure interne en retourne aussi 4. Cela semble sûr, mais ne l’est pas : T-4 disparaît et T-1 apparaît deux fois. Les deux changements se masquent mutuellement. Comptez donc aussi les billets distincts : la jointure interne en a 3, la jointure gauche en a 4.

Exécutez du SQL dans votre navigateur

Essayez : la petite jointure

Cet encadré contient seulement les petites tables ci-dessus (et trois clients, C-1 à C-3). Exécutez la requête : vous devriez voir 4 lignes, avec T-1 deux fois et sans T-4. Remplacez ensuite INNER JOIN par LEFT JOIN et exécutez-la de nouveau. Vous obtenez 5 lignes, et T-4 a NULL pour outcome_id et status.

Requête SQL (vous pouvez la modifier)SELECT t.ticket_id, t.team, o.outcome_id, o.status FROM tickets AS t INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id ORDER BY t.ticket_id, o.outcome_id;

tickets AS t donne à la table un nom court, un alias, pour cette requête. t.ticket_id signifie alors « la colonne ticket_id de tickets ». Les deux tables ont une colonne ticket_id. Chaque nom de colonne doit donc préciser sa table.

Explorer

Quelles lignes une jointure garde-t-elle et répète-t-elle?

SELECT t.ticket_id, o.outcome_id, o.status
FROM tickets AS t
INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id;

tickets (table de gauche)

4 lignes

outcomes (table de droite)

5 lignes

Résultat

4 lignes

Billets distincts dans le résultat

3

INNER JOIN : 4 lignes de billets en entrée, 4 lignes en sortie, pour 3 billets. Répétés : T-1 (2 fois), une fois pour chaque événement correspondant. Retiré : T-4, car il n’a aucun événement. O-5 n’est jamais dans le résultat : son billet, T-9, n’est pas dans tickets.

tickets (table de gauche) : 4 lignes

ticket_id	team	dans le résultat
T-1	delivery	→ 2 lignes
T-2	returns	→ 1 ligne
T-3	payment	→ 1 ligne
T-4	account	retiré (aucun événement)

outcomes (table de droite) : 5 lignes

outcome_id	ticket_id	status	dans le résultat
O-1	T-1	escalated	utilisé
O-2	T-1	resolved	utilisé
O-3	T-2	resolved	utilisé
O-4	T-3	resolved	utilisé
O-5	T-9	resolved	pas utilisé (aucun billet T-9)

• une seule correspondance
• répété : billet trouvé plus d’une fois
• aucune correspondance : gardé par LEFT, NULL ajoutés

Résultat du INNER JOIN : 4 lignes

ticket_id	outcome_id	status	pourquoi
T-1	O-1	escalated	répété
T-1	O-2	resolved	répété
T-2	O-3	resolved	une correspondance
T-3	O-4	resolved	une correspondance

Une jointure interne supprime un billet sans événement et répète un billet avec deux événements. Une jointure gauche garde le billet sans événement. Un événement répété répète son billet une fois de plus dans les deux jointures.

Description textuelle de l’explorateur

Deux tables d’entrée et une table de résultat. La table tickets a 4 lignes : T-1 (delivery), T-2 (returns), T-3 (payment) et T-4 (account). La table outcomes a 5 lignes : O-1 et O-2 pour T-1, O-3 pour T-2, O-4 pour T-3 et O-5 pour T-9, absent de tickets. Des boutons permettent de choisir INNER JOIN ou LEFT JOIN. Un interrupteur ajoute O-6, une copie de O-3 pour T-2. Une étiquette sur chaque ligne du résultat indique « matched once » (une correspondance), « repeated » (répétée, son billet a plusieurs correspondances) ou « kept, NULLs » (conservée avec des valeurs NULL, une ligne de jointure gauche sans correspondance). La jointure interne donne 4 lignes pour 3 billets, et la jointure gauche, 5 lignes pour 4 billets. Avec l’événement répété, T-2 apparaît deux fois : la jointure interne donne 5 lignes et la jointure gauche, 6 lignes. O-5 ne figure jamais dans le résultat.

La règle : après une jointure, chaque ligne de gauche produit une ligne de résultat par correspondance. Dans une jointure gauche, une ligne de gauche sans correspondance produit quand même une ligne. Une correspondance garde le nombre de lignes. Zéro correspondance supprime une ligne (interne) ou la garde avec des valeurs NULL (gauche). Deux correspondances ou plus répètent la ligne.

Exemple détaillé : joindre les vraies tables

Mei veut chaque billet avec le segment de son client et ses événements de résultat. Avant de rédiger le rapport, elle compte les lignes de chaque jointure. Chaque requête entre parenthèses dans ce fichier donne un nombre. Le résultat est donc une ligne de comptes :

queries/24-join-counts.sql

-- Row counts before and after each join. Each query in brackets gives one number.
SELECT
    (SELECT COUNT(*) FROM tickets) AS ticket_rows,
    (SELECT COUNT(*)
       FROM tickets AS t
       INNER JOIN customers AS c ON c.customer_id = t.customer_id) AS inner_customers,
    (SELECT COUNT(*)
       FROM tickets AS t
       LEFT JOIN customers AS c ON c.customer_id = t.customer_id) AS left_customers,
    (SELECT COUNT(*)
       FROM tickets AS t
       INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id) AS inner_outcomes,
    (SELECT COUNT(*)
       FROM tickets AS t
       LEFT JOIN outcomes AS o ON o.ticket_id = t.ticket_id) AS left_outcomes;

Output of python3 q.py queries/24-join-counts.sql

ticket_rows  inner_customers  left_customers  inner_outcomes  left_outcomes
-----------  ---------------  --------------  --------------  -------------
1417         1396             1417            1893            1901

1 row

Mei explique chaque changement avant d’utiliser une jointure.

Billets et clients : la jointure interne perd 21 lignes

Chaque billet a au plus un client, et customer_id est unique dans customers (le module 1 l’a vérifié). Cette jointure ne peut donc pas répéter un billet. La jointure gauche retourne exactement 1 417 lignes, comme tickets. La jointure interne en retourne 1 396, donc 21 lignes de billets n’ont pas de client. Mei les trouve avec une jointure gauche et IS NULL du côté droit :

SELECT t.customer_id, COUNT(*) AS ticket_rows
FROM tickets AS t
LEFT JOIN customers AS c ON c.customer_id = t.customer_id
WHERE c.customer_id IS NULL
GROUP BY t.customer_id;

customer_id	ticket_rows	Signification (selon Grace)
NULL	12	achat comme invité : aucun compte
C-0241	3	compte supprimé
C-0242	2	compte supprimé
C-0243	2	compte supprimé
C-0244	2	compte supprimé

12 + 9 = 21. Ce sont de vrais billets. Une jointure interne les supprime sans rien signaler. Un rapport par segment fondé sur cette jointure omet donc 21 billets. Avec une jointure gauche, ils restent, avec NULL comme segment, et Mei peut les nommer.

Dans l’autre sens : 46 clients n’ont aucun billet. Une jointure qui part de tickets ne les montre jamais, ce qui convient à un rapport sur les billets. Un rapport sur les clients part plutôt de customers : customers LEFT JOIN tickets retourne 1 442 lignes. Cela donne 1 396 paires correspondantes, plus une ligne pour chacun des 46 clients.

Billets et résultats : la jointure ajoute des lignes

outcomes a une ligne par événement, et un billet peut en avoir plusieurs. La jointure répète donc les billets, comme T-1 dans le petit exemple. La jointure interne retourne 1 893 lignes à partir de 1 417 lignes de billets. Ce n’est pas une erreur. La granularité du résultat passe de « un billet » à « un événement d’un billet ». Le danger est de compter ces lignes comme des billets.

La jointure gauche ajoute 8 lignes : 1 901 − 1 893. Ce sont les 8 billets sans événement pour l’instant, comme T-11402, créé le dernier soir de l’exportation. Et dans l’autre sens, 4 événements n’ont pas de billet : T-9990 à T-9993. Ce sont les lignes orphelines de la table outcomes. Aucune jointure qui part de tickets ne les montre.

Mei explique entièrement le total de 1 901 à partir des tables sources :

Étape	Lignes
Événements dans outcomes	1 874
− événements dont le billet est absent de tickets (orphelins)	− 4
= événements qui correspondent à un billet	1 870
+ billets sans événement (une ligne chacun dans une jointure gauche)	+ 8
= jointure gauche pour les 1 403 billets distincts	1 878
+ lignes supplémentaires provenant des 14 lignes de billets en double : chacune répète tous les événements de son billet	+ 23
= jointure gauche de tickets et outcomes	1 901

Chaque ligne est maintenant expliquée. La dernière ligne mérite un examen plus attentif.

Exécutez du SQL dans votre navigateur

Essayez : l'autre sens

Cette jointure part de outcomes, donc elle garde tous les événements. WHERE t.ticket_id IS NULL garde seulement les événements sans billet. Vous devriez en voir 4. Modifiez ensuite la requête : partez de tickets, joignez outcomes, gardez les lignes où o.outcome_id IS NULL et comptez-les. Vous devriez en voir 8.

Requête SQL (vous pouvez la modifier)SELECT COUNT(*) AS event_rows FROM outcomes AS o LEFT JOIN tickets AS t ON t.ticket_id = o.ticket_id WHERE t.ticket_id IS NULL;

Idée fausse : « une jointure ajoute seulement des colonnes »

Beaucoup pensent qu’une jointure ajoute les colonnes de la deuxième table et garde les lignes de la première. C’est vrai seulement si chaque ligne de gauche correspond à au plus une ligne de droite. Si la clé n’est pas unique à droite, les lignes se répètent. Si elle n’est pas unique des deux côtés, les répétitions se multiplient.

Le billet T-11149 le montre. Il apparaît deux fois dans tickets (un chevauchement de pages lors de l’exportation). Il a 5 lignes dans outcomes, dont une est un événement répété. Une nouvelle tentative a enregistré O-01568 de nouveau sous O-01569.

Exécutez du SQL dans votre navigateur

Essayez : un billet, dix lignes

Prédisez le nombre de lignes avant l’exécution : 2 lignes de billets, 5 lignes d’événements. Vous devriez voir 10 lignes; chaque résultat apparaît deux fois. Ajoutez ensuite DISTINCT après SELECT et exécutez de nouveau : 5 lignes. Est-ce le bon nombre d’événements pour T-11149?

Requête SQL (vous pouvez la modifier)SELECT t.ticket_id, o.outcome_id, o.recorded_at, o.status FROM tickets AS t INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id WHERE t.ticket_id = 'T-11149' ORDER BY o.outcome_id;

Contenu de la source	Nombre
Lignes de T-11149 dans tickets	2 (une est un doublon)
Lignes de T-11149 dans outcomes	5 (une est un événement répété)
Lignes après la jointure	2 × 5 = 10
Vrais événements de T-11149	4 : escalated, resolved, reopened, resolved

SELECT DISTINCT supprime la copie provenant de la ligne de billet en double, mais pas l’événement répété. O-01568 et O-01569 ont des identifiants différents. Le total de 5 reste donc trop élevé de 1. Une somme ou un compte d’événements après cette jointure est faux par un facteur de 2,5. SQL ne le signale pas.

Les 14 lignes de billets en double et les 30 événements répétés se trouvent dans toute la table, pas seulement dans T-11149. Chaque ligne de billet en double répète tous les événements de son billet : c’est le +23 du tableau de Mei. Le module 4 supprime les deux sortes de doublons lors d’une étape de nettoyage, avec un compte. D’ici là, protégez chaque compte vous-même. Après une jointure avec outcomes, comptez les billets distincts (COUNT(DISTINCT t.ticket_id)), jamais les lignes.

Erreur courante

Une jointure sans condition

Symptôme : Tomás veut chaque billet avec ses événements et écrit SELECT COUNT(*) FROM tickets JOIN outcomes;. Il oublie la ligne ON. La requête répond 2 655 458. Cela ressemble à « beaucoup d’événements ». Plus tard, il ajoute customers de la même façon, et l’encadré du navigateur s’arrête après 8 secondes : « Stopped after 8 seconds. Does a join match every row with every row? » (Arrêt après 8 secondes. Une jointure associe-t-elle chaque ligne à toutes les autres?)

Cause : sans condition, une jointure associe chaque ligne de la table de gauche à chaque ligne de la table de droite. C’est une jointure croisée : 1 417 × 1 874 = 2 655 458 lignes. Avec une troisième table, le résultat contient des milliards de lignes, et la requête prend trop de temps à terminer.

Diagnostic : comparez le nombre de lignes après la jointure aux nombres de lignes avant celle-ci. Un résultat supérieur à « lignes × nombre maximal de correspondances pour une ligne » est impossible pour une jointure correcte. Si le résultat est proche de rows × rows (lignes × lignes), il manque une condition.

Correction : donnez à chaque jointure une condition ON sur la clé : JOIN outcomes AS o ON o.ticket_id = t.ticket_id. Comptez ensuite de nouveau : 1 893 lignes.

Conséquence en IA : le cas silencieux est pire que le cas lent. Une mauvaise condition, comme une jointure sur customer_id plutôt que sur ticket_id, termine quand même. Elle donne à chaque billet les événements de tous les billets du client. Les caractéristiques ou les cibles construites à partir de ce résultat sont fausses pour chaque ligne. Le modèle apprend à partir de ces données.

Exécutez du SQL dans votre navigateur

Essayez : une requête qui ne peut pas terminer

Une virgule entre les tables est une autre façon d’écrire une jointure croisée. Cette requête demande 1 417 × 1 417 × 1 417 lignes, soit environ 2,8 milliards. Appuyez sur Run (exécuter) et attendez : l’encadré l’arrête après 8 secondes. Sur votre ordinateur, python3 q.py (python q.py sur Windows) ne l’arrête pas. Appuyez sur Ctrl+C pour l’arrêter.

Requête SQL (vous pouvez la modifier)SELECT COUNT(*) AS result_rows FROM tickets AS a, tickets AS b, tickets AS c;

Exercice guidé : trouver les lignes sans correspondance

Avant de faire confiance à une jointure, listez les lignes sans correspondance dans les deux sens. Enregistrez les vérifications dans un fichier par sens.

1. Ouvrez un terminal dans ~/projects/ticket-data.

2. Créez le fichier queries/24-join-counts.sql avec la requête de comptage de Mei tirée de l’exemple détaillé.

3. Exécutez-le :

   macOS

   python3 q.py queries/24-join-counts.sql

   Windows

   python q.py queries/24-join-counts.sql

   Vérification : la sortie est 1417 1396 1417 1893 1901.

4. Dans l’encadré ci-dessous, écrivez une requête qui liste les événements orphelins : ceux dont le billet est absent de tickets.

5. Quand la vérification réussit, enregistrez la requête sous queries/25-orphan-events.sql. Exécutez-la avec python3 q.py queries/25-orphan-events.sql (python q.py … sur Windows). Vérification : 4 lignes.

Exécutez du SQL dans votre navigateur

À vous : les événements orphelins

La requête de départ liste les 1 874 événements. Ajoutez une jointure avec tickets et une ligne WHERE pour garder seulement les événements sans billet. Quand elle est correcte, vous voyez 4 lignes, pour T-9990 à T-9993, et la vérification réussit.

Requête SQL (vous pouvez la modifier)-- Events whose ticket is not in tickets. -- Show outcome_id, ticket_id and status, sorted by outcome_id. SELECT o.outcome_id, o.ticket_id, o.status FROM outcomes AS o -- add the join and the condition here ORDER BY o.outcome_id;

Afficher l’exemple de réponse

queries/25-orphan-events.sql

-- Events whose ticket is not in tickets.
SELECT o.outcome_id, o.ticket_id, o.status
FROM outcomes AS o
LEFT JOIN tickets AS t ON t.ticket_id = o.ticket_id
WHERE t.ticket_id IS NULL
ORDER BY o.outcome_id;

La jointure gauche garde tous les événements. Pour un événement sans billet, chaque colonne de t vaut NULL. t.ticket_id IS NULL trouve donc exactement ces lignes. Une jointure interne ne peut pas les trouver : elle les supprime. Les quatre événements ont le statut resolved (résolu). Testez t.ticket_id, la clé utilisée pour la jointure, et non une autre colonne de t. Une autre colonne peut valoir NULL dans une ligne qui a une correspondance.

Exercice autonome : les clients sans billet

Omar demande : « À quels segments appartiennent les clients sans billet? Peut-être qu’un segment entier ne nous contacte jamais. »

Écrivez une requête qui part de customers, trouve les clients sans billet et les compte par segment. Enregistrez-la dans queries/26-quiet-customers.sql. Avant de l’exécuter, prédisez le total : vous le connaissez grâce à l’exemple détaillé.

Afficher l’exemple de réponse

queries/26-quiet-customers.sql

SELECT c.segment, COUNT(*) AS customers_without_tickets
FROM customers AS c
LEFT JOIN tickets AS t ON t.customer_id = c.customer_id
WHERE t.ticket_id IS NULL
GROUP BY c.segment
ORDER BY customers_without_tickets DESC;

segment	customers_without_tickets
home	38
business	4
trade	3
Trade	1

Le total est 46. Trade, avec un T majuscule, est encore la catégorie désordonnée : avec LOWER(c.segment), trade en compte 4. Aucun segment ne manque complètement. La plupart des clients sans billet sont des clients résidentiels, le plus grand segment (166 sur 240). Ici, COUNT(*) compte correctement les clients. Chaque client sans billet a exactement une ligne après la jointure gauche.

Vérifiez votre compréhension

tickets contient 1 417 lignes. tickets INNER JOIN customers renvoie 1 396 lignes, et tickets LEFT JOIN customers en renvoie 1 417. Que vous indique chaque nombre?

Afficher la réponse

La jointure gauche conserve chaque ligne de billet et renvoie exactement 1 417 lignes. Aucun billet ne correspond donc à plus d’un client. customer_id est unique dans customers, et la jointure ajoute seulement des colonnes. La jointure interne renvoie 21 lignes de moins. Ainsi, 21 lignes de billets n’ont aucun client correspondant. Ce sont 12 billets d’invités dont customer_id vaut NULL et 9 billets de 4 comptes supprimés (C-0241 à C-0244). Un rapport qui utilise la jointure interne perd ces 21 lignes sans produire d’erreur.

Vérifiez votre compréhension

Prédisez : le billet A a 2 lignes identiques dans tickets et 3 événements dans outcomes, sans événement répété. Combien de lignes tickets INNER JOIN outcomes donne-t-il pour A, et que donne COUNT(DISTINCT o.outcome_id)?

Afficher la réponse

2 × 3 = 6 lignes : chaque ligne de billet correspond aux trois événements. COUNT(DISTINCT o.outcome_id) donne 3, le nombre réel d’événements, car chaque événement a son propre identifiant. Cela fonctionne ici seulement parce qu’aucun événement n’est répété. Un événement répété a un nouvel outcome_id. Pour un billet comme T-11149, le nombre d’identifiants distincts reste donc trop élevé.

Vérifiez votre compréhension

Expliquez la différence entre une jointure interne et une jointure gauche, en une phrase chacune. Indiquez laquelle vous utilisez pour trouver les lignes sans correspondance.

Afficher la réponse

Une jointure interne conserve seulement les paires de lignes qui respectent la condition et élimine les lignes sans correspondance. Une jointure gauche conserve toutes les lignes de gauche et met NULL dans les colonnes de droite sans correspondance. Pour trouver les lignes sans correspondance, partez de la table dont vous voulez examiner ces lignes. Utilisez une jointure gauche et conservez les lignes où la clé de la table de droite satisfait IS NULL.

Exercice du module

L’équipe de soutien veut un rapport : le volume de billets et les résultats de résolution par segment de clientèle. Priya l’utilisera aussi pour vérifier le taux d’escalade par groupe avant d’entraîner le modèle. Le rapport doit compter les billets, pas les lignes, et inclure les billets sans client.

Un exemple détaillé sur les petites tables

Mei construit d’abord le rapport sur les petites tables, où elle peut voir chaque ligne. Segments de clientèle : C-1 est home (résidentiel), C-2 est trade (professionnels), C-3 est business (entreprises).

Étape 1 : joindre et examiner les lignes. Deux jointures gauches pour ne perdre aucun billet :

SELECT t.ticket_id, c.segment, o.outcome_id, o.status
FROM tickets AS t
LEFT JOIN customers AS c ON c.customer_id = t.customer_id
LEFT JOIN outcomes AS o ON o.ticket_id = t.ticket_id
ORDER BY t.ticket_id, o.outcome_id;

ticket_id	segment	outcome_id	status	Note
T-1	home	O-1	escalated	T-1 répété : 2 événements
T-1	home	O-2	resolved
T-2	home	O-3	resolved
T-3	trade	O-4	resolved
T-4	NULL	NULL	NULL	invité (sans client), aucun événement

5 lignes pour 4 billets. C-3 n’a aucun billet, et O-5 n’a aucun billet. Aucun des deux ne peut apparaître dans un rapport qui part des billets.

Étape 2 : nommer les groupes. Un billet sans customer_id appartient au groupe guest (invité). Un billet dont le customer_id n’est pas dans customers appartient à unknown (inconnu), un compte supprimé. Mei écrit cette logique avec CASE. LOWER(c.segment) place Trade et trade dans un seul groupe, uniquement pour ce rapport.

Étape 3 : compter les billets distincts par groupe. COUNT(*) donnerait 3 pour home, qui contient 2 billets.

customer_group	tickets	resolved	escalated	no_event
home	2	2	1	0
trade	1	1	0	0
guest	1	0	0	1
Total	4

Le total est 4, le nombre de billets distincts dans tickets. Les totaux du rapport concordent avec la source.

Ne donnez pas à un alias le nom d'une colonne

Si vous écrivez END AS segment, puis GROUP BY segment, SQLite regroupe selon la colonne c.segment, pas selon votre résultat CASE. Le rapport contient alors deux lignes trade et aucune ligne pour les invités, sans produire d’erreur. Utilisez un nouveau nom, comme customer_group.

Votre tâche

Écrivez le même rapport sur les vraies tables, dans queries/27-segment-report.sql. Il contient cinq colonnes, dans cet ordre :

1. le groupe de clientèle
2. le nombre de billets
3. les billets ayant au moins un événement resolved (résolu)
4. les billets ayant au moins un événement escalated (transféré au niveau supérieur)
5. les billets sans événement.

Utilisez les groupes home, trade, business, guest et unknown. Ensuite, vérifiez que le total concorde avec les tables sources et expliquez les lignes sans correspondance.

Exécutez du SQL dans votre navigateur

Tâche du module : le rapport par segment

La requête de départ s’exécute, et ses nombres semblent plausibles : 4 groupes, 1 863 au total. Corrigez-la pour compter les billets distincts, conserver chaque billet, nommer les groupes d’invités et d’inconnus, et ajouter les trois colonnes de résultats. Une fois la requête correcte, vous voyez 5 lignes, le total des billets atteint 1 403 et la vérification réussit.

Requête SQL (vous pouvez la modifier)-- Ticket volume and outcomes by customer group. -- Count distinct tickets. Keep the tickets with no customer. SELECT c.segment AS customer_group, COUNT(*) AS tickets FROM tickets AS t INNER JOIN customers AS c ON c.customer_id = t.customer_id INNER JOIN outcomes AS o ON o.ticket_id = t.ticket_id GROUP BY customer_group;

Afficher la réponse modèle

queries/27-segment-report.sql

-- Ticket volume and outcomes by customer group.
-- Counts distinct tickets, so duplicate rows and repeated events count once.
SELECT
    CASE
        WHEN t.customer_id IS NULL THEN 'guest'
        WHEN c.customer_id IS NULL THEN 'unknown'
        ELSE LOWER(c.segment)
    END AS customer_group,
    COUNT(DISTINCT t.ticket_id) AS tickets,
    COUNT(DISTINCT CASE WHEN o.status = 'resolved' THEN t.ticket_id END) AS resolved,
    COUNT(DISTINCT CASE WHEN o.status = 'escalated' THEN t.ticket_id END) AS escalated,
    COUNT(DISTINCT CASE WHEN o.outcome_id IS NULL THEN t.ticket_id END) AS no_event
FROM tickets AS t
LEFT JOIN customers AS c ON c.customer_id = t.customer_id
LEFT JOIN outcomes AS o ON o.ticket_id = t.ticket_id
GROUP BY customer_group
ORDER BY tickets DESC;

Output of python3 q.py queries/27-segment-report.sql

customer_group  tickets  resolved  escalated  no_event
--------------  -------  --------  ---------  --------
trade           634      632       107        2
home            403      399       44         2
business        345      340       70         4
guest           12       12        1          0
unknown         9        9         3          0

5 rows

Vérification de la concordance avec les tables sources :

Colonne du rapport	Total du rapport	Vérification dans la source	Explication de l’écart
tickets	634 + 403 + 345 + 12 + 9 = 1 403	COUNT(DISTINCT ticket_id) dans tickets = 1 403	aucun : chaque billet appartient à exactement un groupe
resolved	1 392	billets distincts ayant un événement resolved dans outcomes = 1 396	4 : les événements orphelins de T-9990 à T-9993 sont tous resolved, et leurs billets ne sont pas dans tickets
escalated	225	billets distincts ayant un événement escalated = 225; lignes escalated = 228	3 événements d’escalade répétés; comptés comme billets, ils comptent une seule fois
no_event	8	billets sans ligne dans outcomes = 8	aucun

Enregistrements sans correspondance :

• Billets sans client (21 lignes, 21 billets) : 12 billets d’invités dont customer_id vaut NULL et 9 billets de 4 comptes supprimés, C-0241 à C-0244. Ils figurent dans le rapport sous guest et unknown. Une jointure interne les aurait éliminés.
• Clients sans billet (46) : ils ne figurent pas dans le rapport, à juste titre, car il compte les billets.
• Billets sans événement (8) : ils figurent dans le rapport, dans no_event (sans événement). Ce sont des billets récents qui n’ont pas encore de résultat.
• Événements sans billet (4) : ils ne figurent pas dans le rapport, car aucun billet ne leur correspond. Mei les énumère pour que Grace les vérifie dans le système de soutien.

La requête de départ donnait 1 863 pour trois raisons. COUNT(*) comptait les lignes d’événements, pas les billets. Les jointures internes éliminaient les 21 billets sans client et les 8 sans événement. Enfin, Trade et trade formaient deux groupes.

Vérification du module

Vérifiez votre compréhension

Que compte COUNT(DISTINCT ticket_id) que COUNT(*) ne compte pas? Pourquoi un rapport après une jointure avec outcomes doit-il l'utiliser?

Afficher la réponse

COUNT(*) compte les lignes; COUNT(DISTINCT ticket_id) compte les billets différents. Après une jointure avec outcomes, un billet a une ligne par événement. Les lignes de billets en double et les événements répétés ajoutent aussi des lignes. Ainsi, COUNT(*) compte les événements et les copies, pas les billets. Le compte distinct compte chaque billet une seule fois, quel que soit l’effet de la jointure.

Vérifiez votre compréhension

Prédisez : Mei remplace le premier LEFT JOIN du rapport par segment (avec customers) par un INNER JOIN. Quels groupes changent, et quel est le nouveau total de la colonne tickets?

Afficher la réponse

La jointure interne élimine les billets sans client correspondant. Les groupes guest (12) et unknown (9) disparaissent donc. Les autres groupes ne changent pas. Le nouveau total est 1 403 − 21 = 1 382. Le rapport s’exécute toujours, et rien n’indique que 21 billets manquent. Seule la comparaison avec le total source de 1 403 le révèle.

Vérifiez votre compréhension

Vérification finale : exécutez votre rapport par segment. Montrez que son total de billets égale celui de la source. Expliquez pourquoi la colonne resolved compte 4 billets de moins que le nombre de billets résolus dans outcomes. Énumérez chaque type d'enregistrement sans correspondance et son nombre.

Afficher la réponse

La colonne tickets totalise 634 + 403 + 345 + 12 + 9 = 1 403. Cela égale COUNT(DISTINCT ticket_id) dans tickets. La colonne resolved totalise 1 392, tandis que outcomes contient 1 396 billets distincts ayant un événement résolu. L’écart correspond aux 4 événements orphelins (T-9990 à T-9993). Ils sont résolus, mais leurs billets ne sont pas dans tickets. Un rapport qui part des billets ne peut donc pas les inclure. Les enregistrements sans correspondance sont : 21 lignes de billets sans client (12 d’invités et 9 de comptes supprimés, conservées sous guest et unknown); 46 clients sans billet (exclus à juste titre d’un rapport sur les billets); 8 billets sans événement (dans no_event); 4 événements sans billet (énumérés séparément pour Grace).

De bout en boutExpliquer les ventes d'une boutique en ligneÉtude de cas

L’étude de cas de la boutique en ligne montre une jointure qui transforme 16,2 millions de livres sterling de ventes au Royaume-Uni en 1,018 milliard. Elle montre aussi comment éviter ce problème.

Récapitulatif

Dans ce module, vous avez posé des questions à la base de données et vérifié chaque réponse :

• Sélectionner et filtrer : WHERE conserve seulement les lignes où un test est vrai. Les lignes NULL sont donc exclues par = 'email' comme par <> 'email'. La comparaison de texte est exacte : Email n’est pas email. Nommez vos colonnes et triez les résultats pour voir les extrêmes.
• Agréger : COUNT(*) compte les lignes, COUNT(column) les valeurs non NULL, et COUNT(DISTINCT ticket_id) les billets : 1 417 lignes, 1 403 billets. Les agrégations ignorent NULL et calculent correctement même avec des valeurs erronées. Affichez donc MIN et MAX à côté de chaque moyenne.
• Joindre : une jointure interne élimine les lignes sans correspondance; une jointure gauche conserve toutes les lignes de gauche. Une clé non unique répète les lignes, et les doublons des deux côtés les multiplient. Comptez les lignes et les clés distinctes avant et après chaque jointure. Trouvez les lignes sans correspondance dans les deux sens.
• Vérifier la concordance : un rapport est terminé quand ses totaux égalent ceux de la source et que chaque écart s’explique.

Votre rapport par segment est une seule longue requête. Ensuite, vous diviserez une requête semblable en étapes nommées que vous pourrez exécuter et vérifier une à la fois.

Leçon suivante : Des requêtes lisibles