sql requete Récupère la ligne qui a la valeur Max pour une colonne




sql select enregistrement le plus recent (24)

Table:

UserId, Value, Date.

Je veux obtenir le UserId, Value pour le max (Date) pour chaque UserId. C'est-à-dire, la valeur pour chaque UserId qui a la dernière date. Y a-t-il un moyen de le faire simplement en SQL? (De préférence Oracle)

Mise à jour: Toutes mes excuses pour toute ambiguïté: je dois obtenir TOUS les UserIds. Mais pour chaque UserId, seulement cette ligne où cet utilisateur a la dernière date.


select   UserId,max(Date) over (partition by UserId) value from users;

(T-SQL) D'abord obtenir tous les utilisateurs et leur maxdate. Joignez-vous à 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 tous les SQL:

Qui a le meilleur résultat global des devoirs (somme maximale des points de devoirs)?

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 a besoin d'explication, pour lequel je n'ai pas de temps atm:

Donnez 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);

J'espère que cela aide (n'importe qui) .. :)

Cordialement, Guus


Je pense quelque chose comme ça. (Pardonnez-moi pour toute erreur 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
)

If (UserID, 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;

Dans Oracle 12c+ , vous pouvez utiliser les requêtes Top n avec le rank fonction analytique pour réaliser ceci de manière très concise sans sous-requêtes:

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 renvoie toutes les lignes avec max my_date par utilisateur.

Si vous ne voulez qu'une seule ligne avec la date max, remplacez 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; 

Je n'ai pas Oracle pour le tester, mais la solution la plus efficace consiste à utiliser des requêtes analytiques. Ça devrait ressembler a quelque chose comme ca:

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 suggère de lire http://www.orafaq.com/node/55 et http://www.akadia.com/services/ora_analytic_functions.html . Voici le court résumé.

Sous le capot, les requêtes analytiques trient l'ensemble des données, puis les traitent séquentiellement. Au fur et à mesure que vous le traitez, vous partitionnez l'ensemble de données selon certains critères, puis chaque fenêtre regarde une fenêtre (par défaut la première valeur de la partition à la ligne actuelle - cette valeur est aussi la plus efficace) et peut calculer des valeurs nombre de fonctions analytiques (dont la liste est très similaire aux fonctions agrégées).

Dans ce cas, voici ce que fait la requête interne. L'ensemble de données est trié par UserId puis Date DESC. Ensuite, il le traite en un seul passage. Pour chaque ligne, vous renvoyez l'UserId et la première Date vue pour cet UserId (puisque les dates sont triées DESC, c'est la date max). Cela vous donne votre réponse avec des lignes dupliquées. Ensuite, les courges DISTINCT externes se dupliquent.

Ce n'est pas un exemple particulièrement spectaculaire de requêtes analytiques. Pour une victoire beaucoup plus importante, considérez prendre un tableau des reçus financiers et calculer pour chaque utilisateur et reçu, un total cumulé de ce qu'ils ont payé. Les requêtes analytiques résolvent cela efficacement. D'autres solutions sont moins efficaces. C'est pourquoi ils font partie de la norme SQL 2003. (Malheureusement, Postgres ne les a pas encore ... Grrr ...)


SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

Je suis assez en retard pour la partie, mais le hack suivant sera plus performant que les sous-requêtes corrélées et toute fonction d'analyse, mais a une restriction: les valeurs doivent être converties en chaînes. Donc, cela fonctionne pour les dates, les nombres et d'autres chaînes. Le code n'a pas l'air bien mais le profil d'exécution est génial.

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 scanner la table qu'une seule fois. Il ne nécessite aucun index et surtout il n'a pas besoin de trier la table, ce que font la plupart des fonctions analytiques. Les index vous aideront cependant si vous avez besoin de filtrer le résultat pour un seul ID utilisateur.


Juste testé cela et il semble fonctionner sur une table de notation

select ColumnNames, max(DateColumn) from log  group by ColumnNames order by 1 desc


D'abord essayer 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)

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 suis pas familier avec 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:

  1. Réponse acceptée
  2. Ma solution avec Postgres
  3. Ma solution avec Oracle

Cela devrait être aussi simple que:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)

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  

N'étant pas au travail, je n'ai pas Oracle à portée de main, mais je crois me souvenir qu'Oracle permet de faire correspondre plusieurs colonnes 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 bon. idée.

Quelque chose comme ça, peut-être (je ne me souviens pas si la liste des colonnes doit être 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, cela fonctionne, bien que certains des nouveaux éléments mentionnés ailleurs peuvent être plus performants.


Je vois que beaucoup de gens utilisent des sous-requêtes ou des fonctionnalités spécifiques au vendeur pour faire cela, mais je fais souvent ce genre de requête sans sous-requêtes de la manière suivante. Il utilise SQL standard, donc il devrait fonctionner dans n'importe quelle marque de SGBDR.

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: récupérez la ligne à partir de t1 où aucune autre ligne n'existe avec le même UserId et une Date plus grande.

(Je mets l'identifiant "Date" dans les délimiteurs car c'est un mot réservé SQL.)

Dans le cas où t1."Date" = t2."Date" , le doublage apparaît. Habituellement, les tables ont une clé auto_inc(seq) , par exemple id . Pour éviter le doublage 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 joindre t1 avec t2. Par défaut, tous les résultats de t1 sont renvoyés, et s'il y a une correspondance dans t2, elle est également renvoyée. S'il n'y a pas de correspondance dans t2 pour une ligne donnée de t1, alors la requête renvoie toujours la ligne de t1, et utilise NULL comme espace réservé pour toutes les colonnes de t2. C'est ainsi que fonctionnent les jointures externes en général.

L'astuce dans cette requête est de concevoir la condition de correspondance de la jointure de sorte que t2 doit correspondre au même ID utilisateur et à une date plus longue. L'idée étant si une ligne existe dans t2 qui a une date plus grande, alors la ligne dans t1 avec laquelle elle est comparée ne peut pas être la plus grande date pour cet ID utilisateur. Mais s'il n'y a pas de correspondance - c'est-à-dire si aucune ligne n'existe dans t2 avec une date plus grande que la ligne dans t1 - nous savons que la ligne dans t1 était la ligne avec la plus grande date pour l'ID utilisateur donné.

Dans ces cas (lorsqu'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. C'est pourquoi nous utilisons WHERE t2.UserId IS NULL , car nous recherchons les cas où aucune ligne n'a été trouvée avec une date plus grande pour l'ID utilisateur donné.


Cela récupérera toutes les lignes pour lesquelles la valeur de la colonne my_date est égale à la valeur maximale de my_date pour cet ID utilisateur. Cela peut récupérer plusieurs lignes pour l'ID utilisateur où 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'utilisation de requêtes analytiques et d'une auto-jointure annule le but des requêtes analytiques"

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 - un sujet très différent, et une pratique complètement standard.

"La fenêtre par défaut dans Oracle est de la première ligne de la partition à celle en cours"

La clause de fenêtrage n'est applicable qu'en présence de la clause order by. En l'absence de clause order by, aucune clause de fenêtrage n'est appliquée par défaut et aucune clause ne peut être explicitement spécifiée.

Le code fonctionne.


Une clause QUALIFY 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écente de ceci s'exécute en 17s avec cette version de QUALIFY et en 23s avec la solution 'inline view' / Aldridge # 1.


Avec PostgreSQL 9, 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

select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))

Je pense que cela devrait fonctionner?

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

Je devais écrire un exemple "live" au travail :)

Celui-ci prend en charge plusieurs valeurs pour UserId à 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 le rechercher dans le plan d'explication. Je n'ai pas eu le temps de jouer avec.

Bien sûr, si vous cherchez à travers des tables énormes, c'est probablement mieux si vous utilisez des indications FULL dans votre requête.


Je ne connais pas vos noms de colonnes, mais ce serait quelque chose comme ceci:

    select userid, value
      from users u1
     where date = (select max(date)
                     from users u2
                    where u1.userid = u2.userid)




greatest-n-per-group