2-3-33 文字列の中央値(メジアン)のような値

SQLパズル

文字列の中央値(メジアン)のような値を求める。

5個の場合は、3番目
stringMedianFiveテーブル
Val
---
AAA
AAB
ABB  ← 出力対象
CAA
EAA

6個の場合も、3番目
stringMedianSixテーブル
Val
---
AAA
AAB
ABB  ← 出力対象
CAA
EAA
FGH

データ作成スクリプト

create table stringMedianFive(Val) as
select 'AAAXX' from dual union
select 'AABXX' from dual union
select 'ABBXX' from dual union
select 'CAAXX' from dual union
select 'EAAXX' from dual;

create table stringMedianSix(Val) as
select 'AAAYY' from dual union
select 'AABYY' from dual union
select 'ABBYY' from dual union
select 'CAAYY' from dual union
select 'EAAYY' from dual union
select 'FGHYY' from dual;

SQL

--■■■分析関数を使う方法(10g以降)■■■
select Val as medianVal
from (select Val,Rn,median(Rn) over() as medianRn
      from (select Val,Row_Number() over(order by Val) as Rn
            from stringMedianFive))
 where Rn = floor(medianRn);

select Val as medianVal
from (select Val,Rn,median(Rn) over() as medianRn
      from (select Val,Row_Number() over(order by Val) as Rn
            from stringMedianSix))
 where Rn = floor(medianRn);

--■■■標準SQLを意識した方法■■■
select max(decode(Rn,ceil(RecordCount/2),Val)) as medianVal
  from (select count(*) as RecordCount from stringMedianFive),
       (select Val,(select count(*)+1
                      from stringMedianFive b
                     where b.Val < a.Val) as Rn
          from stringMedianFive a);

select max(decode(Rn,ceil(RecordCount/2),Val)) as medianVal
  from (select count(*) as RecordCount from stringMedianSix),
       (select Val,(select count(*)+1
                      from stringMedianSix b
                     where b.Val < a.Val) as Rn
          from stringMedianSix a);

解説

標準SQLを意識した方法では、
順位の最大値を2で割って切り上げた順位を持つ値を
求めてます。

---