腾讯大数据面试SQL-视频播放时长分布统计
⚠️ 待修正
一、题目背景
这道题来自腾讯视频的数据分析岗面试。腾讯视频拥有数亿用户和海量视频内容，用户观看时长是评估内容质量和推荐效果的核心指标。播放时长分布可以告诉运营团队：用户是在"刷短视频"（<10秒即划走）还是"沉浸式观看"（>60秒），从而优化内容策略和推荐算法。
业务场景：视频推荐算法的一个重要特征就是"预估播放时长"。如果大量用户只看10秒就划走，说明封面/标题和内容不匹配（标题党）或前10秒不够吸引人。这道SQL用于制作播放时长分布报表。
二、题目
现有一张视频播放日志表 t13_video_play_log，记录了用户每次播放视频的时长（单位：秒）。请统计视频播放时长的分布情况，按以下区间分段统计播放次数及占比：
| 区间 | 说明 |
|---|---|
| 0-10秒 | 短播放 |
| 10-30秒 | 较短播放 |
| 30-60秒 | 中等播放 |
| 60-300秒 | 较长播放 |
| 300秒以上 | 长播放 |
视频播放日志表 t13_video_play_log：
+----------+----------+---------------+---------------------+
| user_id | video_id | play_duration | play_time |
+----------+----------+---------------+---------------------+
| u01 | v001 | 5 | 2023-03-01 10:00:00 |
| u01 | v002 | 45 | 2023-03-01 10:30:00 |
| u02 | v001 | 120 | 2023-03-01 11:00:00 |
| u02 | v003 | 8 | 2023-03-01 12:00:00 |
| u03 | v001 | 350 | 2023-03-01 12:30:00 |
| u03 | v002 | 25 | 2023-03-01 13:00:00 |
| u04 | v001 | 600 | 2023-03-01 14:00:00 |
| u04 | v003 | 15 | 2023-03-01 15:00:00 |
| u01 | v003 | 200 | 2023-03-02 09:00:00 |
| u02 | v002 | 55 | 2023-03-02 10:00:00 |
| u03 | v003 | 3 | 2023-03-02 11:00:00 |
| u05 | v001 | 80 | 2023-03-02 12:00:00 |
| u05 | v002 | 30 | 2023-03-02 13:00:00 |
| u05 | v004 | 180 | 2023-03-02 14:00:00 |
+----------+----------+---------------+---------------------+
三、思路分析
本题的核心是使用 CASE WHEN 对连续数值（播放时长）进行分段，然后统计各分段的次数和占比。
解题步骤：
- 使用
CASE WHEN将播放时长映射到对应的区间标签； - 按区间分组统计播放次数；
- 使用窗口函数
SUM() OVER()计算总次数，进而计算占比；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 使用 CASE WHEN 对播放时长进行分段
执行SQL
select user_id,
video_id,
play_duration,
case
when play_duration < 10 then '0-10秒'
when play_duration >= 10 and play_duration < 30 then '10-30秒'
when play_duration >= 30 and play_duration < 60 then '30-60秒'
when play_duration >= 60 and play_duration < 300 then '60-300秒'
else '300秒以上'
end as duration_range
from t13_video_play_log
执行结果
+----------+----------+---------------+----------------+
| user_id | video_id | play_duration | duration_range |
+----------+----------+---------------+----------------+
| u01 | v001 | 5 | 0-10秒 |
| u01 | v002 | 45 | 30-60秒 |
| u02 | v001 | 120 | 60-300秒 |
| u02 | v003 | 8 | 0-10秒 |
| u03 | v001 | 350 | 300秒以上 |
| u03 | v002 | 25 | 10-30秒 |
| u04 | v001 | 600 | 300秒以上 |
| u04 | v003 | 15 | 10-30秒 |
| u01 | v003 | 200 | 60-300秒 |
| u02 | v002 | 55 | 30-60秒 |
| u03 | v003 | 3 | 0-10秒 |
| u05 | v001 | 80 | 60-300秒 |
| u05 | v002 | 30 | 30-60秒 |
| u05 | v004 | 180 | 60-300秒 |
+----------+----------+---------------+----------------+
2. 按区间分组统计播放次数及占比
执行SQL
select duration_range,
play_cnt,
round(play_cnt / sum(play_cnt) over (), 4) as play_rate
from (
select case
when play_duration < 10 then '0-10秒'
when play_duration >= 10 and play_duration < 30 then '10-30秒'
when play_duration >= 30 and play_duration < 60 then '30-60秒'
when play_duration >= 60 and play_duration < 300 then '60-300秒'
else '300秒以上'
end as duration_range,
count(1) as play_cnt
from t13_video_play_log
group by case
when play_duration < 10 then '0-10秒'
when play_duration >= 10 and play_duration < 30 then '10-30秒'
when play_duration >= 30 and play_duration < 60 then '30-60秒'
when play_duration >= 60 and play_duration < 300 then '60-300秒'
else '300秒以上'
end
) t
order by
case duration_range
when '0-10秒' then 1
when '10-30秒' then 2
when '30-60秒' then 3
when '60-300秒' then 4
when '300秒以上' then 5
end
执行结果
+----------------+----------+-----------+
| duration_range | play_cnt | play_rate |
+----------------+----------+-----------+
| 0-10秒 | 3 | 0.2143 |
| 10-30秒 | 2 | 0.1429 |
| 30-60秒 | 3 | 0.2143 |
| 60-300秒 | 4 | 0.2857 |
| 300秒以上 | 2 | 0.1429 |
+----------------+----------+-----------+
从结果可以看出，"60-300秒"的播放占比最高（28.57%），而"10-30秒"和"300秒以上"占比较低。
五、常见坑点
坑1：CASE WHEN 分段边界重叠
BETWEEN 0 AND 10 和 BETWEEN 10 AND 30，边界值10同时满足两个条件，会被第一个命中的CASE捕获。正确写法是 0-9.999 或在上层用 < 10、>= 10 AND < 30 明确边界归属。
坑2：占比用子查询 vs 窗口函数
老式写法：子查询 SELECT COUNT(*) FROM table 拿总数再除。窗口函数 SUM(COUNT(*)) OVER() 一次扫描完成，且不缩行，更简洁高效。
六、举一反三
- 按视频类型分层：GROUP BY 加上 video_category，对比不同品类（综艺/电视剧/短视频）的播放时长分布
- 完播率统计：关联视频时长表，计算
播放时长/视频总时长 >= 0.8的完播次数占比 - 用户分层：按用户累计播放时长分高/中/低活跃用户，对比不同用户群的播放时长分布
- AB实验：加 experiment_id 分组，对比新旧推荐算法下的播放时长分布变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 分段统计 | 将连续播放时长映射到离散区间 |
| SUM() OVER() 算占比 | 窗口函数一次扫描获取全局总次数 |
| GROUP BY + COUNT | 按区间分组统计播放次数 |
| ROUND 格式化 | 占比保留4位小数便于展示 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t13_video_play_log (
user_id string COMMENT '用户ID',
video_id string COMMENT '视频ID',
play_duration int COMMENT '播放时长（秒）',
play_time string COMMENT '播放时间'
) COMMENT '视频播放日志表';
-- 数据插入
INSERT INTO t13_video_play_log VALUES
('u01', 'v001', 5, '2023-03-01 10:00:00'),
('u01', 'v002', 45, '2023-03-01 10:30:00'),
('u02', 'v001', 120, '2023-03-01 11:00:00'),
('u02', 'v003', 8, '2023-03-01 12:00:00'),
('u03', 'v001', 350, '2023-03-01 12:30:00'),
('u03', 'v002', 25, '2023-03-01 13:00:00'),
('u04', 'v001', 600, '2023-03-01 14:00:00'),
('u04', 'v003', 15, '2023-03-01 15:00:00'),
('u01', 'v003', 200, '2023-03-02 09:00:00'),
('u02', 'v002', 55, '2023-03-02 10:00:00'),
('u03', 'v003', 3, '2023-03-02 11:00:00'),
('u05', 'v001', 80, '2023-03-02 12:00:00'),
('u05', 'v002', 30, '2023-03-02 13:00:00'),
('u05', 'v004', 180, '2023-03-02 14:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
