SQL 视频互动率：用户行为日志聚合+比率计算（B站面试题）
一、题目背景
这道题来自B站内容运营部门的数据分析岗面试。B站的视频互动数据（投币、点赞、收藏）是衡量内容质量的核心指标——"一键三连"更是B站的站内文化符号。在实际数据仓库中，用户行为数据以明细日志形式存储在ODS层，每一条播放、点赞、投币、收藏都是一行独立的记录。
业务场景：分析师需要从原始行为日志表中，先按视频+行为类型聚合计数，再计算各项互动率。这是从ODS到DWS层聚合的典型ETL场景，考察的是"先聚合、再计算"的分层思维。
二、题目
现有一张用户行为日志表 t2_user_action，记录用户对视频的每次操作。请统计每个视频的播放量、点赞数、投币数、收藏数，并计算投币率、点赞率、收藏率。
| 比率 | 公式 |
|---|---|
| 投币率 | 投币数 / 播放量 |
| 点赞率 | 点赞数 / 播放量 |
| 收藏率 | 收藏数 / 播放量 |
表 t2_user_action：
+----------+-----------+--------------+----------------------+
| user_id | video_id | action_type | action_time |
+----------+-----------+--------------+----------------------+
| u01 | BV001 | play | 2023-03-01 10:00:00 |
| u01 | BV001 | like | 2023-03-01 10:05:00 |
| u01 | BV001 | coin | 2023-03-01 10:05:30 |
| u02 | BV001 | play | 2023-03-01 10:10:00 |
| u02 | BV001 | favorite | 2023-03-01 10:10:30 |
| u03 | BV001 | play | 2023-03-01 10:15:00 |
| u01 | BV002 | play | 2023-03-01 11:00:00 |
| u01 | BV002 | like | 2023-03-01 11:02:00 |
| u02 | BV002 | play | 2023-03-01 11:05:00 |
| u02 | BV002 | coin | 2023-03-01 11:06:00 |
| u02 | BV002 | favorite | 2023-03-01 11:06:30 |
+----------+-----------+--------------+----------------------+
三、思路分析
核心思路是 "先聚合、再计算" —— 这是ODS明细层→DWS汇总层的标准模式：
- 行转列聚合：用
case when+count将action_type字段的不同取值展开为独立的计数列（播放量/点赞数/投币数/收藏数），按video_id分组 - 计算比率：在聚合结果上用除法计算各项互动率，用
nullif防止除零报错，用round控制精度
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：从行为日志中按视频聚合各行为类型的计数
select video_id,
count(case when action_type = 'play' then 1 end) as play_cnt,
count(case when action_type = 'like' then 1 end) as like_cnt,
count(case when action_type = 'coin' then 1 end) as coin_cnt,
count(case when action_type = 'favorite' then 1 end) as fav_cnt
from t2_user_action
group by video_id
执行结果
+-----------+-----------+-----------+-----------+----------+
| video_id | play_cnt | like_cnt | coin_cnt | fav_cnt |
+-----------+-----------+-----------+-----------+----------+
| BV001 | 3 | 1 | 1 | 1 |
| BV002 | 2 | 1 | 1 | 1 |
+-----------+-----------+-----------+-----------+----------+
2 rows selected (0.991 seconds)(https://www.dwsql.com)
每条 action_type 只有一行
then 1被count统计。case when未命中时返回 null，count自动跳过 null，等价于统计命中次数。
步骤2：在聚合结果上计算三项互动率
select video_id, play_cnt, like_cnt, coin_cnt, fav_cnt,
round(coin_cnt / nullif(play_cnt, 0), 4) as coin_rate,
round(like_cnt / nullif(play_cnt, 0), 4) as like_rate,
round(fav_cnt / nullif(play_cnt, 0), 4) as fav_rate
from (
select video_id,
count(case when action_type = 'play' then 1 end) as play_cnt,
count(case when action_type = 'like' then 1 end) as like_cnt,
count(case when action_type = 'coin' then 1 end) as coin_cnt,
count(case when action_type = 'favorite' then 1 end) as fav_cnt
from t2_user_action
group by video_id
) t
执行结果
+-----------+-----------+-----------+-----------+----------+------------+------------+-----------+
| video_id | play_cnt | like_cnt | coin_cnt | fav_cnt | coin_rate | like_rate | fav_rate |
+-----------+-----------+-----------+-----------+----------+------------+------------+-----------+
| BV001 | 3 | 1 | 1 | 1 | 0.3333 | 0.3333 | 0.3333 |
| BV002 | 2 | 1 | 1 | 1 | 0.5 | 0.5 | 0.5 |
+-----------+-----------+-----------+-----------+----------+------------+------------+-----------+
2 rows selected (0.429 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：sum vs count 在条件聚合中的差异
count(case when ... then 1 end) 统计命中行数；sum(case when ... then 1 else 0 end) 也是统计命中行数，但写法不同。注意 count 跳过 null 而 sum 不跳过 0，两者在大数据量下性能有差异：count 不处理 0 值行，通常更快。但如果需要统计的字段本身可能为 0（如某行为的计数列），两者结果相同。
坑2：同一个用户对同一视频重复操作
一个用户可能多次播放同一个视频，表中会有多条 action_type = 'play' 的记录。当前 SQL 会将重复播放计入 play_cnt，这符合"播放量"的业务口径。但如果需求是"播放人数"（UV），则需要用 count(distinct user_id) 替代。面试中应主动确认业务口径。
坑3：case when 分支遗漏导致 NULL 列
如果某视频只有播放和点赞、没有投币和收藏，coin_cnt 和 fav_cnt 的 case when 全部不命中，count 返回 0（不是 NULL，因为 count 对空集返回 0）。但 nullif(play_cnt, 0) 在 play_cnt=0 时返回 null，此时除法结果也是 null 而非报错——这正是防御性编程的正确行为。
六、举一反三
- 按分区维度聚合互动率：在
t2_user_action基础上 join 视频信息表获取category字段，外层 group by 加上 category，对比游戏区 vs 知识区 vs 音乐区的平均互动率差异，辅助分区运营策略调整 - 分时段互动率对比：用
substr(action_time, 1, 10)截取日期，按天统计互动率趋势，观察某视频发布后互动率的衰减曲线——发布时间24h内的互动率 vs 一周后的互动率 - 用户维度的互动行为画像：将
group by video_id改为group by user_id，统计每个用户的总播放量、总点赞数、总投币数，计算用户的"平均投币率"，识别高互动价值用户（投币率 > 5%）给予社区勋章
七、知识点总结
| 考点 | 说明 |
|---|---|
| case when + count 条件聚合 | 将行级行为类型展开为列级指标，日志明细→汇总指标的核心技巧 |
| group by + 聚合 | 按视频维度汇总用户行为，ODS→DWS的标准ETL模式 |
| nullif(value, 0) | 将0转为null避免除零报错，除数null则结果自动为null |
| round(value, n) | 保留n位小数控制输出精度 |
| 子查询分层计算 | 先聚合、再计算，拆分为子查询避免单层SQL过于复杂 |
八、建表语句和数据插入
点击展开 DDL & DML
create table t2_user_action (
user_id string comment '用户ID',
video_id string comment '视频ID（BV号）',
action_type string comment '行为类型：play=播放, like=点赞, coin=投币, favorite=收藏',
action_time string comment '行为发生时间'
) comment '用户视频行为日志表';
insert into t2_user_action values
('u01','BV001','play','2023-03-01 10:00:00'),
('u01','BV001','like','2023-03-01 10:05:00'),
('u01','BV001','coin','2023-03-01 10:05:30'),
('u02','BV001','play','2023-03-01 10:10:00'),
('u02','BV001','favorite','2023-03-01 10:10:30'),
('u03','BV001','play','2023-03-01 10:15:00'),
('u01','BV002','play','2023-03-01 11:00:00'),
('u01','BV002','like','2023-03-01 11:02:00'),
('u02','BV002','play','2023-03-01 11:05:00'),
('u02','BV002','coin','2023-03-01 11:06:00'),
('u02','BV002','favorite','2023-03-01 11:06:30');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
