jak zapytać sql o datę ostatniego rekordu dla każdego użytkownika


228

Mam tabelę, która jest pozycją kolekcji dotyczącą tego, kiedy użytkownik był zalogowany.

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

Jak utworzyć zapytanie, które dałoby mi najnowszą datę dla każdego użytkownika?

Aktualizacja: Zapomniałem, że muszę mieć wartość zgodną z najnowszą datą.


7
Z jakiej bazy danych korzystasz? MySQL, SQL-Server, Oracle, ...?
Peter Lang,

1
Czy potrzebujesz wartości odpowiadającej najnowszej dacie, czy też wartości maksymalnej ORAZ daty maksymalnej?
Matthew Jones,

Odpowiedzi:


381
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

3
Czy podczas pracy z postgresql ta wersja byłaby szybsza niż używanie IN (podzapytania) zamiast łączenia wewnętrznego?
TheOne

3
@TheOne, zgodnie z moim doświadczeniem, używanie sprzężenia wewnętrznego jest szybsze niż w stanie
dada,

14
Ostrożnie z tym podejściem: może zwrócić więcej niż jeden wiersz na użytkownika, jeśli ma więcej niż jeden rekord na datę ( max(date)zwróci datę, która połączy wiele rekordów). Aby uniknąć tego problemu, najlepiej byłoby użyć rozwiązania @ dotjoe: stackoverflow.com/a/2411763/4406793 .
Marco Roy

@ RedFilter To zadziałało idealnie dla mojego problemu. Wielkie dzięki za takie zapytanie techniczne. Nawiasem mówiąc, użyłem daty / godziny zamiast daty, aby uniknąć uzyskania wielu wyników dla określonej daty
Muhammad Khan

dlaczego potrzebujesz „i t.date = tm.MaxDate” nie byłoby wystarczające grupowanie?
duldi

125

Korzystanie z funkcji okna (działa w 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

1
Warto wyjaśnić, który produkt / wersję Sybase. Nie działa na Sybase ASE 16.
levant pied

2
Dużą zaletą tego podejścia jest to, że zawsze zwraca tylko jeden wiersz na partycję ( usernamew tym przypadku) i nie wymaga nawet unikalnego pola „uporządkowanego” (takiego jak dołączenie max(date)w innych odpowiedziach).
Marco Roy

1
Aby dodać coś do tego, co powiedział @MarcoRoy, jeśli zdarzy się, że masz więcej niż jeden rekord z tą samą maksymalną datą, jeśli zmienisz zapytanie, na przykład podczas debugowania, inny rekord może otrzymać numer wiersza 1, więc wyniki mogą być niespójne. Ale dopóki naprawdę cię to nie obchodzi, nie powinno to stanowić problemu. Można to rozwiązać, jeśli dodasz PK po dacie. Na przykład: order by date desc, id desc).
Andrew

40

Widzę, że większość programistów używa wbudowanego zapytania bez rozważania jego wpływu na ogromne dane.

Po prostu możesz to osiągnąć poprzez:

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;

3
w rzeczywistości działa to tylko w przypadku duplikatów, jeśli masz więcej niż 2 wartości, warunek a.date <b.date nie działa, co oznacza, że ​​nie jest to ogólne rozwiązanie, chociaż pomysł pracy z LEFT OUTER JOIN jest ważny rzecz w tej odpowiedzi.
iversoncru

Co ciekawe, Sybase ASE 16 działa dobrze w przypadku mniejszych (<10 tys. Rzędów) tabel, ale w przypadku większych (> 100 tys. Rzędów) to hung ... Myślałem, że byłby to idealny przykład relacyjnych baz danych, które powinny przodować w ...
levant pied

1
@levantpied ... Tak, pozostawienie złączenia jest kosztowne w przypadku większych zestawów danych. Możesz poprawić wydajność, ustawiając warunek filtru na złączeniu, aby w jakiś sposób go obsłużyć.
sujeet

21

Aby uzyskać cały wiersz zawierający maksymalną datę dla użytkownika:

select username, date, value
from tablename where (username, date) in (
    select username, max(date) as date
    from tablename
    group by username
)

1
Praca dla MySQL
School Boy

1
Uważaj, że da to duplikaty, jeśli istnieje więcej niż jeden rekord z tą samą datą dla konkretnego użytkownika. Możesz tego chcieć lub nie.
Andrew

