Le temps et le classement

Ce que vous saurez faire

Sélectionnez les billets selon leur date sans perdre de lignes aux limites d’un intervalle. Puis choisissez exactement un dernier événement de résultat par billet, même si deux événements ont la même heure.

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, dont le cas d’échec lié aux égalités. 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 et le tableau des partitions 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 55 minutes

Ce que vous devez savoir

• Comment écrire une requête en étapes CTE et compter les lignes de chaque étape, dans Des requêtes lisibles : examinez chaque étape.
• Les 14 lignes de billets répétées et SELECT DISTINCT *, dans Les concepts relationnels : vérifier ticket_id.
• Comment une jointure avec outcomes répète un billet une fois par événement, dans Joindre des tables : la jointure ajoute des lignes.

Ce qu’il vous faut sur votre ordinateur (seulement pour le parcours 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é, suivez cette section à nouveau, ou réinitialisez 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 33-customer-history.sql.
• Un terminal ouvert dans ce dossier. Cette leçon utilise python3, comme sur macOS et Linux. Sous 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. Les fonctions de fenêtrage exigent SQLite 3.25 ou une version ultérieure. Le carnet a été testé dans Jupyter.

Les dates sont du texte dans SQLite

SQLite n’a pas de type date. L’exportation de Larkfield stocke chaque date et heure sous forme de texte dans un format fixe, YYYY-MM-DD HH:MM:SS, par exemple 2026-01-05 07:30:24. Les parties suivent cet ordre : année, mois, jour, heure, minute, seconde, de la plus grande unité à la plus petite. L’ordre alphabétique du texte suit donc aussi l’ordre chronologique, et <, > et MAX fonctionnent sur ces valeurs.

SQLite propose des fonctions qui lisent ce texte :

Expression	Résultat pour T-10001 (2026-01-05 07:30:24)	Ce qu’elle fait
date(created_at)	2026-01-05	Garde seulement le jour.
strftime('%Y-%m', created_at)	2026-01	Formate la date et l’heure. %Y est l’année, %m le mois, %H l’heure.
datetime(created_at, '+72 hours')	2026-01-08 07:30:24	Ajoute du temps. '-1 day' et '+30 minutes' fonctionnent aussi.
julianday(created_at)	2461045.81…	Un numéro de jour avec une fraction. La différence entre deux numéros de jour donne un nombre de jours.

Dans PostgreSQL

Les dates et heures ont un vrai type, timestamp. Écrivez created_at::date, date_trunc('month', created_at) ou to_char(created_at, 'YYYY-MM'), et created_at + INTERVAL '72 hours'. La différence entre deux horodatages est un intervalle : EXTRACT(EPOCH FROM b - a) / 86400 donne des jours. Ce code n’est pas exécuté dans ce cours.

Le modèle de ce cours prédit un transfert au niveau supérieur dans les 72 heures suivant la création d’un billet. datetime donne cette échéance. Le billet T-10008 a deux événements de résultat :

Exécutez du SQL dans votre navigateur

Essayez : une échéance de 72 heures

Vous voyez deux lignes. L’échéance est 2026-01-09 08:14:45. Le transfert est survenu environ 4 heures après la création, donc within_72h vaut 1 (vrai). SQLite affiche vrai comme 1 et faux comme 0. Remplacez '+72 hours' par '+2 hours' et exécutez à nouveau : les deux événements dépassent maintenant l’échéance.

Requête SQL (vous pouvez la modifier)SELECT t.ticket_id, t.created_at, datetime(t.created_at, '+72 hours') AS deadline, o.recorded_at, o.status, o.recorded_at <= datetime(t.created_at, '+72 hours') AS within_72h FROM tickets AS t JOIN outcomes AS o ON o.ticket_id = t.ticket_id WHERE t.ticket_id = 'T-10008';

Vérifiez d’abord l’intervalle

Avant de filtrer par date, examinez la plus petite et la plus grande valeur. Mei exécute ceci sur l’exportation :

SELECT MIN(created_at) AS first_ticket, MAX(created_at) AS last_ticket
FROM tickets;

first_ticket	last_ticket
2026-01-05 07:30:24	2036-01-24 21:27:43

L’exportation date du 1 juillet 2026, donc un billet de 2036 ne peut pas exister. Il s’agit de T-10147, un billet de garantie dont l’année a été mal saisie. MAX ne vous avertit pas. Toute requête « dernier billet » ou « 30 derniers jours » qui utilise MAX(created_at) est maintenant décalée de dix ans. Mei ne modifie pas l’exportation. Elle fait ses sélections avec des dates de début et de fin explicites. Le module 4 détermine quoi faire de cette ligne.

Intervalles semi-ouverts

Grace veut les billets de mars. Tomás écrit :

SELECT COUNT(*) FROM tickets
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31';

Le résultat est 242. Mei écrit :

SELECT COUNT(*) FROM tickets
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

Le résultat est 252. Il manque dix lignes de billets au compte de Tomás : tous les billets du 31 mars. BETWEEN a AND b signifie >= a AND <= b. Comme texte, '2026-03-31 08:12:00' vient après '2026-03-31', car il est plus long et commence par les mêmes caractères. Tous les billets du 31 mars après minuit sont donc hors de l’intervalle.

La forme utilisée par Mei est un intervalle semi-ouvert : elle inclut le début et exclut la fin. La fin est le premier instant que vous ne voulez pas, ici le 1 avril. Cette forme a trois avantages :

• Elle fonctionne pour toutes les heures, sans astuces avec 23:59:59.
• Les mois se suivent sans trou ni chevauchement : mars finit là où avril commence.
• La même forme fonctionne dans PostgreSQL avec une vraie colonne timestamp.

Exécutez du SQL dans votre navigateur

À vous : les billets de mai

Exécutez d’abord la requête : 228 lignes. Changez le filtre pour un intervalle semi-ouvert. La vérification réussit alors. Prévoyez la différence avant d’exécuter la requête : combien de billets ont été créés le 31 mai?

Requête SQL (vous pouvez la modifier)-- Count the ticket rows created in May 2026. SELECT COUNT(*) AS may_rows FROM tickets WHERE created_at BETWEEN '2026-05-01' AND '2026-05-31';

Afficher l’exemple de réponse

SELECT COUNT(*) AS may_rows
FROM tickets
WHERE created_at >= '2026-05-01'
  AND created_at <  '2026-06-01';

Le résultat est de 232 lignes. Les 4 lignes supplémentaires sont les billets du 31 mai. (232 compte les lignes. Les lignes de billets répétées sont encore dans la table, donc mai compte 228 billets distincts.)

Fonctions de fenêtrage

Un GROUP BY transforme plusieurs lignes en une ligne par groupe. Parfois, vous voulez garder chaque ligne et ajouter une valeur qui dépend des autres lignes de son groupe. Exemples : « c’est le troisième billet du client » ou « c’est le dernier événement du billet ».

Une fonction de fenêtrage fait cela. Elle calcule une valeur pour chaque ligne à partir d’un groupe de lignes liées, la fenêtre, et garde chaque ligne. Vous l’écrivez avec OVER (...) :

ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY recorded_at DESC, outcome_id DESC)

