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


Table:

UserId, Value, Date.

Je veux obtenir le UserId, Valeur 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)

Mettre à jour: Toutes mes excuses pour toute ambiguïté: j'ai besoin de TOUS les UserIds. Mais pour chaque UserId, seulement cette ligne où cet utilisateur a la dernière date.


503
2017-09-23 14:34


origine


Réponses:


Cela 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 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 ...

"utiliser des requêtes analytiques et une auto-jointure défait 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 - une question très différente 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. Sans clause order by, aucune clause de fenêtrage n'est appliquée par défaut et aucune ne peut être explicitement spécifiée.

Le code fonctionne.


354
2017-09-23 20:01



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 un langage 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.)

Au cas où t1."Date" = t2."Date", le doublage apparaît. Les tables ont généralement auto_inc(seq) clé, par ex. 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 si il y a 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 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 à la même userid, et un plus grand rendez-vous amoureux. L'idée étant si une ligne existe en t2 qui a une plus grande date, alors la ligne en t1 est comparée à ne peut pas être la plus grande date pour cet userid. Mais s'il n'y a pas de correspondance - c'est-à-dire s'il n'y a pas de ligne dans t2 avec une date plus grande que la ligne dans t1 - nous savons que la ligne dans t1 était la rangée avec la plus grande date pour l'ID utilisateur donné.

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. Voilà pourquoi nous utilisons WHERE t2.UserId IS NULL, car nous recherchons les cas où aucune ligne n'a été trouvée avec une date supérieure pour l'ID utilisateur donné.


400
2017-09-23 15:18



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

146
2017-09-23 14:39



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

    select userid, valeur
      des utilisateurs u1
     où date = (sélectionnez max (date)
                     des utilisateurs u2
                    où u1.userid = u2.userid)

45
2017-09-23 20:06



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 (ne peut pas me rappeler 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

Cela fonctionne donc bien que certaines des nouveautés mentionnées ailleurs puissent être plus performantes.


34
2017-09-23 15:22



Je sais que vous avez demandé Oracle, mais dans 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

12
2017-10-19 16:17



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.


6
2017-09-23 15:47



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 en évidence à 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 bref 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 du standard SQL 2003. (Malheureusement, Postgres ne les a pas encore ... Grrr ...)


6
2017-11-01 13:22



Avec PostgreSQL 8.4 ou version ultérieure, 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

4
2017-09-23 14:51