Pobierz wiersz, który ma wartość Max dla kolumny


574

Stół:

UserId, Value, Date.

Chcę uzyskać identyfikator użytkownika, wartość maks. (Data) dla każdego identyfikatora użytkownika. Oznacza to wartość dla każdego identyfikatora użytkownika, który ma najnowszą datę. Czy jest na to sposób po prostu w SQL? (Najlepiej Oracle)

Aktualizacja: Przepraszamy za wszelkie niejasności: muszę uzyskać WSZYSTKIE identyfikatory użytkowników. Ale dla każdego identyfikatora użytkownika tylko ten wiersz, w którym użytkownik ma najnowszą datę.


21
Co się stanie, jeśli istnieje wiele wierszy o maksymalnej wartości daty dla konkretnego identyfikatora użytkownika?
David Aldridge,

Jakie są kluczowe pola tabeli?
vamosrafa

porównano niektóre rozwiązania poniżej: sqlfiddle.com/#!4/6d4e81/1
Used_By_Already

1
@DavidAldridge, ta kolumna jest prawdopodobnie unikalna.
Pacerier

Odpowiedzi:


397

Spowoduje to pobranie wszystkich wierszy, dla których wartość kolumny my_date jest równa maksymalnej wartości my_date dla tego identyfikatora użytkownika. Może to pobrać wiele wierszy dla identyfikatora użytkownika, w którym maksymalna data dotyczy wielu wierszy.

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

„Funkcje analityczne rock”

Edycja: W odniesieniu do pierwszego komentarza ...

„korzystanie z zapytań analitycznych i samozłączenie przeczy celowi zapytań analitycznych”

W tym kodzie nie ma możliwości samodzielnego przyłączenia się. Zamiast tego w wyniku widoku wbudowanego znajduje się predykat, który zawiera funkcję analityczną - zupełnie inna sprawa i całkowicie standardowa praktyka.

„Domyślne okno w Oracle jest od pierwszego wiersza partycji do bieżącego”

Klauzula okienkowa ma zastosowanie wyłącznie w przypadku zamówienia według klauzuli. Bez kolejności według klauzuli domyślnie nie jest stosowana żadna klauzula okienkowa i żadnej nie można wyraźnie określić.

Kod działa.


38
Po zastosowaniu do tabeli zawierającej 8,8 miliona wierszy, zapytanie to zajęło połowę czasu zapytań w niektórych innych wysoko głosowanych odpowiedziach.
Derek Mahar

4
Czy ktoś chce opublikować link do MySQL-a, jeśli taki istnieje?
redolent

2
Czy to nie może zwrócić duplikatów? Na przykład. jeśli dwa wiersze mają ten sam identyfikator_użytkownika i tę samą datę (która przypadkowo jest wartością maksymalną).
jastr

2
@jastr Myślę, że zostało to potwierdzone w pytaniu
David Aldridge

3
Zamiast tego MAX(...) OVER (...)możesz także użyć ROW_NUMBER() OVER (...)(dla największej grupy na grupę) lub RANK() OVER (...)(dla największej grupy na grupę).
MT0,

441

Widzę, że wiele osób korzysta z podkwerend lub funkcji specyficznych dla dostawców, aby to zrobić, ale często wykonuję tego rodzaju zapytania bez podkwerend w następujący sposób. Używa zwykłego, standardowego SQL, więc powinien działać w dowolnej marce 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;

Innymi słowy: pobierz wiersz z miejsca, w t1którym nie istnieje żaden inny wiersz z tym samymUserId i większej dacie.

(Umieszczam identyfikator „Data” w ogranicznikach, ponieważ jest to słowo zastrzeżone SQL).

W przypadku, gdy t1."Date" = t2."Date"pojawi się podwojenie. Zwykle tabele mają auto_inc(seq)klucz, np id. Aby uniknąć podwojenia, można zastosować:

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;

Ponownie skomentuj @Farhan:

Oto bardziej szczegółowe wyjaśnienie:

Zewnętrzna próbach przyłączenia do przyłączenia t1się t2. Domyślnie t1zwracane są wszystkie wyniki , a jeśli istnieje dopasowanie t2, jest ono również zwracane. Jeśli nie ma dopasowania t2dla danego wiersza t1, wówczas zapytanie nadal zwraca wiersz t1i używa NULLjako symbolu zastępczego dla wszystkicht2 kolumn. Tak właśnie działają sprzężenia zewnętrzne.

