SQLにおけるリフト、統計的有意性、ガードレール
コンバージョンリフトと、実験の異常を検出するデータチェックを計算します。
「SQLにおけるリフト、統計的有意性、ガードレール」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
指標から意思決定へ
バリアントごとのコンバージョン率は出発点にすぎません。面接で問われるのは、treatment が実際に勝ったかということです。そのためには、リフトを計算し、その差が実際の効果なのかノイズなのかを見極め、実験の不具合を検出するガードレール指標を確認します。
SQLで完全な統計パッケージを実行することはありませんが、必要な入力値と、面接官が確認したい大まかな有意性のシグナルは計算できます。
バリアントごとのサマリーCTE
以降の処理は、1つの整然としたサマリーを基盤にします。バリアントごとに、ユーザー数 n、コンバージョンユーザー数 c、コンバージョン率 p を持たせます。CTEで一度計算し、再利用してください。
WITH summary AS (
SELECT
variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat
GROUP BY variant
)
SELECT
variant, n, c,
1.0 * c / n AS p
FROM summary;絶対リフトと相対リフト
リフトには2つの定義があり、面接では通常、相対リフトが求められます。
- 絶対リフト = p_treatment - p_control（パーセントポイント）。
- 相対リフト = (p_treatment - p_control) / p_control（改善率）。
「2ポイントの増加」と「相対リフト20%」は、同じ結果を表すことがあります。どちらを示しているのかを明確にしてください。
セルフピボットでリフトを計算する
2つのバリアントを1行で比較するには、条件付き集約を使って control と treatment を横に並べ、その後で計算します。
これにより、壊れやすいセルフジョインを避けながら、リフトの式を読みやすく保てます。
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
),
rates AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p_ctrl,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p_trt
FROM s
)
SELECT
p_ctrl, p_trt,
p_trt - p_ctrl AS abs_lift,
ROUND(100.0 * (p_trt - p_ctrl) / p_ctrl, 2) AS rel_lift_pct
FROM rates;差がノイズである可能性
treatment の率が高くても、無作為抽出による偶然の結果かもしれません。有意性が問うのは、バリアントが本当に同一だった場合に、これほど大きな差が生じる可能性はどの程度かということです。
重要な要素は各率の標準誤差で、サンプルサイズが大きくなるほど小さくなります。大規模なサンプルでは小さなリフトも信頼できますが、小規模なサンプルでは大きなリフトでさえ疑わしくなります。
比率の標準誤差
n人のユーザーに対するコンバージョン率 p の標準誤差は sqrt(p * (1 - p) / n) です。SQLでバリアントごとに直接計算します。
これにより、率を比較する前に、それぞれの率にどの程度の揺らぎがあるかを定量化できます。
WITH s AS (
SELECT variant,
COUNT(DISTINCT user_id) AS n,
COUNT(DISTINCT converted_user) AS c
FROM experiment_flat GROUP BY variant
)
SELECT
variant, n,
1.0 * c / n AS p,
SQRT( (1.0*c/n) * (1 - 1.0*c/n) / n ) AS std_err
FROM s;2つの比率のZスコア
大まかな有意性のシグナルとして、2つの比率のzスコアを使えます。これは、率の差をその差の標準誤差で割ったものです。絶対値が1.96を超える場合、一般的な95%の基準に相当します。
これは近似であり、正式な検定の代わりではないことを明確にしてください。ただし、SQLで「この差は実際の効果だと考えられるか」という問いに答えることはできます。
WITH r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) AS p1,
MAX(CASE WHEN variant='control' THEN n END) AS n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) AS p2,
MAX(CASE WHEN variant='treatment' THEN n END) AS n2
FROM (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
) s
)
SELECT
p2 - p1 AS abs_lift,
(p2 - p1) / SQRT( p1*(1-p1)/n1 + p2*(1-p2)/n2 ) AS z_score
FROM r;Zスコアの解釈
数値を判定に変換し、単なる数学ではなくビジネス上の意味まで伝えます。
|z| >= 1.96：差はおよそ95%の信頼水準で有意です。|z| < 1.96：証拠が不十分であり、リフトはノイズである可能性があります。
CASE でラベルを出力すると読みやすくなります。また、有意性は必ずリフトの実用的な大きさと併せて評価してください。
SELECT
z_score,
CASE WHEN ABS(z_score) >= 1.96
THEN 'significant at 95%'
ELSE 'not significant' END AS verdict
FROM (
SELECT 2.3 AS z_score
) t;サンプル比率の不一致（SRM）
面接官が最初に確認するガードレールは、ユーザーが設計どおりに配分されたかどうかです。50/50の実験が数百万人のユーザーで53/47になった場合は危険信号であり、無作為化またはログ記録に問題があります。
観測された件数を期待される配分と比較します。大きな乖離がある場合は、指標を見る前にテスト全体が無効になります。
WITH cnt AS (
SELECT variant, COUNT(DISTINCT user_id) AS n
FROM experiment_flat GROUP BY variant
),
tot AS (SELECT SUM(n) AS total FROM cnt)
SELECT
c.variant, c.n,
ROUND(100.0 * c.n / t.total, 2) AS observed_pct,
50.0 AS expected_pct
FROM cnt c CROSS JOIN tot t;ガードレール指標
ガードレールとは、主要指標が改善しても悪化してはならない指標です。典型的なガードレールには、ページのレイテンシ、返金率、登録解除率、エラー率があります。
成果を示す指標と並べて、バリアントごとに報告します。コンバージョン率を上げても返金が2倍になる treatment は、成功とはいえません。ガードレールを求められる前に計算すると、プロダクトに対する判断力を示せます。
SELECT
variant,
AVG(load_ms) AS avg_latency_ms,
ROUND(100.0 * SUM(refunded) / COUNT(*), 2) AS refund_rate_pct,
ROUND(100.0 * SUM(errored) / COUNT(*), 2) AS error_rate_pct
FROM experiment_flat
GROUP BY variant;完全な結果レポート
面接官が高く評価する完全な実験結果レポートでは、バリアントごとの率、相対リフト、有意性の判定、SRMチェックという4つを1つの結果にまとめます。CTEを重ね、意思決定にそのまま使える単一のテーブルとして提示します。
最後に、「有意であり、リフトの大きさは許容範囲で、ガードレールは健全、配分も均衡しているため、リリースする、または保留する」と明確に述べます。
WITH s AS (
SELECT variant, COUNT(DISTINCT user_id) n,
COUNT(DISTINCT converted_user) c
FROM experiment_flat GROUP BY variant
),
r AS (
SELECT
MAX(CASE WHEN variant='control' THEN 1.0*c/n END) p1,
MAX(CASE WHEN variant='control' THEN n END) n1,
MAX(CASE WHEN variant='treatment' THEN 1.0*c/n END) p2,
MAX(CASE WHEN variant='treatment' THEN n END) n2
FROM s
)
SELECT
ROUND(100.0*(p2-p1)/p1, 2) AS rel_lift_pct,
CASE WHEN ABS((p2-p1)/SQRT(p1*(1-p1)/n1 + p2*(1-p2)/n2)) >= 1.96
THEN 'significant' ELSE 'not significant' END AS verdict,
CASE WHEN ABS(1.0*n2/(n1+n2) - 0.5) > 0.02
THEN 'SRM warning' ELSE 'split ok' END AS srm_check
FROM r;クイックチェック
treatment ではコンバージョン率が相対的に25%向上しましたが、各バリアントのユーザー数は40人しかいません。正しい結論は何でしょうか？
振り返り：リフト、有意性、ガードレール
これで、バリアントの生の指標を意思決定へ変換できるようになりました。
- 絶対（ポイント）リフトと相対（パーセント）リフトを区別します。
- 各率の標準誤差と、概算の有意性シグナルとして2つの比率のzスコアを計算します（|z| >= 1.96 はおよそ95%）。
- 配分が設計どおりであることを確認するため、SRMチェックを実行します。
- 改善の裏で別の指標が悪化していないことを確認するため、ガードレール指標を報告します。
- 意思決定に使える単一の結果レポートを提示し、有意性は必ずリフトの実用的な大きさと併せて評価します。
これで、SQLによるファネル分析とA/Bテスト分析は完了です。
よくある質問
「SQLにおけるリフト、統計的有意性、ガードレール」レッスンは無料ですか？
はい。「SQLにおけるリフト、統計的有意性、ガードレール」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「SQLにおけるリフト、統計的有意性、ガードレール」で何を学びますか？
コンバージョンリフトと、実験の異常を検出するデータチェックを計算します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。
「SQLにおけるリフト、統計的有意性、ガードレール」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 多段階ファネルの作成
- 順序付きイベントと時間枠
- A/Bテストの割り当てと指標
- SQLにおけるリフト、統計的有意性、ガードレール