B站大数据面试SQL-视频内容召回CTR分析

⚠️ 待修正

一、题目背景

这道题来自B站的数据分析岗面试。B站是弹幕视频社区，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：内容消费相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

现有推荐曝光表 t8_show 和点击表 t8_click，计算不同推荐策略的CTR（点击率）。

曝光表 t8_show：user_id, video_id, strategy, show_time 点击表 t8_click：user_id, video_id, click_time

-- t8_show 样例
+----------+----------+-----------+---------------------+
| user_id  | video_id | strategy  |      show_time      |
+----------+----------+-----------+---------------------+
| u01      | BV001    | 协同过滤  | 2023-03-01 10:00:00 |
| u01      | BV002    | 热门推荐  | 2023-03-01 10:05:00 |
| u02      | BV001    | 协同过滤  | 2023-03-01 11:00:00 |
+----------+----------+-----------+---------------------+

-- t8_click 样例
+----------+----------+---------------------+
| user_id  | video_id |     click_time      |
+----------+----------+---------------------+
| u01      | BV001    | 2023-03-01 10:00:30 |
+----------+----------+---------------------+

三、思路分析

按strategy分组统计曝光数和点击数，关联点击表时需匹配user_id+video_id。

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

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 s.strategy, count(1) as click_cnt
    from t8_click c join t8_show s on c.user_id=s.user_id and c.video_id=s.video_id
    group by s.strategy
) t2 on t1.strategy = t2.strategy

执行结果

+-----------+----------+-----------+--------+
| strategy  | show_cnt | click_cnt |  ctr   |
+-----------+----------+-----------+--------+
| 协同过滤  | 2        | 1         | 0.5000 |
| 热门推荐  | 1        | 0         | 0.0000 |
+-----------+----------+-----------+--------+

协同过滤CTR(50%)优于热门推荐(0%)，但样本量较小需持续观察。

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
NULL值处理	NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t8_show (user_id string, video_id string, strategy string, show_time string);
CREATE TABLE t8_click (user_id string, video_id string, click_time string);

INSERT INTO t8_show VALUES
('u01','BV001','协同过滤','2023-03-01 10:00:00'),
('u01','BV002','热门推荐','2023-03-01 10:05:00'),
('u02','BV001','协同过滤','2023-03-01 11:00:00');

INSERT INTO t8_click VALUES
('u01','BV001','2023-03-01 10:00:30');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题