[SQL] Obtenir les dernières lignes -- Cinq (5) méthodes

Un billet blog de Séb

Un problème récurrent quand on requête une base de données :

Comment obtenir la ligne la plus récente de chaque groupe ?

Le problème n'est pas seulement d'obtenir la date en question, mais l'ensemble des données relatives à la ligne la plus récente.

Pour répondre à ce problème il faut :

1. Identifier la colonne déterminant la notion temporelle
Pour cela on aura généralement affaire à une colonne DATETIME.
Attention, se référer à un ID, même auto-incrémenté serait une très mauvaise idée.

2. Identifier la ou les colonnes déterminant le groupe
Pour cela on aura souvent affaire à un ID utilisateur, un ID catégorie, etc.

Il existe différentes méthodes que j'illustrerai ci-dessous. N'hésitez pas à ajouter un EXPLAIN ANALYZE avant le SELECT pour comprendre le schéma d'exécution.

Prenons l'exemple suivant :
-- Nous souhaitons identifier la dernière action de chaque utilisateur d'une application
-- Un utilisateur donné a 0 ou 1 action par jour

Jeu de données :

action id user_id finished_at
-- ------- -----------
1 123 2023-09-30
2 234 2023-09-16
3 345 2023-07-13
4 123 2023-10-05
5 345 2023-06-20
6 345 2023-03-01

Résultat attendu :

id user_id finished_at
-- ------- -----------
3 345 2023-07-13
2 234 2023-09-16
4 123 2023-10-05

DDL MySQL :

CREATE TABLE action (
id INTEGER UNSIGNED PRIMARY KEY,
user_id INTEGER UNSIGNED NOT NULL,
finished_at DATE NOT NULL,
CONSTRAINT UNIQUE KEY (finished_at, user_id) COMMENT '1 seule action terminée par jour par utilisateur'
);

INSERT INTO action (id, user_id, finished_at)
VALUES
(1, 123, DATE'2023-09-30'),
(2, 234, DATE'2023-09-16'),
(3, 345, DATE'2023-07-13'),
(4, 123, DATE'2023-10-05'),
(5, 345, DATE'2023-06-20'),
(6, 345, DATE'2023-03-01')
;

Méthode 1 -- Agrégat dans une sous-requête du prédicat

On cherche la dernière date pour chaque utilisateur, et on utilise le résultat dans un WHERE.

SELECT ALL id, user_id, finished_at
FROM action
WHERE (user_id, finished_at) IN (
SELECT ALL user_id, MAX(finished_at)
FROM action
GROUP BY user_id
);

Méthode 2 -- Agrégat dans une sous-requête de jointure

On cherche la dernière date pour chaque utilisateur, et on utilise le résultat dans un INNER JOIN.

SELECT ALL a.id, a.user_id, a.finished_at
FROM action AS a
INNER JOIN (
SELECT ALL user_id, MAX(finished_at) AS finished_at
FROM action
GROUP BY user_id
) AS la ON (a.user_id, a.finished_at) = (la.user_id, la.finished_at);

Méthode 3 -- Jointure externe

On cherche dans a les lignes dont la.finished_at est plus grand que a.finished_at.
Si la.id est NULL, alors nous avons la ligne dont a.finished_at est le plus élevé.
Pas simple à expliquer celui-ci :aie:

SELECT ALL a.id, a.user_id, a.finished_at
FROM action AS a
LEFT OUTER JOIN action AS la ON TRUE
AND a.user_id = la.user_id
AND la.finished_at > a.finished_at
WHERE la.id IS NULL;

Méthode 4 -- Test d'existence

Pour chaque utilisateur, on cherche les lignes n'ayant pas de date de fin plus grande.
Ainsi on obtient bien la dernière ligne de chaque utilisateur.

SELECT ALL id, user_id, finished_at
FROM action AS a
WHERE NOT EXISTS (
SELECT ALL *
FROM action
WHERE TRUE
AND user_id = a.user_id
AND finished_at > a.finished_at
);

Méthode 5 -- Fonction fenêtrée

Il existe différentes fonctions fenêtrées pouvant répondre au besoin.
ROW_NUMBER() est la plus simple à utiliser, mais peut-être pas la plus performante ici.
Les fonctions fenêtrées étant exécutées après WHERE et HAVING, elles doivent souvent être utilisées dans une sous-requêtes ou un CTE pour être exploitables.

SELECT ALL id, user_id, finished_at
FROM (
SELECT ALL
id, user_id, finished_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY finished_at DESC) AS i
FROM action
) AS a
WHERE i = 1;

Connaissez-vous d'autres méthodes ?