Des requêtes lisibles

Ce que vous saurez faire

Écrivez une longue requête comme une liste de petites étapes nommées. Vous pourrez compter les lignes de chaque étape et trouver une erreur discrète avant qu’elle atteigne un rapport.

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. Cela comprend les comptes par étape et le cas d’échec. 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 et le diagramme ici, puis 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 45 minutes

Ce que vous devez savoir

• GROUP BY, COUNT(DISTINCT …) et CASE, présentés dans Agréger correctement : GROUP BY et Compter avec une condition : CASE.
• LEFT JOIN, et pourquoi compter les lignes après une jointure, présentés dans Joindre des tables et son idée fausse « une jointure ajoute seulement des colonnes ».
• Les 14 lignes de billets de soutien répétées et SELECT DISTINCT *, présentés dans Les concepts relationnels : vérifier ticket_id.

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 contenant 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é, suivez cette section à nouveau. Vous pouvez aussi réinitialiser la base de données avec python3 get_data.py --reset.
• Le dossier queries/, où vous enregistrez les fichiers .sql et les exécutez avec q.py, comme dans Exécuter une requête enregistrée. Cette leçon ajoute 31- et 32-.
• Un terminal ouvert dans ce dossier. Cette leçon utilise python3, comme sur macOS et Linux. Sur Windows, tapez plutôt python, comme l’explique Quelle commande Python saisir.

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

Larkfield est une boutique en ligne fictive de produits pour la maison et le jardin. Son service de soutien exporte tous les billets. Mei, l’ingénieure de données, prépare cette exportation pour un modèle qui prédit les escalades. Les données sont inventées pour ce cours; elles ne représentent aucune personne réelle. Dans ce module, Mei écrit des requêtes qu’elle réutilisera souvent. Elles suivent des étapes lisibles, traitent correctement le temps et produisent une extraction que tout le monde peut reproduire.

Une requête, plusieurs tâches

Omar, le gestionnaire de produit, demande à Mei : « Quel segment de clientèle a le plus de billets avec escalade? » Pour répondre, une requête doit accomplir quatre tâches :

1. Retirer les 14 lignes de billets répétées.
2. Trouver les billets qui ont un événement escalated (escalade) dans outcomes.
3. Joindre chaque billet à son client pour obtenir le segment.
4. Compter les billets et les escalades par segment.

Vous pouvez écrire tout cela dans une seule requête, avec des sous-requêtes dans des sous-requêtes. Elle s’exécute. Mais si le total est incorrect, vous ne voyez pas quelle tâche a échoué. Les résultats intermédiaires n’ont pas de nom. Vous ne pouvez pas les compter.

Une expression de table commune (CTE) donne un nom à un résultat intermédiaire. Vous l’écrivez avec WITH name AS (query) au début d’une requête. Le résultat nommé existe seulement pendant l’exécution de cette requête. Il n’est pas enregistré dans la base de données. Ensuite, le SELECT principal peut utiliser ce nom comme s’il s’agissait d’une table.

Un petit exemple

Cinq lignes de billets. T-10002 apparaît deux fois dans la table, en copie exacte, comme dans l’exportation réelle :

ticket_id	customer_id	team
T-10001	C-0001	delivery
T-10002	C-0001	returns
T-10002	C-0001	returns
T-10003	C-0002	delivery
T-10004	NULL	account

La CTE unique_tickets garde une seule copie de chaque ligne. La requête principale compte ensuite ses lignes :

WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
)
SELECT COUNT(*) AS row_count
FROM unique_tickets;

Prédisez le compte avant de l’exécuter.

Exécutez du SQL dans votre navigateur

Essayez : une première CTE

Cet encadré contient seulement les cinq lignes ci-dessus. Exécutez la requête : le résultat est 4. Remplacez ensuite la dernière ligne par FROM tickets; et exécutez-la à nouveau. Le résultat est maintenant 5, car la ligne répétée compte deux fois.

Requête SQL (vous pouvez la modifier)WITH unique_tickets AS ( SELECT DISTINCT * FROM tickets ) SELECT COUNT(*) AS row_count FROM unique_tickets;

Deux éléments rendent cette approche utile :

