SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC 中央値(MEDIAN)

2016年2月7日 2025年4月22日 asai

asai

本日の話題もSQL分析関数である。
本日紹介したいのは、PERCENTILEである。パーセンタイル。あまり聞かない単語ではある。ちょっと検索してみたら、母子手帳にはパーセンタイルでグラフが載っているらしい。ふーん。ということは結構メジャーなものなのか。
前回紹介した、PERCENT_RANKは順位をパーセンテージで計算しました、っていうだけなので、パーセンタイルとはちょっと違う気もしないでもないが、同じかも？
どうも、順番に並べ替えて、パーセンテージでそのデータの位置を示したもの。というのがパーセンタイルらしいが...
まさに、PERCENT_RANKで計算できるような感じがするが。
分析関数としては、PERCENTILE_CONTとPERCENTILE_DISCの二つがある。どちらもパーセンタイル値を計算できるようなのだが、使い方が他の分析関数と比べて微妙に違う。
まず、OVER句ではなく、WITHIN GROUPでソート順を指定しないといけない。OVER句も付けることは可能ではあるが、PARTITION BYしか記述できない。さらに、関数に引数が必要なのである。PERCENT_RANKでは必要なかったのに... どうも勝手が違うなぁ、と思っていたら「やっぱり勘違い」していた。
PERCENTILE_CONT関数は、パーセンタイル値を計算してくれるのではなく、パーセンタイル値を引数で与えると、元の値が戻されるという関数なのだ。要は、PERCENT_RANKの逆関数みたいなものか。
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val)
とすると、val列でソートして計算したパーセンタイル値0.5に相当するval列の値を戻してくれる。
パーセンタイル値は0〜1.0の範囲外になることはない(試しに入れてみたらエラーになった)。
引数で0を与えれば、PERCENTILE_CONTは一番優秀とされたvalの値を戻す。1を与えれば逆に最低の成績を戻す。0.5ならちょうど真ん中の値を戻してくれる。という理屈である。
0.5を与えた際に戻ってくる値を「中央値」と呼ぶ。データ分析をする上では、中央値という言葉をよく耳にすると思う。
PERCENTILE_CONTの引数に0.5を入れることで中央値を計算できる。と覚えておこう。

SELECT val, PERCENT_RANK() OVER (ORDER BY val) RANK
FROM test_analyze2
val  RANK
---- ----
1    0
2 0.25
3  0.5
3  0.5
4    1
SELECT
PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY val) hi,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) mid,
PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY val) lo
FROM test_analyze2
hi   mid  lo
---- ---- ----
1    3    4

関連記事

　分析関数percentile_cont 中央値でノイズ除去

蛇足ながら、データ分析で使われる「なんとか値」をまとめてみたいと思う。
・最大値・最小値
まぁね。これは、わかるでしょう。SQLでは集計関数のMAXとMINで計算できる。
・合計値
集合のデータをすべて足し合わせた値。SQLではSUM。
・平均値
データの合計値を個数で割り算した値。SQLではAVGで計算可能。
・パーセンタイル値
あるデータのそれを含む集合の中での位置をパーセンテージで示した値。百分位。
SQLでは、PERCENT_RANKまたは、CUME_DISTで計算できる。
・中央値
パーセンタイル値が、0.5となるデータの値。集合の中でちょうど真ん中に位置するデータの値。
SQLでは、PERCENTILE_CONT、PERCENTILE_DISC、MEDIAN(メジアン)で計算できる。
・最頻値
集合の中で、最も頻繁に出現する数値。
これは、専用の関数は存在しないか...
PERCENTILE_DISC MEDIAN
中央値を計算するのに、PERCENTILE_CONTとPERCENTILE_DISC、さらにはMEDIANの3つが存在する。前の例では、PERCENTILE_CONTを使ったのだが、PERCENTILE_DISCの方が良かったかも知れない。PERCENTILE_CONTは補間された値を計算して戻す可能性がある。
MEDIANは、PERCENTILE_CONTの引数が0.5に固定された、いわばシンタックスシュガーな分析関数である。中央値の計算がしばしば行われるということの裏返しかも知れない。メジアンフィルターとかよく聞きませんか。メジアンフィルターはノイズ除去に使われる。photoshopなんかにもあるよねメジアンフィルター。
真ん中
PERCENTILE_CONTがどうやって補間をするのかを解説していこう。
そもそも、集合の中の真ん中って「いったいどこ」ということを考えていく。
補間の方法が集合全体の要素数が奇数か偶数かで変化する。
集合の要素数が、奇数のとき。例えば、以下の集合があったとして
　{1,2,3,4,5}
真ん中の値って、3ですわな。
集合の要素数が、偶数のとき。例えば、以下の集合だと
　{1,2,3,4}
真ん中って？
PERCENTILE_CONTでは、集合の全体数が奇数である場合と偶数である場合で、処理方法が異なる。奇数のときは、真ん中に対する値が存在するので、その値をそのまま戻す。
偶数のときは、真ん中に位置するふたつの値が存在するので、ふたつの値の真ん中を補間して戻す。
　{1,[2,3],4}
[2,3]が真ん中のふたつの値になる。2と3の中間を線形補間して、2.5を戻す。
PERCENTILE_DISCでも奇数と偶数で処理が異なるのは同じではある。偶数の場合真ん中のふたつの値が存在することになるが、補間をせず、最初の値をそのまま戻す。
　{1,[2,3],4}
[2,3]が真ん中のふたつの値になる。最初の値2を戻す。
サンプルを作って、検証してみよう。

CREATE TABLE test_analyze4 (
val INTEGER
);
INSERT INTO test_analyze4 VALUES(1);
INSERT INTO test_analyze4 VALUES(2);
INSERT INTO test_analyze4 VALUES(3);
INSERT INTO test_analyze4 VALUES(4);
SELECT
PERCENTILE_CONT(0.5) OVER (ORDER BY val) CONT,
PERCENTILE_DISC(0.5) OVER (ORDER BY val) DISC
FROM test_analyze4
CONT DISC
---- ----
2.5    2
-- もう一行作って奇数にしてみる
INSERT INTO test_analyze4 VALUES(5);
SELECT
PERCENTILE_CONT(0.5) OVER (ORDER BY val) CONT,
PERCENTILE_DISC(0.5) OVER (ORDER BY val) DISC
FROM test_analyze4;
CONT DISC
---- ----
3    3

ばっちり。

　分析関数percentile_cont 中央値でノイズ除去

　 SQL分析関数 ROW_NUMBER RANK DENSE_RANK

　SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD

　SQL分析関数 PERCENT_RANK パーセンタイル

　 SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC

　SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

　 WITH 再帰クエリその２