分析関数の衝撃

MySQLで分析関数を模倣5（応用編）

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

山岸賢治[著]

2009/03/19 14:00

ポスト

ダウンロード SourceCode (4.9 KB)

2. メジアン（中央値）を求める

　次に、メジアン（中央値）を求めるSQLについてです。まずは、テーブルのデータと、出力結果を考えます。

MedianTable

ID	Val
AA	100
AA	200
AA	300
AA	400
AA	500
AA	600
BB	200
BB	200
BB	300
CC	333
CC	333
DD	444
EE	100
EE	500
EE	700
FF	10
FF	10
FF	50
FF	50
FF	50
FF	50
FF	100
FF	100
FF	150
FF	150
FF	150
FF	200

　IDごとのValのメジアンを求めます。言いかえれば、Oracleの下記の分析関数を使ったSQLと同じ結果を取得します。

分析関数を使ったSQL

select ID,Val,
median(Val) over(partition by ID) as medianVal
  from MedianTable
order by ID,Val;

出力結果

ID	Val	medianVal
AA	100	350
AA	200	350
AA	300	350
AA	400	350
AA	500	350
AA	600	350
BB	200	200
BB	200	200
BB	300	200
CC	333	333
CC	333	333
DD	444	444
EE	100	500
EE	500	500
EE	700	500
FF	10	75
FF	10	75
FF	50	75
FF	50	75
FF	50	75
FF	50	75
FF	100	75
FF	100	75
FF	150	75
FF	150	75
FF	150	75
FF	200	75

　メジアンを求めるSQLは難しい部類ですが、順を追って考えていきましょう。メジアンは、対象件数が6件（偶数）なら、ソートキーで順位付けした時の3位と4位の相加平均です。対象件数が5件（奇数）なら、ソートキーで順位付けした時の3位の値がメジアンです。

　対象件数は、相関サブクエリで求めることができますし、ソートキーで順位付けした時の順位もサブクエリで求めることができます。そして、対象件数が偶数か奇数かで条件分岐のロジックを使えばよさそうです。以上をふまえて、答えは下記となります。

相関サブクエリを使うSQL

select a.ID,a.Val,b.medianVal
from MedianTable a,
     (select ID,avg(Val) as MedianVal
       from (select ID,Val
               from (select ID,Val,
                     (select count(*)+1 from MedianTable b
                       where b.ID=a.ID
                         and b.Val > a.Val) as Rn,
                     (select count(*) from MedianTable b
                       where b.ID=a.ID) as SameIDCnt
                     from MedianTable a)
             group by ID,Val,Rn,SameIDCnt
             having mod(SameIDCnt,2) = 0
                and (SameIDCnt/2  between Rn and Rn+count(*)-1
                 or SameIDCnt/2+1 between Rn and Rn+count(*)-1)
                 or mod(SameIDCnt,2) = 1
                and ceil(SameIDCnt/2) between Rn and Rn+count(*)-1)
       group by ID) b
 where a.ID = b.ID
order by a.ID,a.Val;

　まずは、内側のクエリにorder byをつけた実行結果について考えてみましょう。

内側のクエリ

select ID,Val
  from (select ID,Val,
        (select count(*)+1 from MedianTable b
          where b.ID=a.ID
            and b.Val > a.Val) as Rn,
        (select count(*) from MedianTable b
          where b.ID=a.ID) as SameIDCnt
        from MedianTable a)
group by ID,Val,Rn,SameIDCnt
having mod(SameIDCnt,2) = 0
   and (SameIDCnt/2  between Rn and Rn+count(*)-1
    or SameIDCnt/2+1 between Rn and Rn+count(*)-1)
    or mod(SameIDCnt,2) = 1
   and ceil(SameIDCnt/2) between Rn and Rn+count(*)-1
order by ID,Val;

出力結果

ID	Val
AA	300
AA	400
BB	200
CC	333
DD	444
EE	500
FF	50
FF	100

　相関サブクエリで、同じID内でのValの順位を求め、列別名をRnとしています。また、相関サブクエリで、同じID内での件数を求め、列別名をSameIDCntとしています。そして、having句で、SameIDCntが偶数か奇数かで場合分けをしています。

　なお、between Rn and Rn+count(*)-1は、Valの重複値を考慮して、Rnが順位の開始値で、Rn+count(*)-1が順位の終了値と考えて、メジアンの計算対象かを判断しています。下記のSQLの結果を見ると分かりやすいでしょう。

メジアンの計算対象かを判断

select ID,Val,
count(*) as cnt,Rn as kaisi,Rn+count(*)-1 as syuuryo
  from (select ID,Val,
        (select count(*)+1 from MedianTable b
          where b.ID=a.ID
            and b.Val > a.Val) as Rn,
        (select count(*) from MedianTable b
          where b.ID=a.ID) as SameIDCnt
        from MedianTable a)
group by ID,Val,Rn,SameIDCnt
order by ID,Val;

出力結果

ID	Val	cnt	kaisi	syuuryo
AA	100	1	6	6
AA	200	1	5	5
AA	300	1	4	4
AA	400	1	3	3
AA	500	1	2	2
AA	600	1	1	1
BB	200	2	2	3
BB	300	1	1	1
CC	333	2	1	2
DD	444	1	1	1
EE	100	1	3	3
EE	500	1	2	2
EE	700	1	1	1
FF	10	2	11	12
FF	50	4	7	10
FF	100	2	5	6
FF	150	3	2	4
FF	200	1	1	1