SQL 統計分析 — 残差分析・PERCENTILE

QUESTION 6

チャネル別相関分析 — CORR + GROUP BY でセグメント間の相関強度を比較・ランキングする

CORRGROUP BY相関分析セグメント比較ABS / CASE WHEN

前提知識 ▼

CORR(Y, X) は通常の集計関数なので GROUP BY と組み合わせてグループごとの相関係数を一括算出できます。ウィンドウ関数としては使えない点が RANK 等との重要な違いです。

-- GROUP BY + CORR でグループ別相関を一括算出
SELECT channel,
  ROUND(CORR(revenue, ad_cost)::numeric, 4) AS corr_coef,
  REGR_COUNT(revenue, ad_cost)                AS n  -- NULL除外の有効ペア数
FROM channel_sales GROUP BY channel;

-- CORR を行レベルで付与したい場合は OVER() 不可 → CTE GROUP BY + JOIN が必要
-- ABS(CORR) で正負の相関を同一軸で強度比較できる

GROUP BY の集計粒度に注意：GROUP BY channel の場合、各チャネル内の全行データで CORR が計算されます。週ごとにも CORR を見たい場合は GROUP BY channel, week_group のように粒度を調整してください。また REGR_COUNT が期待する n より小さい場合、NULL が多くモデルの信頼性が低い可能性があります。

問題 ▼

channel_sales テーブルを使い、チャネル（channel）ごとに広告費（ad_cost）と売上（revenue）のピアソン相関係数（小数第4位まで）・有効ペア数（n）・相関強度ラベル（strength）を算出してください。ABS(CORR) >= 0.9 → '強い相関'、>= 0.7 → '中程度の相関'、それ以外は '弱い相関'。corr_coef の降順で返してください。

使用テーブル

► channel_sales（12行）

week	channel	ad_cost	revenue
1	Online	100	280
2	Online	200	650
3	Online	300	780
4	Online	400	1100
5	Online	500	1250
6	Online	600	1640
1	Offline	100	500
2	Offline	200	420
3	Offline	300	680
4	Offline	400	550
5	Offline	500	820
6	Offline	600	700

期待出力 ▼

channel	corr_coef	n	strength
Online	0.9914	6	強い相関
Offline	0.7498	6	中程度の相関

模範解答コード ▼

SELECT
  channel,
  ROUND(CORR(revenue, ad_cost)::numeric, 4)  AS corr_coef,  -- ピアソン相関係数
  REGR_COUNT(revenue, ad_cost)                 AS n,        -- 両列が非NULLのペア数
  CASE
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.9 THEN '強い相関'
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.7 THEN '中程度の相関'
    ELSE                                           '弱い相関'
  END AS strength
FROM   channel_sales
GROUP BY channel
ORDER BY corr_coef DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM channel_sales       → 12行読込
  2. GROUP BY channel         → 2グループに分割
  3. 各グループで CORR を集計          → 相関係数を計算
  4. CASE WHEN ABS(CORR)      → 強度ラベルを付与
  5. ROUND(::numeric, 4)      → 小数第4位まで
  6. ORDER BY corr_coef DESC  → 相関の高い順
  7. SELECT                   → 2行出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  channel,
  ROUND(CORR(revenue, ad_cost)::numeric, 4)  AS corr_coef,
  REGR_COUNT(revenue, ad_cost) AS n,
  CASE
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.9 THEN '强い相関'
    WHEN ABS(CORR(revenue, ad_cost)) >= 0.7 THEN '中程度の相関'
    ELSE                                           '弱い相関'
  END AS strength
FROM   channel_sales
GROUP BY channel
ORDER BY corr_coef DESC;

LEGEND ▼

データ取得・読込対象

① FROM channel_sales — Online 6行・Offline 6行の広告費と売上 ▼

FROM channel_sales12行のチャネル別データを読み込みます。Online は ad_cost が増えると revenue もほぼ比例して増加（強い正の相関が予想）。Offline は増減にばらつきがあり中程度の相関になります。

1 / 5

week	► channel	► ad_cost	► revenue
1	Online	100	280
2	Online	200	650
3	Online	300	780
4	Online	400	1100
5	Online	500	1250
6	Online	600	1640
1	Offline	100	500
2	Offline	200	420
3	Offline	300	680
4	Offline	400	550
5	Offline	500	820
6	Offline	600	700

channel_sales: 12行（Online×6 / Offline×6）

学習ポイント

CORR + GROUP BY で多軸スクリーニング：グループ数が多くなっても1クエリで完結します。カテゴリ別・地域別・時期別など複数の GROUP BY キーに拡張するだけで、どのセグメントで線形性が高いかを俯瞰できます。ただし CORR は GROUP BY とは組み合わせられますが、OVER()（ウィンドウ関数）としては使えません。行レベルで相関値を付与したい場合は CTE GROUP BY CORR → JOIN の2ステップが必要です。

