Abrufen der Zeile, die den Max-Wert für eine Spalte enthält

Question

Tabelle:

UserId, Value, Date.

Ich möchte die UserId, Wert für die max (Datum) für jede UserId zu erhalten. Das heißt, der Wert für jede UserId, die das späteste Datum hat. Gibt es eine Möglichkeit, dies einfach in SQL zu tun? (Vorzugsweise Oracle)

更新しました。 Entschuldigung für etwaige Unklarheiten: Ich muss ALLE UserIds abrufen. Aber für jede UserId, nur die Zeile, wo dieser Benutzer das neueste Datum hat.

Answer 1

Ich sehe, dass viele Leute Unterabfragen oder andere Fensterfunktionen verwenden, um dies zu tun, aber ich mache diese Art von Abfrage oft ohne Unterabfragen auf die folgende Weise. Es wird einfaches Standard-SQL verwendet, so dass es in jedem RDBMS funktionieren sollte.

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;

Mit anderen Worten: Holen Sie die Zeile aus t1 wo keine andere Zeile mit der gleichen UserId und ein größeres Datum.

(Ich habe den Bezeichner "Datum" in Trennzeichen gesetzt, weil es sich um ein reserviertes SQL-Wort handelt).

Für den Fall, dass t1."Date" = t2."Date" erscheint die Verdoppelung. Normalerweise hat Tabellen auto_inc(seq) Schlüssel, z.B. id . Um Verdoppelungen zu vermeiden, kann folgendes verwendet werden:

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;

---

Zu dem Kommentar von @Farhan:

Hier ist eine ausführlichere Erklärung:

Eine äußere Verknüpfung versucht, die t1 con t2 . Standardmäßig werden alle Ergebnisse von t1 zurückgegeben werden, und si gibt es eine Übereinstimmung in t2 wird sie ebenfalls zurückgegeben. Wenn es keine Übereinstimmung in t2 für eine bestimmte Zeile von t1 dann gibt die Abfrage immer noch die Zeile von t1 und verwendet NULL als Platzhalter für alle t2 Kolumnen. So funktionieren Outer Joins im Allgemeinen.

Der Trick bei dieser Abfrage besteht darin, die Abgleichsbedingung der Verknüpfung so zu gestalten, dass t2 muss mit dem dieselbe userid und eine mehr date . Die Idee ist, dass wenn eine Zeile in t2 die einen größeren date dann die Zeile in t1 es wird verglichen mit kann nicht der Größte sein date dafür userid . Wenn es jedoch keine Übereinstimmung gibt - d. h. wenn keine Zeile in t2 mit einer größeren date als die Zeile in t1 -- wir wissen, dass die Zeile in t1 war die Reihe mit den meisten date für die angegebene userid .

In diesen Fällen (wenn es keine Übereinstimmung gibt), werden die Spalten von t2 wird sein NULL -- auch die in der Verknüpfungsbedingung angegebenen Spalten. Deshalb verwenden wir WHERE t2.UserId IS NULL denn wir suchen nach den Fällen, in denen keine Zeile mit einem größeren Wert gefunden wurde. date für die angegebene userid .

Answer 2

Dadurch werden alle Zeilen abgerufen, bei denen der Wert der Spalte my_date gleich dem Höchstwert von my_date für diese Benutzerkennung ist. Dies kann mehrere Zeilen für die userid abrufen, in denen das maximale Datum in mehreren Zeilen steht.

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

"Analytische Funktionen rocken"

Edit: In Bezug auf den ersten Kommentar ...

"Die Verwendung von analytischen Abfragen und einem Self-Join verfehlt den Zweck von analytischen Abfragen".

In diesem Code gibt es keinen Self-Join. Stattdessen wird ein Prädikat auf das Ergebnis der Inline-Ansicht gesetzt, die die analytische Funktion enthält - eine ganz andere Angelegenheit und eine völlig übliche Praxis.

"Das Standardfenster in Oracle reicht von der ersten Zeile in der Partition bis zur aktuellen Zeile.

Die Windowing-Klausel ist nur anwendbar, wenn die Order-By-Klausel vorhanden ist. Wenn keine order by-Klausel vorhanden ist, wird standardmäßig keine Fensterungsklausel angewendet und es kann auch keine explizit angegeben werden.

Der Code funktioniert.

Answer 3

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

Answer 4

Ich kenne die genauen Namen Ihrer Spalten nicht, aber es könnte in etwa so aussehen:

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

Answer 5

Da ich nicht bei der Arbeit bin, habe ich Oracle nicht zur Hand, aber ich glaube mich zu erinnern, dass Oracle mehrere Spalten in einer IN-Klausel abgleichen kann, was zumindest die Optionen vermeiden sollte, die eine korrelierte Unterabfrage verwenden, was selten eine gute Idee ist.

Etwa so (ich weiß nicht mehr, ob die Spaltenliste in Klammern gesetzt werden sollte oder nicht):

SELECT * 
FROM MyTable
WHERE (User, Date) IN
  ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

EDIT: Ich habe es gerade wirklich ausprobiert:

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

Es funktioniert also, auch wenn einige der an anderer Stelle erwähnten Neuerungen vielleicht leistungsfähiger sind.