分析関数の衝撃

PostgreSQLの分析関数の衝撃1
（モードとメジアン）

PostgreSQLの基本的なwindow関数の使用例

山岸賢治[著]

2009/08/04 14:00

ポスト

ダウンロード SourceCode (1.1 KB)

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

　今度は中央値（メジアン）を求めます。『HAVING句の力』で提示されている以下のSQLを、window関数を使って書き換えてみます。

メジアンを求めるSQL：自己非等値結合をHAVING句で使う

SELECT AVG(DISTINCT income)
  FROM (SELECT T1.income
          FROM Graduates T1, Graduates T2
        GROUP BY T1.income
               --S1の条件
        HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)
                   >= COUNT(*) / 2.0
               --S2の条件
           AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)
                   >= COUNT(*) / 2.0 ) a;

　まずは、データの具体的な例を挙げて考えます。なお、incomeの順位を持つRn列も付与して考えます。

case1         case2
income   Rn   income   Rn
-------  --   -------  --
 10,000   1    10,000   1
 10,000   2    10,000   2
 10,000   3    10,000   3
 15,000   4    15,000   4
 15,000   5    15,000   5
 20,000   6
 20,000   7
 20,000   8
 30,000   9
400,000  10

　case1が、データ件数が偶数の場合で、中央値は、(15000+20000) / 2 = 17500です。case2が、データ件数が奇数の場合で、中央値は、10000です。

　まとめると、データ件数が偶数なら、(データ件数 / 2)番目と(データ件数 / 2 +1)番目の、平均が中央値です。データ件数が奇数なら、(データ件数 / 2 + 0.5)番目が中央値ですので、答えは、下記となります。

window関数で書き換えたSQL

select avg(income)
from (select income,
      Row_Number() over(order by income) as Rn,
      count(*) over() as DataCount
      from Graduates) a
where (mod(DataCount,2) = 0 and Rn in(DataCount/2,DataCount/2+1))
   or (mod(DataCount,2) = 1 and Rn = Ceil(DataCount/2.0));

　インラインビューの中で、Row_Number関数で順位、count関数でデータ件数を求めてます。そして、データ件数が偶数か奇数かでwhere句の条件を分岐させてます。

　なお、PostgreSQLでは、整数同士を/演算子を使って割り算すると、余りを切り捨ててしまうので、2.0で割ってます。