SQLでランダムサンプリングとA/Bテスト割当を行う方法

フェーズ1：母集団から対象者をサンプリングする
         （全顧客の中から「今回のテスト対象」を絞り込む）

フェーズ2：サンプリングした対象者をA/Bグループに割り振る
         （ランダムかつ再現性のある方法で）

-- customers テーブル
-- customer_id   : 顧客ID
-- registered_at : 会員登録日
-- channel       : 初回流入チャネル
-- opt_out_flag  : メルマガ配信停止フラグ（1=停止）

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

-- STEP1: RAND() で全顧客の約10%をサンプリング

SELECT
    customer_id,
    RAND() AS random_value
FROM customers
WHERE opt_out_flag = 0
HAVING random_value < 0.10  -- 10%をサンプリング
ORDER BY random_value;

-- STEP2: ハッシュによる再現性のあるサンプリング

SELECT
    customer_id,
    ABS(HASH(customer_id)) % 100  AS hash_bucket  -- 0〜99の整数
FROM customers
WHERE opt_out_flag = 0;

-- 約10%をサンプリング（再現性あり）
SELECT customer_id
FROM customers
WHERE opt_out_flag = 0
AND ABS(HASH(customer_id)) % 100 < 10
ORDER BY customer_id;

-- STEP3: A/Bグループへの割り振り（再現性あり）

WITH target_population AS (
    -- テスト対象の母集団を定義
    -- 条件：直近6ヶ月に購買あり・配信停止でない・登録から30日以上経過
    SELECT
        c.customer_id,
        c.registered_at,
        c.channel,
        MAX(o.order_date)  AS last_order_date
    FROM customers  c
    JOIN orders     o ON c.customer_id = o.customer_id
    WHERE
        c.opt_out_flag = 0
        AND o.status   = 'completed'
        AND o.order_date >= DATE_ADD('month', -6, CURRENT_DATE)
        AND c.registered_at <= DATE_ADD('day', -30, CURRENT_DATE)
    GROUP BY c.customer_id, c.registered_at, c.channel
),
ab_assigned AS (
    SELECT
        customer_id,
        registered_at,
        channel,
        last_order_date,

        -- ハッシュバケット（0〜99）
        ABS(HASH(customer_id)) % 100  AS hash_bucket,

        -- A/Bグループ割り振り
        -- 0〜49 → グループA（コントロール：施策なし）
        -- 50〜99 → グループB（トリートメント：施策あり）
        CASE
            WHEN ABS(HASH(customer_id)) % 100 < 50 THEN 'A_control'
            ELSE                                        'B_treatment'
        END  AS ab_group
    FROM target_population
)
SELECT
    ab_group,
    COUNT(customer_id)           AS customer_count,
    ROUND(COUNT(customer_id) * 100.0 / SUM(COUNT(customer_id)) OVER (), 1)  AS pct,
    ROUND(AVG(DATEDIFF(CURRENT_DATE, last_order_date)), 1)  AS avg_days_since_last_order
FROM ab_assigned
GROUP BY ab_group
ORDER BY ab_group;

-- STEP4: テストIDをソルトとして使い、テストごとに独立したグループ分け

WITH test_config AS (
    SELECT 'TEST_2024_DEC_EMAIL_SUBJECT' AS test_id  -- テストの識別子
),
ab_assigned AS (
    SELECT
        c.customer_id,

        -- customer_id とテストIDを連結してハッシュ化
        ABS(HASH(c.customer_id || '_' || t.test_id)) % 100  AS hash_bucket,

        CASE
            WHEN ABS(HASH(c.customer_id || '_' || t.test_id)) % 100 < 50
            THEN 'A_control'
            ELSE 'B_treatment'
        END  AS ab_group
    FROM customers      c
    CROSS JOIN test_config  t
    WHERE c.opt_out_flag = 0
)
SELECT
    customer_id,
    hash_bucket,
    ab_group
FROM ab_assigned
ORDER BY customer_id;

-- STEP5: A/Bテスト結果の集計

