Une extraction reproductible

Ce que vous saurez faire

Transformez vos requêtes en une extraction que tout le monde peut refaire. Un script Python exécute les requêtes enregistrées avec des paramètres et écrit des fichiers CSV. Il consigne exactement ce qu’il a sélectionné et dans quel instantané.

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. Vous écrivez les deux fichiers de requête et les trois versions de scripts/extract.py dans les cellules du carnet. Vous exécutez le script et lisez son manifeste. La première cellule télécharge les données d’exercice et construit la base de données. Le carnet fonctionne donc sans le projet local du module 1.

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

• Les intervalles de dates semi-ouverts, dans Le temps et le classement : intervalles semi-ouverts.
• Une seule ligne la plus récente par billet de soutien, dans Le temps et le classement : le dernier résultat de chaque billet.
• Lire un fichier texte avec pathlib, dans Lire et écrire des fichiers : chemins avec pathlib du cours Python pour l’ingénierie de l’IA en pratique.
• Écrire un fichier CSV avec le module csv, dans Lire et écrire des fichiers : CSV et JSON du cours de Python.
• Les options de ligne de commande avec argparse, dans L’interface en ligne de commande : argparse du cours de Python.

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 de données manque ou a changé, refaites cette section. Vous pouvez aussi réinitialiser la base de données avec python3 get_data.py --reset. L’extraction lit seulement la base de données, comme le fait q.py.
• Le dossier queries/, comme dans Exécuter une requête enregistrée. Cette leçon ajoute extract_tickets.sql, extract_outcomes.sql, 35- et 36-.
• Un dossier scripts/ pour extract.py. S’il n’existe pas encore, créez-le dans le dossier du projet avec mkdir scripts. Si votre script ne fonctionne plus, copiez la version terminée du dossier C04/pipeline du cours.
• Un terminal ouvert dans ce dossier. Le script utilise seulement la bibliothèque standard de Python : aucun environnement virtuel pour le moment. 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 commandes Windows n’ont pas été testées. Le carnet a été testé dans Jupyter.

Un fichier que personne ne peut expliquer

Priya reçoit un fichier CSV de billets d’un collègue. Il contient 1 416 lignes. Elle a des questions avant d’entraîner quoi que ce soit :

• De quelle exportation vient-il? Le service de soutien exporte les données chaque mois.
• Quels billets ont été sélectionnés? Pourquoi 1 416 et non 1 417?
• Quelle requête l’a produit? Quelqu’un a-t-il modifié la requête depuis?
• Quand a-t-il été produit?

Le fichier ne peut pas répondre. Mei n’envoie donc jamais un fichier de données seul. Elle l’accompagne d’un manifeste : un petit fichier JSON qui consigne comment les données ont été produites. Son manifeste consigne cinq faits :

Fait	Exemple	Pourquoi
Moment de l’extraction	2026-10-07T03:08:46+00:00	Quand le script a été exécuté, en UTC.
Version de la source	La table snapshot : exportation du 2026-07-01 06:00:00, version 1.0	Quelles données ont servi. Un instantané est une copie fixe des données à un moment donné; il ne change pas après l’exportation.
Critères de sélection	created_from = 2026-01-01, created_before = 2026-07-01	Quelles lignes ont été choisies, et selon quelle règle.
Empreinte de la requête	Un SHA-256 de chaque fichier de requête	Quelle requête exacte a été exécutée.
Nombre de lignes	1 416 lignes de billets; 1 ligne non sélectionnée	Ce qui a été produit et ce qui a été exclu.

Un SHA-256 est une empreinte de 64 caractères d’un texte ou d’un fichier. Le même texte donne toujours la même empreinte. Modifier un seul caractère, même une espace, donne une empreinte complètement différente. Deux manifestes avec le même SHA-256 de requête ont donc utilisé la même requête.

Paramètres : des valeurs qui ne font pas partie de la requête

Les dates de sélection changent d’une extraction à l’autre. La requête ne devrait pas changer. Mei écrit donc la requête avec des paramètres nommés : :created_from et :created_before servent de substituts. Python fournit leurs valeurs séparément.

sql = "SELECT COUNT(*) FROM tickets WHERE team = :team"
con.execute(sql, {"team": "returns"})

sqlite3 envoie séparément le texte de la requête et les valeurs à SQLite. SQLite lit le texte comme du SQL et les valeurs seulement comme des valeurs. Une valeur ne peut jamais devenir une partie du SQL. Le texte de la requête reste aussi identique pour chaque sélection, donc son SHA-256 ne change pas.

