はじめに
データ分析において、**中央値（メディアン）**は平均値と並んで重要な統計指標です。外れ値の影響を受けにくく、データの中心的傾向をより正確に表現できる場合があります。
SQLで中央値を計算する方法はいくつかありますが、PostgreSQLでは**PERCENTILE_CONT(0.5)**を使うことで簡潔に中央値を求めることができます。
本記事では、この関数の使い方を詳しく解説します。
PERCENTILE_CONT関数とは
基本概念
PERCENTILE_CONTは「Percentile Continuous」の略で、連続分布におけるパーセンタイル値を計算する関数です。
PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY column_name)
パラメータ：
-
percentile：0.0〜1.0の値（0.5 = 50パーセンタイル = 中央値） -
column_name：計算対象の列
なぜ0.5なのか？
- 0.0 = 0パーセンタイル（最小値）
- 0.25 = 25パーセンタイル（第1四分位数）
- 0.5 = 50パーセンタイル（中央値）
- 0.75 = 75パーセンタイル（第3四分位数）
- 1.0 = 100パーセンタイル（最大値）
基本的な使い方
シンプルな中央値計算
-- 全体の売上金額の中央値
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM sales;
グループ別の中央値
-- 店舗別の売上金額中央値
SELECT store_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM sales
GROUP BY store_id
ORDER BY median_amount DESC;
実践例
サンプルデータの準備
-- テストデータの作成
CREATE TABLE sales_sample AS
SELECT
'A' as store_cd, 1000 as amount UNION ALL
SELECT 'A', 1500 UNION ALL
SELECT 'A', 2000 UNION ALL
SELECT 'A', 2500 UNION ALL
SELECT 'A', 10000 UNION ALL -- 外れ値
SELECT 'B', 800 UNION ALL
SELECT 'B', 900 UNION ALL
SELECT 'B', 1100 UNION ALL
SELECT 'B', 1200;
平均値 vs 中央値の比較
SELECT store_cd,
AVG(amount) AS average_amount,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
AVG(amount) - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS difference
FROM sales_sample
GROUP BY store_cd;
結果例：
store_cd | average_amount | median_amount | difference
---------|----------------|---------------|------------
A | 3400.0 | 2000.0 | 1400.0
B | 1000.0 | 1000.0 | 0.0
店舗Aでは外れ値（10,000）により平均値が大きく影響を受けていますが、中央値は安定しています。
PERCENTILE_CONTとPERCENTILE_DISCの違い
PERCENTILE_CONT（連続分布）
-- 連続分布：値の間を線形補間
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_cont
FROM (VALUES (100), (200), (300), (400)) AS t(amount);
-- 結果: 250.0（200と300の中間値）
PERCENTILE_DISC（離散分布）
-- 離散分布：実際の値から選択
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_disc
FROM (VALUES (100), (200), (300), (400)) AS t(amount);
-- 結果: 200（実際の値）
どちらを使うべきか？
| 用途 | 推奨 | 理由 |
|---|---|---|
| 一般的な中央値計算 | PERCENTILE_CONT | より正確な統計値 |
| 実際の値が重要 | PERCENTILE_DISC | データに存在する値 |
| データ分析・統計 | PERCENTILE_CONT | 統計学的に標準 |
高度な活用例
四分位数の一括計算
SELECT store_cd,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3,
-- IQR（四分位範囲）の計算
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) -
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS iqr
FROM sales
GROUP BY store_cd;
外れ値の検出
WITH quartiles AS (
SELECT
amount,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) OVER() AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) OVER() AS q3
FROM sales
),
outlier_bounds AS (
SELECT
amount,
q1,
q3,
q1 - 1.5 * (q3 - q1) AS lower_bound,
q3 + 1.5 * (q3 - q1) AS upper_bound
FROM quartiles
)
SELECT amount,
CASE
WHEN amount < lower_bound OR amount > upper_bound
THEN 'outlier'
ELSE 'normal'
END AS outlier_flag
FROM outlier_bounds;
売上分布の分析
-- 売上の分布を10分位で分析
SELECT
PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY amount) AS p10,
PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY amount) AS p20,
PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY amount) AS p30,
PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY amount) AS p40,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS p50_median,
PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY amount) AS p60,
PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY amount) AS p70,
PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY amount) AS p80,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) AS p90
FROM sales;
他のデータベースでの中央値
MySQL（8.0以降）
-- MySQLでも同様の構文
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales;
SQL Server
-- SQL Serverの場合
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER() AS median
FROM sales;
Oracle
-- Oracleの場合
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales;
パフォーマンスの考慮事項
インデックスの活用
-- ORDER BY句で使用する列にインデックスを作成
CREATE INDEX idx_sales_amount ON sales(amount);
-- 複合インデックスでGROUP BYも最適化
CREATE INDEX idx_sales_store_amount ON sales(store_cd, amount);
大量データでの工夫
-- サンプリングを使った近似計算（大量データ用）
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS approx_median
FROM (
SELECT amount
FROM sales
TABLESAMPLE SYSTEM (10) -- 10%サンプリング
) AS sample_data;
よくあるエラーと対処法
エラー1: NULL値の扱い
-- NULL値は自動的に除外される
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales; -- NULL値があっても正常動作
-- 明示的にNULL除外したい場合
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales
WHERE amount IS NOT NULL;
エラー2: GROUP BY句での使用
-- ❌ 間違い：GROUP BY句なしでの集約
SELECT store_cd,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales;
-- ✅ 正しい：GROUP BY句を使用
SELECT store_cd,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales
GROUP BY store_cd;
まとめ
**PERCENTILE_CONT(0.5)**は、SQLで中央値を求める最も効率的で正確な方法です。
主なメリット
- 標準SQLに準拠した関数
- 外れ値に強い統計指標
- グループ別計算が簡単
- 他のパーセンタイルも同時に計算可能
使い分けのポイント
- 一般的な統計分析：PERCENTILE_CONT
- 実データ値が重要：PERCENTILE_DISC
- 平均値と併用：データの偏りを検出
- 四分位数計算：外れ値検出やボックスプロット
データサイエンス100本ノックのようなデータ分析学習においても、この関数をマスターすることで、より深い洞察を得られるようになります。平均値だけでなく中央値も活用して、データの真の姿を読み解きましょう！