Comment puis-je interroger sql pour une date d'enregistrement la plus récente pour chaque utilisateur
J'ai une table qui est une entrée de collection quand un utilisateur a été connecté.
username, date, value
--------------------------
brad, 1/2/2010, 1.1
fred, 1/3/2010, 1.0
bob, 8/4/2009, 1.5
brad, 2/2/2010, 1.2
fred, 12/2/2009, 1.3
etc..
Comment créer une requête qui me donnerait la dernière date pour chaque utilisateur?
Update: j'ai oublié que je devais avoir une valeur qui va de pair avec la dernière date.
21 réponses
select t.username, t.date, t.value
from MyTable t
inner join (
select username, max(date) as MaxDate
from MyTable
group by username
) tm on t.username = tm.username and t.date = tm.MaxDate
Utilisation des fonctions de fenêtre (fonctionne dans Oracle, Postgres 8.4, SQL Server 2005, DB2, Sybase, Firebird 3.0, MariaDB 10.3)
select * from (
select
username,
date,
value,
row_number() over(partition by username order by date desc) as rn
from
yourtable
) t
where t.rn = 1
Je vois que la plupart des développeurs utilisent une requête en ligne sans tenir compte de son impact sur des données énormes.
Simplement, vous pouvez y parvenir par:
SELECT a.username, a.date, a.value
FROM myTable a
LEFT OUTER JOIN myTable b
ON a.username = b.username
AND a.date < b.date
WHERE b.username IS NULL
ORDER BY a.date desc;
Pour obtenir la ligne entière contenant la date maximale pour l'utilisateur:
select username, date, value
from tablename where (username, date) in (
select username, max(date) as date
from tablename
group by username
)
SELECT *
FROM MyTable T1
WHERE date = (
SELECT max(date)
FROM MyTable T2
WHERE T1.username=T2.username
)
Celui-ci devrait vous donner le résultat correct pour votre question éditée.
La sous-requête s'assure de ne trouver que les lignes de la dernière date, et l'extérieur GROUP BY
s'occupera des liens. Quand il y a deux entrées pour la même date pour le même utilisateur, il renverra celle avec le plus haut value
.
SELECT t.username, t.date, MAX( t.value ) value
FROM your_table t
JOIN (
SELECT username, MAX( date ) date
FROM your_table
GROUP BY username
) x ON ( x.username = t.username AND x.date = t.date )
GROUP BY t.username, t.date
Pour Oracle sortes le jeu de résultats dans l'ordre décroissant, et prend le premier enregistrement, de sorte que vous obtiendrez le dernier enregistrement:
select * from mytable
where rownum = 1
order by date desc
SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1)
SELECT Username, date, value
from MyTable mt
inner join (select username, max(date) date
from MyTable
group by username) sub
on sub.username = mt.username
and sub.date = mt.date
Résoudrait le problème mis à jour. Il pourrait ne pas fonctionner si bien sur les grandes tables, même avec une bonne indexation.
SELECT *
FROM ReportStatus c
inner join ( SELECT
MAX(Date) AS MaxDate
FROM ReportStatus ) m
on c.date = m.maxdate
SELECT t1.username, t1.date, value
FROM MyTable as t1
INNER JOIN (SELECT username, MAX(date)
FROM MyTable
GROUP BY username) as t2 ON t2.username = t1.username AND t2.date = t1.date
Select * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)
La requête interne renverra la dernière date pour l'utilisateur actuel, la requête externe tirera toutes les données en fonction du résultat de la requête interne.
J'ai utilisé cette façon de prendre le dernier enregistrement pour chaque utilisateur que j'ai sur ma table. C'était une requête pour obtenir le dernier emplacement pour le vendeur selon l'heure récente détectée sur les périphériques PDA.
CREATE FUNCTION dbo.UsersLocation()
RETURNS TABLE
AS
RETURN
Select GS.UserID, MAX(GS.UTCDateTime) 'LastDate'
From USERGPS GS
where year(GS.UTCDateTime) = YEAR(GETDATE())
Group By GS.UserID
GO
select gs.UserID, sl.LastDate, gs.Latitude , gs.Longitude
from USERGPS gs
inner join USER s on gs.SalesManNo = s.SalesmanNo
inner join dbo.UsersLocation() sl on gs.UserID= sl.UserID and gs.UTCDateTime = sl.LastDate
order by LastDate desc
Ma petite compilation
- auto -
join
mieux que imbriquéselect
- , mais
group by
ne vous donne pasprimary key
, qui est préférable pourjoin
- cette clé peut être donnée par
partition by
, conjointement avecfirst_value
(docs)
Donc, voici une requête:
select t.* from Table t inner join ( select distinct first_value(ID) over(partition by GroupColumn order by DateColumn desc) as ID from Table where FilterColumn = 'value' ) j on t.ID = j.ID
Avantages:
- filtrer les données avec
where
instruction en utilisant n'importe quelle colonne - {[2] } Toutes les colonnes des lignes filtrées
Inconvénients:
- besoin de MS SQL Serveur à partir de 2012.
J'ai fait un peu pour ma demande comme il:
Voici la requête:
select distinct i.userId,i.statusCheck, l.userName from internetstatus
as i inner join login as l on i.userID=l.userID
where nowtime in((select max(nowtime) from InternetStatus group by userID));
D'après mon expérience, le moyen le plus rapide est de prendre chaque ligne pour laquelle il n'y a pas de ligne plus récente dans la table. Voici un petit benchmark avec quelques données que j'ai à portée de main.
Un autre avantage est que la syntaxe utilisée est très simple, et que la signification de la requête est plutôt facile à saisir (prenez toutes les lignes de telle sorte qu'aucune ligne plus récente n'existe pour le nom d'utilisateur considéré).
N'EXISTE PAS
SELECT username, value
FROM t
WHERE NOT EXISTS (
SELECT *
FROM t AS witness
WHERE witness.date > t.date
);
Expliquer le coût total: 2.38136
Numéro de ligne
SELECT username, value
FROM (
SELECT username, value, row_number() OVER (PARTITION BY username ORDER BY date DESC) AS rn
FROM t
) t2
WHERE rn = 1
Coût Total : 61.5823
JOINTURE INTÉRIEURE
SELECT t.username, t.value
FROM t
INNER JOIN (
SELECT username, MAX(date) AS date
FROM t
GROUP BY username
) tm ON t.username = tm.username AND t.date = tm.date;
Expliquer le coût total: 67.5439
JOINTURE EXTERNE GAUCHE
SELECT username, value
FROM t
LEFT OUTER JOIN t AS w ON t.username = w.username AND t.date < w.date
WHERE w.username IS NULL
Expliquer le coût total: 62.964
Les plans explain proviennent d'une base de données avec environ 10k lignes, stockées en XML. Les requêtes utilisées contiennent également un prédicat "group_id = '1'".
Ceci est similaire à l'une des réponses ci-dessus, mais à mon avis c'est beaucoup plus simple et plus propre. En outre, montre une bonne utilisation pour l'instruction CROSS apply. Pour SQL Server 2005 et au-dessus...
select
a.username,
a.date,
a.value,
from yourtable a
cross apply (select max(date) 'maxdate' from yourtable a1 where a.username=a1.username) b
where a.date=b.maxdate
Vous pouvez également utiliser la fonction de classement analytique
with temp as
(
select username, date, RANK() over (partition by username order by date desc) as rnk from t
)
select username, rnk from t where rnk = 1
Vous utiliseriez la fonction d'agrégat MAX et GROUP BY
SELECT username, MAX(date), value FROM tablename GROUP BY username, value
SELECT DISTINCT Username, Dates,value
FROM TableName
WHERE Dates IN (SELECT MAX(Dates) FROM TableName GROUP BY Username)
Username Dates value
bob 2010-02-02 1.2
brad 2010-01-02 1.1
fred 2010-01-03 1.0
Cela devrait également fonctionner afin d'obtenir toutes les dernières entrées pour les utilisateurs.
SELECT username, MAX(date) as Date, value
FROM MyTable
GROUP BY username, value