• L’étape a un nom. unique_tickets indique ce que fait l’étape. La personne qui lit n’a pas à déchiffrer une sous-requête.
• Vous pouvez examiner l’étape. Modifiez seulement le SELECT final pour voir n’importe quelle étape : ses lignes ou leur nombre.

Vous pouvez définir plusieurs CTE après un seul WITH, avec une virgule entre elles. Une étape peut utiliser une étape précédente, comme dans une recette où chaque étape utilise le résultat de la précédente.

Le pipeline

Ce diagramme montre les quatre étapes de Mei pour répondre à Omar, avec le nombre de lignes à chaque étape.

Chaque CTE est une étape avec un nom et un nombre de lignes. ticket_rows doit avoir exactement autant de lignes que unique_tickets : une ligne par billet.

Description textuelle du diagramme

Trois tables servent d’entrées : tickets (1 417 lignes), customers (240 lignes) et outcomes (1 874 lignes). L’étape 1, unique_tickets, retire les lignes de billets répétées : 1 403 lignes. L’étape 2, escalated_tickets, garde une ligne par billet ayant un événement escalated : 225 lignes. L’étape 3, ticket_rows, part de unique_tickets. Deux jointures LEFT JOIN ajoutent le segment du client et un indicateur d’escalade. Elle contient 1 403 lignes, comme l’étape 1. L’étape 4, by_segment, regroupe les lignes par segment : 5 lignes.

Le nombre important est celui qui ne doit pas changer. Une jointure LEFT JOIN garde chaque ligne du côté gauche. Si chaque côté droit a au plus une ligne par clé, la jointure ajoute des colonnes, mais aucune ligne. Donc, ticket_rows doit contenir 1 403 lignes, exactement comme unique_tickets. S’il en contient plus, une jointure a multiplié les lignes.

Exemple détaillé : escalades par segment

Mei écrit les quatre étapes sous forme de quatre CTE. Les commentaires indiquent la tâche de chaque étape :

queries/31-segment-report.sql

-- Escalations by customer segment, in named steps.
WITH unique_tickets AS (
    -- Step 1: one row per ticket (the export has 14 repeated rows)
    SELECT DISTINCT *
    FROM tickets
),
escalated_tickets AS (
    -- Step 2: one row per ticket that has an escalated event
    SELECT DISTINCT ticket_id
    FROM outcomes
    WHERE status = 'escalated'
),
ticket_rows AS (
    -- Step 3: each ticket with its segment and a 0/1 escalated flag
    SELECT
        u.ticket_id,
        CASE
            WHEN u.customer_id IS NULL THEN 'guest'
            WHEN c.customer_id IS NULL THEN 'unknown'
            ELSE LOWER(c.segment)
        END AS segment,
        CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated
    FROM unique_tickets AS u
    LEFT JOIN customers AS c
        ON c.customer_id = u.customer_id
    LEFT JOIN escalated_tickets AS e
        ON e.ticket_id = u.ticket_id
),
by_segment AS (
    -- Step 4: one row per segment
    SELECT
        segment,
        COUNT(*) AS tickets,
        SUM(escalated) AS escalated,
        ROUND(100.0 * SUM(escalated) / COUNT(*), 1) AS escalated_pct
    FROM ticket_rows
    GROUP BY segment
)
SELECT segment, tickets, escalated, escalated_pct
FROM by_segment
ORDER BY tickets DESC;

L’étape 3 utilise CASE pour donner un segment à chaque billet. Une expression CASE retourne la valeur du premier WHEN qui est vrai. Un billet sans customer_id correspond à un achat sans compte; son segment est donc guest (invité). Un billet dont le client est absent de customers (un compte supprimé) reçoit unknown (inconnu). LOWER réunit Trade (3 clients) et trade dans un seul groupe. Le deuxième CASE transforme « a une ligne d’escalade correspondante » en 1 et « aucune correspondance » en 0. Ensuite, SUM(escalated) compte les billets avec escalade.

Le résultat :

segment	tickets	escalated	escalated_pct
trade	634	107	16.9
home	403	44	10.9
business	345	70	20.3
guest	12	1	8.3
unknown	9	3	33.3

Avant de l’envoyer, Mei vérifie que les totaux correspondent à la source :

