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

Séb.

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

Noter ce billet
par , 30/09/2023 à 16h30 (3444 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 :

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 [/c]NULL[/c], alors nous avons la ligne dont a.finished_at est le plus élevé.
Pas simple à expliquer celui-ci

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 ?

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

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.