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

SQLパズル

中央値テーブル
Col1
----
   1
   5
  15
  20

中央値テーブルのCol1の中央値を取得する。

出力結果
中央値
------
    10

データ作成スクリプト

create table 中央値テーブル(Col1 number(2));

insert into 中央値テーブル values(1);
insert into 中央値テーブル values(5);
insert into 中央値テーブル values(15);
insert into 中央値テーブル values(20);
commit;

SQL

--■■■分析関数を使う方法■■■
select avg(Col1) as 中央値
from (select count(*) over() as RecordCount,
      Col1,
      Row_Number() over(order by Col1) as Rank
      from 中央値テーブル)
where (mod(RecordCount,2) = 0 and Rank in(RecordCount/2,RecordCount/2+1))
   or (mod(RecordCount,2) = 1 and Rank = Ceil(RecordCount/2));

--■■■Median関数を使う方法(10g以降)■■■
select Median(Col1) as 中央値
  from 中央値テーブル;

--■■■percentile_cont関数を使う方法■■■
select percentile_cont(0.5) within group(order by Col1) as 中央値
  from 中央値テーブル;

--■■■逆ソートを使う方法(分析関数版)■■■
select avg(Col1) as 中央値
from (select Col1,
      Row_Number() over(order by Col1 asc ,RowID asc ) as Rank,
      Row_Number() over(order by Col1 desc,RowID desc) as RevRank
        from 中央値テーブル)
where abs(Rank - RevRank) <= 1;

--■■■逆ソートを使う方法(相関サブクエリ版)■■■
select avg(Col1) as 中央値
  from 中央値テーブル a
 where (select
          abs(sum(case when a.Col1  < b.Col1
                         or (a.Col1 = b.Col1 and a.RowID <= b.RowID)
                       then 1 else 0 end)
            - sum(case when a.Col1  > b.Col1
                         or (a.Col1 = b.Col1 and a.RowID >= b.RowID)
                       then 1 else 0 end))
          from 中央値テーブル b) <= 1;

--■■■正順位 + 逆順位 = 件数 + 1 を移項した式を使う方法(分析関数を使用)■■■
select avg(Col1) as 中央値
from (select Col1,
      Row_Number() over(order by Col1) as Rank,
      count(*) over() as RecordCount
        from 中央値テーブル)
where abs(2*Rank - RecordCount -1) <= 1;

--■■■正順位 + 逆順位 = 件数 + 1 を移項した式を使う方法(分析関数を使用せず)■■■
select avg(a.Col1) as 中央値
  from (select Col1,RowNum as Rank
          from (select Col1 from 中央値テーブル
                order by Col1)) a,
       (select count(*) as RecordCount from 中央値テーブル) b
where abs(2*Rank - RecordCount -1) <= 1;

解説

分析関数を使う方法では、
件数が偶数の場合と、奇数の場合とで、
対象となるレコードを分岐させています。

■■■■■■■■■■■■■■■■■■■■■■■■■■■
逆ソートを使う方法では、この図をイメージしてます。

偶数の場合
Col1  Rank  RevRank  abs(Rank-RevRank)
----  ----  -------  -----------------
  10     1        6     5
  10     2        5     3
  20     3        4     1
  20     4        3     1
  30     5        2     3
  30     6        1     5

奇数の場合
Col1  Rank  RevRank  abs(Rank-RevRank)
----  ----  -------  -----------------
  10     1        5     4
  10     2        4     2
  20     3        3     0
  20     4        2     2
  30     5        1     4

■■■■■■■■■■■■■■■■■■■■■■■■■■■
正順位 + 逆順位 = 件数 + 1
移項して
逆順位 = 件数 + 1 - 正順位

よって
正順位 - 逆順位
=正順位 - (件数 + 1 - 正順位)
= 2*正順位 - 件数 - 1

を使う方法もあります。

---

マニュアル(Median関数)(英語)
マニュアル(Median関数)

CodeZine：HAVING句の力
CodeZine：分析関数の衝撃（前編）

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

---