SQL 統計分析 — ランキング・期間比較・相関係数の応用

QUESTION 1

ROW_NUMBER / RANK / DENSE_RANK — 同スコアの順位付け挙動を比較する

ROW_NUMBERRANKDENSE_RANKランキング分析同率処理

前提知識 ▼

ウィンドウ関数によるランキングは3種類あり、同値（タイ）の扱い方が異なります。どれを使うかでビジネス要件への適合性が変わるため、違いを正確に把握することが重要です。

ROW_NUMBER() OVER (ORDER BY col)  -- 同値でも必ず連番。ORDER BYの順序が重要
RANK()       OVER (ORDER BY col)  -- 同値は同順位。次のランクは「同率件数分」スキップ
DENSE_RANK() OVER (ORDER BY col)  -- 同値は同順位。次のランクは必ず+1（スキップなし）

どれを使うべきか：順位の「連続性」が必要なら DENSE_RANK（例: 1位・2位・3位と表示したい）、「全体での位置（何番目の行か）」が必要なら ROW_NUMBER（例: ページネーション・重複排除）、「順位の空き」でビジネス的な意味を持たせたい場合は RANK（例: オリンピックの順位表示）を選びます。

問題 ▼

score_board テーブルから、スコア降順で各プレイヤーに ROW_NUMBER・RANK・DENSE_RANK の3種の順位を付与してください。取得列は player_id, score, row_num, rank, dense_rank、score 降順・player_id 昇順で返してください。

使用テーブル

▸ score_board（6行）

player_id	score
P1	980
P2	750
P3	980
P4	620
P5	750
P6	880

期待出力 ▼

player_id	score	row_num	rank	dense_rank
P1	980	1	1	1
P3	980	2	1	1
P6	880	3	3	2
P2	750	4	4	3
P5	750	5	4	3
P4	620	6	6	4

模範解答コード ▼

SELECT
  player_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id)  AS row_num,     -- 同値でも連番（player_idで決定論的に）
  RANK()       OVER (ORDER BY score DESC)              AS rank,       -- 同値→同順位、次はスキップ
  DENSE_RANK() OVER (ORDER BY score DESC)              AS dense_rank  -- 同値→同順位、次は+1で連続
FROM   score_board
ORDER BY score DESC, player_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM score_board                → 6行読込
  2. ウィンドウ関数の評価                      → ROW_NUMBER/RANK/DENSE_RANK で順位付け
  3. SELECT                          → 5列を選択
  4. ORDER BY score DESC, player_id  → 最終ソート
  */

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

SELECT
  player_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, player_id)  AS row_num,
  RANK()       OVER (ORDER BY score DESC)              AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC)              AS dense_rank
FROM   score_board
ORDER BY score DESC, player_id;

LEGEND ▼

データ取得・読込対象

① FROM score_board — 入力データ6行 ▼

FROM score_boardscore_board テーブルの全6行を読み込みます。この時点ではスコアは任意の順序です。P1とP3は共にscore=980、P2とP5は共にscore=750という同値が存在します。

1 / 5

player_id	▸ score
P1	980
P2	750
P3	980
P4	620
P5	750
P6	880

score_board: 6行（同値が2組存在: 980×2, 750×2）

学習ポイント

RANK の「スキップ」が生む意味：score=980が2人いる場合、RANK は 1・1・3 と返します（2位なし）。これは「3番目に強いプレイヤーがいても、上に2人いる」ことを明示します。スポーツ競技・試験の順位表示で一般的です。一方 DENSE_RANK は 1・1・2 と返し「重複を除いた何番目のランク帯か」を示します。

ROW_NUMBER は ORDER BY を一意にしないと非決定論的：ROW_NUMBER() OVER (ORDER BY score DESC) だけでは、同スコア(P1/P3)のどちらが1番になるかが実行のたびに変わる可能性があります。ORDER BY に player_id などの一意キーを追加することで常に同じ結果を保証できます。ページネーション実装には特に重要です。

PARTITION BY と組み合わせるとグループ内ランキングに：RANK() OVER (PARTITION BY department ORDER BY salary DESC) のように PARTITION BY を追加すると「部門内ランキング」が実現できます。各部門の1位を抽出する際は WHERE rank = 1 のように使います（サブクエリか CTE 経由）。これが実務で最も頻繁に使われるランキングパターンです。

