#29│パーセンタイルとヒストグラムをSQLで作る

「平均」だけ見ていると騙される

「平均注文金額は12,000円です」という数字は、一見分かりやすい。しかし実態はこうかもしれない。

大多数の顧客は 3,000〜8,000 円の小さな注文をしている
ごく一部のヘビーユーザーが 100,000 円以上の注文を繰り返している
その少数の高単価顧客が平均を大きく引き上げている

この状況で「平均12,000円」に向けた施策を打っても、実態と大きくズレる。本当に必要なのは「分布の形」を見ることだ。

パーセンタイルとヒストグラムは、分布の形を把握するための道具だ。「全体の何%がどの範囲に収まるか」が分かることで、セグメント設計・価格戦略・リソース配分の判断が変わる。

使用するテーブル

-- orders テーブル
-- order_id     : 注文ID
-- customer_id  : 顧客ID
-- order_date   : 注文日
-- total_amount : 注文金額
-- status       : 'completed' / 'cancelled'

STEP 1 ― パーセンタイルで分布の形を把握する

APPROX_PERCENTILE（Presto / Treasure Data）は指定したパーセンタイル位置の値を高速に返す。

-- STEP1: 注文金額の代表的なパーセンタイルを一覧で出す

SELECT
    COUNT(*)                                     AS total_orders,
    ROUND(MIN(total_amount), 0)                  AS min_amount,
    ROUND(APPROX_PERCENTILE(total_amount, 0.10), 0) AS p10,  -- 下位10%
    ROUND(APPROX_PERCENTILE(total_amount, 0.25), 0) AS p25,  -- 第1四分位数
    ROUND(APPROX_PERCENTILE(total_amount, 0.50), 0) AS p50,  -- 中央値
    ROUND(APPROX_PERCENTILE(total_amount, 0.75), 0) AS p75,  -- 第3四分位数
    ROUND(APPROX_PERCENTILE(total_amount, 0.90), 0) AS p90,  -- 上位10%
    ROUND(APPROX_PERCENTILE(total_amount, 0.95), 0) AS p95,  -- 上位5%
    ROUND(APPROX_PERCENTILE(total_amount, 0.99), 0) AS p99,  -- 上位1%
    ROUND(MAX(total_amount), 0)                  AS max_amount,
    ROUND(AVG(total_amount), 0)                  AS avg_amount
FROM orders
WHERE status = 'completed'
AND order_date >= DATE '2024-01-01'
AND order_date <  DATE '2025-01-01';

出力イメージ

total_orders	min	p10	p25	p50	p75	p90	p95	p99	max	avg
48,412	500	2,800	4,200	7,800	14,200	24,800	38,000	89,000	420,000	12,400

中央値（p50）は7,800円だが、平均は12,400円。平均が中央値より60%高い → 高額注文が平均を大きく引き上げている
p90（24,800円）とp99（89,000円）の差が大きい → 上位1%に超高額顧客が集中している
p10（2,800円）は最小（500円）と大きく離れていない → 少額注文は一定量あるが極端に少ない

APPROX_PERCENTILE と PERCENTILE_CONT の違い
APPROX_PERCENTILE は HyperLogLog の変形アルゴリズムを使った近似計算で誤差は1〜2%程度。大規模データに対して高速。PERCENTILE_CONT は正確な値を返すが重い。通常の分析では APPROX_PERCENTILE で十分だ。

STEP 2 ― 複数パーセンタイルを配列で一括計算する

Presto では APPROX_PERCENTILE に配列を渡すことで、1回の計算で複数パーセンタイルを取得できる。

-- STEP2: 配列渡しで複数パーセンタイルを一括計算（Presto固有）

SELECT
    APPROX_PERCENTILE(total_amount, ARRAY[0.1, 0.25, 0.5, 0.75, 0.9, 0.95, 0.99])
    AS percentiles
FROM orders
WHERE status = 'completed'
AND order_date >= DATE '2024-01-01';

結果は配列で返ってくる。percentiles[1] が p10、percentiles[4] が p75 という形だ。これを縦長に展開したい場合は CROSS JOIN UNNEST を使う。

-- 配列を縦長に展開する

WITH percentile_array AS (
    SELECT
        APPROX_PERCENTILE(total_amount, ARRAY[0.1, 0.25, 0.5, 0.75, 0.9, 0.95, 0.99])
            AS pct_values
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE '2024-01-01'
)
SELECT
    pct_label,
    ROUND(pct_value, 0) AS value
FROM percentile_array
CROSS JOIN UNNEST(
    ARRAY[0.10, 0.25, 0.50, 0.75, 0.90, 0.95, 0.99],
    pct_values
) AS t(pct_label, pct_value);

STEP 3 ― ヒストグラムを CASE WHEN で作る

「各金額帯に何件の注文があるか」を出す。WIDTH_BUCKET 関数または CASE WHEN を使う。

CASE WHEN 版（金額帯を自由に設定できる）

-- STEP3: 注文金額のヒストグラム（CASE WHEN版）

