4. IDごとのValの順位（dense_Rank）を求める
最後は、IDごとValの順位(dense_Rank)を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。
IDTable
| ID | Seq | Val |
| AA | 1 | 100 |
| AA | 2 | 100 |
| AA | 3 | 500 |
| AA | 4 | 200 |
| AA | 5 | 200 |
| AA | 6 | 50 |
| BB | 1 | 200 |
| BB | 2 | 400 |
| BB | 3 | 800 |
| BB | 4 | 900 |
| CC | 1 | 100 |
| CC | 2 | 800 |
| CC | 3 | 700 |
| DD | 1 | 400 |
| EE | 1 | 50 |
| FF | 1 | 10 |
| FF | 3 | 20 |
| FF | 5 | 40 |
| FF | 6 | 80 |
IDごとValの順位(dense_Rank)を求めます。dense_Rankなので同点があっても、順位に歯抜けが発生しません。
言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。
分析関数を使ったSQL
select ID,Seq,Val, dense_Rank() over(partition by ID order by Val desc) as Rn from IDTable order by ID,Rn,Seq;
出力結果
| ID | Seq | Val | Rn |
| AA | 3 | 500 | 1 |
| AA | 4 | 200 | 2 |
| AA | 5 | 200 | 2 |
| AA | 1 | 100 | 3 |
| AA | 2 | 100 | 3 |
| AA | 6 | 50 | 4 |
| BB | 4 | 900 | 1 |
| BB | 3 | 800 | 2 |
| BB | 2 | 400 | 3 |
| BB | 1 | 200 | 4 |
| CC | 2 | 800 | 1 |
| CC | 3 | 700 | 2 |
| CC | 1 | 100 | 3 |
| DD | 1 | 400 | 1 |
| EE | 1 | 50 | 1 |
| FF | 6 | 80 | 1 |
| FF | 5 | 40 | 2 |
| FF | 3 | 20 | 3 |
| FF | 1 | 10 | 4 |
順位(dense_Rank)は、自分より高い得点の数（重複を除く）に1を加算したものだと考えて、下記が答えとなります。
答え
select ID,Seq,Val,
(select count(distinct b.Val)+1
from IDTable b
where b.ID = a.ID
and b.Val > a.Val) as Rn
from IDTable a
order by ID,Rn,Seq;
前問と似た考え方を使っていて、相関サブクエリでwhere b.ID = a.IDを指定して、外側のselect文の結果と同じIDを条件とし、さらに、Valが自分より大きいことを条件として、count関数にdistinctオプションを使用し、重複を除いた得点の数を求め、1を加算してます。結果としてIDごとValの順位(dense_Rank)が求まります。
SQLのイメージは下記となります。
SQLのイメージ
最後に
今回は、基本的な分析関数と同じ結果を取得するSQLを扱いました。次回の中編では、First_Value関数とLast_Value関数と同じ結果を取得するSQLを扱う予定です。
参考資料
- ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL』
本連載の原案となった記事です。
- ONLamp.com Stephane Faroult 『Emulating Analytic （AKA Ranking） Functions with MySQL: Part 2』
本連載の原案となった記事です。
- OracleSQLパズル 『累計を取得』
本稿の「2. IDごとのValの累計を求める」の類題と別解を置いてます。
-
OracleSQLパズル 『相関サブクエリで順位付け』
本稿の「3. IDごとのValの順位（Rank）を求める」と「4. IDごとのValの順位（dense_Rank）を求める」の類題と別解を置いてます。