Sztuczka w tym zapytaniu polega na zaprojektowaniu warunków dopasowania łączenia, które t2muszą być takie same userid i większe date . Pomysł jest, jeśli wiersz istnieje t2, że ma większą date, wówczas wiersz t1to jest porównywana nie może być największym datena to userid. Ale jeśli nie ma dopasowania - tj. Jeśli nie istnieje żaden wiersz t2z wartością większą dateniż wiersz in t1- wiemy, że wiersz w t1był wierszem o największym datedla danego userid.

W tych przypadkach (gdy nie ma dopasowania), kolumny t2będą NULL- nawet kolumny określone w warunku łączenia. Dlatego używamy WHERE t2.UserId IS NULL, ponieważ szukamy przypadków, w których nie znaleziono żadnego wiersza z większym datedla podanego userid.


7
Wow Bill. To najbardziej kreatywne rozwiązanie tego problemu, jakie widziałem. Jest również dość wydajny na moim dość dużym zestawie danych. To z pewnością przewyższa wiele innych rozwiązań, które widziałem lub moje własne próby rozwiązania tego problemu.
Justin Noel

36
Po zastosowaniu do tabeli zawierającej 8,8 miliona wierszy zapytanie to zajęło prawie dwa razy więcej czasu niż w zaakceptowanej odpowiedzi.
Derek Mahar

16
@Derek: Optymalizacje zależą od marki i wersji RDBMS, a także obecności odpowiednich indeksów, typów danych itp.
Bill Karwin

7
Na MySQL tego rodzaju zapytanie faktycznie powoduje, że zapętla się ono w wyniku połączenia kartezjańskiego między tabelami, co powoduje czas O (n ^ 2). Zamiast tego użycie metody podzapytania skróciło czas zapytania z 2,0s do 0,003s. YMMV.
Jesse

1
Czy istnieje sposób, aby dostosować to do wierszy, w których data jest największą datą mniejszą lub równą dacie podanej przez użytkownika? Na przykład, jeśli użytkownik poda datę „23-OCT-2011”, a tabela zawiera wiersze dla „24-OCT-2011”, „22-OCT-2011”, „20-OCT-2011”, to chcę uzyskać „22-OCT-2011”. Drapałem się po głowie i czytam ten fragment od jakiegoś czasu ...
Cory Kendall

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

3
W moich testach z użyciem tabeli o dużej liczbie wierszy rozwiązanie to zajęło około dwa razy więcej czasu niż w zaakceptowanej odpowiedzi.
Derek Mahar

7
Pokaż swój test, proszę
Rob van Wijk,

Potwierdzam, że jest znacznie szybszy niż inne rozwiązania
tamersalama,

5
Problem w tym, że nie zwraca pełnego rekordu
Used_By_Already

@ user2067753 Nie, nie zwraca pełnego rekordu. Możesz użyć tego samego wyrażenia MAX () .. KEEP .. w wielu kolumnach, dzięki czemu możesz wybrać wszystkie potrzebne kolumny. Jest to jednak niewygodne, jeśli chcesz mieć dużą liczbę kolumn i wolisz użyć opcji WYBIERZ *.
Dave Costa

51

Nie znam dokładnych nazw kolumn, ale byłoby to mniej więcej tak:

    wybierz identyfikator użytkownika, wartość
      od użytkowników u1
     gdzie data = (wybierz maks. (datę)
                     od użytkowników u2
                    gdzie u1.userid = u2.userid)

3
Prawdopodobnie niezbyt skuteczny, Steve.
David Aldridge,

7
Prawdopodobnie nie doceniasz optymalizatora zapytań Oracle.
Rafał Dowgird,

3
Ani trochę. Prawie na pewno zostanie to zaimplementowane jako pełny skan z łączeniem w pętli zagnieżdżonej w celu uzyskania dat. Mówisz o logicznych IO w kolejności 4-krotnej liczby wierszy w tabeli i bądź okropny dla nietrywialnych ilości danych.
David Aldridge,

4
Do Twojej wiadomości „Nieefektywne, ale działa” to to samo, co „Działa, ale nie jest wydajne”. Kiedy zrezygnowaliśmy z wydajności jako celu projektowego?
David Aldridge,

6
+1, ponieważ gdy twoje dane nie mają już milionów wierszy, jest to najłatwiejsze do zrozumienia rozwiązanie. gdy masz wielu programistów wszystkich poziomów umiejętności modyfikujących kod, zrozumiałość jest ważniejsza niż ułamek sekundy, co jest niezauważalne.
n00b 24.04.13

