Récupérez la ligne qui a la valeur Max pour une colonne
Table:
UserId, Value, Date.
je veux obtenir L'UserId, valeur pour le max(Date) pour chaque UserId. C'est-à-dire, la valeur pour chaque UserId qui a la date la plus récente. Est-il un moyen de le faire simplement en SQL? (De Préférence Oracle)
mise à Jour: toutes mes Excuses pour toute ambiguïté: j'ai besoin d'obtenir TOUTES les Identifiants. Mais pour chaque UserId, seulement la ligne où cet utilisateur a la date la plus récente.
30 réponses
ceci va récupérer toutes les lignes pour lesquelles la valeur de la colonne my_date est égale à la valeur maximale de my_date pour cet userid. Cela permet de récupérer plusieurs lignes pour l'userid lorsque la date maximale est sur plusieurs lignes.
select userid,
my_date,
...
from
(
select userid,
my_Date,
...
max(my_date) over (partition by userid) max_my_date
from users
)
where my_date = max_my_date
"fonctions Analytiques rock"
Edit: en ce qui concerne le premier commentaire ...
"à l'aide de requêtes analytiques et une auto-jointure défait le but de l'analyse des requêtes"
Il n'y a pas d'auto-jointure dans ce code. Il y a plutôt un prédicat placé sur le résultat de la vue en ligne qui contient la fonction analytique -- une matière très différente, et complètement pratique standard.
"La fenêtre par défaut dans Oracle est à partir de la première ligne de la partition de l'actuel"
La clause de fenêtrage n'est applicable qu'en présence de la clause order by. Sans ordonnance par clause, aucune clause de fenêtrage n'est appliquée par défaut et aucun ne peut être spécifié explicitement.
le code fonctionne.
je vois que beaucoup de gens utilisent des sous-séries ou des fonctionnalités spécifiques au vendeur pour faire cela, mais je fais souvent ce genre de requête sans sous-séries de la manière suivante. Il utilise le SQL standard simple, donc il devrait fonctionner dans n'importe quelle marque de RDBMS.
SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;
En d'autres termes: extraction de la ligne de t1
où aucune autre ligne existe avec le même UserId
et une grande Date.
(j'ai mis l'identifiant "Date" dans les délimiteurs parce que C'est un SQL mot réservé.)
dans le cas où t1."Date" = t2."Date"
, le doublage apparaît. Habituellement les tables ont auto_inc(seq)
clé, par exemple id
.
Pour éviter de doubler peut être utilisé comme suit:
SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date")
OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;
Re commentaire de @Farhan:
Voici une explication plus détaillée:
Une jointure externe tente de rejoindre t1
avec t2
. Par défaut, tous les résultats de t1
sont retournés, et si Il ya une correspondance dans t2
, il est également retourné. S'il n'y a pas de correspondance dans t2
pour une ligne donnée de t1
, alors la requête retourne toujours la ligne de t1
, et utilise NULL
comme un espace réservé pour toutes les colonnes de t2
. C'est exactement comme ça qu'ils se joignent au travail en général.
le truc dans cette requête est de concevoir la condition de correspondance de la jointure telle que t2
doit correspondre au même userid
, et un plus grand date
. L'idée étant si une rangée existe dans t2
qui a une plus grande date
, alors la rangée dans t1
il est comparé contre ne peut pas être le plus grand date
pour que userid
. Mais s'il n'y a pas de correspondance -- c.-à-d. si aucune ligne n'existe dans t2
avec une plus grande date
que la ligne dans t1
-- nous savons que la ligne dans t1
était la ligne avec le plus grand date
pour le userid
.
dans ces cas (quand il n'y a pas de correspondance), les colonnes de t2
seront NULL
-- même les colonnes spécifiées dans la condition de jointure. Donc c'est pourquoi nous utilisons WHERE t2.UserId IS NULL
, parce que nous cherchons les cas où aucune rangée n'a été trouvée avec un plus grand date
pour le donné userid
.
SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
FROM table
GROUP BY userid
Je ne connais pas les noms exacts de vos colonnes, mais ce serait quelque chose comme ça:
select userid, value from users u1 where date = (select max(date) from users u2 where u1.userid = u2.userid)
N'étant pas au travail, je n'ai pas D'Oracle à disposition, mais je crois me rappeler Qu'Oracle permet plusieurs colonnes d'être appariées dans une clause IN, ce qui devrait au moins éviter les options qui utilisent une sous-requête corrélée, ce qui est rarement une bonne idée.
quelque chose comme ceci, peut-être (Je ne me souviens pas si la liste des colonnes doit être mise entre parenthèses ou non):
SELECT *
FROM MyTable
WHERE (User, Date) IN
( SELECT User, MAX(Date) FROM MyTable GROUP BY User)
EDIT: Juste essayé pour de vrai:
SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
2 where (usr, dt) in
3 ( select usr, max(dt) from mytable group by usr)
4 /
U DT
- ---------
A 01-JAN-09
B 01-JAN-09
donc les travaux, bien que certains des trucs New-fangly mentionnés ailleurs peuvent être plus performants.
je sais que vous avez demandé Oracle, mais en SQL 2005 nous utilisons maintenant ceci:
-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1
-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1
une clause de réserve ne serait-elle pas à la fois la plus simple et la meilleure?
select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1
pour le Contexte, sur Teradata ici un test de taille décent de ce s'exécute en 17s avec cette version de qualification et en 23s avec la'vue en ligne' /Aldridge solution #1.
Je n'ai pas Oracle pour le tester, mais la solution la plus efficace est d'utiliser des requêtes analytiques. Il devrait ressembler à quelque chose comme ceci:
SELECT DISTINCT
UserId
, MaxValue
FROM (
SELECT UserId
, FIRST (Value) Over (
PARTITION BY UserId
ORDER BY Date DESC
) MaxValue
FROM SomeTable
)
je soupçonne que vous pouvez vous débarrasser de la requête externe et mettre distinct sur l'intérieur, mais je ne suis pas sûr. En attendant, je sais que celui-ci fonctionne.
si vous voulez en savoir plus sur les requêtes analytiques, je vous conseille de lire http://www.orafaq.com/node/55 et http://www.akadia.com/services/ora_analytic_functions.html . Voici le petit résumé.
sous le capot des requêtes analytiques trient l'ensemble des données, puis les traitent de manière séquentielle. En le traitant, vous partitionnez l'ensemble de données en fonction de certains critères, puis, pour chaque ligne regarde une fenêtre (par défaut à la première valeur dans la partition à la ligne courante - cette valeur par défaut est également la plus efficace) et peut calculer des valeurs en utilisant un nombre de fonctions analytiques (dont la liste est très similaire pour les fonctions d'agrégation).
Dans ce cas, voici ce que la requête interne. L'ensemble de données est trié par UserId puis Date DESC. Puis il le traite en un seul passage. Pour chaque ligne, vous retournez L'identifiant UserId et la première Date vue pour cet identifiant UserId (puisque les dates sont triées DESC, c'est la date max). Ceci vous donne votre réponse avec des lignes dupliquées. Puis les squashs externes distincts dupliquent.
ce n'est pas un exemple particulièrement spectaculaire de requêtes analytiques. Pour une victoire beaucoup plus grande envisager de prendre un tableau des recettes financières et de calculer pour chaque utilisateur et reçu, un total courant de ce qu'ils ont payé. Les requêtes analytiques résolvent ça efficacement. D'autres solutions sont de moins en moins efficace. C'est pourquoi ils font partie de la norme SQL 2003. (Malheureusement Postgres ne les a pas encore. Grrr...)
avec PostgreSQL 8.4 ou plus tard, vous pouvez utiliser ceci:
select user_id, user_value_1, user_value_2
from (select user_id, user_value_1, user_value_2, row_number()
over (partition by user_id order by user_date desc)
from users) as r
where r.row_number=1
In Oracle 12c+
, vous pouvez utiliser Top n requêtes avec la fonction analytique rank
pour atteindre ce très concise sans sous-séries:
select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;
ce qui précède retourne toutes les lignes avec max my_date par utilisateur.
si vous voulez une seule ligne avec date max, remplacer le rank
par row_number
:
select *
from your_table
order by row_number() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;
Select
UserID,
Value,
Date
From
Table,
(
Select
UserID,
Max(Date) as MDate
From
Table
Group by
UserID
) as subQuery
Where
Table.UserID = subQuery.UserID and
Table.Date = subQuery.mDate
je viens Juste d'écrire un "live" exemple au travail :)
celui-ci supporte plusieurs valeurs pour UserId sur la même date.
colonnes: UserId, Valeur, Date
SELECT
DISTINCT UserId,
MAX(Date) OVER (PARTITION BY UserId ORDER BY Date DESC),
MAX(Values) OVER (PARTITION BY UserId ORDER BY Date DESC)
FROM
(
SELECT UserId, Date, SUM(Value) As Values
FROM <<table_name>>
GROUP BY UserId, Date
)
vous pouvez utiliser FIRST_VALUE au lieu de MAX et chercher dans le plan d'explication. Je n'ai pas le temps de jouer avec elle.
bien sûr, si vous cherchez à travers d'énormes tables, il est probablement mieux si vous utilisez plein conseils dans votre requête.
select VALUE from TABLE1 where TIME =
(select max(TIME) from TABLE1 where DATE=
(select max(DATE) from TABLE1 where CRITERIA=CRITERIA))
je pense à quelque chose comme ça. (Pardonnez - moi pour toutes les erreurs de syntaxe; je suis habitué à utiliser HQL à ce stade!)
EDIT: aussi mal lu la question! Correction de la requête...
SELECT UserId, Value
FROM Users AS user
WHERE Date = (
SELECT MAX(Date)
FROM Users AS maxtest
WHERE maxtest.UserId = user.UserId
)
je chose que vous shuold faire cette variante à la requête précédente:
SELECT UserId, Value FROM Users U1 WHERE
Date = ( SELECT MAX(Date) FROM Users where UserId = U1.UserId)
(T-SQL) obtenir D'abord tous les utilisateurs et leur maxdate. Rejoignez la table pour trouver les valeurs correspondantes pour les utilisateurs sur les maxdates.
create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')
select T1.userid, T1.value, T1.date
from users T1,
(select max(date) as maxdate, userid from users group by userid) T2
where T1.userid= T2.userid and T1.date = T2.maxdate
résultats:
userid value date
----------- ----------- --------------------------
2 3 2003-01-01 00:00:00.000
1 2 2002-01-01 00:00:00.000
la réponse ici est Oracle seulement. Voici une réponse un peu plus sophistiquée dans tout SQL:
Qui a le meilleur ensemble de devoirs résultat (somme maximale de devoirs points)?
SELECT FIRST, LAST, SUM(POINTS) AS TOTAL
FROM STUDENTS S, RESULTS R
WHERE S.SID = R.SID AND R.CAT = 'H'
GROUP BY S.SID, FIRST, LAST
HAVING SUM(POINTS) >= ALL (SELECT SUM (POINTS)
FROM RESULTS
WHERE CAT = 'H'
GROUP BY SID)
et un exemple plus difficile, qui ont besoin d'une certaine explication, pour laquelle je n'ai pas atm temps:
donne le livre (ISBN et titre) qui est le plus populaire en 2008, c'est-à-dire qui est emprunté le plus souvent en 2008.
SELECT X.ISBN, X.title, X.loans
FROM (SELECT Book.ISBN, Book.title, count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan
ON Copy.copyId = Loan.copyId
GROUP BY Book.title) X
HAVING loans >= ALL (SELECT count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan
ON Copy.copyId = Loan.copyId
GROUP BY Book.title);
Espérons que cette aide (n'importe qui).. :)
en ce qui Concerne, Guus
en supposant que la Date est unique pour un UserID donné, voici quelques TSQL:
SELECT
UserTest.UserID, UserTest.Value
FROM UserTest
INNER JOIN
(
SELECT UserID, MAX(Date) MaxDate
FROM UserTest
GROUP BY UserID
) Dates
ON UserTest.UserID = Dates.UserID
AND UserTest.Date = Dates.MaxDate
je suis assez en retard à la partie, mais le hack suivant va surpasser à la fois les sous-séries corrélées et n'importe quelle fonction analytique, mais a une restriction: les valeurs doivent se convertir en chaînes. Cela fonctionne donc pour les dates, les nombres et d'autres chaînes. Le code ne semble pas bon mais le profil d'exécution est grand.
select
userid,
to_number(substr(max(to_char(date,'yyyymmdd') || to_char(value)), 9)) as value,
max(date) as date
from
users
group by
userid
la raison pour laquelle ce code fonctionne si bien est qu'il n'a besoin de balayer la table qu'une seule fois. Il ne nécessite pas d'indices et surtout il n'a pas besoin de trier la table, ce que la plupart des fonctions d'analyse font. L'index de l'aide si vous avez besoin de filtrer les résultats pour un seul identifiant.
utiliser ROW_NUMBER()
pour assigner un classement unique en descendant Date
pour chaque UserId
, puis filtrer à la première ligne pour chaque UserId
(i.e., ROW_NUMBER
= 1).
SELECT UserId, Value, Date
FROM (SELECT UserId, Value, Date,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
FROM users) u
WHERE rn = 1;
select userid, value, date
from thetable t1 ,
( select t2.userid, max(t2.date) date2
from thetable t2
group by t2.userid ) t3
where t3.userid t1.userid and
t3.date2 = t1.date
IMHO ça marche. HTH
je pense que ça devrait marcher?
Select
T1.UserId,
(Select Top 1 T2.Value From Table T2 Where T2.UserId = T1.UserId Order By Date Desc) As 'Value'
From
Table T1
Group By
T1.UserId
Order By
T1.UserId
premier essai j'ai mal lu la question, en suivant la réponse du haut, voici un exemple complet avec des résultats corrects:
CREATE TABLE table_name (id int, the_value varchar(2), the_date datetime);
INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'a','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'b','2/2/2002');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'c','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'d','3/3/2003');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'e','3/3/2003');
--
select id, the_value
from table_name u1
where the_date = (select max(the_date)
from table_name u2
where u1.id = u2.id)
--
id the_value
----------- ---------
2 d
2 e
1 b
(3 row(s) affected)
cela permettra également de traiter les doublons (retourner une ligne pour chaque user_id):
SELECT *
FROM (
SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
FROM users u
) u2
WHERE u2.rowid = u2.last_rowid
vient de tester cela et il semble fonctionner sur une table de journalisation
select ColumnNames, max(DateColumn) from log group by ColumnNames order by 1 desc
cela devrait être aussi simple que:
SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)
si vous utilisez Postgres, vous pouvez utiliser array_agg
comme
SELECT userid,MAX(adate),(array_agg(value ORDER BY adate DESC))[1] as value
FROM YOURTABLE
GROUP BY userid
Je ne connais pas Oracle. C'est ce que j'ai trouvé
SELECT
userid,
MAX(adate),
SUBSTR(
(LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)),
0,
INSTR((LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)), ',')-1
) as value
FROM YOURTABLE
GROUP BY userid
les deux requêtes renvoient les mêmes résultats que la réponse acceptée. Voir SQLFiddles:
si (nom D'Utilisateur, Date) est unique, c'est-à-dire qu'aucune date n'apparaît deux fois pour le même utilisateur, alors:
select TheTable.UserID, TheTable.Value
from TheTable inner join (select UserID, max([Date]) MaxDate
from TheTable
group by UserID) UserMaxDate
on TheTable.UserID = UserMaxDate.UserID
TheTable.[Date] = UserMaxDate.MaxDate;
select UserId,max(Date) over (partition by UserId) value from users;
Solution pour MySQL qui n'a pas de concepts de conservation de partition, DENSE_RANK.
select userid,
my_date,
...
from
(
select @sno:= case when @pid<>userid then 0
else @sno+1
end as serialnumber,
@pid:=userid,
my_Date,
...
from users order by userid, my_date
) a
where a.serialnumber=0
référence: http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html