• PARTITION BY ticket_id sépare les lignes en partitions, une par billet. Chaque partition est numérotée séparément.
• ORDER BY recorded_at DESC, outcome_id DESC ordonne les lignes dans chaque partition : l’événement le plus récent en premier.
• ROW_NUMBER() donne 1, 2, 3, … dans cet ordre. Chaque ligne reçoit un numéro différent, même si deux lignes sont égales dans le ORDER BY.

Trois fonctions suffisent pour ce cours :

Fonction	Donne	Égalités (valeurs égales dans ORDER BY)
ROW_NUMBER()	1, 2, 3, …	Des numéros différents. La base de données décide quelle ligne à égalité vient en premier, sauf si vous ajoutez une colonne pour les départager.
RANK()	1, 2, 2, 4, …	Le même numéro. Après une égalité, elle saute des numéros.
LAG(column)	La valeur de la colonne dans la ligne précédente de la partition	NULL pour la première ligne.

La fenêtre partitionnée

Voici les événements de résultat de trois billets, numérotés avec le ROW_NUMBER() ci-dessus. La première colonne montre les partitions : une cellule couvre tous les événements d’un billet. La ligne surlignée dans chaque partition a rn = 1 : c’est le dernier événement, la ligne à garder.

Partition (ticket_id)	outcome_id	recorded_at	status	rn	RANK selon l’heure seulement
T-10120	O-00246	2026-01-29 19:41:19	resolved (résolu)	1 (gardé)	1
	O-00155	2026-01-21 21:26:23	escalated (transféré au niveau supérieur)	2	2