アンチパターン

ROW_NUMBER で「上位N件」を取ろうとして ORDER BY を省略する：WHERE ROW_NUMBER() OVER () = 1 のように ORDER BY なしで ROW_NUMBER を使うと、どの行が1番目になるかが非決定的です。必ず ORDER BY を指定してランキング基準を明示してください。

RANK で "上位N件" を抽出すると件数が変動する：WHERE rank <= 3 で上位3位を取ろうとしても、3位が同率2人いると4件が返ります。件数を厳密にN件にしたい場合は ROW_NUMBER、「3位タイまで全員」取りたい場合は RANK または DENSE_RANK、という要件に応じた使い分けが必要です。

実務コラム：ランキング関数とサブクエリの組み合わせ

ランキング関数の結果を WHERE 句で絞り込む場合、ウィンドウ関数は SELECT の評価段階で計算されるため WHERE 句の中に直接書けません。CTE またはサブクエリ経由で絞り込む必要があります。WITH ranked AS (SELECT *, RANK() OVER (...) AS r FROM t) SELECT * FROM ranked WHERE r = 1 というパターンを覚えておくと、「各グループの最高値行を取得」「重複排除で最新レコードを1件取得」など多くの実務パターンに対応できます。

QUESTION 2

LAG / LEAD — 前月比成長率を1クエリで算出する

LAGLEAD期間比較前月比成長率NULLIF

前提知識 ▼

LAG / LEAD は、現在行から見て「N行前 / N行後」の値を参照するウィンドウ関数です。GROUP BY による自己結合なしに前期・翌期との差分を計算できます。

LAG(col, 1)  OVER (ORDER BY date_col)  -- 1行前の値（デフォルトオフセット=1）
LAG(col, 3)  OVER (ORDER BY date_col)  -- 3行前（四半期比較など）
LEAD(col, 1) OVER (ORDER BY date_col)  -- 1行後の値

-- ゼロ除算を防ぐ: NULLIF(val, 0) → val が 0 なら NULL を返す
NULLIF(prev_revenue, 0)   -- 0除算ではなく NULL を返す（実務必須）

先頭・末尾行の NULL：LAG は最初の行（前行が存在しない）を NULL で返します。LEAD は最終行（次行が存在しない）を NULL で返します。NULL を 0% 成長として扱うか除外するかは要件次第です。第3引数でデフォルト値を指定することもできます: LAG(col, 1, 0)。

問題 ▼

monthly_revenue テーブルから、各月の売上・前月売上・前月比成長率（%）を算出してください。取得列は month, revenue, prev_revenue, growth_rate_pct（成長率は2桁丸め）、month 昇順で返してください。先頭月は prev_revenue・growth_rate_pct ともに NULL になります。

使用テーブル

▸ monthly_revenue（6行）

month	revenue
2024-01	1000000
2024-02	1200000
2024-03	1100000
2024-04	1350000
2024-05	1500000
2024-06	1420000

期待出力 ▼

month	revenue	prev_revenue	growth_rate_pct
2024-01	1000000	NULL	NULL
2024-02	1200000	1000000	20.00
2024-03	1100000	1200000	-8.33
2024-04	1350000	1100000	22.73
2024-05	1500000	1350000	11.11
2024-06	1420000	1500000	-5.33

模範解答コード ▼

WITH lag_data AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)  AS prev_revenue  -- 1行前の売上（先頭行はNULL）
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    100.0 * (revenue - prev_revenue)
           / NULLIF(prev_revenue, 0),  -- prev_revenue=0 なら NULL を返しゼロ除算を回避
    2
  ) AS growth_rate_pct
FROM   lag_data
ORDER BY month;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE lag_data    → 前月の revenue を付与
  2. 外側クエリ           → 前月比を計算し ROUND
  3. ORDER BY month  → 月昇順
  */

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

WITH lag_data AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)  AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    100.0 * (revenue - prev_revenue)
           / NULLIF(prev_revenue, 0),
    2
  ) AS growth_rate_pct