REGR_COUNT で信頼性チェック：REGR_COUNT(Y, X) はどちらかが NULL のペアを除いた有効件数を返します。COUNT(*) と REGR_COUNT の乖離が大きいグループは欠損多発のサインです。一般に n < 10 の場合は CORR 値の信頼区間が広く、結論を保留すべきです。

ABS(CORR) で正負混在のセグメントを統一比較：在庫と欠品率のような「強い負の相関（r = −0.92）」も ABS(CORR) >= 0.9 でまとめて「強い相関」に分類できます。ビジネス上の解釈は符号で行い、強度分類は ABS を使うと実務的です。

アンチパターン

サンプル数が少ないグループの CORR を過信する：n=4 などの小サンプルでは偶然の一致で CORR が極端な値になりやすい（例: n=2 で必ず |CORR|=1）。n < 10 のグループは CORR 値に「* 参考値」と注記し、データ収集を優先するのが実務上の誠実な対応です。

相関係数だけから因果関係を結論づける：Offline の CORR=0.75 でも「広告費を増やせば売上が上がる」とは断言できません。相関は共変動の強さを測るだけで因果を証明しない。因果推論にはランダム化実験（A/B テスト）や操作変数法が必要です。

実務コラム：チャネル効果スクリーニングへの応用

GROUP BY CORR を使うと、チャネル・地域・商品カテゴリ全体の「広告効果の線形性」を一括スクリーニングできます。相関が高いチャネルは次のステップ（REGR_SLOPE での ROI 定量化）へ、相関が低いチャネルは「非線形な関係がある」か「データ品質に問題がある」かを個別調査するという分析フローの入口として機能します。また HAVING REGR_COUNT(Y,X) >= 20 でサンプル不足グループをフィルタリングすると、信頼性の低い CORR 値を結果から自動除外できます。

QUESTION 7

残差分析 — REGR_SLOPE + CROSS JOIN で全行に予測値・残差を付与し外れ値を特定する

REGR_SLOPECROSS JOIN残差分析外れ値検出CTE多段

前提知識 ▼

回帰モデルの残差（residual）= 実測値 − 予測値を各行に付与することで、モデルから大きく外れた行（外れ値・異常値候補）を特定できます。SQL では CTE で集計（1行）→ CROSS JOIN で全行に展開 → 予測値・残差を計算という3段階のパターンが定番です。

-- ① CTE でモデルパラメータを1行に集約
WITH model AS (
  SELECT REGR_SLOPE(Y, X) AS slope, REGR_INTERCEPT(Y, X) AS intercept
  FROM data_table
),
-- ② CROSS JOIN で全行にパラメータを展開 → 予測値・残差を計算
with_pred AS (
  SELECT d.*, ROUND((m.slope * d.X + m.intercept)::numeric, 0) AS predicted,
         ROUND((d.Y - (m.slope * d.X + m.intercept))::numeric, 0) AS residual
  FROM data_table d CROSS JOIN model m  -- 1行×N行 = N行に展開
)
SELECT *, ABS(residual) AS abs_residual FROM with_pred ORDER BY abs_residual DESC;

残差の符号と意味：正の残差（実測 > 予測）はモデルが過小評価、負の残差（実測 < 予測）はモデルが過大評価を示します。絶対値 ABS(residual) が大きい行が外れ値候補です。R²（決定係数）が外れ値の影響で低下していないか REGR_R2 も同時に確認しましょう。

問題 ▼

store_data テーブルで床面積（floor_sqm）→ 日次売上（daily_sales）の線形回帰モデルをCTEで構築し、各店舗の予測売上（predicted）・残差（residual）・残差絶対値（abs_residual）を算出してください。abs_residual の降順で返し、外れ値候補の店舗を特定してください。

使用テーブル

► store_data（8行）

store_id	floor_sqm	daily_sales
S1	100	500
S2	200	950
S3	300	1400
S4	400	1850
S5	500	2300
S6	600	2200
S7	700	3200
S8	800	3700

期待出力 ▼

store_id	floor_sqm	daily_sales	predicted	residual	abs_residual
S6	600	2200	2664	-464	464
S8	800	3700	3533	167	167
S7	700	3200	3099	101	101
S5	500	2300	2230	70	70
S4	400	1850	1795	55	55
S3	300	1400	1361	39	39
S2	200	950	926	24	24
S1	100	500	492	8	8

模範解答コード ▼

