- PR -

SQLのSELECT文　TOP2

1
投稿者投稿内容
nyan
常連さん
会議室デビュー日: 2007/01/18
投稿数: 21
投稿日時: 2007-07-02 15:38

下記のような表があったとき、Date毎のTop2を取得したいと思っています。
どのようなSQL文になるのでしょうか。
いろいろ試しましたが抽出できません。

Date Value
7/10 1
7/10 2
7/10 3
7/11 2
7/11 1
ぶさいくろう
ぬし
会議室デビュー日: 2005/11/22
投稿数: 1232
お住まい・勤務地: 川崎市（は俺も含めてロクな人間が住んでないよｗ）
投稿日時: 2007-07-02 15:51
つかDBMSくらい書いとこうぜ。な！
OracleならROWNUMじゃね？
あーひょっとして日付のグループ単位でTOP2ってことか？
KOX
大ベテラン
会議室デビュー日: 2004/08/23
投稿数: 142
投稿日時: 2007-07-02 16:04
どんな調査をして、何を試しましたか？
この手の内容は、いろんなところに転がっていますよ。
たとえば、かつのりさんのブログとか。
http://blogs.wankuma.com/kacchan6/archive/2007/06/07/79946.aspx
とんくま
ベテラン
会議室デビュー日: 2005/08/02
投稿数: 56
お住まい・勤務地: 東京
投稿日時: 2007-07-03 01:54
確かに、Web でいろいろ探せば答えは見つかるかも知れませんが、nyanさんの問題に完全に一致した答えを見つけるのは難しいかもしれません。そのためには、かなりWeb Search のテクニックが必要でしょう。
例えば、かつのりさんのブログでは、Primary key or Unique key の存在が前提で、同じ値が有っても常に2件だけが得られるようになっています。しかし、nyanさんが示したデータ例ではそのような列が存在しない（Table の設計が悪いと言われればそれまでですが）か、省略されていますので、同じ値が有った場合、それらが全部結果に含まれます。
　
少なくとも私の知識範囲では、そのような場合でも正確に2件だけ得るには、ROWNUMBER OLAP関数を使うか、Oracle の Psudocolumn ROWNUM を使う方法しか思いつきません。

OLAP関数は、（私の知識範囲では）主要な商業DBMS（Oracle, DB2, MS SQL Server)で使えます。
コード:
SELECT Date, Value

  FROM (SELECT Date, Value

             , ROWNUMBER() OVER(PARTITION BY Date

                                ORDER BY Value DESC) rn

          FROM Test_TopN

       ) S

 WHERE rn <= 2

 ORDER BY Date, Value DESC;

かつのりさんのブログと同じ考え方ですが、Unique key が利用できない場合、
コード:
SELECT Date, Value

  FROM Test_TopN t

 WHERE (Date, Value)

       IN (SELECT Date, Value

             FROM Test_TopN tn

            WHERE tn.Date = t.Date

            ORDER BY Value DESC

            FETCH FIRST 2 ROWS ONLY

          )

 ORDER BY Date, Value DESC;

（FETCH FIRST 2 ROWS ONLY は、DB2 の構文ですが、MS SQL Server では TOP(2) が使えますし、その他の DBMS では、かつのりさんのブログと同じく LIMIT 2 が使えるものが有ります）
　
Oracle の ROWNUM は追加の inline view が必要と思います。
コード:
SELECT Date, Value

  FROM Test_TopN t

 WHERE (Date, Value)

       IN (SELECT Date, Value

             FROM (SELECT Date, Value

                     FROM Test_TopN tn

                    WHERE tn.Date = t.Date

                    ORDER BY Value DESC

                  )

            WHERE ROWNUM <= 2

          )

 ORDER BY Date, Value DESC;


さらに言えば、この点（正確に2件だけ）に目をつぶれる、或いは同じ Value が存在しない前提ならば、別の方法も考えられます。

例えば、　
コード:
SELECT Date, Value

  FROM Test_TopN t

 WHERE (SELECT COUNT(*)

          FROM Test_TopN tn

         WHERE tn.Date = t.Date

           AND tn.Value >= t.Value

       ) <= 2

 ORDER BY Date, Value DESC;

や
コード:
SELECT t.Date, t.Value

  FROM Test_TopN t

     , Test_TopN tn

 WHERE tn.Date = t.Date

   AND tn.Value >= t.Value

 GROUP BY

       t.Date, t.Value 

HAVING COUNT(*) <= 2

 ORDER BY Date, Value DESC;

等が考えられます。（まだ、あるでしょうが、とりあえず思いついたものを書いてみました。）
テストはしていませんので、それぞれの DBMS でそのまま動くかは分かりません。
（実行する DBMS により、多少の修正がいるかもしれません、）
　
いろいろな方法の中から、お使いの DBMS でサポートしている構文、及び表の設計、データの特性（同じValueが存在するか否か、等）、アプリケーションの要求（同じValueが有った場合、どう取り扱うか、等）、その他要求項目を考慮し、パーフォーマンスやメンテナンス等の観点から適当なものを選べばよいと思います。

[ メッセージ編集済み 編集者: とんくま 編集日時 2007-07-03 02:01 ]
忠犬
大ベテラン
会議室デビュー日: 2006/05/01
投稿数: 109
投稿日時: 2007-07-03 14:04
RDBMS名とバージョンを明示してもらえないと、適切な回答はできません。

かつのりさんのブログの「limit」を使う方法は、PostgreSQL以外では使えない場合が
多いと思います。「limit」自体がサポートされていなかったり、MySQLのように
「limitはサポート済だが、サブクエリ中には使えない」といったRDBMSも存在します。

分析関数は、Oracle、DB2に続き、SQL Serverでもサポートされましたが、2005から
です。
1

スキルアップ／キャリアアップ（JOB@IT）