Ten sql jest wolny w Oracle z klauzulą, nie będzie używał indeksu
meadlai 10.10.19

9
SELECT *     
FROM MyTable T1    
WHERE date = (
   SELECT max(date)
   FROM MyTable T2
   WHERE T1.username=T2.username
)

4
Chociaż jest to inne możliwe rozwiązanie, zwykle nie jest to dobry sposób na rozwiązanie tego problemu. Zrobienie tego w ten sposób spowoduje, że wewnętrzne zapytanie zostanie uruchomione jeden raz dla każdej nazwy w tabeli, powodując poważne spowolnienie dla dowolnej tabeli o znacznych rozmiarach. Wykonanie osobnego zapytania, które nie zawiera elementu z pierwszego zapytania w klauzuli where, następnie połączenie dwóch tabel będzie zwykle szybsze.
Scott Chamberlain

Ma to tę zaletę, że jest jednym z bardziej zrozumiałych rozwiązań, które nie są specyficzne dla implementacji.
Michael Szczepaniak,

7

Z mojego doświadczenia wynika, że ​​najszybszym sposobem jest pobranie każdego wiersza, dla którego nie ma nowszego wiersza w tabeli.

Kolejną zaletą jest to, że zastosowana składnia jest bardzo prosta i że znaczenie zapytania jest raczej łatwe do uchwycenia (weź wszystkie wiersze, aby nie było nowszego wiersza dla rozważanej nazwy użytkownika).

NIE ISTNIEJE

SELECT username, value
FROM t
WHERE NOT EXISTS (
  SELECT *
  FROM t AS witness
  WHERE witness.username = t.username AND witness.date > t.date
);

NUMER WIERSZA

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

DOŁĄCZ DO WEWNĘTRZNEGO

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;

DOŁĄCZ DO LEWEGO ZEWNĘTRZNEGO

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

Mam trudności ze zrozumieniem wersji NOT EXISTS. Nie brakuje agregacji w części podzapytania? Jeśli uruchomię to na swoim stole, odzyskam tylko 3 rekordy pracowników od 40 pracowników, których mam w tabeli. Powinienem zdobyć co najmniej 40 płyt. Czy w wewnętrznym zapytaniu nie powinniśmy również dopasowywać według nazwy użytkownika?
Narshe,

Działa dla mnie przy użyciu następujących opcji:SELECT username, value FROM t WHERE NOT EXISTS ( SELECT * FROM t AS witness WHERE witness.date > t.date AND witness.username = t.username );
Narshe,

Patrzyłem na NIE ISTNIEJE i wygląda na to, że zwraca tylko wyższy wpis dla wszystkich użytkowników, w przeciwieństwie do: „zapytania, które dałoby mi najnowszą datę dla każdego użytkownika”.
Tasos Zervos

Rzeczywiście masz rację, aktualizuję moje zapytanie. Dziękuję za uwagę! @ Przykro mi, ale z jakiegoś powodu przegapiłem twoje komentarze: / Ale masz całkowitą rację.
Fabian Pijcke

2

Ten powinien dać ci poprawny wynik dla edytowanego pytania.

Pod-zapytanie upewnia się, że znajdziesz tylko wiersze z najnowszą datą, a zewnętrzna GROUP BYzajmie się więzami. Jeśli dla tego samego użytkownika będą dwa wpisy dla tej samej daty, zwróci ten z najwyższym 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

1

Możesz także użyć analitycznej funkcji rangi

    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

0
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

Rozwiązałoby zaktualizowany problem. Może nie działać tak dobrze na dużych stołach, nawet przy dobrym indeksowaniu.


0
SELECT *
FROM ReportStatus c
inner join ( SELECT 
  MAX(Date) AS MaxDate
  FROM ReportStatus ) m
on  c.date = m.maxdate

0

Dla Oracle sortuje zestaw wyników w kolejności malejącej i pobiera pierwszy rekord, aby uzyskać najnowszy rekord:

select * from mytable
where rownum = 1
order by date desc

0
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       

Prawdopodobnie nie zadziałałoby, jeśli wielu użytkowników otrzymało zamówienia w tym samym dniu; co jeśli brad i bob mieli zamówienie 2 stycznia?
AHiggins