35

Nie będąc w pracy, nie mam pod ręką Oracle, ale wydaje mi się, że pamiętam, że Oracle pozwala na dopasowanie wielu kolumn w klauzuli IN, co powinno przynajmniej unikać opcji wykorzystujących skorelowane podzapytanie, które rzadko jest dobrym pomysł.

Być może coś takiego (nie pamiętam, czy lista kolumn powinna być nawiasowana, czy nie):

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

EDYCJA: Właśnie wypróbowałem to naprawdę:

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

Więc to działa, chociaż niektóre nowiutkie rzeczy wspomniane gdzie indziej mogą być bardziej wydajne.


4
Działa to również dobrze na PostgreSQL. I podoba mi się jego prostota i ogólność - podzapytanie mówi „Oto moje kryteria”, zapytanie zewnętrzne mówi „I oto szczegóły, które chcę zobaczyć”. +1.
j_random_hacker

13

Wiem, że poprosiłeś o Oracle, ale w SQL 2005 używamy teraz tego:


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

7

Nie mam Oracle, aby to przetestować, ale najbardziej wydajnym rozwiązaniem jest użycie zapytań analitycznych. Powinno to wyglądać mniej więcej tak:

SELECT DISTINCT
    UserId
  , MaxValue
FROM (
    SELECT UserId
      , FIRST (Value) Over (
          PARTITION BY UserId
          ORDER BY Date DESC
        ) MaxValue
    FROM SomeTable
  )

Podejrzewam, że możesz pozbyć się zewnętrznego zapytania i wyróżnić wewnętrzne, ale nie jestem pewien. Tymczasem wiem, że to działa.

Jeśli chcesz dowiedzieć się więcej na temat zapytań analitycznych, sugeruję przeczytanie http://www.orafaq.com/node/55 i http://www.akadia.com/services/ora_analytic_functions.html . Oto krótkie podsumowanie.

Kwerendy analityczne pod maską posortuj cały zestaw danych, a następnie przetworz go sekwencyjnie. Podczas przetwarzania partycjonujesz zestaw danych według określonych kryteriów, a następnie dla każdego wiersza patrzy na niektóre okna (domyślnie na pierwszą wartość w partycji na bieżący wiersz - ta domyślna jest również najbardziej wydajna) i możesz obliczyć wartości przy użyciu liczba funkcji analitycznych (lista, która jest bardzo podobna do funkcji agregujących).

W tym przypadku jest to, co robi wewnętrzne zapytanie. Cały zestaw danych jest sortowany według UserId, a następnie Date DESC. Następnie przetwarza go w jednym przejściu. Dla każdego wiersza zwracany jest identyfikator użytkownika i pierwsza data widziana dla tego identyfikatora użytkownika (ponieważ daty są sortowane DESC, to jest data maksymalna). To daje odpowiedź ze zduplikowanymi wierszami. Następnie zewnętrzne DISTINCT wyciskają duplikaty.

To nie jest szczególnie spektakularny przykład zapytań analitycznych. W przypadku znacznie większej wygranej należy wziąć pod uwagę tabelę wpływów finansowych i obliczać dla każdego użytkownika i pokwitowania bieżącą sumę zapłaconych kwot. Kwerendy analityczne rozwiązują to skutecznie. Inne rozwiązania są mniej wydajne. Dlatego są częścią standardu SQL 2003. (Niestety Postgres nie ma ich jeszcze. Grrr ...)


Musisz także zwrócić wartość daty, aby całkowicie odpowiedzieć na pytanie. Jeśli to oznacza inną klauzulę first_value, sugerowałbym, że rozwiązanie jest bardziej złożone niż powinno być, a metoda analityczna oparta na max (data) brzmi lepiej.
David Aldridge,

W pytaniu nie ma mowy o zwrocie daty. Możesz to zrobić, dodając kolejny PIERWSZY (Data) lub po prostu sprawdzając datę i zmieniając zapytanie zewnętrzne na GROUP BY. Użyłbym pierwszego i oczekuję, że optymalizator obliczy oba za jednym razem.
user11318,

