相関係数をSQLで計算する方法｜広告費と売上の関係を分析するクエリ

r = Σ(xi - x̄)(yi - ȳ) / √[Σ(xi - x̄)² × Σ(yi - ȳ)²]

r = (n × Σ(xi × yi) - Σxi × Σyi)
  / √[(n × Σxi² - (Σxi)²) × (n × Σyi² - (Σyi)²)]

-- monthly_ad_sales（月次広告費・売上テーブル）
-- sales_month   : 集計月（DATE型）
-- ad_spend      : 広告費（円）
-- total_sales   : 売上金額（円）
-- channel       : 広告チャネル（'search', 'display', 'social' など）

-- STEP1: 広告費と売上の相関係数を計算する

SELECT
    channel,
    COUNT(*)                              AS n,
    ROUND(AVG(ad_spend), 0)              AS avg_ad_spend,
    ROUND(AVG(total_sales), 0)           AS avg_total_sales,

    -- ピアソン相関係数
    ROUND(
        (
            COUNT(*) * SUM(ad_spend * total_sales)
            - SUM(ad_spend) * SUM(total_sales)
        )
        / NULLIF(
            SQRT(
                (COUNT(*) * SUM(ad_spend * ad_spend) - SUM(ad_spend) * SUM(ad_spend))
                *
                (COUNT(*) * SUM(total_sales * total_sales) - SUM(total_sales) * SUM(total_sales))
            )
        , 0)
    , 4)  AS pearson_r
FROM monthly_ad_sales
GROUP BY channel
ORDER BY pearson_r DESC;

-- STEP2: CORR() 関数を使った実装（Presto / BigQuery）

SELECT
    channel,
    COUNT(*)                              AS n,
    ROUND(CORR(ad_spend, total_sales), 4) AS pearson_r
FROM monthly_ad_sales
GROUP BY channel
ORDER BY pearson_r DESC;

-- STEP3: 相関係数にサンプル数と解釈ラベルを付与する

WITH correlation AS (
    SELECT
        channel,
        COUNT(*)                              AS n,
        ROUND(CORR(ad_spend, total_sales), 4) AS pearson_r
    FROM monthly_ad_sales
    GROUP BY channel
)
SELECT
    channel,
    n,
    pearson_r,

    -- 相関の強さをラベルで表示
    CASE
        WHEN ABS(pearson_r) >= 0.8  THEN '強い相関'
        WHEN ABS(pearson_r) >= 0.6  THEN '中程度の相関'
        WHEN ABS(pearson_r) >= 0.4  THEN '弱い相関'
        WHEN ABS(pearson_r) >= 0.2  THEN 'ごく弱い相関'
        ELSE                             'ほぼ無相関'
    END  AS correlation_label,

    -- 方向性
    CASE
        WHEN pearson_r > 0 THEN '正の相関（広告費↑→売上↑）'
        WHEN pearson_r < 0 THEN '負の相関（広告費↑→売上↓）'
        ELSE                    '無相関'
    END  AS direction,

    -- サンプル数の警告
    CASE
        WHEN n < 6  THEN '⚠ サンプル数が少なく信頼性が低い'
        WHEN n < 12 THEN '△ サンプル数がやや少ない'
        ELSE             '○ サンプル数は十分'
    END  AS sample_warning
FROM correlation
ORDER BY ABS(pearson_r) DESC;

-- STEP4: 散布図用データの出力（標準化も行う）

WITH stats AS (
    SELECT
        channel,
        AVG(ad_spend)     AS avg_x,
        STDDEV(ad_spend)  AS std_x,
        AVG(total_sales)  AS avg_y,
        STDDEV(total_sales) AS std_y
    FROM monthly_ad_sales
    GROUP BY channel
)
SELECT
    m.sales_month,
    m.channel,
    m.ad_spend,
    m.total_sales,

    -- 標準化（Zスコア）：どちらの変数も平均0・標準偏差1に変換
    ROUND((m.ad_spend    - s.avg_x) / NULLIF(s.std_x, 0), 3)  AS ad_spend_z,
    ROUND((m.total_sales - s.avg_y) / NULLIF(s.std_y, 0), 3)  AS total_sales_z

FROM monthly_ad_sales  m
JOIN stats             s  ON m.channel = s.channel
ORDER BY m.channel, m.sales_month;

-- 前半6ヶ月と後半6ヶ月の相関係数を比較する

WITH period_labels AS (
    SELECT
        *,
        CASE
            WHEN sales_month < DATE '2024-07-01' THEN '前半（1〜6月）'
            ELSE                                      '後半（7〜12月）'
        END  AS period
    FROM monthly_ad_sales
    WHERE sales_month >= DATE '2024-01-01'
    AND   sales_month <  DATE '2025-01-01'
)
SELECT
    channel,
    period,
    COUNT(*)                              AS n,
    ROUND(CORR(ad_spend, total_sales), 4) AS pearson_r
FROM period_labels
GROUP BY channel, period
ORDER BY channel, period;