• Billets : 634 + 403 + 345 + 12 + 9 = 1 403, le nombre de billets distincts.
• Billets avec escalade : 107 + 44 + 70 + 1 + 3 = 225, le nombre de lignes dans escalated_tickets.

Sa réponse à Omar : les clients du segment business ont le plus souvent une escalade (20,3 % de leurs billets). Les clients du segment trade ont le plus de billets avec escalade (107). Les groupes guest et unknown sont trop petits pour les comparer. Ce rapport compte toutes les escalades de l’exportation. La cible du modèle au module 5 est plus précise : une escalade dans les 72 heures suivant la création.

Remarque

La syntaxe WITH est la même dans PostgreSQL. SQLite prend en charge les CTE depuis la version 3.8.3 (2014).

Examinez chaque étape

Le SELECT principal à la fin peut lire n’importe quelle CTE. Pour vérifier une étape, remplacez seulement la dernière partie de la requête. Exécutez l’encadré ci-dessous tel quel : il affiche le nombre de lignes de ticket_rows.

Exécutez du SQL dans votre navigateur

Essayez : compter les lignes d'une étape

Le résultat est 1 403 lignes. Remplacez FROM ticket_rows par FROM escalated_tickets et exécutez à nouveau : 225. Remplacez ensuite les deux dernières lignes par SELECT * FROM ticket_rows WHERE segment = 'unknown'; pour voir les 9 billets de clients supprimés.

Requête SQL (vous pouvez la modifier)WITH unique_tickets AS ( SELECT DISTINCT * FROM tickets ), escalated_tickets AS ( SELECT DISTINCT ticket_id FROM outcomes WHERE status = 'escalated' ), ticket_rows AS ( SELECT u.ticket_id, CASE WHEN u.customer_id IS NULL THEN 'guest' WHEN c.customer_id IS NULL THEN 'unknown' ELSE LOWER(c.segment) END AS segment, CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated FROM unique_tickets AS u LEFT JOIN customers AS c ON c.customer_id = u.customer_id LEFT JOIN escalated_tickets AS e ON e.ticket_id = u.ticket_id ) SELECT COUNT(*) AS row_count FROM ticket_rows;

Exercice guidé : enregistrer le rapport et ses vérifications

Exécutez les mêmes étapes sur votre ordinateur. Enregistrez une deuxième requête qui affiche en une fois le nombre de lignes de chaque étape.

1. Ouvrez un terminal dans le dossier de votre projet, ~/projects/ticket-data.

2. Créez le fichier queries/31-segment-report.sql. Copiez-y la requête de Mei de l’exemple détaillé. Le numéro dans le nom sert seulement à garder vos fichiers en ordre.

3. Exécutez la requête :

   python3 q.py queries/31-segment-report.sql

   Sortie

   segment   tickets  escalated  escalated_pct
   --------  -------  ---------  -------------
   trade     634      107        16.9
   home      403      44         10.9
   business  345      70         20.3
   guest     12       1          8.3
   unknown   9        3          33.3
   
   5 rows

4. Créez un deuxième fichier, queries/32-step-counts.sql. Copiez-y les trois premières CTE (unique_tickets, escalated_tickets, ticket_rows).

5. Après les CTE, ajoutez un SELECT par étape, reliés par UNION ALL. UNION ALL place les lignes de plusieurs requêtes les unes sous les autres. Chaque SELECT doit avoir le même nombre de colonnes.

   queries/32-step-counts.sql (end of the file)

   SELECT 'tickets' AS step, COUNT(*) AS row_count FROM tickets
   UNION ALL
   SELECT 'unique_tickets', COUNT(*) FROM unique_tickets
   UNION ALL
   SELECT 'escalated_tickets', COUNT(*) FROM escalated_tickets
   UNION ALL
   SELECT 'ticket_rows', COUNT(*) FROM ticket_rows
   UNION ALL
   SELECT 'distinct tickets in ticket_rows', COUNT(DISTINCT ticket_id) FROM ticket_rows;

