順位付けするSQL

/ db

SQLでテーブルのデータに順位付けするのって、どうやるんだろう。ということで、ちょっとばかし考えてみた。

例として、次のような商品テーブルがあって、その価格が高い方から順に、1位、2位…と順位付けするケースを考えてみる。サンプルに深い意味はないです。

idnameprice
1FOMA F702iD19200
2FOMA F902i24800
3FOMA N701i12350
4FOMA P901iS22560
5FOMA P901iTV32400
6FOMA SH702iD19740
7FOMA SH901iS17690
8FOMA SH902iS31220
9FOMA SO702i16600
10FOMA SIMPURE N3680
11FOMA N902i24100

順位付けには、次のようなSQLを考えてみた。

SELECT 
    COALESCE(rank, 1) AS rank,
    name,
    price
FROM
    product
LEFT OUTER JOIN (
    SELECT
       (count(*) + 1) AS rank,
       p1.id
    FROM product AS p1
    CROSS JOIN product AS p2
    WHERE p2.price>p1.price
    GROUP BY p1.id
) AS rank_table
USING (id)
ORDER BY rank

もし価格が同じ商品があった場合は、順位タイということで、同じ順番が振られる。なので、ユニークな順位が求められるケースでは、ちょいと工夫が必要です。

できるにはできたんだけど、使うことは無さそうだなあ。相当数あるときは、CROSS JOIN でパフォーマンスが落ちそうだし、JavaなりPHPなり別言語でやった方がラクチンだし。もっと効率の良い方法があれば、嬉しいんだけど。

このエントリーのトラックバックURL
http://www.deftrash.com/admin/mt4/mt-tb.cgi/336