WITH bucketed AS (
    SELECT
        total_amount,
        CASE
            WHEN total_amount <   1000  THEN '01_〜999円'
            WHEN total_amount <   3000  THEN '02_1,000〜2,999円'
            WHEN total_amount <   5000  THEN '03_3,000〜4,999円'
            WHEN total_amount <  10000  THEN '04_5,000〜9,999円'
            WHEN total_amount <  20000  THEN '05_10,000〜19,999円'
            WHEN total_amount <  50000  THEN '06_20,000〜49,999円'
            WHEN total_amount < 100000  THEN '07_50,000〜99,999円'
            ELSE                             '08_100,000円以上'
        END  AS bucket
    FROM orders
    WHERE status = 'completed'
    AND order_date >= DATE '2024-01-01'
    AND order_date <  DATE '2025-01-01'
)
SELECT
    bucket,
    COUNT(*)                                             AS order_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2)  AS pct,
    -- バー表示（簡易ASCII棒グラフ）
    REPEAT('█', CAST(COUNT(*) * 40 / MAX(COUNT(*)) OVER () AS INTEGER))  AS bar
FROM bucketed
GROUP BY bucket
ORDER BY bucket;

出力イメージ

bucket	order_count	pct	bar
01_〜999円	1,241	2.6	████
02_1,000〜2,999円	4,812	9.9	███████████████
03_3,000〜4,999円	8,924	18.4	████████████████████████████
04_5,000〜9,999円	14,218	29.4	████████████████████████████████████████
05_10,000〜19,999円	10,841	22.4	██████████████████████████████
06_20,000〜49,999円	6,412	13.2	████████████████████
07_50,000〜99,999円	1,812	3.7	█████
08_100,000円以上	152	0.3	█

分布の山は5,000〜9,999円帯（29.4%）にある。10,000〜19,999円（22.4%）もボリュームが大きい。「平均12,000円」という数字が中心帯を少し上回る高い位置にあることが視覚的に確認できる。

WIDTH_BUCKET 版（等幅でバケットを自動生成）

等幅の金額帯に自動分割したい場合は WIDTH_BUCKET が便利だ。

-- WIDTH_BUCKET版（0〜50,000円を10分割）

SELECT
    WIDTH_BUCKET(total_amount, 0, 50000, 10)  AS bucket_num,
    -- バケット番号から金額帯の文字列を生成
    CAST((WIDTH_BUCKET(total_amount, 0, 50000, 10) - 1) * 5000 AS VARCHAR)
    || '〜'
    || CAST(WIDTH_BUCKET(total_amount, 0, 50000, 10) * 5000 AS VARCHAR) || '円'  AS bucket_label,
    COUNT(*)  AS order_count
FROM orders
WHERE status = 'completed'
AND total_amount BETWEEN 0 AND 49999  -- 範囲外は別途処理
AND order_date >= DATE '2024-01-01'
GROUP BY WIDTH_BUCKET(total_amount, 0, 50000, 10)
ORDER BY bucket_num;

WIDTH_BUCKET(value, min, max, num_buckets) は value が min〜max の範囲で何番目の等幅バケットに入るかを返す。ただし範囲外の値（50,000円超）はバケット番号が num_buckets + 1 になるため、別途 CASE WHEN でフォローが必要だ。

STEP 4 ― 顧客別LTVのパーセンタイルで投資対象を決める

実務での重要な応用として、「顧客別LTV（累計購買金額）の分布」を分析する。どのLTV帯にどれだけの顧客がいて、売上全体への貢献がどう分布しているかが分かる。

-- STEP4: 顧客別LTVのパーセンタイル分析