FROM   lag_data
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① CTE: FROM monthly_revenue — 月次売上データ読込 ▼

FROM monthly_revenue (in CTE lag_data)monthly_revenue テーブルの全6行を読み込みます。この段階では前月データはありません。次のステップで LAG 関数が各行の1行前の revenue を参照します。

1 / 5

month	▸ revenue
2024-01	1000000
2024-02	1200000
2024-03	1100000
2024-04	1350000
2024-05	1500000
2024-06	1420000

monthly_revenue: 6行

学習ポイント

CTE で LAG を1度だけ計算する設計の重要性：CTE を使わず直接 ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) と書くと LAG 関数が2回評価され可読性が著しく下がります。ウィンドウ関数の結果を再利用する場合は CTE で一度計算してから参照するのが実務の標準パターンです。

LAG(col, N, default) の第3引数でNULL制御：LAG(revenue, 1, 0) と書くと前月が存在しない先頭行を NULL ではなく 0 として扱えます。ただしこの場合 成長率の分母が 0 になりゼロ除算が発生するため、NULLIF による防御は常に必要です。要件に応じて NULL のままにするか 0 や前月値を埋めるかを明確にしておきましょう。

LEAD を使うと「翌月予実対比」も同じ構造で実現できる：LEAD(revenue) OVER (ORDER BY month) は1行後（翌月）の値を参照します。予算テーブルに実績を結合した上で 当月実績 vs 翌月予算の乖離を計算するダッシュボード指標でよく使われます。LAG と LEAD は対称な概念なので、どちらかを理解すれば両方使えます。

アンチパターン

NULLIF を省いてゼロ除算エラーに気づかない：実データでは「前月売上が 0」のケース（サービス停止月など）が稀に発生します。(revenue - prev_revenue) / prev_revenue と書くと PostgreSQL は division by zero エラーを返します。除算の分母には必ず NULLIF(分母, 0) を付けてください。

LAG の OVER 内の ORDER BY と外側クエリの ORDER BY を混同する：LAG(revenue) OVER (ORDER BY month) の ORDER BY はウィンドウ内の処理順を決定し、FROM ... ORDER BY month は出力行の並び順を決定します。外側の ORDER BY を省略しても OVER 内の ORDER BY があれば計算は正しく行われますが、出力順序は保証されません。両方を必ず明記してください。

実務コラム：YoY（前年同月比）への拡張

月次データが1年以上ある場合、前月比ではなく前年同月比（YoY）の方がビジネス的に意義深いケースが多いです。LAG(revenue, 12) OVER (ORDER BY month) とオフセットを12にするだけで前年同月の売上を取得できます。季節性（年末商戦など）の影響を除いた成長率比較が可能になり、トレンドが本物か季節要因かを判断できます。実務のダッシュボードでは MoM（前月比）と YoY（前年同月比）を両方表示するのが標準です。

QUESTION 3

累積合計・累積比率 — SUM OVER で売上のパレート分析を行う

SUM OVERUNBOUNDED PRECEDING累積合計パレート分析累積比率

前提知識 ▼

累積合計（Running Total）は「先頭行から現在行まで」を積み上げた合計で、ウィンドウ関数のフレーム指定で実現します。累積比率（%）を組み合わせると 80:20の法則（パレートの法則）の確認が1クエリで完結します。

SUM(col) OVER (
  ORDER BY col DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭〜現在行の合計
)                                                  → 各行での累積合計

SUM(col) OVER ()                                    → 全行の総合計（PARTITION BY なし）

ROWS BETWEEN vs デフォルトフレームの罠：ORDER BY 付きの OVER 句でフレーム指定を省略すると、デフォルトは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（値ベース）になります。同値の行が複数ある場合、RANGE は同値の行を全てフレームに含めてしまうため累積合計の結果が変わります。累積合計には必ず ROWS BETWEEN を明示してください。

問題 ▼

category_sales テーブルから、売上降順で累積合計と累積比率（%）を算出してください。CTE で累積合計と総合計を計算し、外側クエリで比率を求めてください。取得列は category, sales_amount, cum_sales, cum_pct（cum_pct は2桁丸め）、sales_amount 降順で返してください。

使用テーブル

