阿里巴巴大数据面试SQL-千人千面AB测试

数据仓库晨曦

发布于 2026-07-13 20:32:02

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

淘宝"猜你喜欢"是典型的个性化推荐场景——千人千面算法根据用户的浏览、购买、收藏历史，为每个用户推荐不同的商品。但个性化推荐的计算成本远高于非个性化（如热销榜），产品团队需要通过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  |
+------------+-------------+----------+------------+---------+
2 rows selected (0.397 seconds)(https://www.dwsql.com)

个性化组CTR=71.43%，非个性化组=40%。直观上个性化效果显著，但需要统计检验（卡方检验或t检验）确认显著性。实际AB实验中，即便CTR差异看起来很大，若样本量不足也可能是随机波动。

步骤2：按品类交叉分析 + 计算提升率

执行SQL

select category,
       max(casewhen exp_group = 'test'then ctr end) as test_ctr,
       max(casewhen exp_group = 'control'then ctr end) as control_ctr,
       round((max(casewhen exp_group = 'test'then ctr end) -
              max(casewhen exp_group = 'control'then ctr end)) /
              nullif(max(casewhen exp_group = 'control'then ctr end), 0) * 100, 2) as lift_pct
from (
    selectcategory, exp_group,
           round(sum(is_click) * 1.0 / count(1), 4) as ctr
    from t14_reco_log
    groupbycategory, exp_group
) t
groupbycategory
orderby lift_pct desc

执行结果

+-----------+-----------+--------------+-----------+
| category  | test_ctr  | control_ctr  | lift_pct  |
+-----------+-----------+--------------+-----------+
| 女装        | 1.0000    | 0.6667       | 49.99     |
| 家电        | 0.5000    | 0.0000       | NULL      |
| 数码        | 0.6667    | 0.0000       | NULL      |
+-----------+-----------+--------------+-----------+
3 rows selected (8.315 seconds)(https://www.dwsql.com)

家电和数码在个性化组有显著点击，非个性化组无人点击（样本量小导致 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

八、建表语句和数据插入

CREATE TABLE t14_reco_log (
    id        intCOMMENT'日志ID',
    user_id   stringCOMMENT'用户ID',
    item_id   stringCOMMENT'商品ID',
    categorystringCOMMENT'商品类目',
    exp_group stringCOMMENT'实验分组: test-个性化, control-非个性化',
    is_click  intCOMMENT'是否点击: 1-是, 0-否',
    exp_time  stringCOMMENT'曝光时间'
) COMMENT'推荐曝光点击日志表';

INSERTINTO 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');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-11，如有侵权请联系 cloudcommunity@tencent.com 删除

大数据