WITH model AS (
  -- 全店舗データから回帰パラメータを一括算出
  SELECT
    REGR_SLOPE(daily_sales, floor_sqm)     AS slope,
    REGR_INTERCEPT(daily_sales, floor_sqm) AS intercept,
    REGR_R2(daily_sales, floor_sqm)        AS r2
  FROM store_data
),
with_pred AS (
  -- CROSS JOIN でパラメータを全行に展開 → 各店舗の予測値・残差を計算
  SELECT
    s.store_id,
    s.floor_sqm,
    s.daily_sales,
    ROUND((m.slope * s.floor_sqm + m.intercept)::numeric, 0) AS predicted,
    ROUND((s.daily_sales - (m.slope * s.floor_sqm + m.intercept))::numeric, 0) AS residual
  FROM   store_data s
  CROSS JOIN model m               -- 1行×8行 = 8行に展開
)
SELECT
  store_id, floor_sqm, daily_sales,
  predicted,
  residual,
  ABS(residual) AS abs_residual    -- 外れ値スコア（大きいほど外れ値候補）
FROM   with_pred
ORDER BY abs_residual DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE model — FROM store_data  → 8行読込
  2. REGR_SLOPE(...)              → 傾きを計算
  3. REGR_INTERCEPT               → 切片を計算
  4. REGR_R2                      → 決定係数を計算
  5. CTE with_pred — CROSS JOIN   → モデルを全行に結合
  6. predicted                    → 予測値を計算
  7. residual                     → 残差を計算
  8. ABS(residual)                → 外れ値スコア
  9. ORDER BY abs_residual DESC   → 外れ値の大きい順
  10. SELECT                       → 8行出力
  */

解説（テーブル変化・ポイント） ▼

WITH model AS (
  SELECT
    REGR_SLOPE(daily_sales, floor_sqm)     AS slope,
    REGR_INTERCEPT(daily_sales, floor_sqm) AS intercept,
    REGR_R2(daily_sales, floor_sqm)        AS r2
  FROM   store_data
),
with_pred AS (
  SELECT
    s.store_id, s.floor_sqm, s.daily_sales,
    ROUND((m.slope * s.floor_sqm + m.intercept)::numeric, 0) AS predicted,
    ROUND((s.daily_sales - (m.slope * s.floor_sqm + m.intercept))::numeric, 0) AS residual
  FROM   store_data s
  CROSS JOIN model m
)
SELECT
  store_id, floor_sqm, daily_sales,
  predicted, residual,
  ABS(residual) AS abs_residual
FROM   with_pred
ORDER BY abs_residual DESC;

LEGEND ▼

データ取得・読込対象

① FROM store_data — 8店舗の床面積と日次売上（S6が外れ値候補） ▼

FROM store_data8店舗の面積（floor_sqm）と日次売上（daily_sales）を読み込みます。S1〜S5・S7・S8はほぼ線形のトレンドに乗っていますが、S6（600m²）だけ売上2,200と予想より低い値です。

1 / 5

store_id	► floor_sqm	► daily_sales
S1	100	500
S2	200	950
S3	300	1400
S4	400	1850
S5	500	2300
S6	600	2,200 ⚠
S7	700	3200
S8	800	3700

store_data: 8行（S6は外れ値候補 — 同面積帯より売上が低い）

学習ポイント

CROSS JOIN を安全に使う条件：CROSS JOIN はデカルト積（M×N行）を生成します。今回のようにCTE が確実に1行（集計結果）の場合にのみ安全です。CTE が複数行になる可能性がある場合は CROSS JOIN を避け、JOIN ... ON 1=1 や LATERAL を使って意図を明示してください。

残差の符号からビジネス洞察を読む：負の残差（実測 < 予測） = モデルが過大評価 = 店舗が同規模の平均を下回る。S6 の −464 は「600m² の店舗が通常稼ぐべき売上 2,664 より 464 低い」を意味し、競合・立地・商品力などの追加調査トリガーになります。

外れ値除去後に R² を再計算して比較する：S6 を除いた残り7店舗で再度 REGR_R2 を計算すると R² が大幅に改善します。WHERE store_id != 'S6' を追加した CTE を並列で作成し、R² の変化量で外れ値の影響度を定量評価するのが実務的な残差分析の仕上げです。

アンチパターン

CROSS JOIN する CTE が複数行になりうる場合：WHERE や GROUP BY を含まない CTE がデータ次第で複数行になると、CROSS JOIN はその行数分のデカルト積を生成し結果が数倍に膨れ上がります。CROSS JOIN の相手は必ず1行であることを設計段階で保証し、コメントに「1行のみ」と明記する習慣をつけてください。

外れ値を「データ誤り」と即断して削除する：S6 の低売上は「入力ミス」かもしれませんが「改装中」「近隣競合の影響」など正当な理由がある場合も多い。外れ値は削除ではなく調査起点として扱い、ドメイン知識で確認してから除去判断を行ってください。

実務コラム：品質管理・KPI 異常検知への応用

