如何修改A/B对照测试SQL查询以补全缺失日期的各变体数据

解决方案 #

你需要先构造「所有日期 × 所有变体」的全量维度组合，再关联原表的点击数据，空值补0即可，修改后的SQL如下：

SELECT 
    d.day,
    v.type AS variant,
    COALESCE(t.clicks, 0) AS variant_clicks,
    'control' AS control,
    COALESCE(t2.clicks, 0) AS control_clicks
FROM 
    -- 提取所有存在的日期
    (SELECT DISTINCT day FROM table1) d
CROSS JOIN 
    -- 提取所有变体类型（包含control）
    (SELECT DISTINCT type FROM table1) v
LEFT JOIN 
    table1 t ON d.day = t.day AND v.type = t.type
LEFT JOIN 
    table1 t2 ON d.day = t2.day AND t2.type = 'control'
ORDER BY d.day, v.type

逻辑说明 #

1. 用CROSS JOIN对全量日期和全量变体做笛卡尔积，保证每个日期下的所有变体都有对应行，不会出现缺项
2. 左关联原表匹配对应「日期+变体」的点击数据，通过COALESCE函数将无匹配的空点击值转为0
3. 最后左关联匹配对应日期的对照组（control）点击数据即可

如果需要覆盖测试周期内所有日期（包含所有变体都无数据的空白日期），可以将第一个子查询(SELECT DISTINCT day FROM table1)替换为对应数据库的连续日期生成逻辑：

• PostgreSQL 可用 generate_series('起始日期'::date, '结束日期'::date, '1 day'::interval) AS day
• MySQL 8.0+ 可用递归CTE生成连续日期序列

内容的提问来源于stack exchange，提问作者Charlie B