„Pytanie nie mówi nic o zwrocie daty”… tak, masz rację. Przepraszam. Ale dodanie większej liczby klauzul FIRST_VALUE stałoby się dość nieuporządkowane. Jest to sortowanie według pojedynczego okna, ale jeśli masz 20 kolumn do zwrócenia dla tego wiersza, to napisałeś dużo kodu, aby przejść.
David Aldridge,

Przyszło mi również do głowy, że to rozwiązanie nie jest deterministyczne w przypadku danych, w których pojedynczy identyfikator użytkownika ma wiele wierszy o maksymalnej dacie i różnych wartościach. Więcej winy w pytaniu niż odpowiedzi.
David Aldridge,

1
Zgadzam się, że to boleśnie gadatliwe. Jednak czy ogólnie nie jest tak w przypadku SQL? I masz rację, że rozwiązanie jest niedeterministyczne. Istnieje wiele sposobów radzenia sobie z więzami, a czasem każdy jest tym, czego chcesz.
user11318,

6

Czy klauzula QUALIFY nie byłaby jednocześnie najprostsza i najlepsza?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

Dla kontekstu, na Teradata tutaj, przyzwoity test tego rozmiaru jest uruchamiany w 17s w tej wersji QUALIFY, aw 23s w „widoku wbudowanym” / rozwiązaniu Aldridge nr 1.


1
To jest najlepsza odpowiedź moim zdaniem. Uważaj jednak na tę rank()funkcję w sytuacjach, w których istnieją więzi. Możesz skończyć z więcej niż jednym rank=1. Lepiej użyć, row_number()jeśli naprawdę chcesz zwrócić tylko jeden rekord.
cartbeforehorse

1
Należy również pamiętać, że QUALIFYklauzula jest specyficzna dla Teradata. W Oracle (przynajmniej) musisz zagnieździć swoje zapytanie i filtrować, używając WHEREklauzuli na zawijającej instrukcji select (która prawdopodobnie uderzy w wydajność, jak sądzę).
cartbeforehorse

5

W Oracle 12c+można użyć zapytań Top n wraz z funkcją analityczną, rankaby osiągnąć to bardzo zwięźle bez podkwerend:

select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

Powyższe zwraca wszystkie wiersze z maksymalną datą_moje na użytkownika.

Jeśli chcesz tylko jeden wiersz z max daty, a następnie zastąpić rankz 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; 

5

Użyj, ROW_NUMBER()aby przypisać unikalny ranking malejącej Datedla każdego UserId, a następnie filtruj do pierwszego wiersza dla każdego UserId(tj. 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;

5

W PostgreSQL 8.4 lub nowszym możesz użyć tego:

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

3

myślę, że powinieneś zrobić ten wariant do poprzedniego zapytania:

SELECT UserId, Value FROM Users U1 WHERE 
Date = ( SELECT MAX(Date)    FROM Users where UserId = U1.UserId)

3
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  

3

Po prostu musiałem napisać „na żywo” przykład w pracy :)

Ta obsługuje wiele wartości dla UserId w tym samym dniu.

Kolumny: identyfikator użytkownika, wartość, data

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
)

Możesz użyć FIRST_VALUE zamiast MAX i sprawdzić w planie wyjaśniania. Nie miałem czasu się z tym bawić.

Oczywiście, jeśli przeszukujesz ogromne tabele, prawdopodobnie lepiej jest użyć PEŁNYCH wskazówek w zapytaniu.


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

2

Myślę, że coś takiego. (Wybacz mi błędy w składni; w tym momencie jestem przyzwyczajony do używania HQL!)

EDYCJA: Również źle odczytałem pytanie! Poprawiono zapytanie ...

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)

Nie spełnia warunku „dla każdego identyfikatora użytkownika”
David Aldridge,

Gdzie by to nie zawiodło? Dla każdego identyfikatora użytkownika w użytkownikach zostanie zagwarantowane, że zwrócony zostanie co najmniej jeden wiersz zawierający ten identyfikator użytkownika. A może gdzieś brakuje jakiegoś specjalnego przypadku?
jdmichal

2

(T-SQL) Najpierw pobierz wszystkich użytkowników i ich maksymalną datę. Dołącz do tabeli, aby znaleźć odpowiednie wartości dla użytkowników w maksymalnych terminach.

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

wyniki:

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

2

Odpowiedzią jest tylko Oracle. Oto nieco bardziej wyrafinowana odpowiedź we wszystkich SQL:

Kto ma najlepszy ogólny wynik pracy domowej (maksymalna suma punktów pracy domowej)?

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)