残差分析は製造ラインの不良率監視・営業目標達成率の逸脱検知・広告効果の異常フラグなど、あらゆる「基準値との乖離」を検出する汎用パターンです。ABS(residual) > 2 * STDDEV(residual)（2σ以上の逸脱）でフラグを立てる実装や、日次バッチで残差絶対値トップ10をアラートメールに送る自動監視も同じSQL構造で実現できます。REGR_R2 を週次でモニタリングし、R² の急落をモデル更新のトリガーとする運用も実務では一般的です。

QUESTION 8

カテゴリ別回帰モデル + バッチ予測 — GROUP BY REGR でモデルを構築し新データに一括適用する

REGR_SLOPEREGR_R2カテゴリ別回帰バッチ予測GROUP BY + JOIN

前提知識 ▼

GROUP BY と REGR_SLOPE / REGR_INTERCEPT を組み合わせると、カテゴリごとに独立した回帰モデルを1クエリで構築できます。さらに JOIN で新データテーブルと結合することで、複数シナリオの予測を一括バッチ処理できます。

-- GROUP BY でカテゴリ別モデルを並列構築
WITH models AS (
  SELECT category,
    REGR_SLOPE(sales, ad_cost)     AS slope,
    REGR_INTERCEPT(sales, ad_cost) AS intercept,
    REGR_R2(sales, ad_cost)        AS r2
  FROM product_data GROUP BY category
)
-- JOIN で新データに各カテゴリのモデルを適用してバッチ予測
SELECT f.category, f.future_ad,
  ROUND((m.slope * f.future_ad + m.intercept)::numeric, 0) AS predicted_sales
FROM forecast_input f JOIN models m USING (category);

単回帰の slope はカテゴリ別 ROI として解釈できる：slope = Δ売上 / Δ広告費は「広告費1円あたりの売上増分」＝広告 ROI の代理指標です。slope が高いカテゴリほど広告投資対効果が高いため、予算配分の根拠として使えます。R² を見ながら「モデルの信頼性が高いカテゴリ」のみで意思決定する習慣をつけましょう。

問題 ▼

product_data テーブルを使い、カテゴリ（category）ごとに広告費（ad_cost）→ 売上（sales）の回帰モデルを構築してください。次に forecast_input の予測広告費（future_ad）に各カテゴリのモデルを適用し、予測売上（predicted_sales）・傾き（slope）・切片（intercept）・決定係数（r2）を出力してください。category・future_ad 昇順で返してください。

使用テーブル

► product_data（10行）

category	ad_cost	sales
Electronics	100	300
Electronics	200	720
Electronics	300	1000
Electronics	400	1420
Electronics	500	1660
Fashion	100	220
Fashion	200	300
Fashion	300	480
Fashion	400	490
Fashion	500	680

► forecast_input（4行）

category	future_ad
Electronics	400
Electronics	600
Fashion	400
Fashion	600

期待出力 ▼

category	future_ad	predicted_sales	slope	intercept	r2
Electronics	400	1362	3.4200	-6.00	0.9926
Electronics	600	2046	3.4200	-6.00	0.9926
Fashion	400	545	1.1100	101.00	0.9513
Fashion	600	767	1.1100	101.00	0.9513

模範解答コード ▼

WITH model_by_cat AS (
  -- カテゴリ別に GROUP BY して独立した回帰モデルを並列構築
  SELECT
    category,
    REGR_SLOPE(sales, ad_cost)     AS slope,
    REGR_INTERCEPT(sales, ad_cost) AS intercept,
    REGR_R2(sales, ad_cost)        AS r2
  FROM   product_data
  GROUP BY category
)
SELECT
  f.category,
  f.future_ad,
  ROUND((m.slope * f.future_ad + m.intercept)::numeric, 0) AS predicted_sales,
  ROUND(m.slope::numeric,     4) AS slope,      -- 広告費1単位あたりの売上増分（ROI指標）
  ROUND(m.intercept::numeric, 2) AS intercept,
  ROUND(m.r2::numeric,         4) AS r2         -- モデル信頼性の確認用
FROM   forecast_input f
JOIN   model_by_cat m USING (category)            -- カテゴリをキーに各行へ対応モデルを結合
ORDER BY f.category, f.future_ad;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE model_by_cat — FROM product_data  → 10行読込
  2. GROUP BY category                     → 2グループに分割
  3. 各グループで REGR 集計                        → slope/intercept/R2 を計算
  4. FROM forecast_input                   → 4行
  5. JOIN model_by_cat USING (category)    → モデルを結合
  6. predicted_sales                       → 予測値を計算
  7. ORDER BY category, future_ad          → 最終ソート
  8. SELECT                                → 4行出力
  */

解説（テーブル変化・ポイント） ▼