6. Exécutez la requête :

   python3 q.py queries/32-step-counts.sql

   Sortie

   step                             row_count
   -------------------------------  ---------
   tickets                          1417
   unique_tickets                   1403
   escalated_tickets                225
   ticket_rows                      1403
   distinct tickets in ticket_rows  1403
   
   5 rows

7. Vérifiez les deux dernières lignes. Elles doivent être égales : une ligne par billet dans ticket_rows.

Afficher le fichier complet 32-step-counts.sql

queries/32-step-counts.sql

-- Row count of each step in 31-segment-report.sql.
WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
),
escalated_tickets AS (
    SELECT DISTINCT ticket_id
    FROM outcomes
    WHERE status = 'escalated'
),
ticket_rows AS (
    SELECT
        u.ticket_id,
        CASE
            WHEN u.customer_id IS NULL THEN 'guest'
            WHEN c.customer_id IS NULL THEN 'unknown'
            ELSE LOWER(c.segment)
        END AS segment,
        CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated
    FROM unique_tickets AS u
    LEFT JOIN customers AS c
        ON c.customer_id = u.customer_id
    LEFT JOIN escalated_tickets AS e
        ON e.ticket_id = u.ticket_id
)
SELECT 'tickets' AS step, COUNT(*) AS row_count FROM tickets
UNION ALL
SELECT 'unique_tickets', COUNT(*) FROM unique_tickets
UNION ALL
SELECT 'escalated_tickets', COUNT(*) FROM escalated_tickets
UNION ALL
SELECT 'ticket_rows', COUNT(*) FROM ticket_rows
UNION ALL
SELECT 'distinct tickets in ticket_rows', COUNT(DISTINCT ticket_id) FROM ticket_rows;

Gardez ce fichier à côté du rapport. Quand vous modifiez le rapport, exécutez à nouveau les comptes. Si ticket_rows et distinct tickets in ticket_rows (billets distincts dans ticket_rows) diffèrent, une jointure a ajouté des lignes.

À vous de jouer : escalades par équipe

Grace, la responsable du soutien, pose la même question par équipe : delivery, returns, payment, warranty et account. Changez une condition : regroupez par team plutôt que par segment du client. L’équipe est une colonne de tickets; vous n’avez donc pas besoin de la table customers.

Exécutez du SQL dans votre navigateur

À vous de jouer : escalades par équipe

Écrivez la requête. Le résultat contient cinq lignes, une par équipe, et trois colonnes : team, tickets, escalated. Les billets doivent totaliser 1 403 et les escalades, 225. La vérification compare votre résultat au nôtre; l’ordre des lignes ne compte pas.

Requête SQL (vous pouvez la modifier)WITH unique_tickets AS ( SELECT DISTINCT * FROM tickets ), escalated_tickets AS ( SELECT DISTINCT ticket_id FROM outcomes WHERE status = 'escalated' ) -- Add a step ticket_rows with ticket_id, team and a 0/1 escalated flag. -- Then return three columns: team, tickets, escalated. SELECT COUNT(*) FROM unique_tickets;

Afficher l’exemple de réponse

WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
),
escalated_tickets AS (
    SELECT DISTINCT ticket_id
    FROM outcomes
    WHERE status = 'escalated'
),
ticket_rows AS (
    SELECT
        u.ticket_id,
        u.team,
        CASE WHEN e.ticket_id IS NULL THEN 0 ELSE 1 END AS escalated
    FROM unique_tickets AS u
    LEFT JOIN escalated_tickets AS e
        ON e.ticket_id = u.ticket_id
)
SELECT team, COUNT(*) AS tickets, SUM(escalated) AS escalated
FROM ticket_rows
GROUP BY team
ORDER BY tickets DESC;

team	tickets	escalated
delivery	449	52
returns	321	46
payment	240	51
warranty	205	60
account	188	16

Les totaux sont de 1 403 billets et 225 escalades, comme pour les segments. Les billets de warranty ont le plus souvent une escalade : 60 sur 205, soit 29,3 %. Les comptes par équipe sont un peu plus faibles que dans la table brute (delivery : 449, et non 452). C’est parce que unique_tickets a retiré les lignes répétées.

Erreur courante

Une étape sans DISTINCT ajoute trois billets

