B站大数据面试SQL-视频弹幕峰值时刻

⚠️ 待修正

一、题目背景

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

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

一、题目

现有一张弹幕表 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 将时间分桶，按桶统计弹幕数，排序取峰值。

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

四、逐步推导

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)
order by danmaku_cnt desc

执行结果

+----------+-------------+--------------+
| video_id | time_window | danmaku_cnt  |
+----------+-------------+--------------+
| BV001    | 10-15秒     | 3            |
| BV001    | 45-50秒     | 2            |
+----------+-------------+--------------+

10-15秒窗口弹幕最密集（3条），为弹幕峰值时刻。

五、常见坑点

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

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

六、举一反三

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

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

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

七、知识点总结

考点	说明
SUM() OVER 累积求和	窗口聚合实现滚动累计，配合ORDER BY实现时间轴展开
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t4_danmaku (video_id string, danmaku_time int, content string);
INSERT INTO t4_danmaku VALUES
('BV001',10,'前方高能'),('BV001',12,'哈哈哈'),('BV001',15,'打卡'),
('BV001',45,'名场面'),('BV001',46,'泪目');

📱关注公众号

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

💬加群交流

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