2-3-30 グループごとのメジアン(中央値)を取得

SQLパズル

MedianTable
key  seq  Val
---  ---  ---
  1    1   50
  1    2  100
  1    3  150
  1    4  200
  1    5  250
  2    1  300
  2    2  350
  2    3  400
  2    4  450
  3    1  500
  3    2  550
  4    1  600
  4    2  650
  4    3  700

keyごとの
Valのメジアン(中央値)を取得する。

出力結果
key  MedianVal
---  ---------
  1        150
  2        375
  3        525
  4        650

データ作成スクリプト

create table MedianTable as
select 1 as Key,1 as seq,50 as Val from dual
union select 1,2,100 from dual
union select 1,3,150 from dual
union select 1,4,200 from dual
union select 1,5,250 from dual
union select 2,1,300 from dual
union select 2,2,350 from dual
union select 2,3,400 from dual
union select 2,4,450 from dual
union select 3,1,500 from dual
union select 3,2,550 from dual
union select 4,1,600 from dual
union select 4,2,650 from dual
union select 4,3,700 from dual;

SQL

--■■■Median関数を使う方法(10g以降)■■■
select Key,Median(Val) as MedianVal
  from MedianTable
group by Key
order by Key;

--■■■percentile_cont関数を使う方法■■■
select Key,percentile_cont(0.5) within group(order by Val) as MedianVal
  from MedianTable
group by Key
order by Key;

--■■■分析関数を使う方法■■■
select key,avg(Val) as MedianVal
  from (select key,Val,
        count(*) over(partition by key) as RecordCount,
        Row_Number() over(partition by key order by Val) as Rank
          from MedianTable)
 where (mod(RecordCount,2) = 0 and Rank in(RecordCount/2,RecordCount/2+1))
    or (mod(RecordCount,2) = 1 and Rank = Ceil(RecordCount/2))
group by key
order by key;

解説

Median関数を使う方法が一番分かりやすいでしょうね

2-3-6 メジアン(中央値)を取得
2-3-31 標準SQLでメジアン(中央値)を取得

---