L’autre méthode consiste à construire le texte SQL avec une chaîne formatée (f-string) : f"... WHERE team = '{team}'". Elle fonctionne pour returns. Le cas d’échec ci-dessous montre ce qui arrive avec d’autres valeurs. N’insérez jamais une valeur dans du texte SQL avec une f-string.

Dans PostgreSQL

Avec la bibliothèque psycopg, les paramètres nommés s’écrivent %(team)s plutôt que :team. Les paramètres positionnels s’écrivent %s plutôt que ?. La règle reste la même : passez les valeurs séparément. Cet exemple n’est pas exécuté dans ce cours.

Vérifier la sélection dans SQL

Avant d’écrire le script, Mei vérifie ce que la sélection contiendra. Elle remplace les paramètres par les deux dates, seulement pour ce test :

Exécutez du SQL dans votre navigateur

Essayez : vérifier la sélection

Une comparaison vaut 1 quand elle est vraie et 0 quand elle est fausse. SUM compte donc les lignes où elle est vraie. Vous devriez voir 1 416 lignes sélectionnées, 1 non sélectionnée et 1 417 au total. Ajoutez une requête en dessous, SELECT ticket_id, created_at FROM tickets WHERE created_at >= '2026-07-01';, puis exécutez à nouveau. La ligne non sélectionnée est T-10147, le billet de 2036.

Requête SQL (vous pouvez la modifier)SELECT SUM(created_at >= '2026-01-01' AND created_at < '2026-07-01') AS selected, SUM(NOT (created_at >= '2026-01-01' AND created_at < '2026-07-01')) AS not_selected, COUNT(*) AS all_rows FROM tickets;

L’extraction garde les 14 lignes de billets répétées. Les supprimer est une décision de nettoyage; le module 4 prend cette décision et compte les suppressions. Une extraction ne fait que sélectionner : elle ne change pas les valeurs.

Les deux fichiers de requête

Mei enregistre chaque requête dans son propre fichier. Le script peut ainsi calculer son SHA-256, et une personne peut la lire pour la réviser.

queries/extract_tickets.sql

-- One row per ticket row in the export, with the customer's attributes.
-- LEFT JOIN keeps tickets with no customer (guests) and tickets whose
-- customer is no longer in the customer table. Duplicates are kept here:
-- the cleaning step removes them and counts them.
SELECT
    t.ticket_id,
    t.customer_id,
    t.created_at,
    t.channel,
    t.team,
    t.priority,
    t.order_value,
    t.word_count,
    t.first_reply_minutes,
    t.priority_now,
    t.closed_at,
    c.segment,
    c.region,
    c.joined_on
FROM tickets AS t
LEFT JOIN customers AS c
    ON c.customer_id = t.customer_id
WHERE t.created_at >= :created_from
  AND t.created_at <  :created_before
ORDER BY t.ticket_id;

queries/extract_outcomes.sql

-- Every outcome event of the tickets in the selection. Events for tickets
-- that are not in the ticket table are left out by the join.
SELECT
    o.outcome_id,
    o.ticket_id,
    o.recorded_at,
    o.status,
    o.resolution_code,
    o.csat
FROM outcomes AS o
WHERE o.ticket_id IN (
    SELECT t.ticket_id
    FROM tickets AS t
    WHERE t.created_at >= :created_from
      AND t.created_at <  :created_before
)
ORDER BY o.outcome_id;

Trois choix dans ces fichiers sont importants :

• La même règle dans les deux fichiers. Un événement de résultat est sélectionné quand son billet est sélectionné. Les deux fichiers décrivent donc toujours les mêmes billets.
• ORDER BY. Sans cette clause, la base de données peut retourner les lignes dans n’importe quel ordre. Le fichier CSV peut alors différer entre deux exécutions sur les mêmes données.
• L’extraction garde des colonnes comme priority_now. Elles constituent une fuite de données : de l’information postérieure à la création du billet. L’extraction est une copie fidèle; le module 5 décide quelles colonnes un modèle peut utiliser.

La requête des résultats utilise IN (subquery), pas une jointure. IN vérifie seulement si le billet est dans la liste. Un billet présent deux fois dans tickets ne peut donc pas doubler ses événements.

Exécutez du SQL dans votre navigateur