Symptôme : Tomás copie le rapport et retire DISTINCT de escalated_tickets, parce qu’« un billet a une escalade ou non ». La requête s’exécute sans erreur. La table des segments semble normale. Mais les billets totalisent 1 406, et non 1 403, et les escalades, 228, et non 225.

Cause : outcomes contient une ligne par événement, pas par billet. Pour trois billets, l’événement d’escalade est enregistré deux fois (une nouvelle tentative : T-10115, T-10887 et T-11005). Sans DISTINCT, escalated_tickets contient 228 lignes pour 225 billets. La jointure LEFT JOIN trouve deux correspondances pour chacun des trois billets; ticket_rows contient donc 1 406 lignes.

Diagnostic : exécutez les comptes par étape (queries/32-step-counts.sql). ticket_rows contient 1 406 lignes, mais distinct tickets in ticket_rows reste à 1 403. La première étape dont le compte augmente est celle qui pose problème.

Correction : donnez à chaque étape la granularité dont l’étape suivante a besoin. Ici : une ligne par billet, donc SELECT DISTINCT ticket_id. Exécutez ensuite les comptes à nouveau.

En IA : un billet présent deux fois dans une table d’entraînement compte deux fois. Il reçoit plus de poids pendant l’entraînement et peut se retrouver dans les données d’entraînement et de test. Le module 5 explique ces ensembles de données. Rien ne vous avertit. Seul un compte de lignes le fait.

Vérifiez votre compréhension

Dans le rapport de Mei, vous retirez DISTINCT de unique_tickets. L'étape lit donc chaque ligne de tickets. Prédisez le nombre de lignes de ticket_rows et expliquez-le.

Afficher la réponse

1 417 lignes. tickets contient 1 417 lignes, dont 14 copies exactes. Les jointures LEFT JOIN gardent chaque ligne de gauche et n’ajoutent aucune ligne. En effet, customers et escalated_tickets ont au plus une ligne par clé. Donc, ticket_rows contient une ligne par ligne de billet, et les 14 billets répétés comptent deux fois. Les comptes par étape le montrent : ticket_rows donne 1 417, mais distinct tickets in ticket_rows donne 1 403.

Vérifiez votre compréhension

Le même rapport peut être une seule requête avec des sous-requêtes imbriquées. Donnez deux raisons de l'écrire avec des CTE.

Afficher la réponse

1. Chaque étape a un nom qui indique ce qu’elle fait (unique_tickets, escalated_tickets). La personne qui lit peut donc suivre la requête de haut en bas.
2. Vous pouvez vérifier chaque étape : modifiez seulement le SELECT final pour compter ou afficher les lignes de n’importe quelle CTE. Avec des sous-requêtes imbriquées, les résultats intermédiaires n’ont pas de nom. Vous devez découper la requête pour les voir.

Vérifiez votre compréhension

Quel nombre de lignes du pipeline ne doit pas changer, et pourquoi?

Afficher la réponse

ticket_rows doit avoir le même compte que unique_tickets : 1 403. L’étape 3 ajoute seulement des colonnes (le segment et l’indicateur) avec des jointures LEFT JOIN. Une jointure LEFT JOIN garde chaque ligne de gauche. Elle ajoute des lignes seulement si le côté droit a plusieurs correspondances. Un compte plus élevé signifie donc qu’un côté droit contient des clés répétées.

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

L’étude de cas de la boutique en ligne construit une requête de fidélisation des clients comme une chaîne d’étapes nommées.

Récapitulatif

• Une CTE (WITH name AS (...)) donne un nom à un résultat intermédiaire pour une seule requête. Plusieurs CTE, séparées par des virgules, forment un pipeline d’étapes.
• Pour vérifier une étape, modifiez seulement le SELECT final : comptez ses lignes ou affichez-les.
• Notez le nombre de lignes que chaque étape doit avoir. Une jointure LEFT JOIN qui ajoute seulement des colonnes ne doit pas changer ce compte.
• Enregistrez une requête de comptes par étape à côté de chaque rapport. Exécutez-la après chaque modification.

Ensuite, vous travaillerez avec le temps : filtrer les billets par date et trouver le dernier événement de chaque billet avec des fonctions de fenêtrage.

Leçon suivante : Le temps et le classement