[Actualité] [SQL] Obtenir les dernières lignes -- 5 méthodes
par
, 30/09/2023 à 15h30 (9225 Affichages)
Un problème récurrent quand on requête une base de données :
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.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 :
Résultat attendu :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
DDL MySQL :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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 ?