2-3-32 メジアン(中央値)を取得(分析関数版)

SQLパズル

OLAPMedianTable
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ごとの中央値(メジアン)を求める

出力結果
key  seq  Val  MedianVal
---  ---  ---  ---------
  1    1   50        150
  1    2  100        150
  1    3  150        150
  1    4  200        150
  1    5  250        150
  2    1  300        375
  2    2  350        375
  2    3  400        375
  2    4  450        375
  3    1  500        525
  3    2  550        525
  4    1  600        650
  4    2  650        650
  4    3  700        650

データ作成スクリプト

create table OLAPMedianTable(Key,seq,Val) as
select 1,1, 50 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,seq,Val,
median(Val) over(partition by key) as OLAPMedian
  from OLAPMedianTable;

--■■■分析関数のpercentile_cont関数を使う方法■■■
select Key,seq,Val,
percentile_cont(0.5) within group(order by Val) over(partition by Key) as OLAPMedian
  from OLAPMedianTable;

--■■■分析関数を使う方法■■■
select key,seq,Val,
avg(case when (mod(RecordCount,2) = 0 and Rank in(RecordCount/2,RecordCount/2+1))
           or (mod(RecordCount,2) = 1 and Rank = Ceil(RecordCount/2))
         then Val end) over(partition by key) as OLAPMedian
  from (select key,seq,Val,
        count(*) over(partition by key) as RecordCount,
        Row_Number() over(partition by key order by Val) as Rank
          from OLAPMedianTable)
order by key;

--■■■スカラーサブクエリを使う方法1(10g以降)■■■
select Key,seq,Val,
(select Median(b.Val)
   from OLAPMedianTable b
  where b.key = a.key) as OLAPMedian
from OLAPMedianTable a
order by Key,seq;

--■■■スカラーサブクエリを使う方法2■■■
select Key,seq,Val,
(select percentile_cont(0.5) within group(order by Val)
   from OLAPMedianTable b
  where b.key = a.key) as OLAPMedian
from OLAPMedianTable a
order by Key,seq;

--■■■標準SQLで求める方法■■■
select aa.key,aa.seq,aa.Val,bb.OLAPMedian
  from OLAPMedianTable aa,
       (select key,avg(Val) as OLAPMedian
          from (select key,Val,
                (select count(*)
                   from OLAPMedianTable b
                  where b.key = a.key) as RecordCount,
                (select count(*)+1
                   from OLAPMedianTable b
                  where b.key = a.key
                    and b.Val > a.Val) as Rank
                  from OLAPMedianTable a)
           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) bb
 where aa.key = bb.key
order by aa.key,aa.seq;

解説

標準SQLで求める方法では、
集約した形でメジアンを求める2-3-30 グループごとのメジアン(中央値)を取得
の分析関数を使う方法を、標準SQLに変形したのものを、インラインビューで使い、
内部結合させてます。

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

2-3-6 メジアン(中央値)を取得

---