Grupuję według nazwy użytkownika, więc będzie działać, a wyniki będą wyglądać następująco: Nazwa użytkownika Daty wartość bob 2010-02-02 1.2 brad 2010-02-02 1.4 fred 2010-01-03 1.0
wara

0
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

4
Zdanie lub dwa na temat implementacji lub wyjaśnienia znacznie przyczynia się do stworzenia wysokiej jakości odpowiedzi.

0

Select * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)

Zapytanie wewnętrzne zwróci ostatnią datę dla bieżącego użytkownika, zapytanie zewnętrzne pobierze wszystkie dane zgodnie z wynikiem zapytania wewnętrznego.


0

W ten sposób pobrałem ostatni rekord dla każdego użytkownika, który mam na stole. To było zapytanie o ostatnią lokalizację dla sprzedawcy zgodnie z ostatnim czasem wykrytym na urządzeniach 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

0
SELECT * FROM TABEL1 WHERE DATE= (SELECT MAX(CREATED_DATE) FROM TABEL1)

Witamy w StackOverflow i dziękuję za próbę pomocy. Odpowiedzi zawierające tylko kod, takie jak twoje, są mniej doceniane w porównaniu z odpowiedziami wyjaśniającymi rozwiązanie.
Yunnosch

Przeczytaj ten poradnik, aby uzyskać wysokiej jakości odpowiedź.
thewaywewere

i. nie wraca do MAX dla każdej nazwy użytkownika, tylko do ostatniego pojedynczego wiersza.
IrvineCAGuy

0

Moja mała kompilacja

  • siebie joinlepiej niż zagnieżdżoneselect
  • ale group bynie daje ci tego, primary keyco jest lepszejoin
  • ten klucz można podać partition byw połączeniu z first_value( dokumentami )

Oto pytanie:

Wybierz
 t. *
z 
 Tabela t połączenie wewnętrzne (
  wybierz odrębną pierwszą wartość (ID) ponad (podziel według kolejności GroupColumn według DateColumn desc) jako ID
  z tabeli
  gdzie FilterColumn = „wartość”
 ) j na t.ID = j.ID

Plusy:

  • Filtruj dane za wherepomocą instrukcji przy użyciu dowolnej kolumny
  • select dowolne kolumny z filtrowanych wierszy

Cons:

  • Potrzebujesz MS SQL Server od 2012 roku.

0

Zrobiłem trochę dla mojej aplikacji, ponieważ:

Poniżej znajduje się zapytanie:

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

0

Jest to podobne do jednej z powyższych odpowiedzi, ale moim zdaniem jest o wiele prostsze i uporządkowane. Pokazuje również dobre zastosowanie instrukcji cross Apply. Dla SQL Server 2005 i nowszych ...

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

0
SELECT MAX(DATE) AS dates 
FROM assignment  
JOIN paper_submission_detail ON  assignment.PAPER_SUB_ID = 
     paper_submission_detail.PAPER_SUB_ID 

1
Chociaż ten kod może rozwiązać pytanie, w tym wyjaśnienie, w jaki sposób i dlaczego to rozwiązuje problem, naprawdę pomógłby poprawić jakość twojego postu i prawdopodobnie doprowadziłby do większej liczby głosów. Pamiętaj, że odpowiadasz na pytanie czytelników w przyszłości, a nie tylko osoby zadającej teraz pytanie. Proszę edytować swoje odpowiedzi, aby dodać wyjaśnień i dać wskazówkę co zastosować ograniczenia i założenia. Z recenzji
podwójny sygnał dźwiękowy

-2

Powinno to również działać, aby uzyskać wszystkie najnowsze wpisy dla użytkowników.

SELECT username, MAX(date) as Date, value
FROM MyTable
GROUP BY username, value

1
Cześć, kolumna wartości musi znajdować się w grupie według klauzuli.
Juan Ruiz de Castilla

-4

Użyłbyś funkcji agregującej MAX i GROUP BY

SELECT username, MAX(date), value FROM tablename GROUP BY username, value

7
Twoja edycja wybierze tylko losowy value, a nie powiązany z MAX(date)wierszem.
Alison R.

poda maksymalną datę, ale nazwa użytkownika i wartość mogą nie być tego samego rekordu.
SKR
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.