MySQLで分析関数を模倣1（前編）

MySQLで、Oracleの分析関数と同じ結果を取得する1

3. IDごとのValの順位（Rank）を求める

　次は、IDごとValの順位(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の順位(Rank)を求めます。同点があったら、その分だけ順位に歯抜けが発生します。

　言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

select ID,Seq,Val,
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	4
AA	2	100	4
AA	6	50	6
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

　順位は、自分より高得点の人の数に1を加算したものだと考えて、下記が答えとなります。

答え

select ID,Seq,Val,
(select count(*)+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関数で件数を求めて、1を加算してます。結果としてIDごとValの順位(Rank)が求まります。

　SQLのイメージは下記となります。

SQLのイメージ