SQL 视频分区热度排名：按播放量分组排序（B站面试题）

一、题目背景

这道题来自B站内容运营部门的数据分析岗面试。B站的视频分区体系（游戏、知识、音乐、舞蹈、生活等20+分区）是内容分发和流量分配的基础框架。首页推荐位的分区配比、分区排行榜的展示策略，都需要基于各分区的总播放热度来决策。

业务场景：季度运营复盘时，需要输出"各分区播放量排名及占比"报表，作为下季度分区流量资源分配的依据。播放量持续下滑的分区可能需要运营活动拉动。

二、题目

现有一张视频播放表 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         |
+-----------+--------------+-----------+
3 rows selected (1.316 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：聚合函数不能直接嵌套窗口函数

rank() over (order by sum(play_cnt) desc) 是正确的写法——窗口函数可以包含聚合函数。但写法 sum(rank() over (...)) 是错误的——聚合函数不能包裹窗口函数。记住：窗口函数在 SELECT/ORDER BY 中最后执行，只能被外层子查询引用。

坑2：RANK vs DENSE_RANK 的业务选择

假设两个分区播放量相同（并列第一），RANK 会跳过第二名直接排第三，DENSE_RANK 则连续排第二。业务上："各区热度排名"用 DENSE_RANK 更合理（不下跳）；"TOP 3 分区评选"用 ROW_NUMBER 只取三个人为更优。面试中主动说明选择理由会加分。

坑3：长尾分区数据稀疏问题

如果表里只有个别分区的视频，排名结果可能仅有 2-3 行。运营方期望看到全部分区的排名（包括0播放的分区），此时需要 LEFT JOIN 分区维表，用 coalesce(total_plays, 0) 补全。

六、举一反三

1. 增加播放占比列：在排名结果中增加 ratio 列（total_plays / sum(total_plays) over()），一眼看出各分区播放量占全站的比重
2. 二级分区排名：B站还有"二级分区"（如游戏→手游/端游/单机），partition by parent_category 实现一级分区下的二级排名
3. 趋势排名 vs 快照排名：分别统计过去7天、30天、90天的播放量排名，对比发现"快速崛起的分区"和"正在衰退的分区"

七、知识点总结

考点	说明
SUM() + GROUP BY	分组聚合得到每个分区的总播放量
RANK() OVER (ORDER BY ...)	在聚合结果上做全局排名，注意与DENSE_RANK的差异
窗口函数中嵌套聚合	over(order by sum(x)) 是合法写法，但反过来不行
COALESCE	补全NULL值，常用于LEFT JOIN维表后填充默认值

八、建表语句和数据插入

点击展开 DDL & DML

create table t3_video_play (
    video_id string comment '视频ID（BV号）',
    category string comment '视频分区',
    play_cnt bigint comment '播放量'
) comment '视频播放统计表';

insert into t3_video_play values
('BV001','游戏',500000),('BV002','音乐',300000),
('BV003','游戏',200000),('BV004','知识',400000);

📱关注公众号

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

💬加群交流

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