▸ category_sales（5行）

category	sales_amount
Electronics	4800000
Apparel	3200000
Food	2100000
Books	1400000
Others	900000

期待出力 ▼

category	sales_amount	cum_sales	cum_pct
Electronics	4800000	4800000	38.71
Apparel	3200000	8000000	64.52
Food	2100000	10100000	81.45
Books	1400000	11500000	92.74
Others	900000	12400000	100.00

模範解答コード ▼

WITH running AS (
  SELECT
    category,
    sales_amount,
    SUM(sales_amount) OVER (
      ORDER BY sales_amount DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭行〜現在行の累積合計
    )                             AS cum_sales,
    SUM(sales_amount) OVER ()     AS grand_total  -- フレームなし → 全行合計
  FROM category_sales
)
SELECT
  category,
  sales_amount,
  cum_sales,
  ROUND(100.0 * cum_sales / grand_total, 2)  AS cum_pct  -- 累積比率(%)の計算
FROM   running
ORDER BY sales_amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE running
  2. 外側クエリ
  3. ORDER BY sales_amount DESC  → 売上降順
  */

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

WITH running AS (
  SELECT
    category,
    sales_amount,
    SUM(sales_amount) OVER (
      ORDER BY sales_amount DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                             AS cum_sales,
    SUM(sales_amount) OVER ()     AS grand_total
  FROM category_sales
)
SELECT
  category,
  sales_amount,
  cum_sales,
  ROUND(100.0 * cum_sales / grand_total, 2)  AS cum_pct
FROM   running
ORDER BY sales_amount DESC;

LEGEND ▼

データ取得・読込対象

① CTE: FROM category_sales — カテゴリ別売上データ読込 ▼

FROM category_sales (in CTE running)category_sales テーブルの全5行を読み込みます。次のステップで SUM OVER により各行に累積合計が付与されます。総合計は 4,800,000+3,200,000+2,100,000+1,400,000+900,000 = 12,400,000 になります。

1 / 5

category	▸ sales_amount
Electronics	4800000
Apparel	3200000
Food	2100000
Books	1400000
Others	900000

category_sales: 5行（総合計 = 12,400,000）

学習ポイント

ROWS BETWEEN vs RANGE BETWEEN の重要な差異：フレームを省略すると ORDER BY 付きの場合デフォルトは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（値ベース）になります。同じ sales_amount の行が複数あると、RANGE はそれらを同一フレームに含め「累積合計が先行して加算」されます。累積合計には必ず ROWS BETWEEN を明示してください。

SUM OVER () の "全行合計" 活用：SUM(col) OVER () は PARTITION BY も ORDER BY もフレームも指定しないため、全行の合計が全行に同じ値でコピーされます。このテクニックで「グループ内の合計に対する各行のシェア」を1クエリで計算できます。PARTITION BY を追加すると「部門内シェア」にも拡張できます。

パレート分析のSQLへの応用：累積比率が 80% を超える行を特定することで「売上の80%を占める上位カテゴリ・商品・顧客」を識別できます。WHERE cum_pct <= 80 と絞り込むと「パレートライン内」の行だけを取得できます。このパターンは商品管理（重点管理品目）・顧客セグメント（重点顧客）・バグ管理（影響大のバグ）など多くの分析に応用できます。

アンチパターン

ORDER BY 省略で "累積" ではなく "全合計" になる：SUM(sales_amount) OVER () と SUM(sales_amount) OVER (ORDER BY sales_amount DESC) は全く異なります。ORDER BY を省略すると全行合計（grand_total と同じ値）が全行にコピーされ、累積合計になりません。累積合計には必ず ORDER BY を指定し、ROWS BETWEEN も明示してください。

100 * cum_sales / grand_total（整数除算）で比率が 0 になる：100 * cum_sales / grand_total と整数同士で計算すると PostgreSQL では整数除算が行われ、比率が 0 や切り捨て値になります。100.0 * cum_sales / grand_total のように小数リテラルを使うか、cum_sales::numeric / grand_total * 100 とキャストしてください。

実務コラム：累積比率と ABC 分析

在庫管理の世界では累積売上比率で商品を A（上位70%）・B（70〜90%）・C（90〜100%）に分類する「ABC分析」が標準手法です。CASE WHEN cum_pct <= 70 THEN 'A' WHEN cum_pct <= 90 THEN 'B' ELSE 'C' END AS abc_rank を追加するだけでABC分類が完成します。Aランク商品は欠品リスクを徹底管理し、Cランク商品は発注頻度を下げるという在庫最適化の意思決定が、このSQLパターン1つで支援できます。

QUESTION 4

NTILE — 四分位によるユーザー購買セグメント分類

NTILECASE WHENセグメント分析四分位CTE

前提知識 ▼

NTILE(n) は、ORDER BY で並べた行を n 等分したバケット番号（1〜n）を各行に付与するウィンドウ関数です。等頻度でグループ分けできる点が、固定閾値で分割する CASE WHEN と根本的に異なります。

NTILE(4) OVER (ORDER BY col DESC)
-- 全行を高い順に並べて4等分。行数が4の倍数でない場合、余りは上位バケットに配分
-- 例: 9行を4分割 → バケット1 が 3行、バケット2〜4 が各2行 ← 余りを前から順に1行ずつ配分

-- WIDTH_BUCKET との違い（基礎編参照）
WIDTH_BUCKET: 等幅バケット（区間の幅が同じ、件数は不均等になり得る）
NTILE:        等頻度バケット（件数が均等、区間の幅は不均等になり得る）

NTILE の結果を CASE WHEN でラベル化：NTILE が返すバケット番号を直接表示するより、ビジネス用語に変換（1=VIP, 2=Gold など）するのが実務の定番です。CTE でNTILEを計算してから CASE WHEN を適用することで、同じ OVER 句の二重記述を避けられます。

問題 ▼

user_spending テーブルの購買金額を NTILE(4) で高い順に四分位分類し、VIP / Gold / Silver / Bronze のセグメントラベルを付与してください。取得列は user_id, total_spend, quartile, segment、total_spend 降順で返してください。

使用テーブル

▸ user_spending（8行）

user_id	total_spend
U01	15000
U02	8500
U03	52000
U04	3200
U05	28000
U06	6400
U07	41000
U08	1800

期待出力 ▼

user_id	total_spend	quartile	segment
U03	52000	1	VIP
U07	41000	1	VIP
U05	28000	2	Gold
U01	15000	2	Gold
U02	8500	3	Silver
U06	6400	3	Silver
U04	3200	4	Bronze
U08	1800	4	Bronze

模範解答コード ▼

WITH ranked AS (
  SELECT
    user_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC)  AS quartile  -- 上位から4等分
  FROM user_spending
)
SELECT
  user_id,
  total_spend,
  quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
    ELSE        'Bronze'
  END AS segment
