SQL練習室
← SQLガイド

SQLで中央値を求めるには?PERCENTILE_CONTの使い方・PERCENTILE_DISCとの違い

PERCENTILE_CONTは、並べた値の指定した位置(0.5なら真ん中)の値を返す関数で、SQLで中央値を求める標準的な方法です。AVGと違い、極端に大きい値の影響を受けません。社員給与を使い、書き方と平均との違いを確認しましょう。

例で使う表

staffは1行が社員1人です。deptは部署、salaryは月給(円)で、開発の1人は未入力でNULLです。営業には1,500,000円という外れ値があります。営業は5人、開発は給与のある4人(NULL除く)で、NULLは計算の対象に含まれません。

staff:社員10人(salaryは月給・円)
iddeptsalary
1営業250000
2営業300000
3営業300000
4営業320000
5営業1500000
6開発400000
7開発420000
8開発450000
9開発500000
10開発NULL

中央値・PERCENTILE_CONTの基本構文

sql
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary)
FROM staff;

WITHIN GROUPで中央値(0.5)を求める

percentile_cont(割合) WITHIN GROUP (ORDER BY 列)の形で書きます。割合は0〜1で、0.5が中央値です。並べ替えの列は括弧の外のWITHIN GROUPの中に書きます。NULLは無視されるので、給与のある9人の5番目の値、400000円が返ります。

sql
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary)
FROM staff;
実行結果
percentile_cont
400000

偶数件では隣り合う2値の間を補間する

開発の給与は4件(400000、420000、450000、500000)です。真ん中は2番目と3番目の間にあたるため、PERCENTILE_CONTは420000と450000の間を補間して435000を返します。実在しない値が結果に出ることがある点に注意します。

sql
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary)
FROM staff
WHERE dept = '開発';
実行結果
percentile_cont
435000

PERCENTILE_DISCは補間せず実在する値を返す

PERCENTILE_DISCは、指定した割合以上になる最初の値を返し、補間しません。開発の0.5では420000です。実在する値だけを返したいときはDISC、なめらかな中央値が欲しいときはCONTを使います。ORDER BYの対象は数値以外(日付や文字列)でもDISCなら使えます。

sql
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS cont,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY salary) AS disc
FROM staff
WHERE dept = '開発';
実行結果
contdisc
435000420000

GROUP BYで部署ごとの中央値を求める

GROUP BYと組み合わせると、グループごとの中央値が出ます。営業は5件の3番目の300000円、開発は435000円です。1文で複数グループの中央値を比べられます。

sql
SELECT dept, percentile_cont(0.5) WITHIN GROUP (ORDER BY salary)
FROM staff
GROUP BY dept
ORDER BY min(id);
実行結果
deptpercentile_cont
営業300000
開発435000

平均と中央値の違いを外れ値で確認する

営業は1,500,000円の外れ値があるため、平均は534000円まで引き上げられますが、中央値は300000円のままです。開発は外れ値がなく、平均442500円と中央値435000円が近い値です。分布が偏るデータでは、平均だけでは「ふつうの値」を表せません。

sql
SELECT dept,
       avg(salary) AS average,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM staff
GROUP BY dept
ORDER BY min(id);
実行結果
deptaveragemedian
営業534000300000
開発442500435000

MODEで最も多い値(最頻値)を求める

mode() WITHIN GROUP (ORDER BY 列)は、最も多く現れる値を返します。300000円は2人いるので、他の値より多く、結果は300000です。同数の値が複数あるときは、そのうちの1つが返されます。

sql
SELECT mode() WITHIN GROUP (ORDER BY salary)
FROM staff;
実行結果
mode
300000

実際に1問解いてみる

PERCENTILE_CONT

全社員の給与の中央値を取得してください。別名は付けません。給与が未入力(NULL)の社員は計算に含まれません。

テーブル

読み込み中…

実行結果

関連構文

中央値・PERCENTILE_CONTについてよくある質問

AVGと同じようにOVERでウィンドウ関数として使えますか?
PERCENTILE_CONTはWITHIN GROUPを使う順序集合集約で、OVERを付けるウィンドウ関数としては使えません。GROUP BYの集約として使います。
NULLの行はどう扱われますか?
並べ替えの対象列がNULLの行は無視されます。開発のNULLの1人は計算に含まれず、4件で中央値を求めます。
割合に0〜1以外を指定するとどうなりますか?
エラーになります。0.5が中央値、0.25と0.75が四分位数、0が最小、1が最大です。
PERCENTILE_DISCとMODEの違いは?
PERCENTILE_DISCは順位の位置で値を選びます。MODEは最も多く現れる値を選びます。この表の全体では、DISCの0.5は400000、MODEは300000です。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。