SQL 弹幕热词统计：文本清洗+停用词过滤+词频Top N（B站面试题）

一、题目背景

这道题来自B站社区内容分析部门的数据分析岗面试。B站每天产生数亿条弹幕，弹幕热词是社区流行文化的风向标——"前方高能""注入灵魂""梦开始的地方"等弹幕梗都是从高频词演变而来的。但原始弹幕数据存在两个常见问题：一是用户发送弹幕时经常无意带上前后空格，二是"哈哈哈""打卡""111"等无意义内容会占据热词榜。

业务场景：每周"弹幕热词周报"需要先清洗弹幕文本（去除空格）、过滤无意义停用词，再统计高频弹幕Top 20。运营团队据此识别新兴社区梗和流行语，辅助热搜运营和社区活动策划。

二、题目

现有一张弹幕内容表 t6_danmaku_content 和一张停用词维表 t6_dim_stopwords。请先清洗弹幕（去除前后空格），过滤停用词表中的无意义弹幕，再统计高频弹幕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  |
| BV001     | u04      | 打卡       | 2023-03-01 10:03:00  |
| BV001     | u05      |  打卡      | 2023-03-01 10:04:00  |
| BV002     | u01      | 梦开始的地方   | 2023-03-02 14:00:00  |
| BV002     | u02      | 前方高能     | 2023-03-02 14:01:00  |
| BV002     | u03      |  111     | 2023-03-02 14:02:00  |
| BV002     | u04      | 注入灵魂     | 2023-03-02 14:03:00  |
| BV002     | u05      | 哈哈哈      | 2023-03-02 14:04:00  |
+-----------+----------+----------+----------------------+

注意：部分弹幕前后带有空格（如 哈哈哈、打卡、111），直接 group by 会被视为不同内容。

停用词维表 t6_dim_stopwords：

+-----------+
| stopword  |
+-----------+
| 哈哈哈       |
| 打卡        |
| 111       |
| 666       |
| 来了        |
| 第一        |
+-----------+

三、思路分析

核心思路是 "清洗 → 过滤 → 统计" 三步走：

1. 文本清洗：用 trim() 去除弹幕内容的前后空格，统一格式
2. 停用词过滤：用 left join 关联停用词维表，保留 stopword is null 的行（即不在停用词表中的弹幕）
3. 词频统计：按清洗后的弹幕内容 group by + count，降序取 Top 20

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

四、逐步推导

步骤1：trim 清洗弹幕文本，去除前后空格

select video_id, user_id,
       trim(content) as clean_content,
       send_time
from t6_danmaku_content

执行结果