WITH model_by_cat AS (
  SELECT
    category,
    REGR_SLOPE(sales, ad_cost)     AS slope,
    REGR_INTERCEPT(sales, ad_cost) AS intercept,
    REGR_R2(sales, ad_cost)        AS r2
  FROM   product_data
  GROUP BY category
)
SELECT
  f.category,
  f.future_ad,
  ROUND((m.slope * f.future_ad + m.intercept)::numeric, 0) AS predicted_sales,
  ROUND(m.slope::numeric, 4) AS slope,
  ROUND(m.intercept::numeric, 2) AS intercept,
  ROUND(m.r2::numeric, 4) AS r2
FROM   forecast_input f
JOIN   model_by_cat m USING (category)
ORDER BY f.category, f.future_ad;

LEGEND ▼

データ取得・読込対象

① FROM product_data — 全カテゴリの学習データ ▼

FROM product_data10行のデータを category でグループ化します。Electronics（傾きが急）と Fashion（傾きが緩い）でまったく異なる線形関係を持つため、全体で1つの回帰を使うよりカテゴリ別モデルが精度・解釈性ともに優れています。

1 / 5

category	► ad_cost	► sales
Electronics	100	300
Electronics	200	720
Electronics	300	1000
Electronics	400	1420
Electronics	500	1660
Fashion	100	220
Fashion	200	300
Fashion	300	480
Fashion	400	490
Fashion	500	680

product_data: 10行（Electronics×5 / Fashion×5）

学習ポイント

slope が広告 ROI の代理指標になる：単回帰の slope は「説明変数 X が1単位増えたときの目的変数 Y の変化量」です。今回の文脈ではslope = 広告費1円あたりの売上増分＝広告投資収益率（ROI）の代理指標として機能します。Electronics(3.42) は Fashion(1.11) の約3倍の slope であり、同じ広告予算ならElectronicsに集中投資すべきという定量的根拠になります。

USING vs ON での JOIN：USING (category) は結合キーが両テーブルで同名かつ同型の場合に使える省略記法です。結果の列に category が1列しか出力されない点が ON f.category = m.category との違いです。SELECT で明示的に別名を付けたい場合は ON を使います。

R² でモデルの信頼性を確認してから予測を使う：R² が低いカテゴリの予測値は精度が低く意思決定に使うべきではありません。R² < 0.7 の場合は「参考値」として扱い、追加データ収集や非線形モデルの検討を優先してください。今回の両カテゴリは R² > 0.95 と高く、予測値の信頼性は十分です。

アンチパターン

全カテゴリを単一回帰で混ぜる：Electronics と Fashion を GROUP BY なしで1つの REGR_SLOPE に混ぜると、2つの異なる線形関係が平均化された「どちらの実態も表さないモデル」ができます。異なるグループを混在させた回帰は Simpson's Paradox の原因になりうるため、必ず GROUP BY でセグメント別に分離してください。

学習データの範囲外に外挿する：Electronics の学習データは ad_cost=100〜500 の範囲です。future_ad=600 はわずかな外挿ですが、1,000 など大幅に外れた値の予測は信頼できません。外挿範囲には必ずコメントや注釈を付け、利用者が外挿リスクを認識できるようにしてください。

実務コラム：予算シナリオプランニングの自動化

この構造を拡張すると、「来期の広告予算シナリオ（500万・700万・1000万）×商品カテゴリ（10種）= 30通りの予測売上」を1クエリで一括生成できます。forecast_input に全シナリオ行を挿入しておけば、CTE でモデルを構築した後の JOIN で自動的にすべての組み合わせが予測されます。さらに predicted_sales × margin_rate で予測利益も同時算出できるため、経営会議向けの予算配分試算レポート生成クエリとして直接活用できます。

QUESTION 9

順序統計量と外れ値検出 — PERCENTILE_CONT と Tukey フェンスで IQR ベース外れ値を特定する

PERCENTILE_CONTWITHIN GROUP四分位数外れ値検出Tukeyフェンス

前提知識 ▼

PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col) はデータを昇順ソートして位置 p（0〜1）に相当する値を線形補間で返す順序集合集計関数です。p=0.25・0.5・0.75 で第1四分位数・中央値・第3四分位数が得られます。

-- 書式: PERCENTILE_CONT(分位値) WITHIN GROUP (ORDER BY 列)
-- 線形補間: 行位置 = 1 + p×(N−1)  例: N=12, p=0.25 → 行位置=3.75
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) AS q1
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY score) AS median
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) AS q3

-- PERCENTILE_DISC: 補間せず最近傍の実際値を返す（離散版）
-- Tukey フェンス（箱ひげ図の外れ値基準）:
--   lower_fence = Q1 − 1.5×IQR  / upper_fence = Q3 + 1.5×IQR
--   fence の外側を外れ値（outlier）とみなす