À vous : combien d'événements l'extraction gardera-t-elle?

La requête compte les 1 874 événements. Ajoutez la règle de sélection de extract_outcomes.sql, avec les deux dates à la place des paramètres. Faites d’abord une prédiction : 4 événements appartiennent à des billets absents de tickets, et le billet de 2036 a 1 événement.

Requête SQL (vous pouvez la modifier)-- Count the outcome events whose ticket is in the selection -- (created from 2026-01-01, before 2026-07-01). SELECT COUNT(*) AS events FROM outcomes;

Afficher l’exemple de réponse

SELECT COUNT(*) AS events
FROM outcomes
WHERE ticket_id IN (
    SELECT ticket_id
    FROM tickets
    WHERE created_at >= '2026-01-01'
      AND created_at <  '2026-07-01'
);

Le résultat est 1 869 : 1 874 − 4 événements des billets manquants T-9990 à T-9993 − 1 événement de T-10147.

Exercice guidé : construire extract.py

Construisez le script en trois versions. Exécutez chaque version avant d’ajouter la suite.

1. Créez les deux fichiers queries/extract_tickets.sql et queries/extract_outcomes.sql. Copiez-y le texte ci-dessus.

2. Créez le fichier scripts/extract.py avec cette première version. Elle exécute une requête avec des paramètres et affiche le nombre de lignes.

   scripts/extract.py (version 1)

   """Extract tickets and outcomes from larkfield.db into CSV files, with a manifest."""
   
   import sqlite3
   from pathlib import Path
   
   
   def main():
       params = {"created_from": "2026-01-01", "created_before": "2026-07-01"}
       sql = Path("queries/extract_tickets.sql").read_text(encoding="utf-8")
   
       con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)
       rows = con.execute(sql, params).fetchall()
       con.close()
   
       print(f"{len(rows)} ticket rows")
       print(rows[0])
   
   
   if __name__ == "__main__":
       main()

   L’adresse file:larkfield.db?mode=ro avec uri=True ouvre la base de données en lecture seule, comme le fait q.py. Les clés de params sont les noms des paramètres sans le deux-points.

3. Exécutez-le depuis le dossier du projet. Les chemins du script partent du dossier où vous l’exécutez.

   python3 scripts/extract.py

   Sortie

   1416 ticket rows
   ('T-10001', 'C-0228', '2026-01-05 07:30:24', 'email', 'returns', 2, 47.25, 134, 9, 2, '2026-01-06 17:58:45', 'home', 'north', '2025-10-11')

   Si vous voyez sqlite3.ProgrammingError: You did not supply a value for binding parameter :created_before. (aucune valeur fournie pour le paramètre), une clé de params manque ou s’écrit autrement.

4. Remplacez tout le fichier par la version 2. La fonction run exécute un fichier de requête et écrit un fichier CSV. Elle retourne les faits pour le manifeste : le fichier de requête, son SHA-256, le fichier de sortie et le nombre de lignes.

   scripts/extract.py (version 2)

   """Extract tickets and outcomes from larkfield.db into CSV files, with a manifest."""
   
   import csv
   import hashlib
   import sqlite3
   from pathlib import Path
   
   OUT = Path("data/extract")
   
   
   def run(con, query_file, params, out_file):
       sql = Path(query_file).read_text(encoding="utf-8")
       cursor = con.execute(sql, params)
       header = [d[0] for d in cursor.description]
       rows = cursor.fetchall()
       with open(out_file, "w", newline="", encoding="utf-8") as f:
           writer = csv.writer(f, lineterminator="\n")
           writer.writerow(header)
           writer.writerows(rows)
       return {"query": query_file, "query_sha256": hashlib.sha256(sql.encode()).hexdigest(),
               "file": str(out_file), "rows": len(rows)}
   
   
   def main():
       params = {"created_from": "2026-01-01", "created_before": "2026-07-01"}
       con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)
       OUT.mkdir(parents=True, exist_ok=True)
       files = [
           run(con, "queries/extract_tickets.sql", params, OUT / "tickets.csv"),
           run(con, "queries/extract_outcomes.sql", params, OUT / "outcomes.csv"),
       ]
       con.close()
       for f in files:
           print(f)
   
   
   if __name__ == "__main__":
       main()

   Ligne	Pourquoi
   cursor.description	Les noms des colonnes du résultat. Ils deviennent l’en-tête du CSV.
   lineterminator="\n"	Les mêmes fins de ligne sur chaque ordinateur rendent le fichier identique, octet par octet, sur macOS et Windows.
   hashlib.sha256(sql.encode()).hexdigest()	Le SHA-256 du texte de la requête, sous forme de 64 caractères hexadécimaux.
   OUT.mkdir(parents=True, exist_ok=True)	Crée data/extract/ s’il n’existe pas encore.

