SQL 推荐策略CTR分析：末次触点归因+ROW_NUMBER精确匹配（B站面试题）
一、题目背景
这道题来自B站推荐算法团队的数据分析岗面试。B站的首页推荐流是用户内容消费的核心入口，推荐系统包含多个召回策略（协同过滤、热门推荐、兴趣标签、关注UP主等）。数据分析师需要持续监控各策略的CTR（点击率），帮助算法团队评估策略效果、调整召回权重。
核心挑战在于点击归因：同一个用户+视频可能被多个策略先后曝光，点击到底算谁的？业界有两种主流方案——多点归因（每个触达策略都算一次）和末次触点归因（只算最近一次曝光）。后者更精确，也是本次面试的考察重点。
业务场景：策略周会上，算法团队需要看到"末次触点归因下各策略的CTR"。如果协同过滤曝光量很大但末次触点CTR低，说明它虽然触达了用户但"最后一脚"总被其他策略抢走，需要调整曝光时机或内容质量。
二、题目
现有推荐曝光表 t8_show 和点击表 t8_click。请按末次触点归因规则——每次点击归给同一用户+视频的点击前最近一次曝光所用的策略——计算各策略的曝光量、点击量、CTR。
曝光表 t8_show：user_id, video_id, strategy, show_time
+----------+-----------+-----------+----------------------+
| user_id | video_id | strategy | show_time |
+----------+-----------+-----------+----------------------+
| u01 | BV001 | 协同过滤 | 2023-03-01 10:00:00 |
| u02 | BV001 | 协同过滤 | 2023-03-01 10:05:00 |
| u03 | BV001 | 协同过滤 | 2023-03-01 10:00:00 |
| u03 | BV001 | 热门推荐 | 2023-03-01 10:35:00 |
| u03 | BV001 | 兴趣标签 | 2023-03-01 11:00:00 |
| u04 | BV003 | 协同过滤 | 2023-03-01 10:15:00 |
| u01 | BV003 | 协同过滤 | 2023-03-01 10:20:00 |
| u01 | BV002 | 热门推荐 | 2023-03-01 10:25:00 |
| u02 | BV002 | 热门推荐 | 2023-03-01 10:30:00 |
| u05 | BV003 | 热门推荐 | 2023-03-01 10:40:00 |
| u03 | BV002 | 协同过滤 | 2023-03-01 10:10:00 |
| u04 | BV001 | 兴趣标签 | 2023-03-01 11:05:00 |
| u05 | BV002 | 兴趣标签 | 2023-03-01 11:10:00 |
| u01 | BV004 | 兴趣标签 | 2023-03-01 11:15:00 |
| u04 | BV002 | 关注UP主 | 2023-03-01 11:20:00 |
| u05 | BV001 | 关注UP主 | 2023-03-01 11:25:00 |
| u02 | BV004 | 关注UP主 | 2023-03-01 11:30:00 |
| u03 | BV004 | 关注UP主 | 2023-03-01 11:35:00 |
+----------+-----------+-----------+----------------------+
点击表 t8_click：user_id, video_id, click_time
+----------+-----------+----------------------+
| user_id | video_id | click_time |
+----------+-----------+----------------------+
| u01 | BV001 | 2023-03-01 10:00:30 |
| u02 | BV001 | 2023-03-01 10:06:00 |
| u04 | BV003 | 2023-03-01 10:16:00 |
| u03 | BV001 | 2023-03-01 11:00:45 |
| u04 | BV001 | 2023-03-01 11:06:00 |
| u05 | BV002 | 2023-03-01 11:10:30 |
| u04 | BV002 | 2023-03-01 11:21:00 |
| u03 | BV004 | 2023-03-01 11:36:00 |
+----------+-----------+----------------------+
关键场景：u03 对 BV001 被三个策略先后曝光——协同过滤(10:00)、热门推荐(10:35)、兴趣标签(11:00)。u03 在 11:00:45 点击了 BV001，末次触点归因下，这次点击归给最近的兴趣标签(11:00)，而非更早的协同过滤或热门推荐。
三、思路分析
核心思路是 "筛选前置曝光 → 取最近一条 → 按策略聚合"：
- 统计曝光量：按 strategy + count 得到各策略的总曝光次数
- 展开归因明细：点击表 join 曝光表（user_id + video_id 匹配，且 show_time ≤ click_time），用
row_number() over (partition by user_id, video_id, click_time order by show_time desc)为每条点击匹配的所有前置曝光编号，展示完整的归因链路 - 取末次触点聚合：
where rn = 1只保留最近一次曝光，按 strategy 聚合得到各策略的归因点击量 - LEFT JOIN 合并：以曝光统计为主表关联归因后的点击统计，
coalesce补零，计算 CTR
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：按策略统计曝光量
select strategy, count(1) as show_cnt
from t8_show
group by strategy
执行结果
+-----------+-----------+
| strategy | show_cnt |
+-----------+-----------+
| 兴趣标签 | 4 |
| 协同过滤 | 6 |
| 热门推荐 | 4 |
| 关注UP主 | 4 |
+-----------+-----------+
4 rows selected (0.337 seconds)(https://www.dwsql.com)
步骤2：展开归因明细 — 为每次点击找到所有前置曝光，按时间排序编号
select c.user_id, c.video_id, c.click_time,
s.strategy, s.show_time,
row_number() over (
partition by c.user_id, c.video_id, c.click_time
order by s.show_time desc
) as rn
from t8_click c
join t8_show s
on c.user_id = s.user_id
and c.video_id = s.video_id
where s.show_time <= c.click_time
执行结果
+----------+-----------+----------------------+-----------+----------------------+-----+
| user_id | video_id | click_time | strategy | show_time | rn |
+----------+-----------+----------------------+-----------+----------------------+-----+
| u01 | BV001 | 2023-03-01 10:00:30 | 协同过滤 | 2023-03-01 10:00:00 | 1 |
| u02 | BV001 | 2023-03-01 10:06:00 | 协同过滤 | 2023-03-01 10:05:00 | 1 |
| u03 | BV001 | 2023-03-01 11:00:45 | 兴趣标签 | 2023-03-01 11:00:00 | 1 |
| u03 | BV001 | 2023-03-01 11:00:45 | 热门推荐 | 2023-03-01 10:35:00 | 2 |
| u03 | BV001 | 2023-03-01 11:00:45 | 协同过滤 | 2023-03-01 10:00:00 | 3 |
| u03 | BV004 | 2023-03-01 11:36:00 | 关注UP主 | 2023-03-01 11:35:00 | 1 |
| u04 | BV001 | 2023-03-01 11:06:00 | 兴趣标签 | 2023-03-01 11:05:00 | 1 |
| u04 | BV002 | 2023-03-01 11:21:00 | 关注UP主 | 2023-03-01 11:20:00 | 1 |
| u04 | BV003 | 2023-03-01 10:16:00 | 协同过滤 | 2023-03-01 10:15:00 | 1 |
| u05 | BV002 | 2023-03-01 11:10:30 | 兴趣标签 | 2023-03-01 11:10:00 | 1 |
+----------+-----------+----------------------+-----------+----------------------+-----+
10 rows selected (0.508 seconds)(https://www.dwsql.com)
关键观察：u03 对 BV001 的一次点击，匹配到 3 条前置曝光。
row_number()按show_time desc排序，兴趣标签(11:00) 排 rn=1，热门推荐(10:35) 排 rn=2，协同过滤(10:00) 排 rn=3。如果不用归因而直接 JOIN（多点归因），这 3 条各计一次点击，协同过滤的 CTR 会虚高。
步骤3：取末次触点（rn = 1），按策略聚合点击量
select strategy, count(1) as click_cnt
from (
select s.strategy,
row_number() over (
partition by c.user_id, c.video_id, c.click_time
order by s.show_time desc
) as rn
from t8_click c
join t8_show s
on c.user_id = s.user_id
and c.video_id = s.video_id
where s.show_time <= c.click_time
) t
where rn = 1
group by strategy
执行结果
+-----------+------------+
| strategy | click_cnt |
+-----------+------------+
| 兴趣标签 | 3 |
| 协同过滤 | 3 |
| 关注UP主 | 2 |
+-----------+------------+
3 rows selected (0.779 seconds)(https://www.dwsql.com)
where rn = 1确保每次点击只归给最近一次曝光。协同过滤 3 次、兴趣标签 3 次、关注UP主 2 次。热门推荐无一命中 rn=1（即使有 4 次曝光），说明它虽然曝光多但从未成为"最后一脚"。
步骤4：LEFT JOIN 合并曝光和归因点击，计算 CTR
select t1.strategy,
t1.show_cnt,
coalesce(t2.click_cnt, 0) as click_cnt,
round(coalesce(t2.click_cnt, 0) / t1.show_cnt, 4) as ctr
from (
select strategy, count(1) as show_cnt from t8_show group by strategy
) t1
left join (
select strategy, count(1) as click_cnt
from (
select s.strategy,
row_number() over (
partition by c.user_id, c.video_id, c.click_time
order by s.show_time desc
) as rn
from t8_click c
join t8_show s
on c.user_id = s.user_id
and c.video_id = s.video_id
where s.show_time <= c.click_time
) t
where rn = 1
group by strategy
) t2 on t1.strategy = t2.strategy
order by ctr desc
执行结果
+-----------+-----------+------------+-------+
| strategy | show_cnt | click_cnt | ctr |
+-----------+-----------+------------+-------+
| 兴趣标签 | 4 | 3 | 0.75 |
| 协同过滤 | 6 | 3 | 0.5 |
| 关注UP主 | 4 | 2 | 0.5 |
| 热门推荐 | 4 | 0 | 0.0 |
+-----------+-----------+------------+-------+
4 rows selected (0.778 seconds)(https://www.dwsql.com)
末次触点归因下的解读：
兴趣标签 CTR 75%，是最高效的策略——曝光不多但"临门一脚"能力强，往往在用户决策前的最后时刻触达。
协同过滤曝光量最大(6次)但 CTR 仅 50%。它触达了 u03(+BV001)，但最后一刻被兴趣标签抢走。如果看多点归因，协同过滤的点击量是 4 而非 3（u03 那次也算），CTR 会虚高到 67%——两种归因结论完全不同。
热门推荐 4 次曝光零点击，需排查内容匹配度或展示位置。
五、常见坑点
坑1：show_time <= click_time 条件容易遗漏
不加时间条件，点击可能被归给发生在点击之后的曝光（如用户先在搜索结果点击了 BV001，2 小时后 BV001 才被推荐曝光）。s.show_time <= c.click_time 确保只归因给点击前已经发生的曝光。
坑2：partition by 必须包含 click_time
row_number() over (partition by user_id, video_id, click_time ...) 中 click_time 是关键。只按 user_id + video_id 分区，会把这个用户+视频的所有点击合并到一个窗口里，最终每个(user, video)只产生一条记录——丢失了同一用户在不同时间多次点击同一视频的信息。
坑3：某次点击在曝光表中完全没有前置曝光
如果用户在搜索中发现了视频并点击，而该视频从未在推荐流中对这个用户曝光过，join ... where s.show_time <= c.click_time 此次点击无法匹配到任何曝光行，点击不会被计入任何策略。这是正确的——推荐系统的 CTR 只统计"推荐驱动的点击"。但会导致总点击量 < 实际总点击量，报告时需要注明。
六、举一反三
- 对比多点归因 vs 末次触点归因：去掉
row_number() + where rn = 1直接 join 统计，对比两种归因方式下各策略的 CTR 差异。差异大的策略说明它经常被"截胡"——触达了用户但用户最终通过其他策略转化 - 时间衰减归因：不是硬性只取最近一次，而是按曝光距点击的时间差分配权重——曝光距点击越近权重越高。用
1.0 / (datediff(click_time, show_time) + 1)计算权重再聚合，消除硬截断带来的信息损失 - 按视频分区+策略交叉分析：JOIN 视频信息表加入 category，对比不同分区下各策略的末次触点CTR。比如协同过滤在游戏区可能是主要转化策略，但在知识区被兴趣标签碾压——结论决定分区级的策略权重调优
七、知识点总结
| 考点 | 说明 |
|---|---|
| row_number 窗口函数 + partition by | 按(user, video, click)分组，show_time 降序取最近一次曝光，实现末次触点归因 |
join 时间条件 <= | 限定只匹配点击前的曝光，防止未来曝光被错误归因 |
| 子查询分层：筛选→排序→聚合 | 先 join + row_number 做归因，再 group by 聚合，最后 left join 合并曝光量 |
| left join + coalesce 保护零值策略 | 无点击的策略在归因结果中不存在，left join 保留 + coalesce 补 0 |
| 归因口径的业务影响 | 多点归因 vs 末次触点结论不同，面试中主动讨论业务选择是 hard 题的区分点 |
八、建表语句和数据插入
点击展开 DDL & DML
create table t8_show (
user_id string comment '用户ID',
video_id string comment '视频ID（BV号）',
strategy string comment '推荐策略：协同过滤/热门推荐/兴趣标签/关注UP主',
show_time string comment '曝光时间'
) comment '推荐曝光表';
create table t8_click (
user_id string comment '用户ID',
video_id string comment '视频ID（BV号）',
click_time string comment '点击时间'
) comment '推荐点击表';
insert into t8_show values
('u01','BV001','协同过滤','2023-03-01 10:00:00'),
('u02','BV001','协同过滤','2023-03-01 10:05:00'),
('u03','BV001','协同过滤','2023-03-01 10:00:00'),
('u03','BV001','热门推荐','2023-03-01 10:35:00'),
('u03','BV001','兴趣标签','2023-03-01 11:00:00'),
('u04','BV003','协同过滤','2023-03-01 10:15:00'),
('u01','BV003','协同过滤','2023-03-01 10:20:00'),
('u01','BV002','热门推荐','2023-03-01 10:25:00'),
('u02','BV002','热门推荐','2023-03-01 10:30:00'),
('u05','BV003','热门推荐','2023-03-01 10:40:00'),
('u03','BV002','协同过滤','2023-03-01 10:10:00'),
('u04','BV001','兴趣标签','2023-03-01 11:05:00'),
('u05','BV002','兴趣标签','2023-03-01 11:10:00'),
('u01','BV004','兴趣标签','2023-03-01 11:15:00'),
('u04','BV002','关注UP主','2023-03-01 11:20:00'),
('u05','BV001','关注UP主','2023-03-01 11:25:00'),
('u02','BV004','关注UP主','2023-03-01 11:30:00'),
('u03','BV004','关注UP主','2023-03-01 11:35:00');
insert into t8_click values
('u01','BV001','2023-03-01 10:00:30'),
('u02','BV001','2023-03-01 10:06:00'),
('u04','BV003','2023-03-01 10:16:00'),
('u03','BV001','2023-03-01 11:00:45'),
('u04','BV001','2023-03-01 11:06:00'),
('u05','BV002','2023-03-01 11:10:30'),
('u04','BV002','2023-03-01 11:21:00'),
('u03','BV004','2023-03-01 11:36:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