集計関数として GROUP BY 可：PERCENTILE_CONT は GROUP BY category と組み合わせてカテゴリ別の中央値を一括算出できます。また OVER() ウィンドウ関数としては使えません。行レベルで各行の分位数を付与したい場合は NTILE が代替候補ですが、NTILE は件数ベースの等分、PERCENTILE_CONT は値ベースの補間という違いを意識してください。

問題 ▼

exam_results テーブルを使い、CTE で Q1・中央値・Q3 から IQR・下限フェンス・上限フェンスを算出し、各学生のスコアが Tukey フェンス（lower=Q1−1.5×IQR, upper=Q3+1.5×IQR）の範囲内か否かを outlier_flag（'外れ値' / '正常'） でラベリングしてください。score 昇順で返してください。

使用テーブル

► exam_results（12行）

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58

期待出力 ▼

student_id	score	lower_fence	upper_fence	outlier_flag
S07	42	30.25	114.25	正常
S03	55	30.25	114.25	正常
S12	58	30.25	114.25	正常
S05	63	30.25	114.25	正常
S01	67	30.25	114.25	正常
S09	71	30.25	114.25	正常
S10	75	30.25	114.25	正常
S06	80	30.25	114.25	正常
S02	82	30.25	114.25	正常
S11	85	30.25	114.25	正常
S08	91	30.25	114.25	正常
S04	138	30.25	114.25	外れ値

模範解答コード ▼

WITH stats AS (
  -- PERCENTILE_CONT で線形補間して 第1四分位数・中央値・第3四分位数を算出
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY score) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) AS q3
  FROM exam_results
),
fences AS (
  -- Tukey フェンスを算出（外れ値判定の閾値）
  SELECT
    q1, median, q3,
    q3 - q1                  AS iqr,
    q1 - 1.5 * (q3 - q1)    AS lower_fence,
    q3 + 1.5 * (q3 - q1)    AS upper_fence
  FROM stats
)
SELECT
  e.student_id,
  e.score,
  ROUND(f.lower_fence::numeric, 2) AS lower_fence,
  ROUND(f.upper_fence::numeric, 2) AS upper_fence,
  CASE
    WHEN e.score < f.lower_fence OR e.score > f.upper_fence
    THEN '外れ値'
    ELSE '正常'
  END AS outlier_flag
FROM   exam_results e
CROSS JOIN fences f
ORDER BY e.score;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE stats — FROM exam_results  → 12行読込
  2. PERCENTILE_CONT(0.25)          → 第1四分位数を補間
  3. PERCENTILE_CONT(0.50)          → 中央値を補間
  4. PERCENTILE_CONT(0.75)          → 第3四分位数を補間
  5. CTE fences                     → IQR から外れ値境界を算出
  6. CROSS JOIN fences              → 境界を全行に結合
  7. CASE                           → 外れ値を判定
  8. ORDER BY score                 → 昇順ソート
  9. SELECT                         → 12行出力
  */

解説（テーブル変化・ポイント） ▼

WITH stats AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY score) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) AS q3
  FROM exam_results
),
fences AS (
  SELECT q1, median, q3,
    q3 - q1                  AS iqr,
    q1 - 1.5 * (q3 - q1)    AS lower_fence,
    q3 + 1.5 * (q3 - q1)    AS upper_fence
  FROM stats
)
SELECT
  e.student_id, e.score,
  ROUND(f.lower_fence::numeric, 2) AS lower_fence,
  ROUND(f.upper_fence::numeric, 2) AS upper_fence,
  CASE
    WHEN e.score < f.lower_fence OR e.score > f.upper_fence
    THEN '外れ値'
    ELSE '正常'
  END AS outlier_flag
FROM   exam_results e
CROSS JOIN fences f
ORDER BY e.score;

LEGEND ▼

データ取得・読込対象

① FROM exam_results — 12名のスコアを昇順ソートして準備 ▼

FROM exam_results ORDER BY score12名の試験スコアを昇順ソートします。S04（138点）が他より大きく孤立しており外れ値候補です。PERCENTILE_CONT はソート順の「行位置」から線形補間で分位数を計算します。

1 / 6

行番号	student_id	► score（昇順）
1	S07	42
2	S03	55
3	S12	58
4	S05	63
5	S01	67
6	S09	71
7	S10	75
8	S06	80
9	S02	82
10	S11	85
11	S08	91
12	S04	138 ⚠

exam_results: 12行（昇順 sorted / S04が最大の孤立値）

学習ポイント

PERCENTILE_CONT vs PERCENTILE_DISC の違い：PERCENTILE_CONT は線形補間で「行と行の間の値」を返す（今回 Q1=61.75 は実データに存在しない）。PERCENTILE_DISC は補間なしで「その分位以上の最小実データ値」を返す（整数スコアなら必ず整数値）。連続データには CONT、離散データには DISC が直感的です。どちらも WITHIN GROUP (ORDER BY col) 構文は同じです。