5. Exécutez-le à nouveau :

   Sortie

   {'query': 'queries/extract_tickets.sql', 'query_sha256': '28a0267e280062b0e02f9727cb5a95d2c8b60309e5fc992a578e44ba61b54338', 'file': 'data/extract/tickets.csv', 'rows': 1416}
   {'query': 'queries/extract_outcomes.sql', 'query_sha256': '533e9573c06975a463369aea503c4218c806640b99067d240607c1cea6fdc864', 'file': 'data/extract/outcomes.csv', 'rows': 1869}

   Vos SHA-256 sont identiques à ceux-ci seulement si vos fichiers de requête sont des copies exactes, commentaires et dernier saut de ligne compris. Si une valeur diffère, comparez les fichiers caractère par caractère. Le nombre de lignes doit être identique.

6. Remplacez tout le fichier par la version finale. Elle ajoute trois éléments : les dates comme options de ligne de commande, les faits de l’instantané et le manifeste. Les faits de l’instantané viennent de la base de données.

   scripts/extract.py

   """Extract tickets and outcomes from larkfield.db into CSV files, with a manifest."""
   
   import argparse
   import csv
   import hashlib
   import json
   import sqlite3
   from datetime import datetime, timezone
   from pathlib import Path
   
   OUT = Path("data/extract")
   
   
   def run(con, query_file, params, out_file):
       sql = Path(query_file).read_text(encoding="utf-8")
       cursor = con.execute(sql, params)
       header = [d[0] for d in cursor.description]
       rows = cursor.fetchall()
       with open(out_file, "w", newline="", encoding="utf-8") as f:
           writer = csv.writer(f, lineterminator="\n")
           writer.writerow(header)
           writer.writerows(rows)
       return {"query": query_file, "query_sha256": hashlib.sha256(sql.encode()).hexdigest(),
               "file": str(out_file), "rows": len(rows)}
   
   
   def main():
       parser = argparse.ArgumentParser()
       parser.add_argument("--created-from", default="2026-01-01")
       parser.add_argument("--created-before", default="2026-07-01")
       args = parser.parse_args()
       params = {"created_from": args.created_from, "created_before": args.created_before}
   
       con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)
       snapshot = dict(con.execute("SELECT name, value FROM snapshot").fetchall())
       total = con.execute("SELECT COUNT(*) FROM tickets").fetchone()[0]
       OUT.mkdir(parents=True, exist_ok=True)
       files = [
           run(con, "queries/extract_tickets.sql", params, OUT / "tickets.csv"),
           run(con, "queries/extract_outcomes.sql", params, OUT / "outcomes.csv"),
       ]
       con.close()
   
       manifest = {
           "extracted_at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
           "source": snapshot,
           "selection": {**params, "rule": "created_from <= created_at < created_before"},
           "ticket_rows_in_source": total,
           "ticket_rows_not_selected": total - files[0]["rows"],
           "files": files,
       }
       (OUT / "manifest.json").write_text(json.dumps(manifest, indent=2) + "\n", encoding="utf-8")
       for f in files:
           print(f"{f['file']}: {f['rows']} rows")
       print(f"Not selected: {manifest['ticket_rows_not_selected']} ticket row(s)")
   
   
   if __name__ == "__main__":
       main()

   Partie	Pourquoi
   argparse	--created-from et --created-before changent la sélection sans modifier le code. Les valeurs par défaut couvrent tout le premier semestre de 2026.
   SELECT name, value FROM snapshot	La base de données consigne sa propre source et le moment de l’exportation. dict(...) transforme les trois lignes en dictionnaire.
   total - files[0]["rows"]	Les lignes de billets exclues par la sélection. Un nombre différent de zéro doit avoir une raison.
   datetime.now(timezone.utc)	Le moment de l’extraction en UTC, pour qu’il ait le même sens dans tous les pays.

7. Exécutez la version finale :

   python3 scripts/extract.py

   Sortie

   data/extract/tickets.csv: 1416 rows
   data/extract/outcomes.csv: 1869 rows
   Not selected: 1 ticket row(s)