T-10135	O-00182	2026-01-24 05:13:30	resolved	1 (gardé)	1
T-10136	O-00229	2026-01-28 19:46:52	resolved	1 (gardé)	1
	O-00228	2026-01-28 19:46:52	reopened (rouvert)	2	1
	O-00183	2026-01-24 08:26:29	resolved	3	3

Lisez une partition à la fois :

• T-10120 a été transféré au niveau supérieur, puis résolu environ 8 jours plus tard. L’événement de résolution est le plus récent.
• T-10135 a un seul événement. Il porte le numéro 1 dans sa propre partition.
• T-10136 a été résolu le 24 janvier. Le 28 janvier, le client a écrit à nouveau : le billet a été rouvert et résolu dans la même seconde. recorded_at ne permet pas de savoir lequel est venu en premier. outcome_id le permet : le service de soutien attribue les identifiants dans l’ordre d’enregistrement. O-00229 a donc été enregistré après O-00228. Le dernier événement est la résolution.

La dernière colonne montre RANK() OVER (PARTITION BY ticket_id ORDER BY recorded_at DESC), sans outcome_id. Pour T-10136, elle donne deux lignes de rang 1. Un filtre WHERE rank = 1 garde les deux, et le billet apparaît deux fois.

Exemple détaillé : le dernier résultat de chaque billet

Priya, la scientifique des données, demande à Mei une ligne par billet avec son dernier statut. Une fonction de fenêtrage ne peut pas aller dans une clause WHERE, car WHERE s’exécute avant le calcul de la fenêtre. Mei utilise donc deux étapes CTE : d’abord numéroter les lignes, puis garder le numéro 1.

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 ticket_id, outcome_id, recorded_at, status
    FROM ranked
    WHERE rn = 1
)
SELECT
    COUNT(*) AS latest_rows,
    COUNT(DISTINCT ticket_id) AS tickets
FROM latest;

latest_rows	tickets
1399	1399

Les deux nombres sont égaux : une ligne par billet. outcomes contient des événements pour 1 399 identifiants de billets différents. Quatre d’entre eux (T-9990 à T-9993) ne sont pas dans tickets. L’extraction de la prochaine leçon les exclut.

Puis Mei vérifie le traitement des égalités. Elle remplace la fonction de fenêtrage par RANK() et omet outcome_id :

Classement	Lignes portant le numéro 1	Billets
ROW_NUMBER(), ORDER BY recorded_at DESC, outcome_id DESC	1 399	1 399
RANK(), ORDER BY recorded_at DESC	1 432	1 399

33 billets ont plus d’un événement à leur heure la plus récente. Huit sont comme T-10136 : rouverts et résolus dans la même seconde. Les 25 autres sont des nouvelles tentatives : le même événement enregistré deux fois, avec un nouvel outcome_id. RANK() montre toutes les égalités, ce qui aide à les trouver. Pour garder exactement une ligne, utilisez ROW_NUMBER() et une colonne qui départage les égalités.

Exécutez du SQL dans votre navigateur

Essayez : ROW_NUMBER et RANK côte à côte

Vous voyez les six lignes du tableau ci-dessus. Regardez T-10136 : rn vaut 1 et 2, rank_by_time vaut 1 et 1. Puis ajoutez 'T-10115' à la liste. Son transfert au niveau supérieur a été enregistré deux fois (O-00143 et O-00144) : une nouvelle tentative.

Requête SQL (vous pouvez la modifier)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, RANK() OVER ( PARTITION BY ticket_id ORDER BY recorded_at DESC ) AS rank_by_time FROM outcomes ) SELECT ticket_id, outcome_id, recorded_at, status, rn, rank_by_time FROM ranked WHERE ticket_id IN ('T-10120', 'T-10135', 'T-10136') ORDER BY ticket_id, rn;

Ordonner les billets de chaque client

Les mêmes outils répondent à une question sur les clients. Omar demande : « Les clients reviennent-ils peu après un billet? » Pour chaque client, Mei numérote les billets en ordre chronologique et calcule les jours depuis le billet précédent avec LAG.