FROM   ranked
ORDER BY total_spend DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE ranked
  2. 外側クエリ
  3. ORDER BY total_spend DESC  → 金額降順
  */

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

WITH ranked AS (
  SELECT
    user_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC)  AS quartile
  FROM user_spending
)
SELECT
  user_id,
  total_spend,
  quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
    ELSE        'Bronze'
  END AS segment
FROM   ranked
ORDER BY total_spend DESC;

LEGEND ▼

データ取得・読込対象

① CTE: FROM user_spending — ユーザー購買データ読込 ▼

FROM user_spending (in CTE ranked)user_spending テーブルの全8行を読み込みます。total_spend の範囲は 1,800〜52,000 と広く分散しています。

1 / 5

user_id	▸ total_spend
U01	15000
U02	8500
U03	52000
U04	3200
U05	28000
U06	6400
U07	41000
U08	1800

user_spending: 8行（total_spend: 1,800〜52,000）

学習ポイント

NTILE と PERCENTILE_DISC の使い分け：基礎編Q2で学んだ PERCENTILE_DISC(0.25) は「第1四分位点の値（閾値）」を返しますが、NTILE(4) は「各行がどの四分位に属するか」のバケット番号を返します。分析したいのが「閾値を知りたい」なら PERCENTILE_DISC、「各ユーザーをQ1〜分類したい」なら NTILE という使い分けが実務標準です。