8. Ouvrez data/extract/manifest.json. Votre extracted_at correspond à votre propre moment d’exécution. Tout le reste est identique à l’exemple, sauf les deux valeurs query_sha256 si vos fichiers de requête diffèrent.

   data/extract/manifest.json

   {
     "extracted_at": "2026-10-07T03:08:46+00:00",
     "source": {
       "source": "Larkfield help-desk export (synthetic, Nextia Learning C04)",
       "exported_at": "2026-07-01 06:00:00",
       "data_version": "1.0"
     },
     "selection": {
       "created_from": "2026-01-01",
       "created_before": "2026-07-01",
       "rule": "created_from <= created_at < created_before"
     },
     "ticket_rows_in_source": 1417,
     "ticket_rows_not_selected": 1,
     "files": [
       {
         "query": "queries/extract_tickets.sql",
         "query_sha256": "28a0267e280062b0e02f9727cb5a95d2c8b60309e5fc992a578e44ba61b54338",
         "file": "data/extract/tickets.csv",
         "rows": 1416
       },
       {
         "query": "queries/extract_outcomes.sql",
         "query_sha256": "533e9573c06975a463369aea503c4218c806640b99067d240607c1cea6fdc864",
         "file": "data/extract/outcomes.csv",
         "rows": 1869
       }
     ]
   }

Le manifeste répond aux quatre questions de Priya. La source est l’exportation du 1er juillet 2026, version 1,0. La sélection a une règle et deux dates. Les empreintes des requêtes et le moment de l’extraction sont consignés. La ligne non sélectionnée est le billet de 2036. Les cellules vides des fichiers CSV sont des valeurs NULL dans la base de données. Le module 4 les charge avec soin.

À vous : extraire seulement mars

Changez une condition : extrayez seulement les billets de mars 2026. Prédisez d’abord le nombre de lignes de billets. Vous avez compté les billets de mars dans la dernière leçon.

1. Exécutez le script avec deux options :

   python3 scripts/extract.py --created-from 2026-03-01 --created-before 2026-04-01

2. Lisez la sortie et les valeurs selection et ticket_rows_not_selected dans le manifeste.

3. Exécutez à nouveau python3 scripts/extract.py sans options pour rétablir l’extraction complète avant le prochain module.

Afficher le résultat attendu

Sortie

data/extract/tickets.csv: 252 rows
data/extract/outcomes.csv: 330 rows
Not selected: 1165 ticket row(s)

252 lignes de billets, comme le compte de mars avec un intervalle semi-ouvert dans la dernière leçon. Le manifeste consigne "created_from": "2026-03-01" et "created_before": "2026-04-01", ainsi que 1 165 lignes non sélectionnées. Les deux valeurs query_sha256 ne changent pas, car les dates sont des paramètres, pas une partie du texte de la requête. Les fichiers de mars remplacent les fichiers précédents dans data/extract/. Sans le manifeste, personne ne pourrait savoir quelle sélection le dossier contient maintenant.

Erreur courante

Une requête avec une f-string échoue sur une apostrophe ou retourne toutes les lignes

Symptôme : Tomás construit une requête pour une équipe avec une f-string. Elle fonctionne pendant des semaines. Puis elle s’arrête avec une erreur pour une valeur. Pour une autre valeur, elle retourne les 1 417 lignes sans erreur.

Pour le voir, créez un fichier temporaire scripts/try_quote.py :

scripts/try_quote.py

import sqlite3

con = sqlite3.connect("file:larkfield.db?mode=ro", uri=True)

for team in ["returns", "customer's account", "returns' OR '1'='1"]:
    print("value:", team)
    safe = con.execute("SELECT COUNT(*) FROM tickets WHERE team = :team", {"team": team}).fetchone()[0]
    print("  parameter:", safe)
    try:
        unsafe = con.execute(f"SELECT COUNT(*) FROM tickets WHERE team = '{team}'").fetchone()[0]
        print("  f-string: ", unsafe)
    except sqlite3.Error as error:
        print("  f-string:  error:", type(error).__name__, error)

con.close()

Output of python3 scripts/try_quote.py

value: returns
  parameter: 327
  f-string:  327
value: customer's account
  parameter: 0
  f-string:  error: OperationalError near "s": syntax error
value: returns' OR '1'='1
  parameter: 0
  f-string:  1417

