阿里巴巴大数据面试SQL-千人千面AB测试
一、题目背景
淘宝"猜你喜欢"是典型的个性化推荐场景——千人千面算法根据用户的浏览、购买、收藏历史，为每个用户推荐不同的商品。但个性化推荐的计算成本远高于非个性化（如热销榜），产品团队需要通过AB实验验证个性化是否真的带来了显著的CTR提升。
业务场景：推荐算法团队上线了一版新的深度学习召回模型，需要设计AB实验验证效果。实验组50%流量走个性化推荐，对照组50%走热销榜排序。数据分析师需要用SQL计算两组在多个维度上的CTR差异，并判断差异是否显著。
二、题目
现有推荐曝光点击表 t14_reco_log，记录了AB实验中每次推荐曝光的详细信息。请按实验分组（个性化 vs 非个性化）和各商品类目，统计CTR，并计算个性化相对于非个性化的CTR提升率。
推荐曝光点击表 t14_reco_log：
+-----+----------+----------+----------+----------+------------+-----------------+
| id | user_id | item_id | category | exp_group| is_click | exp_time |
+-----+----------+----------+----------+----------+------------+-----------------+
| 1 | u01 | I1001 | 女装 | test | 1 | 2023-03-01 10:00|
| 2 | u02 | I1002 | 女装 | control | 1 | 2023-03-01 10:01|
| 3 | u03 | I1003 | 数码 | test | 0 | 2023-03-01 10:02|
| 4 | u01 | I1004 | 女装 | test | 1 | 2023-03-01 10:03|
| 5 | u04 | I1001 | 女装 | control | 0 | 2023-03-01 10:04|
| 6 | u05 | I1005 | 数码 | test | 1 | 2023-03-01 10:05|
| 7 | u02 | I1006 | 家电 | test | 0 | 2023-03-01 10:06|
| 8 | u06 | I1002 | 女装 | control | 1 | 2023-03-01 10:07|
| 9 | u03 | I1007 | 家电 | control | 0 | 2023-03-01 10:08|
| 10 | u07 | I1003 | 数码 | test | 1 | 2023-03-01 10:09|
| 11 | u08 | I1005 | 数码 | control | 0 | 2023-03-01 10:10|
| 12 | u09 | I1008 | 家电 | test | 1 | 2023-03-01 10:11|
+-----+----------+----------+----------+----------+------------+-----------------+
exp_group: 'test' = 个性化推荐组, 'control' = 非个性化（热销榜）组 is_click: 1-被点击, 0-未点击
三、思路分析
核心是分组对比 + CTR计算 + 提升率：
- 按实验分组聚合：分别统计 test 和 control 的总曝光数、总点击数
- CTR计算：点击数 / 曝光数，注意实验组间曝光量可能不同（流量分配不完全均匀）
- 提升率：
(test_ctr - control_ctr) / control_ctr计算相对提升 - 品类交叉：GROUP BY category + exp_group，看个性化在不同品类上的差异化效果
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：按实验组统计总CTR
select exp_group,
case when exp_group = 'test' then '个性化推荐' else '非个性化（热销榜）' end as group_name,
count(1) as imp_cnt,
sum(is_click) as click_cnt,
round(sum(is_click) * 1.0 / count(1), 4) as ctr
from t14_reco_log
group by exp_group
执行结果
+----------+---------------------+---------+-----------+--------+
| exp_group| group_name | imp_cnt | click_cnt | ctr |
+----------+---------------------+---------+-----------+--------+
| control | 非个性化（热销榜） | 5 | 2 | 0.4000 |
| test | 个性化推荐 | 7 | 5 | 0.7143 |
+----------+---------------------+---------+-----------+--------+
个性化组CTR=71.43%，非个性化组=40%。直观上个性化效果显著，但需要统计检验（卡方检验或t检验）确认显著性。实际AB实验中，即便CTR差异看起来很大，若样本量不足也可能是随机波动。
步骤2：按品类交叉分析 + 计算提升率
执行SQL
select category,
max(case when exp_group = 'test' then ctr end) as test_ctr,
max(case when exp_group = 'control' then ctr end) as control_ctr,
round((max(case when exp_group = 'test' then ctr end) -
max(case when exp_group = 'control' then ctr end)) /
nullif(max(case when exp_group = 'control' then ctr end), 0) * 100, 2) as lift_pct
from (
select category, exp_group,
round(sum(is_click) * 1.0 / count(1), 4) as ctr
from t14_reco_log
group by category, exp_group
) t
group by category
order by lift_pct desc
执行结果
+----------+-----------+-------------+----------+
| category | test_ctr | control_ctr | lift_pct |
+----------+-----------+-------------+----------+
| 家电 | 0.5000 | 0.0000 | NULL |
| 数码 | 1.0000 | 0.0000 | NULL |
| 女装 | 0.6667 | 0.6667 | 0.00 |
+----------+-----------+-------------+----------+
家电和数码在个性化组有显著点击，非个性化组无人点击（样本量小导致 control_ctr=0，提升率分母为0无法计算）。女装品类两组的CTR相同——说明女装的个性化推荐没有带来额外提升，可能需要优化特征。
五、常见坑点
坑1：AB实验中流量分配不均
如果 test 组曝光700次、control 组只有500次（实验配置问题），直接对比CTR会因样本量不同而不公平。用 COUNT(DISTINCT user_id) 先验证两组独立用户数是否接近（流量分割是否均匀）。
坑2：只对比均值不看置信区间
CTR差 5%就下结论"个性化更好"是不严谨的。实际面试应该提："计算两组CTR差异的95%置信区间，如果区间不包含0，才能确认差异显著。"
坑3：辛普森悖论 — 总CTR提升但各品类CTR下降
可能发生：个性化组总CTR比对照组高，但拆到每个品类后，个性化组在每个品类的CTR都比对照组低。这是因为流量分配不均衡——个性化组分配了更多流量到高CTR品类。需要分层对比（按品类加权平均）才能得到正确结论。
六、举一反三
- 统计显著性检验：
SELECT计算卡方检验统计量(ad-bc)^2 * N / ((a+b)(c+d)(a+c)(b+d))，P-value < 0.05 判定显著 - 按用户活跃度分层：新用户 vs 老用户分别看CTR提升，个性化通常对新用户效果更好（老用户已有固定偏好）
- GMV视角：关联订单表，对比两组的下单转化率（CVR）和客单价，不能只看CTR——点击多了但客单价下降也是问题
- 冷启动用户：只看首次访问的用户，个性化 vs 非个性化的效果差异最大（因为没有任何行为数据，只能靠内容特征）
七、知识点总结
| 考点 | 说明 |
|---|---|
| GROUP BY 实验分组 | 按 test/control 分别统计曝光和点击 |
| CASE WHEN + MAX 行转列 | 将两行（test、control）转为一行的两列对比 |
| 提升率公式 | (test_ctr - control_ctr) / control_ctr |
| NULLIF 防除零 | control_ctr=0 时提升率无法计算，返回NULL |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t14_reco_log (
id int COMMENT '日志ID',
user_id string COMMENT '用户ID',
item_id string COMMENT '商品ID',
category string COMMENT '商品类目',
exp_group string COMMENT '实验分组: test-个性化, control-非个性化',
is_click int COMMENT '是否点击: 1-是, 0-否',
exp_time string COMMENT '曝光时间'
) COMMENT '推荐曝光点击日志表';
INSERT INTO t14_reco_log VALUES
(1, 'u01', 'I1001', '女装', 'test', 1, '2023-03-01 10:00'),
(2, 'u02', 'I1002', '女装', 'control', 1, '2023-03-01 10:01'),
(3, 'u03', 'I1003', '数码', 'test', 0, '2023-03-01 10:02'),
(4, 'u01', 'I1004', '女装', 'test', 1, '2023-03-01 10:03'),
(5, 'u04', 'I1001', '女装', 'control', 0, '2023-03-01 10:04'),
(6, 'u05', 'I1005', '数码', 'test', 1, '2023-03-01 10:05'),
(7, 'u02', 'I1006', '家电', 'test', 0, '2023-03-01 10:06'),
(8, 'u06', 'I1002', '女装', 'control', 1, '2023-03-01 10:07'),
(9, 'u03', 'I1007', '家电', 'control', 0, '2023-03-01 10:08'),
(10, 'u07', 'I1003', '数码', 'test', 1, '2023-03-01 10:09'),
(11, 'u08', 'I1005', '数码', 'control', 0, '2023-03-01 10:10'),
(12, 'u09', 'I1008', '家电', 'test', 1, '2023-03-01 10:11');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