余りがある場合のバケット配分ルール：行数が n の倍数でない場合（例: 9行÷4）、余り分（1行）は上位バケットから順に1行ずつ追加配分されます（バケット1が3行、バケット2〜4が2行）。このルールにより、バケット1（上位グループ）の件数が他より多くなることがあるため、厳密に等件数が必要な要件では注意が必要です。

セグメント別の統計を追加するには GROUP BY を組み合わせる：CTE でNTILEを付与した後、GROUP BY segment + 集計関数でセグメント別の平均・最小・最大購買額を1クエリで算出できます。例:

SELECT segment, COUNT(*), AVG(total_spend), MIN(total_spend), MAX(total_spend) FROM ranked GROUP BY segment ORDER BY MIN(total_spend) DESC

。この「NTILE → GROUP BY 集計」パターンは顧客分析の定番です。

アンチパターン

固定閾値で分類しても「均等分類」にはならない：CASE WHEN total_spend >= 30000 THEN 'VIP' WHEN total_spend >= 10000 THEN 'Gold' ... と固定閾値で分類すると、データが偏っている場合（高額ユーザーが1人だけなど）特定バケットに件数が集中します。「件数を均等にしたい」ビジネス要件には必ず NTILE を使ってください。

NTILE の ORDER BY を省略すると非決定的な分類になる：NTILE(4) OVER () のように ORDER BY を省略すると行の処理順が不定になりバケット番号が実行のたびに変わります。NTILE には必ず ORDER BY を指定してください。また ORDER BY に一意性がない（同値が多い）場合は、player_id などの一意キーを追加して結果を安定化させましょう。

実務コラム：十分位（デシル）分析への拡張

マーケティングでは四分位（NTILE(4)）よりさらに細かく十分位（Decile）分析が使われます。NTILE(10) OVER (ORDER BY total_spend DESC) で購買額上位10%（Decile 1）〜下位10%（Decile 10）に分類し、Decile 1 の平均購買額が全体平均の何倍かを分析します。ECサイトでは「Decile 1 のユーザー向け限定オファー」「Decile 8〜10 の離反防止施策」のように階層ごとの施策設計に直結します。NTILE(10) に変えるだけでこのパターンが即座に実現できます。

QUESTION 5

CORR — 広告費と売上の相関係数で投資効率を統計的に分析する

CORRCOVAR_SAMP相関分析相関係数統計検定

前提知識 ▼

相関係数（CORR）は2変数の線形関係の強さを -1〜1 の数値で表します。PostgreSQL の CORR(y, x) 関数は標本相関係数を返します。

CORR(y, x)        -- 標本相関係数（-1〜1）: 線形関係の強さと方向
COVAR_SAMP(y, x) -- 標本共分散（n-1 で割る）: CORR の "分子に相当"
COVAR_POP(y, x)  -- 母共分散（n で割る）

-- CORR の内部計算:
-- CORR(y, x) = COVAR_SAMP(y, x) / (STDDEV_SAMP(x) * STDDEV_SAMP(y))

相関係数の解釈ガイドライン：|r| ≥ 0.9 → 非常に強い相関 / |r| 0.7〜0.9 → 強い相関 / |r| 0.5〜0.7 → 中程度 / |r| < 0.5 → 弱い相関。ただし相関係数は線形関係の強さしか測りません。非線形関係・外れ値の影響を必ず確認してください。「相関関係は因果関係を意味しない」点も重要です（第三因子の存在）。

問題 ▼

ad_performance テーブルから、広告費（ad_spend）と売上（revenue）の相関係数・各平均・データ件数を1行で算出してください。取得列は corr_spend_revenue, avg_spend, avg_revenue, data_points（相関係数は2桁丸め、各平均は整数丸め）。

使用テーブル

▸ ad_performance（5行）

channel	ad_spend	revenue
Search	500000	2000000
Social	300000	2000000
Display	100000	500000
Video	400000	1800000
Email	200000	700000

期待出力 ▼

corr_spend_revenue	avg_spend	avg_revenue	data_points
0.88	300000	1400000	5

模範解答コード ▼