Cause : la f-string insère la valeur dans le texte SQL. L’apostrophe dans customer's termine la chaîne SQL trop tôt. SQLite lit donc s account' comme du SQL et échoue. La troisième valeur est pire : ses apostrophes la transforment en code SQL, team = 'returns' OR '1'='1', vrai pour chaque ligne. Des attaquants utilisent cette méthode, appelée injection SQL, pour lire ou modifier des données auxquelles ils ne doivent pas accéder.

Correction : utilisez un paramètre pour chaque valeur : WHERE team = :team, et passez {"team": team}. Avec un paramètre, les deux valeurs inhabituelles restent du texte : aucune équipe ne porte ces noms, donc le compte est 0. Supprimez scripts/try_quote.py quand vous avez terminé.

Dans le travail en IA : les valeurs viennent souvent de l’extérieur. Par exemple, une date d’un planificateur, un nom de client, le texte d’un billet ou la sortie d’un modèle de langage. Chacune peut contenir une apostrophe. Un paramètre garde chacune comme une valeur.

Vérifiez votre compréhension

Vous exécutez extract.py pour janvier, puis pour juin. Les valeurs query_sha256 des deux manifestes diffèrent-elles? Pourquoi?

Afficher la réponse

Non. Le script calcule le SHA-256 à partir du texte du fichier de requête, et les dates ne figurent pas dans ce texte. Ce sont des paramètres, passés séparément. La partie selection du manifeste montre les différentes dates. Supposons qu’une f-string insère les dates dans le texte de la requête. Chaque sélection aurait alors une empreinte différente, et vous ne pourriez pas voir si la logique de la requête a changé.

Vérifiez votre compréhension

Le manifeste indique ticket_rows_not_selected : 1. Pourquoi ce nombre figure-t-il dans le manifeste, et que devez-vous en faire?

Afficher la réponse

Il rend visibles les lignes exclues par la sélection. Une extraction qui exclut des lignes sans le signaler ressemble à une extraction qui n’en exclut aucune. Chaque ligne exclue doit avoir une raison : ici, T-10147 a un created_at en 2036, hors de l’intervalle. Si le nombre change lors d’une extraction ultérieure avec les mêmes dates, les données sources ont changé.

Exercice du module

La tâche prévue pour ce module : extraire un seul résultat le plus récent par billet, sans duplication accidentelle.

Exemple détaillé : d’où viennent les doublons

Mei commence avec trois billets de la dernière leçon. L’étape de fenêtrage numérote les événements de chaque billet, et le filtre rn = 1 garde une ligne par billet :

ticket_id	événements	dernier événement (rn = 1)	status
T-10120	2	O-00246	resolved
T-10135	1	O-00182	resolved
T-10136	3 (deux à la même seconde)	O-00229	resolved

Trois billets, trois lignes. Dans les vraies données, deux situations produisent plus de lignes que de billets, sans avertissement :

1. Les égalités. Un ORDER BY sans outcome_id donne 1 432 lignes avec RANK(), pour 1 399 billets.

2. Une jointure avec les lignes de billets répétées. Mei joint les derniers événements (1 399 lignes) à tickets pour garder seulement les billets de l’exportation :

   WITH ranked AS (
       SELECT ticket_id, outcome_id, recorded_at, status,
              ROW_NUMBER() OVER (
                  PARTITION BY ticket_id
                  ORDER BY recorded_at DESC, outcome_id DESC
              ) AS rn
       FROM outcomes
   ),
   latest AS (
       SELECT * FROM ranked WHERE rn = 1
   )
   SELECT COUNT(*) AS result_rows, COUNT(DISTINCT t.ticket_id) AS tickets
   FROM tickets AS t
   JOIN latest AS l
       ON l.ticket_id = t.ticket_id;

   result_rows	tickets
   1409	1395

   Les 14 billets présents deux fois dans tickets correspondent chacun deux fois à leur dernier événement. La jointure a aussi retiré les 4 événements de billets absents de tickets (1 399 → 1 395 billets).

La correction consiste à joindre une liste qui contient une ligne par billet : SELECT DISTINCT ticket_id FROM tickets. La vérification devient alors : lignes du résultat = billets distincts = billets qui ont des événements. Dans cet instantané, ce nombre est 1 395 : 1 403 billets distincts, moins les 8 billets sans événement.

Votre tâche

