字节跳动大数据面试SQL-视频完播率统计

数据仓库晨曦

发布于 2026-06-23 17:30:57

1370

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

一、题目背景

这道题来自字节跳动短视频业务的数据分析岗面试。对于抖音这样的产品，完播率是衡量内容质量的核心指标——用户有没有把这个视频看完？完播率高的视频会被推荐算法加权分发，获得更多曝光。

业务场景：运营需要一份"视频质量报表"，按视频维度统计完播率，完播率低的视频需要人工复审（是不是封面党？开头太拖沓？），完播率高的视频则进入精品池。

二、题目

现有一张用户播放行为表 t10_zj_video_play，记录了用户每次播放视频的时长和视频总时长。请统计每个视频的完播率。

t10_zj_video_play 表

+----------+-----------+----------------+---------------+
| user_id  | video_id  | play_duration  | video_length  |
+----------+-----------+----------------+---------------+
| 1        | V001      | 45             | 60            |
| 2        | V001      | 60             | 60            |
| 3        | V001      | 30             | 60            |
| 1        | V002      | 15             | 15            |
| 2        | V002      | 10             | 15            |
| 3        | V002      | 15             | 15            |
| 1        | V003      | 90             | 120           |
| 2        | V003      | 120            | 120           |
| 3        | V003      | 60             | 120           |
| 4        | V003      | 5              | 120           |
+----------+-----------+----------------+---------------+

定义：完播 = 播放时长 >= 视频时长。完播率 = 完播次数 / 总播放次数。

期望输出：每个视频的播放次数、完播次数、完播率(%)。

三、思路分析

核心是条件聚合：SUM(CASE WHEN 完播条件 THEN 1 ELSE 0 END)。

判断每行是否完播：play_duration >= video_length
按 video_id 分组，SUM 统计完播次数，COUNT 统计总次数
完播率 = 完播次数 / 总播放次数 × 100

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

四、逐步推导

步骤1：逐行判断是否完播

SELECT
    user_id,
    video_id,
    play_duration,
    video_length,
    CASE WHEN play_duration >= video_length THEN 1 ELSE 0 END AS is_complete
FROM t10_zj_video_play;

执行结果：

+----------+-----------+----------------+---------------+--------------+
| user_id  | video_id  | play_duration  | video_length  | is_complete  |
+----------+-----------+----------------+---------------+--------------+
| 1        | V001      | 45             | 60            | 0            |
| 2        | V001      | 60             | 60            | 1            |
| 3        | V001      | 30             | 60            | 0            |
| 1        | V002      | 15             | 15            | 1            |
| 2        | V002      | 10             | 15            | 0            |
| 3        | V002      | 15             | 15            | 1            |
| 1        | V003      | 90             | 120           | 0            |
| 2        | V003      | 120            | 120           | 1            |
| 3        | V003      | 60             | 120           | 0            |
| 4        | V003      | 5              | 120           | 0            |
+----------+-----------+----------------+---------------+--------------+
10 rows selected (0.369 seconds)(dwsql.com)

步骤2：按视频聚合统计

SELECT
    video_id,
    COUNT(*) AS total_plays,
    SUM(CASEWHEN play_duration >= video_length THEN1ELSE0END) AS complete_plays,
    ROUND(
        SUM(CASEWHEN play_duration >= video_length THEN1ELSE0END) * 100.0 / COUNT(*),
        1
    ) AS completion_rate
FROM t10_zj_video_play
GROUPBY video_id
ORDERBY video_id;

最终结果：

+-----------+--------------+-----------------+------------------+
| video_id  | total_plays  | complete_plays  | completion_rate  |
+-----------+--------------+-----------------+------------------+
| V001      | 3            | 1               | 33.3             |
| V002      | 3            | 2               | 66.7             |
| V003      | 4            | 1               | 25.0             |
+-----------+--------------+-----------------+------------------+
3 rows selected (1.234 seconds)(dwsql.com)

分析：V002 是15秒短视频，完播率最高 66.7%。V003 是120秒长视频，完播率仅 25%——可能需要优化开头3秒的"黄金前奏"。

五、常见坑点

坑1：播放时长超过视频时长

用户可能拖拽进度条重复观看，导致 play_duration > video_length。这仍然算完播——条件用 >= 而非 = 即可覆盖。

坑2：video_length 为 0

如果视频时长为 0（数据异常），play_duration >= 0 恒为 True，所有播放都算完播。应提前过滤或标记异常。

六、举一反三

按用户维度：统计每个用户的完播率，用于用户画像——"高质量内容消费者"标签
分段完播率：不是只看 >= 100%，而是分桶 (0-25%, 25-50%, 50-75%, 75-100%)，画出播放完成度分布
加权完播率：如果用户是"点击后5秒内关闭"，权重比"看了80%后关闭"更低

七、知识点总结

SUM(CASE WHEN cond THEN 1 ELSE 0 END) 条件计数

八、建表语句和数据插入

CREATE TABLEIFNOTEXISTS t10_zj_video_play (
    user_id INT,
    video_id STRING,
    play_duration INT,
    video_length INT
);

INSERTINTO t10_zj_video_play VALUES
(1, 'V001', 45, 60),
(2, 'V001', 60, 60),
(3, 'V001', 30, 60),
(1, 'V002', 15, 15),
(2, 'V002', 10, 15),
(3, 'V002', 15, 15),
(1, 'V003', 90, 120),
(2, 'V003', 120, 120),
(3, 'V003', 60, 120),
(4, 'V003', 5, 120);

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

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

统计