SQL 弹幕峰值时刻：视频播放中弹幕密度最高时间点（B站面试题）

一、题目背景

这道题来自B站弹幕业务部门的数据分析岗面试。弹幕是B站最核心的社区互动形式，弹幕峰值时刻代表了视频中最精彩或最具争议的片段。产品团队需要通过弹幕密度分析来优化"高能进度条"（视频进度条上的弹幕密度热力图），帮助用户快速定位精彩片段。

业务场景：弹幕产品经理需要知道每个视频的"弹幕峰值时刻"——弹幕最密集的时间窗口，用来校准高能进度条的标记准确性。

二、题目

现有一张弹幕表 t4_danmaku，记录每条弹幕在视频中的发送时间（秒）。请以5秒为窗口统计弹幕密度，找出每个视频弹幕最密集的峰值时刻。

表 t4_danmaku：

+-----------+---------------+----------+
| video_id  | danmaku_time  | content  |
+-----------+---------------+----------+
| BV001     | 10            | 前方高能     |
| BV001     | 12            | 哈哈哈      |
| BV001     | 15            | 打卡       |
| BV001     | 45            | 名场面      |
| BV001     | 46            | 泪目       |
+-----------+---------------+----------+

三、思路分析

使用 floor(danmaku_time/5)*5 将时间分桶，按桶统计弹幕数，排序取峰值。

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

四、逐步推导

步骤1：按5秒窗口统计每个视频各时间段的弹幕数

select video_id,
       concat(floor(danmaku_time/5)*5, '-', floor(danmaku_time/5)*5+5, '秒') as time_window,
       count(1) as danmaku_cnt
from t4_danmaku
group by video_id, floor(danmaku_time/5)

执行结果

+-----------+--------------+--------------+
| video_id  | time_window  | danmaku_cnt  |
+-----------+--------------+--------------+
| BV001     | 10-15秒       | 2            |
| BV001     | 45-50秒       | 2            |
| BV001     | 15-20秒       | 1            |
+-----------+--------------+--------------+
3 rows selected (0.545 seconds)(https://www.dwsql.com)

每个视频可能有多个时间窗口，下一步需要取每个视频弹幕最多的窗口。

步骤2：用ROW_NUMBER取每个视频的弹幕峰值窗口

select video_id, time_window, danmaku_cnt
from (
    select video_id,
           concat(floor(danmaku_time/5)*5, '-', floor(danmaku_time/5)*5+5, '秒') as time_window,
           count(1) as danmaku_cnt,
           row_number() over (partition by video_id order by count(1) desc) as rn
    from t4_danmaku
    group by video_id, floor(danmaku_time/5)
) t
where rn = 1

执行结果

+-----------+--------------+--------------+
| video_id  | time_window  | danmaku_cnt  |
+-----------+--------------+--------------+
| BV001     | 10-15秒       | 2            |
+-----------+--------------+--------------+
1 row selected (0.584 seconds)(https://www.dwsql.com)

10-15秒窗口弹幕最密集（2条），为BV001的弹幕峰值时刻。每个视频只返回一条峰值记录。

五、常见坑点

坑1：时间窗口边界归属问题

floor(danmaku_time/5)*5 将 0-4.99→0, 5-9.99→5, 10-14.99→10。边界值 5.00 属于 5-10秒窗口。业务上需要明确这个归属规则，面试时主动确认"5秒整的弹幕算前一个窗口还是后一个？"展示严谨性。

坑2：窗口大小参数硬编码

5秒窗口是示例值，实际业务中窗口大小（5秒/10秒/30秒）会影响峰值判定。更大的窗口更平滑但丢失精度。建议将窗口大小作为参数传入，或对比多种窗口的结果一致性。

坑3：峰值并列问题

如果一个视频有两个窗口弹幕数相同且最高，row_number 只会随机选一个。如果需要展示所有并列峰值，改用 rank() = 1 替代 row_number() = 1。

六、举一反三

1. 弹幕密度热力图数据：不取峰值而输出全部时间窗口的数据（跳过 where rn = 1），给前端绘制完整的弹幕密度曲线，用于"高能进度条"功能
2. 跨视频峰值对比：统计所有视频的峰值弹幕密度，TOP 10的视频通常是爆款内容，可用于首页推荐
3. 弹幕情感与峰值的关系：JOIN弹幕情感标签表，分析峰值时刻的弹幕是正面（"泪目""前方高能"）还是负面（"烂""尴尬"），区分"精彩峰值"和"争议峰值"

七、知识点总结

考点	说明
FLOOR 分桶	将连续时间值离散化为等宽窗口，常用于时间轴降维
ROW_NUMBER + PARTITION BY	每组内排序后取第一名，实现"每组Top 1"
COUNT + GROUP BY	按窗口分组统计弹幕数，配合窗口函数取峰值
RANK vs ROW_NUMBER	并列峰值时，RANK保留所有并列第一，ROW_NUMBER只取一个

八、建表语句和数据插入

点击展开 DDL & DML

create table t4_danmaku (
    video_id      string comment '视频ID（BV号）',
    danmaku_time  int    comment '弹幕发送时间（视频播放秒数）',
    content       string comment '弹幕内容'
) comment '弹幕表';

insert into t4_danmaku values
('BV001',10,'前方高能'),('BV001',12,'哈哈哈'),('BV001',15,'打卡'),
('BV001',45,'名场面'),('BV001',46,'泪目');

📱关注公众号

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

💬加群交流

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