Écrivez une requête qui retourne une ligne par billet de l’exportation ayant au moins un événement de résultat. Retournez quatre colonnes du dernier événement : ticket_id, outcome_id, recorded_at et status. Départagez les égalités avec outcome_id.

Exécutez du SQL dans votre navigateur

Exercice du module : un seul résultat le plus récent par billet

L’encadré affiche les 50 premières lignes et le nombre total. Le total doit être 1 395. La vérification compare chaque ligne à la nôtre. Un mauvais choix en cas d’égalité (par exemple, reopened (rouvert) pour T-10136) échoue donc même si le compte est correct.

Requête SQL (vous pouvez la modifier)-- One latest outcome event per ticket in the export. -- Columns: ticket_id, outcome_id, recorded_at, status. -- Write your query here. SELECT ticket_id, outcome_id, recorded_at, status FROM outcomes LIMIT 5;

Faites ensuite la même chose sur votre ordinateur et enregistrez aussi les vérifications :

1. Enregistrez votre requête dans queries/35-latest-outcome.sql, puis exécutez python3 q.py queries/35-latest-outcome.sql. La dernière ligne doit être 1395 rows (showing 20; add --all to see every row) (1 395 lignes, dont 20 affichées; ajoutez –all pour toutes les voir).
2. Créez queries/36-latest-outcome-check.sql. Ce fichier affiche trois nombres : les lignes du résultat, les billets distincts du résultat et les billets de l’exportation ayant des événements.
3. Exécutez-le. Les trois nombres doivent être égaux.

Afficher la réponse modèle

queries/35-latest-outcome.sql

-- One latest outcome event per ticket in the export.
WITH unique_tickets AS (
    SELECT DISTINCT ticket_id
    FROM tickets
),
ranked AS (
    SELECT
        o.ticket_id,
        o.outcome_id,
        o.recorded_at,
        o.status,
        ROW_NUMBER() OVER (
            PARTITION BY o.ticket_id
            ORDER BY o.recorded_at DESC, o.outcome_id DESC
        ) AS rn
    FROM outcomes AS o
    JOIN unique_tickets AS u
        ON u.ticket_id = o.ticket_id
)
SELECT ticket_id, outcome_id, recorded_at, status
FROM ranked
WHERE rn = 1
ORDER BY ticket_id;

Output (first and last lines)

ticket_id  outcome_id  recorded_at          status
---------  ----------  -------------------  --------
T-10001    O-00005     2026-01-06 17:58:45  resolved
T-10002    O-00001     2026-01-05 15:29:21  resolved
T-10003    O-00075     2026-01-13 18:56:26  resolved
...

1395 rows (showing 20; add --all to see every row)

queries/36-latest-outcome-check.sql

-- Checks for 35-latest-outcome.sql: all three numbers must be equal.
WITH unique_tickets AS (
    SELECT DISTINCT ticket_id
    FROM tickets
),
ranked AS (
    SELECT
        o.ticket_id,
        o.outcome_id,
        o.recorded_at,
        o.status,
        ROW_NUMBER() OVER (
            PARTITION BY o.ticket_id
            ORDER BY o.recorded_at DESC, o.outcome_id DESC
        ) AS rn
    FROM outcomes AS o
    JOIN unique_tickets AS u
        ON u.ticket_id = o.ticket_id
),
latest AS (
    SELECT ticket_id, outcome_id, recorded_at, status
    FROM ranked
    WHERE rn = 1
)
SELECT
    (SELECT COUNT(*) FROM latest) AS latest_rows,
    (SELECT COUNT(DISTINCT ticket_id) FROM latest) AS distinct_tickets,
    (SELECT COUNT(*) FROM unique_tickets
     WHERE ticket_id IN (SELECT ticket_id FROM outcomes)) AS tickets_with_events;

Sortie

latest_rows  distinct_tickets  tickets_with_events
-----------  ----------------  -------------------
1395         1395              1395

1 row

Sur les 1 395 derniers événements, 1 391 sont resolved (résolus), 3 sont escalated (transmis au niveau supérieur) et 1 est reopened. La jointure avec unique_tickets exclut les 4 événements de billets manquants. DISTINCT empêche les 14 lignes de billets répétées de doubler les événements. outcome_id DESC départage les 33 égalités : l’événement consigné en dernier l’emporte.

Vérification du module

Vérifiez votre compréhension

Rappel : quelle est la différence entre une CTE et une fonction de fenêtre?

Afficher la réponse