Tukey の 1.5×IQR ルールの意味：IQR は「中央50%データの幅」です。その1.5倍外側まで正常範囲とすることで、正規分布に近いデータでは約99.3%が正常範囲内に収まります。3×IQR フェンスも使われ（「極端な外れ値」の検出）、ビジネス要件に応じて係数を調整してください。平均・標準偏差を使った 2σ・3σ ルールは外れ値自身に影響されやすいため、IQR ベースはロバスト（外れ値の影響を受けにくい）という利点があります。

GROUP BY と組み合わせたカテゴリ別外れ値検出：PERCENTILE_CONT ... WITHIN GROUP ... FROM exam_results GROUP BY exam_type のように GROUP BY を追加すると、試験の種類ごとに独立したフェンスを設定できます。科目別・部門別・時期別の外れ値基準が異なる場合はこのパターンが必須です。

アンチパターン

平均±2σ（標準偏差）で外れ値を検出する：AVG と STDDEV_SAMP を使った ±2σ 検出は外れ値自身が平均・標本標準偏差を歪めるため、外れ値が多いほど検出感度が下がる「自己矛盾」があります。外れ値に強い（ロバスト）検出には IQR ベースの Tukey フェンスを使ってください。S04(138)が平均(75.6)・標本標準偏差(24.1)に影響し、標準偏差ルールでは検出が甘くなります。

外れ値フラグを付けた後、調査せずに削除する：S04の138点が「入力ミス（正しくは38点）」か「本当に超高得点の天才」かはデータだけでは判断できません。外れ値フラグは削除の即断ではなく調査のトリガーとして使い、ドメイン専門家の確認後に処置を決定してください。

実務コラム：データ品質モニタリングへの応用

PERCENTILE_CONT + Tukey フェンスはETLパイプラインのデータ品質チェック・KPI の異常値アラート・センサーデータの外れ値検出など幅広く応用できます。月次バッチで「先月の IQR フェンスをベースラインとして保存 → 当月データと比較して外れ値件数を計算」するパターンが実務の自動品質監視の定番です。また FILTER (WHERE outlier_flag = '正常') で外れ値除外後の統計（平均・合計）を別カラムで同時出力すると、外れ値の影響前後の KPI 比較が1クエリで完結します。

QUESTION 10

移動平均 — ROWS BETWEEN ウィンドウフレームで3ヶ月移動平均と前月比を同時算出する

ROWS BETWEENAVG OVER移動平均時系列分析LAG + NULLIF

前提知識 ▼

ウィンドウ関数の ROWS BETWEEN n PRECEDING AND CURRENT ROW でウィンドウフレームを明示指定すると、現在行を含む直近 n+1 行の移動平均（Moving Average）を計算できます。

-- ROWS BETWEEN で移動平均ウィンドウフレームを明示指定
AVG(revenue) OVER (
  ORDER BY month
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS ma3                -- 3ヶ月移動平均（当月含む直近3行）

-- デフォルトフレームとの違い:
-- ORDER BY のみ指定: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
--   → 先頭行から当月まで全累積（累積平均）
-- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
--   → 物理的に直近3行のみ（移動平均）← こちらが正しい

-- NULLIF で 0 除算を防ぐ前月比パターン（LAG の応用）
-- (revenue - prev_revenue)::numeric / NULLIF(prev_revenue, 0) * 100

ROWS vs RANGE の違い：ORDER BY を含むウィンドウ関数のデフォルトフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（値が同じ行も含む累積）です。移動平均のように「物理的に直近 N 行」を指定したい場合は必ず ROWS BETWEEN を明示してください。ORDER BY 列に重複値がある場合、RANGE と ROWS では結果が異なります。

問題 ▼

sales_monthly テーブルを使い、CTE で前月売上（prev_revenue）を LAG で付与し、外側クエリで3ヶ月移動平均（ma3: 直近3行 ROUND 整数）と前月比成長率（mom_pct: 小数第2位、NULLIF でゼロ除算防止）を同時算出してください。month 昇順で返してください。

使用テーブル

► sales_monthly（8行）

month	revenue
2024-01	1200000
2024-02	1350000
2024-03	1180000
2024-04	1520000
2024-05	1680000
2024-06	1450000
2024-07	1820000
2024-08	1960000

期待出力 ▼

month	revenue	ma3	mom_pct
2024-01	1200000	1200000	NULL
2024-02	1350000	1275000	12.50
2024-03	1180000	1243333	-12.59
2024-04	1520000	1350000	28.81
2024-05	1680000	1460000	10.53
2024-06	1450000	1550000	-13.69
2024-07	1820000	1650000	25.52
2024-08	1960000	1743333	7.69

模範解答コード ▼

WITH lagged AS (
  -- LAG で前月売上を同行に付与（CTE で先に確定し外側クエリで再利用）
  SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue
  FROM sales_monthly
)
SELECT
  month,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 直近3行の移動平均
    )::numeric
  , 0) AS ma3,
  ROUND(
    (revenue - prev_revenue)::numeric
    / NULLIF(prev_revenue, 0) * 100             -- NULLIF でゼロ除算防止
  , 2) AS mom_pct
