webdevqa.jp.net

列の最大値を持つ行を取得します

表:

UserId, Value, Date.

UserId、各UserIdのmax(Date)の値を取得します。つまり、最新の日付を持つ各UserIdの値です。単純にSQLでこれを行う方法はありますか? (できればOracle)

更新: /あいまいさをお詫び申し上げます:私はすべてのユーザーIDを取得する必要があります。しかし、各UserIdについては、そのユーザーが最新の日付を持つ行のみです。

534
Umang

これにより、my_date列の値がそのユーザーIDのmy_dateの最大値に等しいすべての行が取得されます。これは、最大日付が複数の行にあるユーザーIDに対して複数の行を検索する場合があります。

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

「解析機能が揺れる」

編集:最初のコメントに関して...

「分析クエリと自己結合を使用すると、分析クエリの目的が無効になる」

このコードには自己結合はありません。代わりに、分析関数を含むインラインビューの結果に置かれた述語があります。これは非常に異なる問題で、完全に標準的な方法です。

「Oracleのデフォルトウィンドウは、パーティションの最初の行から現在の行までです。」

ウィンドウ句はorder by句がある場合にのみ適用できます。 order by句がない場合、デフォルトではwindowing句は適用されず、明示的に指定することもできません。

コードは動作します。

372
David Aldridge

私は多くの人がこれを行うためにサブクエリあるいは他のベンダー特有の機能を使うのを見ますが、私はしばしば次のようにサブクエリなしでこの種のクエリをする。標準の標準SQLを使用しているため、どのブランドの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;

言い換えると、同じUserIdとそれ以上のDateを持つ行が他に存在しないt1から行を取得します。

(SQL予約語なので、識別子 "Date"を区切り文字に入れています。)

t1."Date" = t2."Date"の場合は、2倍になります。通常テーブルはauto_inc(seq)キーを持ちます。 id。倍増を避けるためには、次のようにします。

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;

@Farhanからの再コメント:

これがより詳細な説明です。

外部結合はt1t2と結合しようとします。デフォルトでは、t1のすべての結果が返され、t2に一致がある場合は、{ifも返されます。指定されたt2の行とt1が一致しない場合でも、クエリはt1の行を返し、すべてのt2の列のプレースホルダとしてNULLを使用します。それがまさにアウタージョインが一般的に機能する方法です。

このクエリのトリックは、t2sameuserid、およびgreaterdateに一致する必要があるように、結合の一致条件を設計することです。より大きなdateを持つ行がt2に存在する場合、cannotと比較されるt1の行がそのdateの最大のuseridになります。しかし、一致がない場合、つまりt2の行よりもdateが大きい行がt1に存在しない場合、t1の行は、指定されたdateに対して最大のuseridの行であることがわかります。

このような場合(一致しない場合)、t2の列は、結合条件で指定された列でもNULLになります。そのため、特定のdateに対して、より大きいuseridを持つ行が見つからない場合を検索しているので、WHERE t2.UserId IS NULLを使用するのはそのためです。

423
Bill Karwin
SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid
154
Dave Costa

正確なカラム名はわかりませんが、これは次のようになります。

 useridを選択し、値
をユーザーu1 
に設定します。date =(max(date)
をユーザーu2から選択します。
ここで、u1.userid = u2 。ユーザーID)
48
Steve K

仕事をしていないので、Oracleに手渡す必要はありませんが、OracleではIN句で複数の列を一致させることができるため、少なくとも相関副問い合​​わせを使用するオプションは避けられるべきです。アイディア。

このようなもの、おそらく(列リストを括弧でくくるべきかどうかを思い出せない):

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

編集:ちょうど本当のためにそれを試してみました:

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

他の場所で言及されている新しく目立つもののいくつかはもっとパフォーマンスがあるかもしれませんが、それでうまくいきます。

35
Mike Woodhouse

私はあなたがOracleを要求したことを知っています、しかしSQL 2005では、我々は今これを使います:


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

テストするためのオラクルはありませんが、最も効率的な解決策は、分析照会を使用することです。これは次のようになります。

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

私はあなたが外側のクエリを取り除き、内側に明確に置くことができると思いますが、私はよくわかりません。それまでの間、私はこれがうまくいくことを知っています。

分析クエリについて知りたい場合は、 http://www.orafaq.com/node/55 を読むことをお勧めします。 http://www.akadia.com/services/ora_analytic_functions.html。これが短い要約です。

フードの下では、分析クエリはデータセット全体をソートしてから、順次処理します。それを処理するとき、あなたは特定の基準に従ってデータセットを分割し、そして各行について何らかのウィンドウを見て(デフォルトは現在の行へのパーティションの最初の値です。そのデフォルトも最も効率的です)分析関数の数(そのリストは集約関数と非常によく似ています)。

この場合、これは内部クエリの動作です。データセット全体が、UserId、Date DESCの順にソートされています。それからそれはワンパスでそれを処理します。各行について、UserIdとそのUserIdに最初に表示されたDateを返します(日付はDESCでソートされているため、これが最大日付です)。これにより、重複した行であなたの答えが得られます。それから、外側のDISTINCTは重複を潰します。

これは分析クエリの特に壮観な例ではありません。もっと大きな勝利のためには、金融の領収書の表を取って、各ユーザーと領収書、彼らが支払ったものの積算合計を計算することを検討してください。分析クエリはそれを効率的に解決します。他の解決策はそれほど効率的ではありません。これらが2003標準SQLの一部である理由です。 (残念ながらPostgresはまだそれらを持っていません。Grrr...)

6
user11318

QUALIFY句は最も単純で最良のものではないでしょうか。

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

コンテキストとしては、ここTeradataでは、このQUALIFYバージョンでは17秒、「インラインビュー」/ Aldridgeソリューションでは1秒の適切なサイズのテストが実行されます。

6
wcw

PostgreSQL 8.4以降では、これを使うことができます。

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

Oracle 12c+では、分析関数rankと一緒に Top n クエリを使用して、これを非常に簡潔に行うことができます なし サブクエリ:

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

上記は、ユーザーごとにmax my_dateを持つすべての行を返します。

最大日付の1行のみが必要な場合は、rankrow_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
Gurwinder Singh

ROW_NUMBER() を使用して、各Dateに対して降順のUserIdに一意のランク付けを割り当て、次に各UserIdの最初の行にフィルタをかけます(つまり、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;
4
markusk
select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))
3
nouky

