B站大数据面试SQL-弹幕关键词词云统计

⚠️ 待修正

一、题目背景

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

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

一、题目

现有一张弹幕内容表 t6_danmaku_content，统计高频弹幕Top 20。

表 t6_danmaku_content：

+----------+---------+-------------+---------------------+
| video_id | user_id |   content   |      send_time      |
+----------+---------+-------------+---------------------+
| BV001    | u01     | 前方高能    | 2023-03-01 10:00:00 |
| BV001    | u02     | 哈哈哈      | 2023-03-01 10:01:00 |
| BV001    | u03     | 前方高能    | 2023-03-01 10:02:00 |
| BV002    | u01     | 打卡        | 2023-03-02 14:00:00 |
| BV002    | u03     | 哈哈哈      | 2023-03-02 14:05:00 |
+----------+---------+-------------+---------------------+

三、思路分析

按完整弹幕内容分组统计频次，取Top 20。进阶可用explode+split分词后统计。

四、逐步推导

select content, count(1) as cnt
from t6_danmaku_content
group by content
order by cnt desc
limit 20

执行结果

+-------------+-----+
|   content   | cnt |
+-------------+-----+
| 前方高能    | 2   |
| 哈哈哈      | 2   |
| 打卡        | 1   |
+-------------+-----+

五、常见坑点

坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。

坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。

六、举一反三

1. 动态分段：阈值存在配置表中用JOIN取值，灵活调整分段策略

2. 累计分布CDF：SUM(cnt) OVER (ORDER BY bucket) 算累计占比，画帕累托图

3. 按时间维度对比：按天/周/月分区，观察分布的时序变化

七、知识点总结

考点	说明
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t6_danmaku_content (
    video_id string, user_id string, content string, send_time string
);
INSERT INTO t6_danmaku_content VALUES
('BV001','u01','前方高能','2023-03-01 10:00:00'),
('BV001','u02','哈哈哈','2023-03-01 10:01:00'),
('BV001','u03','前方高能','2023-03-01 10:02:00'),
('BV002','u01','打卡','2023-03-02 14:00:00'),
('BV002','u03','哈哈哈','2023-03-02 14:05:00');

📱关注公众号

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

💬加群交流

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