SQLで特定の列の上位3件のデータを取得したい

Question

環境：PostgreSQL 9.6

商品ごとに対して値段を高い順番に並べ、上位3件を取得したいです。

以下のデータに対して、
商品 値段
A 100
A 80
A 130
A 110
A 50
B 70
B 80
B 90
B 140
B 60
以下のデータを取得したいです。
商品 値段
A 130
A 110
A 100
B 140
B 90
B 80
または、
商品 値段1 値段2 値段3
A 130 110 100
B 140 90 80

例としては2商品で5件づつしかないですが、
実際は何百商品に対して、100件以上あります。

よろしくお願い致します。

Comments

[yambejp]

商品と値段の組み合わせはユニークが確保されているのでしょうか？もしそうでない場合は同順位の処理が必要です。また商品はかならず3つ以上存在するのでしょうか？最終的にソート順は商品名(ASC)、値段（DESC）？

[jess2pg]

商品と値段の組み合わせはユニークではありませんが、4件以上取得出来ても問題ありません。今のところ商品は3つ以上存在しますが、2つ以下だったとしても問題無く行いたいと思います。ソート順はご指摘の通りです。

Answer 1

Window関数（分析関数）を使用するとシンプルに実現できます。Window関数ではPARTITION BYで指定したグループごとに順序づけや集約関数の適用を行うことができます。GROUP BYとは異なり結果行そのものは集約せずに取り出すことが可能です。

SELECT
    *
FROM
    (
        SELECT
            shouhin
            ,nedan
            ,RANK() OVER (PARTITION BY shouhin ORDER BY nedan DESC) AS junban
        FROM
            table01
    )
WHERE
    junban <= 3
ORDER BY
    shouhin
    ,junban

このSQLでは同順位があると4件以上出力される場合があります。ちょうど3件ずつにしたい場合はRANK()をROW_NUMBER()に変更してください。

3.5. ウィンドウ関数

Comments

[jess2pg]

ありがとうございました。自分のイメージ通りに出来ました。自分なりに色々とSQLを作っていたのですが、複雑なうえに遅いため困っていました。

Answer 2

一般的な処理だとこんか感じでもいけるかもしれませんね

select *
from tbl as t1
having (
select count(*)+1 from tbl as t2 
where t1.商品=t2.商品 and t1.値段<t2.値段)<=3
order by 商品 asc,値段 desc