1. Créez le fichier queries/33-customer-history.sql avec cette requête :

   queries/33-customer-history.sql

   -- Each customer's tickets in time order, with the days since the previous ticket.
   WITH unique_tickets AS (
       SELECT DISTINCT *
       FROM tickets
   )
   SELECT
       customer_id,
       ticket_id,
       created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS nth_ticket,
       ROUND(
           julianday(created_at)
           - julianday(LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id)),
           1
       ) AS days_since_previous
   FROM unique_tickets
   WHERE customer_id = 'C-0003'
   ORDER BY customer_id, nth_ticket;

2. Prévoyez la valeur de days_since_previous dans la première ligne.

3. Exécutez la requête :

   python3 q.py queries/33-customer-history.sql

   Sortie

   customer_id  ticket_id  created_at           nth_ticket  days_since_previous
   -----------  ---------  -------------------  ----------  -------------------
   C-0003       T-10047    2026-01-10 12:00:09  1           NULL
   C-0003       T-10391    2026-02-24 13:05:53  2           45.0
   C-0003       T-10414    2026-02-26 17:40:38  3           2.2
   C-0003       T-10598    2026-03-20 13:35:27  4           21.8
   C-0003       T-10877    2026-04-24 08:18:10  5           34.8
   
   5 rows

4. Vérifiez la première ligne. LAG vaut NULL, car le premier billet n’a pas de billet précédent, et la différence avec NULL vaut NULL.

5. Vérifiez une différence à la main. Du 10 janvier à 12 h au 24 février à 13 h 05, il y a 45 jours et environ une heure : 45,0.

6. Supprimez la ligne WHERE customer_id = 'C-0003' et exécutez la requête à nouveau. Chaque client a maintenant une partition, et nth_ticket commence à 1 pour chacun.

Deux détails de la requête comptent :

• ORDER BY created_at, ticket_id dans OVER. Si un client ouvre deux billets dans la même seconde, ticket_id décide de l’ordre. Sans cette colonne, l’ordre de ces deux billets pourrait changer d’une exécution à l’autre.
• WHERE s’exécute avant la fenêtre. Un filtre sur customer_id est sûr ici, car chaque client forme une partition complète. Un filtre sur une date supprimerait les billets plus anciens. Le « billet précédent » serait alors un autre billet.

À vous : un client avec un billet répété

Le client C-0052 a un billet qui apparaît deux fois dans l’exportation. La requête ci-dessous lit tickets directement. Exécutez-la et examinez la deuxième ligne.

Exécutez du SQL dans votre navigateur

À vous : une ligne par billet

La deuxième ligne indique que C-0052 est revenu après 0 jour, et le client semble avoir 5 billets. Modifiez la requête pour compter chaque billet une seule fois. Gardez les quatre colonnes et leur ordre. La vérification réussit quand le résultat contient 4 lignes.

Requête SQL (vous pouvez la modifier)SELECT ticket_id, created_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS nth_ticket, ROUND( julianday(created_at) - julianday(LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id)), 1 ) AS days_since_previous FROM tickets WHERE customer_id = 'C-0052' ORDER BY nth_ticket;

Afficher l’exemple de réponse

WITH unique_tickets AS (
    SELECT DISTINCT *
    FROM tickets
)
SELECT
    ticket_id,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id) AS nth_ticket,
    ROUND(
        julianday(created_at)
        - julianday(LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at, ticket_id)),
        1
    ) AS days_since_previous
FROM unique_tickets
WHERE customer_id = 'C-0052'
ORDER BY nth_ticket;

ticket_id	created_at	nth_ticket	days_since_previous
T-10609	2026-03-22 07:00:30	1	NULL
T-10924	2026-04-30 06:35:02	2	39
T-10980	2026-05-08 05:08:09	3	7.9
T-11109	2026-05-25 19:41:30	4	17.6

T-10609 figurait deux fois dans l’exportation. La copie est devenue le « deuxième billet » du client, 0 jour après le premier. Une caractéristique comme « billets des 90 derniers jours » le compterait deux fois.

Erreur courante

Les événements à égalité font paraître ouverts des billets résolus

Symptôme : Tomás écrit la requête du dernier résultat avec ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY recorded_at DESC), sans outcome_id. La requête renvoie 1 399 lignes, une par billet, donc la vérification du compte réussit. Mais son résumé des statuts indique que 9 billets finissent avec reopened (rouvert). Celui de Mei en indique 1. Grace vérifie deux des billets dans le système de soutien : ils sont résolus.