WITH customer_ltv AS (
    SELECT
        customer_id,
        SUM(total_amount)  AS lifetime_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
ltv_with_rank AS (
    SELECT
        customer_id,
        lifetime_value,
        PERCENT_RANK() OVER (ORDER BY lifetime_value ASC)  AS percentile_rank
    FROM customer_ltv
)
SELECT
    -- パーセンタイル帯
    CASE
        WHEN percentile_rank >= 0.99 THEN '上位1%（p99〜）'
        WHEN percentile_rank >= 0.95 THEN '上位5%（p95〜99）'
        WHEN percentile_rank >= 0.80 THEN '上位20%（p80〜95）'
        WHEN percentile_rank >= 0.50 THEN '中位（p50〜80）'
        ELSE                              '下位50%（〜p50）'
    END  AS ltv_tier,

    COUNT(customer_id)                                 AS customer_count,
    ROUND(COUNT(customer_id) * 100.0
        / SUM(COUNT(customer_id)) OVER (), 1)          AS customer_pct,

    ROUND(AVG(lifetime_value), 0)                      AS avg_ltv,
    ROUND(MIN(lifetime_value), 0)                      AS min_ltv,
    ROUND(MAX(lifetime_value), 0)                      AS max_ltv,

    SUM(lifetime_value)                                AS total_ltv,
    ROUND(SUM(lifetime_value) * 100.0
        / SUM(SUM(lifetime_value)) OVER (), 1)         AS ltv_contribution_pct
FROM ltv_with_rank
GROUP BY
    CASE
        WHEN percentile_rank >= 0.99 THEN '上位1%（p99〜）'
        WHEN percentile_rank >= 0.95 THEN '上位5%（p95〜99）'
        WHEN percentile_rank >= 0.80 THEN '上位20%（p80〜95）'
        WHEN percentile_rank >= 0.50 THEN '中位（p50〜80）'
        ELSE                              '下位50%（〜p50）'
    END
ORDER BY MAX(percentile_rank) DESC;

出力イメージ

ltv_tier	customer_count	customer_pct	avg_ltv	total_ltv	ltv_contribution_pct
上位1%（p99〜）	142	1.0	284,000	40,328,000	22.4
上位5%（p95〜99）	568	4.0	98,000	55,664,000	30.9
上位20%（p80〜95）	2,130	15.0	34,000	72,420,000	40.2
中位（p50〜80）	4,260	30.0	8,200	34,932,000	19.4
下位50%（〜p50）	7,100	50.0	1,800	12,780,000	7.1

上位5%の顧客（全体の5%）が売上全体の53.3%（22.4% + 30.9%）を占めていることが分かる。これがパレート法則の実態だ。この上位5%に対するVIP施策の ROI は、下位50%への施策とは桁が違う。

STEP 5 ― カテゴリ別パーセンタイルの比較

異なるカテゴリ間で注文金額の分布を比較する。GROUP BY に category を追加するだけで同じ計算が複数カテゴリで走る。

-- STEP5: カテゴリ別注文金額の分布比較

SELECT
    oi.category,
    COUNT(DISTINCT o.order_id)                              AS order_count,
    ROUND(APPROX_PERCENTILE(oi.quantity * oi.unit_price, 0.25), 0)  AS p25,
    ROUND(APPROX_PERCENTILE(oi.quantity * oi.unit_price, 0.50), 0)  AS median,
    ROUND(APPROX_PERCENTILE(oi.quantity * oi.unit_price, 0.75), 0)  AS p75,
    ROUND(APPROX_PERCENTILE(oi.quantity * oi.unit_price, 0.90), 0)  AS p90,
    ROUND(AVG(oi.quantity * oi.unit_price), 0)              AS avg_amount,
    -- IQR（四分位範囲）：分布の広がりを示す
    ROUND(
        APPROX_PERCENTILE(oi.quantity * oi.unit_price, 0.75)
        - APPROX_PERCENTILE(oi.quantity * oi.unit_price, 0.25)
    , 0)  AS iqr
FROM orders       o
JOIN order_items  oi  ON o.order_id = oi.order_id
WHERE o.status = 'completed'
AND o.order_date >= DATE '2024-01-01'
AND o.order_date <  DATE '2025-01-01'
GROUP BY oi.category
ORDER BY median DESC;

出力イメージ

category	order_count	p25	median	p75	p90	avg_amount	iqr
プレミアムスキンケア	4,218	8,400	14,800	28,000	48,000	18,200	19,600
ヘアケア	12,841	2,800	5,200	9,400	16,800	6,800	6,600
ボディケア	8,412	1,800	3,400	6,200	11,200	4,100	4,400
日用品	18,241	800	1,800	3,600	6,400	2,400	2,800

IQR（四分位範囲：p75 – p25）はデータのばらつきを示す。プレミアムスキンケアはIQR 19,600円と分布が広く、顧客によって購買金額が大きく異なる。ヘアケアはIQR 6,600円と比較的安定した購買パターンだ。

実務での運用ヒント

① ヒストグラムのバケット設定は「分布を見てから」決める

最初から細かいバケットを設定するより、まず STEP1 のパーセンタイルで分布の山とテールの位置を確認してからバケット境界を決めるほうが意味のある区分けになる。p25 〜 p75 の範囲を細かく分けて、テールは粗くまとめるのが一般的な設計だ。

② 外れ値の扱い

最大値が平均の何十倍にもなる場合、外れ値がヒストグラムの可読性を損なう。WHERE total_amount < APPROX_PERCENTILE(total_amount, 0.99) のように99パーセンタイル未満に絞ったヒストグラムと、外れ値を含む全体版の両方を出すと分析が充実する。

③ 時系列でパーセンタイルの変化を追う

「今年の注文金額の中央値は昨年より上がったか下がったか」を追うには、STEP1 のクエリに sales_year の GROUP BY を加えるだけでよい。平均ではなく中央値の推移を追うことで、外れ値に引っ張られない実態の変化が見える。

まとめ

パーセンタイルとヒストグラムを SQL で作る方法を振り返る。

APPROX_PERCENTILE(col, 0.5) で中央値・四分位数・上位N%の閾値を計算する
PERCENT_RANK() ウィンドウ関数で各行の相対的な位置（パーセンタイルランク）を求める
CASE WHEN でバケットを定義し、COUNT と SUM() OVER () でヒストグラムを作る
LTV 帯別の売上貢献率を出して「上位5%が売上の半分以上」という実態を定量化する
IQR（四分位範囲）でカテゴリ間の分布の広がりを比較する

「平均だけ見ていると騙される」という問題意識から始まったこの記事の結論は、平均は必ず中央値・パーセンタイルと一緒に見るということだ。SQL でこれが計算できるようになれば、データドリブンな意思決定の質が一段上がる。