SELECT
  ROUND(CORR(revenue, ad_spend)::numeric, 2)  AS corr_spend_revenue,  -- 相関係数（-1〜1）
  ROUND(AVG(ad_spend))                       AS avg_spend,           -- 平均広告費（整数丸め）
  ROUND(AVG(revenue))                        AS avg_revenue,         -- 平均売上（整数丸め）
  COUNT(*)                                    AS data_points          -- データ件数
FROM ad_performance;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM ad_performance           → 5行読込
  2. CORR(revenue, ad_spend)       → 相関係数を計算
  3. AVG(ad_spend) / AVG(revenue)  → 平均を計算
  4. COUNT(*)                      → 件数を集計
  5. SELECT                        → 1行出力
  */

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

SELECT
  ROUND(CORR(revenue, ad_spend)::numeric, 2)  AS corr_spend_revenue,
  ROUND(AVG(ad_spend))                       AS avg_spend,
  ROUND(AVG(revenue))                        AS avg_revenue,
  COUNT(*)                                    AS data_points
FROM ad_performance;

LEGEND ▼

データ取得・読込対象

① FROM ad_performance — チャンネル別広告データ読込 ▼

FROM ad_performancead_performance テーブルの全5行を読み込みます。Search(500K→2,000K)・Video(400K→1,800K)は高広告費・高売上。Display(100K→500K)は低広告費・低売上です。

1 / 5

channel	▸ ad_spend	▸ revenue
Search	500000	2000000
Social	300000	2000000
Display	100000	500000
Video	400000	1800000
Email	200000	700000

ad_performance: 5行（2変数の関係性を分析）

学習ポイント

CORR の内部構造を理解する：CORR(y, x) = COVAR_SAMP(y, x) / (STDDEV_SAMP(x) × STDDEV_SAMP(y)) と等価です。COVAR_SAMP(revenue, ad_spend) で共分散（偏差積の平均）を確認し、どのチャンネルがCORRを押し上げているかを逆算することも実務では重要です。CORR が 0.88 でも、1チャンネルの外れ値が相関を歪めている場合があります。

相関係数と ROI（費用対効果）の違い：相関係数は「全体的な傾向」を捉えますが、チャンネル別の ROI (revenue / ad_spend) は個別の投資効率を示します。Social の ROI = 2,000,000 / 300,000 ≈ 6.67 は Search の ROI = 4.0 より高く、広告費増加の候補チャンネルが分かります。CORR は全体構造を把握し、個別 ROI で意思決定するという2段階分析が実務標準です。

::numeric キャストと ROUND の精度：PostgreSQL の CORR は double precision を返します。ROUND(CORR(...)::numeric, 2) のように ::numeric にキャストしてから ROUND を適用しないと型エラーになります（STDDEV・AVG も同様）。実務でよく見るエラーパターンなので、集計関数と ROUND を組み合わせる際は必ず ::numeric キャストを付ける習慣を身につけましょう。

アンチパターン

CORR だけで因果関係を結論付ける：CORR = 0.88 を見て「広告費を増やせば売上が増える」と結論付けるのは危険です。両方が景気の影響を受けている（第三因子）可能性や、逆因果（売上が高いから広告を増やしている）の可能性があります。相関係数はあくまで仮説生成ツールであり、A/Bテストなど実験設計で因果を検証する必要があります。

サンプルサイズが小さいと CORR は不安定：5行のデータでの CORR = 0.88 は、外れ値1つで大きく変動します。CORR を信頼できる目安として扱うには最低でも 30 件以上のデータが推奨されます（統計学的には p 値・信頼区間の確認が必要）。小規模データでは CORR の値よりも散布図の目視確認を優先してください。

実務コラム：マーケティングミックスと相関分析

複数の広告チャンネルを持つ企業では、チャンネル間の相関行列を CORR を複数組み合わせた SELECT で一度に算出できます。CORR(revenue, search_spend), CORR(revenue, social_spend), CORR(revenue, display_spend) を並べると「どのチャンネルが売上と最も相関しているか」の概要が分かります。ただし多重共線性（チャンネル間の相関）があると解釈が複雑になるため、重回帰分析や MMM（Marketing Mix Modeling）などより高度な統計手法と組み合わせることが実務では一般的です。SQLの CORR はその 探索的データ分析（EDA）の第一歩として使われます。