+-----------+----------+----------------+----------------------+
| video_id  | user_id  | clean_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  |
| BV001     | u04      | 打卡             | 2023-03-01 10:03:00  |
| BV001     | u05      | 打卡             | 2023-03-01 10:04:00  |
| BV002     | u01      | 梦开始的地方         | 2023-03-02 14:00:00  |
| BV002     | u02      | 前方高能           | 2023-03-02 14:01:00  |
| BV002     | u03      | 111            | 2023-03-02 14:02:00  |
| BV002     | u04      | 注入灵魂           | 2023-03-02 14:03:00  |
| BV002     | u05      | 哈哈哈            | 2023-03-02 14:04:00  |
+-----------+----------+----------------+----------------------+
10 rows selected (0.256 seconds)(https://www.dwsql.com)

trim() 后 哈哈哈 → 哈哈哈、打卡 → 打卡、111 → 111，带空格的弹幕被归一化。

步骤2：left join 停用词维表，过滤无意义弹幕

select trim(t6.content) as clean_content
from t6_danmaku_content t6
left join t6_dim_stopwords s on trim(t6.content) = s.stopword
where s.stopword is null

执行结果

+----------------+
| clean_content  |
+----------------+
| 前方高能           |
| 前方高能           |
| 梦开始的地方         |
| 前方高能           |
| 注入灵魂           |
+----------------+
5 rows selected (0.476 seconds)(https://www.dwsql.com)

stopword is null 表示 trim 后的弹幕内容不在停用词表中。"哈哈哈""打卡""111"被过滤掉，剩下的都是有语义价值的弹幕。

步骤3：分组统计 + 降序取 Top 20

select clean_content, count(1) as cnt
from (
    select trim(t6.content) as clean_content
    from t6_danmaku_content t6
    left join t6_dim_stopwords s on trim(t6.content) = s.stopword
    where s.stopword is null
) t
group by clean_content
order by cnt desc
limit 20

执行结果

+----------------+------+
| clean_content  | cnt  |
+----------------+------+
| 前方高能           | 3    |
| 注入灵魂           | 1    |
| 梦开始的地方         | 1    |
+----------------+------+
3 rows selected (1.367 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：trim 必须在 join 条件中与 select 中保持一致

left join ... on trim(t6.content) = s.stopword 需要用 trim() 包裹，否则 哈哈哈（带空格）不会匹配到停用词表中的 哈哈哈，导致本该被过滤的弹幕漏网。同样，select 中的 trim() 也不可省略，否则最终结果中 哈哈哈 和 哈哈哈 仍会被视为两个不同内容。

坑2：left join + is null 的语义理解

where s.stopword is null 不是"停用词为 null"，而是"没有匹配到停用词表中的任何行"。left join 对右表无匹配时，右表所有字段填充为 null，因此 stopword is null 等价于"不在停用词表中"。如果用 inner join 替代 left join ... is null，逻辑恰好相反（只保留在停用词表中的弹幕），是常见的笔误。

坑3：停用词维表数据不全导致榜单噪音

如果停用词表缺少某个高频无意义词（如新增的"卧槽""nb"等），该词会进入Top 20。实际运营中需定期更新停用词表。面试中可以补充："建议建立停用词审核机制，每月review Top 50 弹幕，把新出现的噪音词加入停用词表"。

六、举一反三

1. 对比清洗前后的Top 20差异：分别统计原始 content 和 trim(content) 的 Top 20，用 union all 放在一起对比，量化"因空格导致的重复计数"有多少条——这是数据质量报告的常见内容
2. 停用词本身的频率统计：反过来统计被过滤掉的停用词各出现了多少次（where s.stopword is not null），了解噪音弹幕的占比和分布，评估停用词表的覆盖效果
3. 按视频分区统计热词：在子查询中 join 视频信息表获取 category，外层 group by 加上 category，对比游戏区 vs 番剧区 vs 生活区的弹幕热词差异，反映不同用户群体的文化特征

七、知识点总结

考点	说明
trim() 文本清洗	去除前后空格，归一化文本格式，避免"相同内容、不同空格"被重复计数
left join + is null 反选	保留左表有、右表无的行，实现"排除停用词"的过滤语义
group by + count 词频统计	按清洗后的弹幕分组计数，降序取Top N
维表驱动过滤	把过滤规则从硬编码改为维表管理，新增停用词只需 insert 一行，无需改SQL
order by + limit	降序取 Top N 高频词，简单但面试高频

八、建表语句和数据插入

点击展开 DDL & DML

create table t6_danmaku_content (
    video_id  string comment '视频ID（BV号）',
    user_id   string comment '用户ID',
    content   string comment '弹幕内容（可能含前后空格）',
    send_time string comment '弹幕发送时间'
) comment '弹幕内容表';

create table t6_dim_stopwords (
    stopword string comment '停用词'
) comment '弹幕停用词维表';

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'),
('BV001','u04','打卡','2023-03-01 10:03:00'),
('BV001','u05',' 打卡 ','2023-03-01 10:04:00'),
('BV002','u01','梦开始的地方','2023-03-02 14:00:00'),
('BV002','u02','前方高能','2023-03-02 14:01:00'),
('BV002','u03',' 111 ','2023-03-02 14:02:00'),
('BV002','u04','注入灵魂','2023-03-02 14:03:00'),
('BV002','u05','哈哈哈 ','2023-03-02 14:04:00');

insert into t6_dim_stopwords values
('哈哈哈'),('打卡'),('111'),('666'),('来了'),('第一');

📱关注公众号

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

💬加群交流

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