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

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

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.

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.

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

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)

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.

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

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

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.

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; 

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;

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

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)

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  

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.

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

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
)

(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

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

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 

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.

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

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

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)

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

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

Powinno to być tak proste, jak:

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

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

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

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;

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