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 ?
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
id user_id finished_at
-- ------- -----------
3 345 2023-07-13
2 234 2023-09-16
4 123 2023-10-05
| Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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.
| Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 | 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.
| Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 | 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 [/c]NULL[/c], alors nous avons la ligne dont a.finished_at est le plus élevé.
Pas simple à expliquer celui-ci
| Code sql : | Sélectionner tout |
1 2 3 4 5 6 | 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.
| Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 | 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.
| Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 | 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 ?