FROM   lagged
ORDER BY month;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE lagged — FROM sales_monthly   → 8行読込
  2. LAG(revenue, 1) OVER (...)        → 前月の revenue を付与
  3. FROM lagged                       → CTE を参照
  4. AVG(revenue) OVER (... ROWS ...)  → 3行移動平均を計算
  5. NULLIF(prev_revenue, 0)           → 0除算回避
  6. (revenue - prev) / prev * 100     → 前月比を計算
  7. ROUND(...)                        → 丸め
  8. ORDER BY month                    → 時系列昇順
  9. SELECT                            → 8行出力
  */

解説（テーブル変化・ポイント） ▼

WITH lagged AS (
  SELECT
    month, revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue
  FROM   sales_monthly
)
SELECT
  month, revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric
  , 0) AS ma3,
  ROUND(
    (revenue - prev_revenue)::numeric
    / NULLIF(prev_revenue, 0) * 100
  , 2) AS mom_pct
FROM   lagged
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① FROM sales_monthly — 月次売上データ ▼

FROM sales_monthly2024年1月〜8月の月次売上8行を読み込みます。

1 / 5

month	► revenue
2024-01	1,200,000
2024-02	1,350,000
2024-03	1,180,000
2024-04	1,520,000
2024-05	1,680,000
2024-06	1,450,000
2024-07	1,820,000
2024-08	1,960,000

sales_monthly: 8行

学習ポイント

ROWS vs RANGE の決定的な違い：ORDER BY のみを指定すると PostgreSQL はデフォルトで RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（累積集計）を使います。この場合 AVG は「先頭から当月までの全行の平均」になり移動平均にはなりません。移動平均には必ず ROWS BETWEEN n PRECEDING AND CURRENT ROW を明示してください。

NULLIF でゼロ除算を防ぐ：NULLIF(expr, 0) は expr が 0 のとき NULL を返し、0 を分母にした除算エラーを防ぎます。前月売上が 0 の月に mom_pct を計算するとゼロ除算エラーになるため、LAG の結果には必ず NULLIF を噛ませるのが実務の定番パターンです。NULL 伝播（演算結果が自動的に NULL になる）を積極的に利用することでエラーなくスキップできます。

移動平均ウィンドウ幅の選択：3ヶ月 MA（ma3）はデータの変動が速い場合に短いトレンドを捉え、6ヶ月や12ヶ月 MA は季節性を平滑化します。ROWS BETWEEN 5 PRECEDING AND CURRENT ROW で6ヶ月 MA、ROWS BETWEEN 11 PRECEDING AND CURRENT ROW で12ヶ月 MA に変更するだけです。1クエリで複数の移動平均を並べて「短期・長期のゴールデンクロス」を検出する応用もあります。

アンチパターン

ORDER BY のみで ROWS BETWEEN を省略して移動平均を計算しようとする：AVG(revenue) OVER (ORDER BY month) はデフォルトで RANGE（累積平均）になります。「なぜか毎月平均が増え続ける」現象が起きたら ROWS BETWEEN の欠落を疑ってください。移動平均には必ず ROWS BETWEEN を明示するのが鉄則です。

月次データに欠損月がある場合、ROWS BETWEEN は機能しない：2024-03 が欠落していると ROWS BETWEEN 2 PRECEDING は「2024-04 の直前2行」として 2024-02・2024-01 を使います。つまり月次でなく「物理的な行数」をカウントします。欠損月がある場合は LAG(revenue, 1, NULL) に代えて GENERATE_SERIES でカレンダーを補完するか、日付差（DATEDIFF）で条件を指定するアプローチが必要です。

実務コラム：時系列ダッシュボードの標準設計

ROWS BETWEEN + LAG を組み合わせると、MA3・MA6・前月比・前年同月比（LAG(revenue,12)）を1クエリで4列並べた時系列 KPI ダッシュボードが構築できます。さらに PARTITION BY product_category を ROWS BETWEEN の OVER 句に加えるとカテゴリ別の移動平均を同時計算でき、BI ツールへのデータ供給クエリとして直接活用できます。「MA が revenue を上回り始めた月」をトレンド転換点として自動検出するロジックも、CASE WHEN revenue > ma3 AND LAG(revenue) <= LAG(ma3) のように1クエリに収められます。

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58

student_id	score
S01	67
S02	82
S03	55
S04	138
S05	63
S06	80
S07	42
S08	91
S09	71
S10	75
S11	85
S12	58