I trudniejszy przykład, który wymaga wyjaśnienia, dla którego nie mam czasu atm:

Podaj książkę (ISBN i tytuł), która jest najbardziej popularna w 2008 r., Tj. Która jest najczęściej wypożyczana w 2008 r.

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

Mam nadzieję, że to pomoże (każdemu) .. :)

Pozdrawiam, Guus


Przyjęta odpowiedź to nie „tylko Oracle” - to standardowy SQL (obsługiwany przez wiele DBMS)
a_horse_w_na_name

2

Zakładając, że data jest unikalna dla danego ID użytkownika, oto niektóre 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 

2

Jestem spóźniony na imprezę, ale następujący hack prześcignie zarówno skorelowane podkwerendy, jak i dowolną funkcję analityczną, ale ma jedno ograniczenie: wartości muszą zostać przekonwertowane na ciągi. Działa to dla dat, liczb i innych ciągów. Kod nie wygląda dobrze, ale profil wykonania jest świetny.

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

Powodem, dla którego ten kod działa tak dobrze, jest to, że wystarczy zeskanować tabelę tylko raz. Nie wymaga żadnych indeksów, a co najważniejsze, nie musi sortować tabeli, co robi większość funkcji analitycznych. Indeksy będą pomocne, jeśli będziesz musiał przefiltrować wynik dla pojedynczego identyfikatora użytkownika.


Jest to dobry plan wykonania w porównaniu do większości, ale zastosowanie wszystkich tych sztuczek na więcej niż kilku polach byłoby żmudne i może działać przeciwko niemu. Ale bardzo interesujące - dzięki. patrz sqlfiddle.com/#!4/2749b5/23
Used_By_Already

Masz rację, może stać się żmudny, dlatego należy to zrobić tylko wtedy, gdy wymaga tego wydajność zapytania. Tak często dzieje się w przypadku skryptów ETL.
aLevelOfIndirection

to jest bardzo miłe. zrobił coś podobnego przy użyciu LISTAGG, ale wygląda brzydko. postgres ma lepszą alternatywę przy użyciu array_agg. zobacz moją odpowiedź :)
Bruno Calza

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 to działa. HTH


1

Myślę, że to powinno działać?

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

1

Najpierw źle odczytałem pytanie, podążając za najlepszą odpowiedzią, oto kompletny przykład z poprawnymi wynikami:

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)

1

To również zajmie się duplikatami (zwróci jeden wiersz dla każdego identyfikatora użytkownika):

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

1

Właśnie to przetestowałem i wydaje się, że działa na tabeli rejestrowania

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

1

Powinno to być tak proste, jak:

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

1

Rozwiązanie dla MySQL, które nie ma koncepcji partycji KEEP, 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

Odniesienie: http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html


To nie działa „ na innych bazach danych ”. Działa to tylko na MySQL i prawdopodobnie na SQL Server, ponieważ ma podobną koncepcję zmiennych. Na pewno nie będzie działać na Oracle, Postgres, DB2, Derby, H2, HSQLDB, Vertica, Greenplum. Dodatkowo zaakceptowaną odpowiedzią jest standardowy ANSI SQL (który, jak wiadomo, nie obsługuje MySQL)
a_horse_w_na_nazwie

koń, myślę, że masz rację. Nie mam wiedzy na temat innych baz danych ani ANSI. Moje rozwiązanie jest w stanie rozwiązać problem w MySQL, który nie ma odpowiedniego wsparcia dla ANSI SQL, aby rozwiązać go w standardowy sposób.
Ben Lin

1

Jeśli korzystasz z Postgres, możesz użyć array_agglike

SELECT userid,MAX(adate),(array_agg(value ORDER BY adate DESC))[1] as value
FROM YOURTABLE
GROUP BY userid

Nie znam Oracle. Właśnie to wymyśliłem

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 

Oba zapytania zwracają takie same wyniki, jak zaakceptowana odpowiedź. Zobacz SQLFiddles:

  1. Zaakceptowana odpowiedź
  2. Moje rozwiązanie z Postgres
  3. Moje rozwiązanie z Oracle

0

Jeśli (identyfikator użytkownika, data) jest unikalny, tzn. Data nie pojawia się dwukrotnie dla tego samego użytkownika, wówczas:

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;

Uważam, że musisz dołączyć również UserID
Tom H

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

2
Zwróci to wszystkie wiersze, a nie tylko jeden wiersz na użytkownika.
Jon Heller
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.