仕事で「生きた」例を書く必要がありました:)

これは 同じ 日付のUserIdの複数の値をサポートします。

列:UserId、Value、Date

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
)

MAXの代わりにFIRST_VALUEを使用して、EXPLAIN PLANで検索することができます。遊ぶ時間がありませんでした。

もちろん、巨大なテーブルを検索する場合は、クエリにFULLヒントを使用した方がおそらく良いでしょう。

3
Truper
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
Aheho

ここでの答えはOracleだけです。これはすべてのSQLでもう少し洗練された答えです。

誰が最高の全体的な宿題の結果(宿題ポイントの最大合計)を持っていますか?

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)

そしてもう少し説明が必要な、もっと難しい例です。

2008年に最も人気のある、つまり2008年に最も頻繁に借りられている本(ISBNとタイトル)を渡します。

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

これが(だれでも)助けになることを願っています.. :)

よろしく、フース

2
Guus

私はこのようなものだと思います。 (構文上の間違いはご容赦ください。この時点ではHQLを使用していました。)

編集:また質問を誤解!クエリを修正しました...

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

(T - SQL)最初にすべてのユーザーとその最大日付を取得します。テーブルと結合して、最大日付のユーザーに対応する値を見つけます。

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

結果:

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

私はあなたが前のクエリにこの変形を加えるべきだと思います:

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

Dateが与えられたUserIDに対してユニークであると仮定すると、ここにいくつかの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
marc

私はパーティーにはかなり遅れていますが、以下のハックは相関サブクエリと分析機能の両方よりも優れていますが、1つ制限があります。そのため、日付、数字、その他の文字列に対して機能します。コードは見栄えがよくありませんが、実行プロファイルは素晴らしいです。

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

このコードがうまく機能するのは、テーブルを一度だけスキャンすればよいからです。インデックスを必要とせず、最も重要なのは、ほとんどの分析機能が行うようにテーブルをソートする必要がないということです。ただし、単一のユーザーIDに対して結果をフィルター処理する必要がある場合は、索引が役立ちます。

2

Postgresを使っているのなら、array_aggを使えます

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

私はOracleに慣れていません。これは私が思い付いたものです

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 

どちらのクエリも、受け入れられた回答と同じ結果を返します。 SQLFiddlesを参照してください。

  1. 受け入れられた答え
  2. Postgresによる私の解決策
  3. オラクルとの私の解決策
1
Bruno Calza

これは以下のように単純なはずです。

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

最初に答えを答えて、私が質問を誤解してみてください、これは正しい結果を伴う完全な例です:

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
KyleLanser

これもまた、重複の面倒を見ます(user_idごとに1行を返します)。

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
na43251

これをテストしただけで、ログテーブルで動作するようです

select ColumnNames, max(DateColumn) from log  group by ColumnNames order by 1 desc
1
Mauro
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

私見これは動作します。 HTH

1
Zsolt Botykai

私はこれでうまくいくと思いますか?

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
GateKiller

check このリンク あなたの質問がそのページに似ているようであれば、私はあなたにそのリンクのための解決策を与えるであろう以下の質問を提案するでしょう

select distinct sno,item_name,max(start_date) over(partition by sno),max(end_date) over(partition by sno),max(creation_date) over(partition by sno), max(last_modified_date) over(partition by sno) from uniq_select_records order by sno,item_name asc;

そのリンクに関連する正確な結果が得られます

0
Smart003
SELECT a.userid,a.values1,b.mm 
FROM table_name a,(SELECT userid,Max(date1)AS mm FROM table_name GROUP BY userid) b
WHERE a.userid=b.userid AND a.DATE1=b.mm;
0
praveen

(UserID、Date)が一意である場合、すなわち同じユーザに対して2回は日付が表示されない場合:

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;
0
finnw

コードを使用してください:

select T.UserId,T.dt from (select UserId,max(dt) 
over (partition by UserId) as dt from t_users)T where T.dt=dt;

UserIdの重複値に関係なく、これにより結果が取得されます。あなたのUserIdがユニークなら、それはもっと簡単になります。

select UserId,max(dt) from t_users group by UserId;
0
Natty
select   UserId,max(Date) over (partition by UserId) value from users;
0
Amitābha

パーティションKEEP、DENSE_RANKの概念がないMySQL用のソリューション。

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

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

0
Ben Lin