Hatena::ブログ(Diary)

CAMUSのこくばん −クエリの板書所− このページをアンテナに追加 RSSフィード

2006/01/05 特別な方法を利用しなくてもできる順位付けの方法の考え方

[] 特別な方法を利用しなくてもできる順位付けの方法の考え方*1

順位付けのクエリの書き方はRDBMSによってさまざまです。

Oracleでしたら、擬似列のROWNUMを利用したりCursorで1行ずつ連番をふったりする方法がポピュラーでしょう。

SQL Serverでしたら、こちらの記事に書いたとおり、一時テーブルとidentityのコラボレーションを利用するのが簡単でしょう。

…SQL Server2005だったら、専用関数があったような気もします。(^^;) …後で調べとこ。


そして、どうしてもクエリだけで、しかも製品に縛られない方法で…というリクエストもあるでしょう。

そのために、こんなサンプルがあります。


 SQLでグループ毎に連番・ランクを付与(見るにはOTNへの会員登録が必要です)


イコールでつなぐ結合条件がUniqueになれば一意に連番をふることができますが、Uniqueにならなければ「同着X位」ができてしまうという特徴のある方法です。

この手法、どういう仕組みかをきちんと抑えておけば累計を求めるという応用などにも利くので、どうしてこんな結果を得ることができるのか、ここで解説してみようと思います。

累計なんて帳票ソフトを使ったら一発で出るやんというツッコミはここでは置いておいてくらはい。(^^;)


例えば、こんなテーブル(TBL)があるとします。


NAMESCORE
AAAA20
BBBB40
CCCC30

このテーブルを使って1位から3位までを求めるクエリを書いてみましょう。

SELECT 
 A.NAME,
 A.SCORE,
 COUNT(B.SCORE)
FROM
 TBL A
 INNER JOIN TBL B 
  ON A.SCORE <= B.SCORE
GROUP BY A.NAME, A.SCORE
ORDER BY COUNT(B.SCORE)

正解は上記のとおりなのですが、その前にGROUP BYと集計関数を取った状態のクエリを書いてみましょう。

SELECT 
 A.NAME,
 A.SCORE,
 B.SCORE
FROM
 TBL A
 INNER JOIN TBL B 
  ON A.SCORE <= B.SCORE

このクエリの結果はこんな感じになります。

A.NAMEA.SCOREB.SCORE
AAAA2020
BBBB4020
BBBB4040
BBBB4030
CCCC3020
CCCC3030

A.NAMEがAAAAである行は1行、BBBBが3行、CCCCが2行あることがわかると思います。

この行数を数えると、順位が決まるというわけです。


この途中の状況が頭の中に浮かべられるようになると、こっちのものですネ。

とたんに応用が利くようになるわけです。


同じように、累計を考えるには、A.NAMEごとのB.SCOREの合計をA.SCORE順に並べると、SCORE順に累計が表示されます。

もし、A.NAME順にしたいのであれば、結合条件の A.SCORE<=B.SCORE をA.NAME<=B.NAMEに変更すればよいだけです。


さぁ、これであなたも連番&累計マスターだ〜♪

…なんて書いてみたけど、そんな保障はありません。(^^;)


長いことここをほったらかしにしていてスミマセンでした。

こんな感じではありますが、今年もゆるゆると更新していきたいと思います。

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

追記

順位というのは自分より点数が高い人の数に1足したものであるという考え方で、

count(distinct 点数)+1 とすればdenseな順位になります

参考サイト

http://blogs.sqlpassj.org/yoshihirokawabata/archive/2004/05/13/2216.aspx

http://oraclesqlpuzzle.hp.infoseek.co.jp/6-3.html

というご意見をコメントにて頂戴しました。

上の例をこれに当てはめると、こういう形になりますね。

SELECT 
 A.NAME,
 A.SCORE,
 (SELECT COUNT(B.SCORE)+1
  FROM TBL B
  WHERE A.SCORE < B.SCORE) RANK
FROM
 TBL A
ORDER BY A.SCORE

ということで、情報ありがとうございました。

*1:この記事はDoblogのこちらの記事を元にはてな用に書き直したものです。

とおりがかりとおりがかり 2006/01/06 09:45 他の、製品に依存しない方法としては、
select句で相関サブクエリでカウントを求めるという方法がありますね

順位というのは自分より点数が高い人の数に1足したものであるという考え方で、
count(distinct 点数)+1 とすればdenseな順位になります

参考サイト
http://blogs.sqlpassj.org/yoshihirokawabata/archive/2004/05/13/2216.aspx
http://oraclesqlpuzzle.hp.infoseek.co.jp/6-3.html

CAMUSCAMUS 2006/01/08 11:16 とおりがかりさま
いつも情報ありがとうございます。
そうですね。相関サブクエリを使う手を忘れていました。(^^;)
相関サブクエリのほうがストレスが低そうな気がしますのでいいかもしれません。

投稿したコメントは管理者が承認するまで公開されません。

トラックバック - http://d.hatena.ne.jp/CAMUS/20060105/1136469086