WITH test_config AS (
    SELECT
        'TEST_2024_DEC_EMAIL_SUBJECT' AS test_id,
        DATE '2024-12-01'             AS test_start,
        DATE '2024-12-08'             AS test_end    -- 計測期間（7日間）
),
target_population AS (
    SELECT
        c.customer_id,
        CASE
            WHEN ABS(HASH(c.customer_id || '_' || t.test_id)) % 100 < 50
            THEN 'A_control'
            ELSE 'B_treatment'
        END  AS ab_group
    FROM customers      c
    CROSS JOIN test_config  t
    WHERE c.opt_out_flag = 0
    -- テスト前の条件で対象者を絞る（直近6ヶ月購買あり など）
),
post_test_orders AS (
    -- テスト期間中の購買データ
    SELECT
        o.customer_id,
        COUNT(DISTINCT o.order_id)  AS order_count,
        SUM(o.total_amount)         AS total_sales
    FROM orders       o
    CROSS JOIN test_config  t
    WHERE
        o.status    = 'completed'
        AND o.order_date >= t.test_start
        AND o.order_date <  t.test_end
    GROUP BY o.customer_id
)
SELECT
    tp.ab_group,

    -- 母数
    COUNT(tp.customer_id)                              AS total_customers,

    -- 購買者数と転換率
    COUNT(po.customer_id)                              AS purchasers,
    ROUND(COUNT(po.customer_id) * 100.0
        / NULLIF(COUNT(tp.customer_id), 0), 2)         AS conversion_rate_pct,

    -- 売上
    COALESCE(SUM(po.total_sales), 0)                   AS total_sales,
    COALESCE(ROUND(AVG(po.total_sales), 0), 0)         AS avg_sales_per_converter,

    -- 1顧客あたりの売上（転換しなかった顧客も含む）
    ROUND(COALESCE(SUM(po.total_sales), 0) * 1.0
        / NULLIF(COUNT(tp.customer_id), 0), 0)         AS revenue_per_customer
FROM target_population      tp
LEFT JOIN post_test_orders  po  ON tp.customer_id = po.customer_id
GROUP BY tp.ab_group
ORDER BY tp.ab_group;

-- STEP6: 転換率の差分と信頼区間の近似計算

WITH ab_results AS (
    -- STEP5の結果を再利用（実際はCTEとして連結する）
    SELECT 'A_control'   AS group_name, 8412 AS n, 1178 AS conversions
    UNION ALL
    SELECT 'B_treatment'            , 8489       , 1443
),
with_rates AS (
    SELECT
        group_name,
        n,
        conversions,
        ROUND(conversions * 1.0 / n, 4)  AS rate
    FROM ab_results
),
-- 制御群の転換率
control_rate AS (
    SELECT rate AS cr FROM with_rates WHERE group_name = 'A_control'
),
-- 処置群の転換率
treatment_rate AS (
    SELECT rate AS tr FROM with_rates WHERE group_name = 'B_treatment'
)
SELECT
    cr.cr                                          AS control_rate,
    tr.tr                                          AS treatment_rate,
    ROUND((tr.tr - cr.cr) * 100, 2)               AS absolute_lift_pct,
    ROUND((tr.tr - cr.cr) / cr.cr * 100, 2)       AS relative_lift_pct,

    -- プールした標準誤差（正規近似によるz検定の近似）
    ROUND(
        SQRT(
            ((cr.cr * (1 - cr.cr)) / 8412)
            + ((tr.tr * (1 - tr.tr)) / 8489)
        )
    , 6)  AS pooled_se,

    -- z値（1.96以上で95%水準で有意と近似）
    ROUND(
        (tr.tr - cr.cr)
        / NULLIF(
            SQRT(
                ((cr.cr * (1 - cr.cr)) / 8412)
                + ((tr.tr * (1 - tr.tr)) / 8489)
            )
        , 0)
    , 3)  AS z_score
FROM control_rate cr, treatment_rate tr;

-- ab_test_registry テーブルの設計例
-- test_id        : テストID
-- test_name      : テスト名（「12月メール件名A/Bテスト」など）
-- test_start     : 開始日
-- test_end       : 終了日
-- salt           : ハッシュに使ったソルト文字列
-- split_ratio    : 分割比率（例：0.5 = 50:50）
-- target_criteria: 対象者の条件（JSONまたはテキスト）
-- created_by     : 実施者
-- created_at     : 登録日時

最低サンプルサイズ（各グループ）=
    (z_α/2 + z_β)² × p(1-p) × 2
    / (最小検出差異)²

where:
    z_α/2 = 1.96（有意水準5%）
    z_β   = 0.84（検出力80%）
    p     = 現在の転換率（基準値）
    最小検出差異 = 検出したい転換率の差（例：0.02 = 2ポイント改善）

-- サンプルサイズ計算（SQLで見積もる）

WITH params AS (
    SELECT
        0.14   AS base_rate,          -- 現在の転換率
        0.03   AS minimum_detectable_effect,  -- 最低検出差異（3ポイント改善）
        1.96   AS z_alpha,            -- 有意水準5%
        0.84   AS z_beta              -- 検出力80%
)
SELECT
    CEIL(
        POWER(z_alpha + z_beta, 2)
        * 2 * base_rate * (1 - base_rate)
        / POWER(minimum_detectable_effect, 2)
    )  AS required_sample_size_per_group
FROM params;