【SQL】PERCENTILE_CONT(0.5)で中央値を求める


## はじめに

データ分析において、**中央値（メディアン）**は平均値と並んで重要な統計指標です。外れ値の影響を受けにくく、データの中心的傾向をより正確に表現できる場合があります。

SQLで中央値を計算する方法はいくつかありますが、PostgreSQLでは**PERCENTILE_CONT(0.5)**を使うことで簡潔に中央値を求めることができます。
本記事では、この関数の使い方を詳しく解説します。

## PERCENTILE_CONT関数とは

### 基本概念

**PERCENTILE_CONT**は「**Percentile Continuous**」の略で、**連続分布におけるパーセンタイル値**を計算する関数です。

```sql
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パーセンタイル（最大値）

## 基本的な使い方

### シンプルな中央値計算

```sql
-- 全体の売上金額の中央値
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM sales;
```

### グループ別の中央値

```sql
-- 店舗別の売上金額中央値
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;
```

## 実践例：データサイエンス100本ノック風

### サンプルデータの準備

```sql
-- テストデータの作成
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 中央値の比較

```sql
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（連続分布）

```sql
-- 連続分布：値の間を線形補間
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（離散分布）

```sql
-- 離散分布：実際の値から選択
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 | 統計学的に標準 |

## 高度な活用例

### 四分位数の一括計算

```sql
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;
```

### 外れ値の検出

```sql
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;
```

### 売上分布の分析

```sql
-- 売上の分布を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以降）

```sql
-- MySQLでも同様の構文
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales;
```

### SQL Server

```sql
-- SQL Serverの場合
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER() AS median
FROM sales;
```

### Oracle

```sql
-- Oracleの場合
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM sales;
```

## パフォーマンスの考慮事項

### インデックスの活用

```sql
-- ORDER BY句で使用する列にインデックスを作成
CREATE INDEX idx_sales_amount ON sales(amount);

-- 複合インデックスでGROUP BYも最適化
CREATE INDEX idx_sales_store_amount ON sales(store_cd, amount);
```

### 大量データでの工夫

```sql
-- サンプリングを使った近似計算（大量データ用）
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値の扱い

```sql
-- 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句での使用

```sql
-- ❌ 間違い：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本ノックのようなデータ分析学習においても、この関数をマスターすることで、より深い洞察を得られるようになります。平均値だけでなく中央値も活用して、データの真の姿を読み解きましょう！

## 参考リンク

- [PostgreSQL公式ドキュメント - 集約関数](https://www.postgresql.org/docs/current/functions-aggregate.html)
- [データサイエンス100本ノック](https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess)
- [統計学基礎 - 中央値とパーセンタイル]()