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 :

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 ?