Cause : pour 8 billets, les événements reopened et resolved (résolu) ont le même recorded_at. Avec seulement recorded_at dans le ORDER BY, les deux lignes sont à égalité, et ROW_NUMBER() peut les numéroter dans n’importe quel ordre. Dans la version de SQLite de ce cours, il a placé reopened en premier pour les 8 billets. Une autre version de la base de données, ou la même requête après un changement d’index, peut choisir autrement. Le résultat n’est pas erroné de façon évidente : il n’est pas reproductible.

Diagnostic : comptez les égalités. Utilisez RANK() avec le même ORDER BY, et comparez les lignes de rang 1 au nombre de billets : 1 432 lignes pour 1 399 billets. Puis examinez quelques billets à égalité, par exemple T-10136.

Correction : ajoutez une colonne qui rend l’ordre unique et qui a un sens : ORDER BY recorded_at DESC, outcome_id DESC. outcome_id suit l’ordre d’enregistrement, donc le dernier événement enregistré est choisi. Notez cette règle, car Priya doit la connaître.

Dans le travail en IA : le dernier statut devient souvent la cible, la valeur qu’un modèle apprend à prédire. Les égalités départagées arbitrairement donnent des cibles erronées à certaines lignes. Une nouvelle exécution de la même requête peut aussi donner des données d’entraînement différentes.

Vérifiez votre compréhension

BETWEEN '2026-06-01' AND '2026-06-30' sélectionne les billets de juin. Quels billets manque-t-il, et comment écrire le filtre à la place?

Afficher la réponse

Il manque tous les billets créés le 30 juin après minuit, car '2026-06-30 20:04:36' est supérieur à '2026-06-30' comme texte. Écrivez un intervalle semi-ouvert : created_at >= '2026-06-01' AND created_at < '2026-07-01'. Dans cette exportation, la différence est de 12 lignes : les billets du 30 juin.

Vérifiez votre compréhension

Pour un billet dont les deux derniers événements ont le même recorded_at, que renvoient ROW_NUMBER() et RANK() si ORDER BY contient seulement recorded_at DESC?

Afficher la réponse

RANK() donne aux deux lignes le numéro 1, donc un filtre sur 1 garde deux lignes pour le billet. ROW_NUMBER() donne 1 et 2, donc un filtre garde une ligne. Mais la base de données choisit laquelle, et elle peut choisir autrement une autre fois. Ajoutez outcome_id DESC au ORDER BY pour rendre le choix fixe et correct.

Vérifiez votre compréhension

Pourquoi ne pouvez-vous pas écrire WHERE ROW_NUMBER() OVER (...) = 1 directement?

Afficher la réponse

La base de données exécute WHERE avant de calculer les fonctions de fenêtrage, donc les numéros de lignes n’existent pas encore. Calculez le numéro dans une étape (une CTE), puis filtrez sur ce numéro dans l’étape suivante : WHERE rn = 1.

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

L’étude de cas de la boutique en ligne utilise LAG pour trouver où deux copies des données se chevauchent, et ROW_NUMBER avec de vraies égalités.

De bout en boutPlanifier le rééquilibrage des vélos pendant la nuitÉtude de cas

L’étude de cas des vélos utilise LAG pour chaque vélo afin de trouver des déplacements qu’aucun cycliste n’a faits.

Récapitulatif

• Dans SQLite, les dates et heures sont du texte au format YYYY-MM-DD HH:MM:SS. date(), strftime(), datetime(x, '+72 hours') et julianday() les lisent et les modifient.
• Vérifiez MIN et MAX avant de vous fier à une colonne temporelle : ici, MAX(created_at) est un billet de 2036.
• Utilisez des intervalles semi-ouverts : >= start AND < end. BETWEEN perd le dernier jour.
• Une fonction de fenêtrage ajoute à chaque ligne une valeur calculée à partir de sa partition et garde chaque ligne. ROW_NUMBER() numérote les lignes, RANK() montre les égalités, LAG() lit la ligne précédente.
• Pour le « dernier événement par billet », ordonnez selon l’heure et une colonne qui départage les égalités : ORDER BY recorded_at DESC, outcome_id DESC. Puis vérifiez que lignes = billets distincts.

Ensuite, vous exécuterez ces requêtes depuis Python avec des paramètres et noterez votre sélection, pour que chacun puisse répéter l’extraction.

Leçon suivante : Une extraction reproductible