Une CTE (WITH name AS (...)) nomme un résultat intermédiaire pour permettre d’écrire et de vérifier une requête par étapes. Une fonction de fenêtre (... OVER (PARTITION BY ... ORDER BY ...)) ajoute une valeur à chaque ligne à partir d’un groupe de lignes liées. Elle conserve toutes les lignes. Les deux se combinent : une fonction de fenêtre calcule rn dans une CTE, puis l’étape suivante filtre avec rn = 1.

Vérifiez votre compréhension

Prédiction : dans votre requête du dernier résultat, vous remplacez ROW_NUMBER() par RANK() et retirez outcome_id DESC. Combien de lignes la requête renvoie-t-elle, et quelle vérification détecte le problème?

Afficher la réponse

Plus de 1 395 lignes. Pour 33 billets de soutien, plusieurs événements partagent l’heure la plus récente. RANK() donne donc deux lignes de rang 1 pour chacun de ces billets. Pour les billets de l’exportation, cela donne 1 428 lignes. La vérification « lignes du résultat = billets distincts » détecte le problème : 1 428 lignes, mais 1 395 billets distincts.

Vérifiez votre compréhension

Vérification finale : expliquez comment votre requête départage les égalités. Montrez ensuite que votre extraction donne la même sélection sur l'instantané fixe.

Afficher la réponse

Égalités. Quand deux événements d’un billet ont la même valeur recorded_at, la requête les trie selon outcome_id DESC. Le service de soutien attribue les identifiants de résultat dans l’ordre d’enregistrement. Le dernier événement enregistré l’emporte donc. Pour T-10136, resolved (résolu, O-00229) l’emporte sur reopened (rouvert, O-00228), tous deux enregistrés le 28 janvier 2026 à 19 h 46 min 52 s. Sans cette règle, RANK() conserve les deux lignes et ROW_NUMBER() conserve une ligne choisie par la base de données.

Reproduire. Exécutez python3 scripts/extract.py deux fois. Comparez les deux manifestes : extracted_at change, et tout le reste demeure identique. Comparez ensuite les empreintes SHA-256 des fichiers CSV avec les nôtres :

macOS

shasum -a 256 data/extract/tickets.csv data/extract/outcomes.csv

Linux

sha256sum data/extract/tickets.csv data/extract/outcomes.csv

Windows

Get-FileHash data\extract\tickets.csv, data\extract\outcomes.csv

Expected (macOS)

96786af9e2ebb2492219867ac302f57fabdefb4c53fb60a1071d9412e70e6f39  data/extract/tickets.csv
60c828f37db48a01ac6f003f5879a2c3ed5254f2c0e12816f814ed661c4067b4  data/extract/outcomes.csv

PowerShell affiche les mêmes valeurs en majuscules. Les comptes doivent être de 1 416 lignes de billets et de 1 869 événements de résultat. Ils proviennent de l’instantané exporté à 2026-07-01 06:00:00, version 1.0. Les empreintes des fichiers dépendent seulement de l’instantané, de la sélection et des résultats des requêtes. Elles correspondent donc aux nôtres même si les commentaires de vos requêtes diffèrent.

Récapitulatif

Dans ce module, vous avez rendu vos requêtes lisibles, correctes dans le temps et reproductibles :

• Les CTE divisent une requête en étapes nommées. Comptez les lignes à chaque étape et sachez quel compte ne doit pas changer.
• Dans SQLite, les heures sont du texte. Vérifiez d’abord MIN et MAX, puis filtrez avec des intervalles semi-ouverts.
• Les fonctions de fenêtre numérotent, classent et comparent les lignes dans les partitions. Pour obtenir le « dernier résultat par billet », départagez les égalités avec outcome_id. Vérifiez que le nombre de lignes égale le nombre de billets distincts.
• Les paramètres nommés gardent les valeurs hors du texte SQL : pas de guillemets mal fermés, pas d’injection. L’empreinte de la requête reste identique pour chaque sélection.
• scripts/extract.py écrit l’extraction et un manifeste : l’instantané, la sélection, les valeurs SHA-256 des requêtes et les comptes de lignes. Exécutez-le deux fois : les fichiers sont identiques.

Ensuite, le module 4 profile les données extraites avant toute modification. Il examine les valeurs manquantes, les lignes répétées, les catégories mal orthographiées et les valeurs dans la mauvaise unité.

Leçon suivante : Profiler avant de modifier