SQL 如何用 SQL 做简单 A/B 测试分析？

用GROUP BY和条件聚合可直接在sql中计算A/B组转化率：按variant分组，用count(CASE WHEN…)统计各行为，NULLIF防除零，需限定时间范围并验证基线均衡性，避免去重偏差，置信区间辅助判断显著性。

怎么用 GROUP BY 和条件聚合算 A/B 组转化率

直接在 SQL 里做 A/B 测试分析，核心是把用户按实验分组（比如 variant = 'A' 或 variant = 'B'），再分别统计各组的曝光、点击、下单等行为。别先 JOIN 多张表，从单表聚合起步更稳。

常见错误是用子查询套子查询，或者对同一张日志表反复扫描。其实一条带条件聚合的 select 就能搞定基础指标：

SELECT   variant,   COUNT(*) AS impressions,   COUNT(CASE WHEN event = 'click' THEN 1 END) AS clicks,   COUNT(CASE WHEN event = 'purchase' THEN 1 END) AS purchases,   ROUND(100.0 * COUNT(CASE WHEN event = 'purchase' THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS conv_rate_pct FROM experiment_events WHERE variant IN ('A', 'B')   AND event IN ('impression', 'click', 'purchase') GROUP BY variant;

注意点：

• NULLIF(COUNT(*), 0) 防止除零 —— 某些变体可能没数据，不加这个会报错或返回 NULL
• 用 CASE WHEN 而不是多次 WHERE，避免漏掉同一用户的多行为（比如一个用户既曝光又购买）
• 时间范围必须显式限定，否则容易混入实验前/后的脏数据；建议加 AND event_time BETWEEN '2024-06-01' AND '2024-06-15'

怎么确认分组是否真正随机（检查基线均衡性）

A/B 结果可信的前提是 A 组和 B 组在关键维度上分布一致。不能只看总数，得查人口属性、设备类型、地域、历史活跃度这些协变量。

例如验证新老用户比例是否均衡：

SELECT   variant,   COUNT(*) Filter (WHERE user_type = 'new') AS new_users,   COUNT(*) FILTER (WHERE user_type = 'returning') AS returning_users,   ROUND(100.0 * COUNT(*) FILTER (WHERE user_type = 'new') / NULLIF(COUNT(*), 0), 2) AS new_pct FROM experiment_users WHERE variant IN ('A', 'B') GROUP BY variant;

关键判断标准：

• 新用户占比差异超过 ±2% 就值得警惕，尤其当总样本量 > 10k 时
• 如果发现显著不均衡（比如 A 组 ios 用户占 70%，B 组只有 45%），说明分流逻辑有 bug，结果不能直接解读
• postgresql 支持 FILTER，mysql 要改用 CASE WHEN；别直接复制粘贴，先看自己数据库版本

怎么避免因会话/用户去重导致的统计偏差

曝光和点击常发生在同一会话内，但转化（如购买）可能隔天发生。如果只按事件粒度聚合，会高估转化率；如果盲目按 user_id 去重，又会低估真实路径漏斗。

正确做法是分层定义“单位”：

• 曝光、点击：按 (user_id, session_id, event_time::date) 去重，防刷量
• 转化：按 user_id 去重，因为一个人下一次单就是一次有效转化，不管他看了多少次商品页
• 若需计算“会话级转化率”，则统一用 session_id 作为主键，但要确保 session 切分逻辑稳定（比如超 30 分钟无活动即断开）

典型错误写法：COUNT(DISTINCT user_id) 算所有事件 —— 这会让 A 组因用户更活跃而显得“转化更高”，实际只是统计口径歪了。

为什么不能直接用 t 检验 p 值？SQL 里怎么近似看显著性

纯 SQL 不适合算 t 检验或卡方检验，但可以用置信区间快速判断差异是否可能纯属随机波动。Z 值近似法足够用于初筛：

WITH rates AS (   SELECT     variant,     COUNT(CASE WHEN event = 'purchase' THEN 1 END)::FLOAT / COUNT(*) AS p,     COUNT(*) AS n   FROM experiment_events   WHERE variant IN ('A', 'B') AND event = 'purchase'   GROUP BY variant ) SELECT   a.variant AS a_variant,   b.variant AS b_variant,   ROUND(100.0 * (a.p - b.p), 3) AS diff_pct_points,   ROUND(100.0 * (a.p - b.p) - 1.96 * SQRT(a.p*(1-a.p)/a.n + b.p*(1-b.p)/b.n), 3) AS ci_lower,   ROUND(100.0 * (a.p - b.p) + 1.96 * SQRT(a.p*(1-a.p)/a.n + b.p*(1-b.p)/b.n), 3) AS ci_upper FROM rates a, rates b WHERE a.variant = 'A' AND b.variant = 'B';

如果整个置信区间（ci_lower 到 ci_upper）不包含 0，说明差异大概率真实存在。但要注意：

• 这个公式假设样本量够大（每组 ≥ 500）、转化率不太极端（1%–30% 最稳妥）
• 如果 A 组转化率 1.2%，B 组 1.5%，看着差 0.3 个百分点，但置信区间是 [-0.1, +0.7]，那就还不能下结论
• 真要发报告，还是导出数据用 python/R 做标准检验；SQL 只负责快筛和归因对齐

最常被忽略的是实验周期是否覆盖完整用户生命周期 —— 比如只看首日数据，会严重低估 B 组长期价值，因为它的用户需要更长时间建立信任。