B站大数据面试SQL-视频分区热度排名
⚠️ 待修正
一、题目背景
这道题来自B站的数据分析岗面试。B站是弹幕视频社区，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：内容消费相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张视频播放表 t3_video_play，统计每个分区（category）的总播放量并按热度排名。
表 t3_video_play：
+----------+----------+----------+
| video_id | category | play_cnt |
+----------+----------+----------+
| BV001 | 游戏 | 500000 |
| BV002 | 音乐 | 300000 |
| BV003 | 游戏 | 200000 |
| BV004 | 知识 | 400000 |
+----------+----------+----------+
三、思路分析
SUM聚合 + RANK窗口函数排名。考察GROUP BY与窗口函数组合。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
select category, sum(play_cnt) as total_plays,
rank() over (order by sum(play_cnt) desc) as rank_num
from t3_video_play
group by category
执行结果
+----------+-------------+----------+
| category | total_plays | rank_num |
+----------+-------------+----------+
| 游戏 | 700000 | 1 |
| 知识 | 400000 | 2 |
| 音乐 | 300000 | 3 |
+----------+-------------+----------+
五、常见坑点
坑1：RANK vs DENSE_RANK vs ROW_NUMBER — 并列值处理不同：ROW_NUMBER硬区分，RANK跳跃，DENSE_RANK连续。统计"前N名"需明确业务倾向。
坑2：NULL值排序位置 — ORDER BY 默认 NULLS LAST，如果排序列有 NULL，Top N可能被挤掉。
六、举一反三
-
分组Top N：
PARTITION BY category实现每个品类/区域的前N名 -
含占比的排名：
SUM() OVER()算总量，每个条目除总量得贡献度 -
滚动时间窗口Top N：限定过去30天数据，更适合分析趋势变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| RANK / DENSE_RANK / ROW_NUMBER | 排名函数三剑客，并列处理方式不同 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t3_video_play (video_id string, category string, play_cnt bigint);
INSERT INTO t3_video_play VALUES
('BV001','游戏',500000),('BV002','音乐',300000),
('BV003','游戏',200000),('BV004','知识',400000);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
