IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Séb.

[Actualité] [SQL] Obtenir les dernières lignes -- 5 méthodes

Note : 3 votes pour une moyenne de 3,00.
par , 30/09/2023 à 15h30 (8304 Affichages)
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 ?

Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog Viadeo Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog Twitter Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog Google Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog Facebook Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog Digg Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog Delicious Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog MySpace Envoyer le billet « [SQL] Obtenir les dernières lignes -- 5 méthodes » dans le blog Yahoo

Mis à jour 15/03/2024 à 09h08 par Malick (Ajout balises code)

Tags: mysql, sql
Catégories
Programmation

Commentaires

  1. Avatar de Waldar
    • |
    • permalink
    Référence croisée avec le post d'escartefigue car c'est le même sujet :
    https://www.developpez.net/forums/bl...cente-critere/
  2. Avatar de Séb.
    • |
    • permalink
    Citation Envoyé par Waldar
    Référence croisée avec le post d'escartefigue car c'est le même sujet :
    https://www.developpez.net/forums/bl...cente-critere/
    Ah oui zut Merci pour la réf.
  3. Avatar de escartefigue
    • |
    • permalink
    Si l'on choisit la méthode par jointure externe (méthode 3), il faut veiller à sélectionner une colonne "not null", sans quoi le résultat peut être faussé .
    En général, on choisit en ce cas la PK pour éviter toute équivoque
  4. Avatar de TotoMonkey
    • |
    • permalink
    Autre méthode :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select t2.* 
    from 
     (select user_id , max (finished_at) as finished_at 
      from [action]  
      group by user_id  ) t1
    inner join action t2 on t1.user_id = t2.user_id and t1.finished_at = t2.finished_at