SQL UP主粉丝增长趋势：日期序列+累计求和（B站面试题）

一、题目背景

这道题来自B站内容生态部门的数据分析岗面试。B站有超百万UP主，内容创作者生态是B站的核心竞争力。运营团队需要持续监控UP主的粉丝增长趋势，识别快速涨粉的优质创作者给予流量扶持，同时发现掉粉异常的UP主及时干预。

业务场景：每周运营周报需要输出"本周涨粉最快TOP10 UP主"和"涨粉速率趋势图"。这道题的 SQL 逻辑就是该报表的基础查询——计算每个UP主的粉丝日增量并排名。

二、题目

现有一张UP主粉丝表 t1_up_fans，记录每个UP主每日粉丝数。请计算每个UP主的粉丝日增长量并排名。

表 t1_up_fans：

+--------+-------------+-----------+
| up_id  |    date     | fans_cnt  |
+--------+-------------+-----------+
| UP001  | 2023-03-01  | 10000     |
| UP001  | 2023-03-02  | 10500     |
| UP001  | 2023-03-03  | 11200     |
| UP002  | 2023-03-01  | 50000     |
| UP002  | 2023-03-02  | 50800     |
+--------+-------------+-----------+

三、思路分析

核心思路是 LAG 求环比增量 + RANK 排名：

1. 计算日增量：用 LAG 获取每个UP主前一天的粉丝数，当天减去前一天 = 日增长量
2. 排名：按日增量降序排名，找出涨粉最快的UP主
3. 注意：每个UP主的第一条记录没有前一日数据，LAG 返回 NULL，日增量也为 NULL，排名时应排除

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

四、逐步推导

步骤1：用 LAG 计算每个UP主的粉丝日增量

select up_id, date, fans_cnt,
       fans_cnt - lag(fans_cnt, 1) over (partition by up_id order by date) as daily_growth
from t1_up_fans

执行结果

+--------+-------------+-----------+---------------+
| up_id  |    date     | fans_cnt  | daily_growth  |
+--------+-------------+-----------+---------------+
| UP001  | 2023-03-01  | 10000     | NULL          |
| UP001  | 2023-03-02  | 10500     | 500           |
| UP001  | 2023-03-03  | 11200     | 700           |
| UP002  | 2023-03-01  | 50000     | NULL          |
| UP002  | 2023-03-02  | 50800     | 800           |
+--------+-------------+-----------+---------------+
5 rows selected (7.85 seconds)(https://www.dwsql.com)

每个UP主的第一天没有前值，daily_growth为NULL。

步骤2：排除NULL后按日增量降序排名

select up_id, date, daily_growth,
       rank() over (order by daily_growth desc) as rn
from (
    select up_id, date,
           fans_cnt - lag(fans_cnt, 1) over (partition by up_id order by date) as daily_growth
    from t1_up_fans
) t
where daily_growth is not null

执行结果

+--------+-------------+---------------+-----+
| up_id  |    date     | daily_growth  | rn  |
+--------+-------------+---------------+-----+
| UP002  | 2023-03-02  | 800           | 1   |
| UP001  | 2023-03-03  | 700           | 2   |
| UP001  | 2023-03-02  | 500           | 3   |
+--------+-------------+---------------+-----+
3 rows selected (0.565 seconds)(https://www.dwsql.com)

UP002以单日涨粉800排名第一，UP001的3月3日涨粉700排第二。

五、常见坑点

坑1：窗口函数结果不能直接在WHERE中引用

LAG/LEAD/RANK 等窗口函数在 WHERE 子句执行之后才计算，写在 WHERE 里会报错。必须用子查询或 CTE 先算出窗口函数结果，再在外层 WHERE 过滤。

坑2：第一条记录的NULL值影响排名

每个UP主的第一条记录 daily_growth = NULL，如果不过滤直接 RANK，NULL 可能被排到最前面或最后面（取决于数据库实现），导致排名混乱。应该用 where daily_growth is not null 排除，或 coalesce(daily_growth, 0) 按需处理。

六、举一反三

1. 周/月涨粉榜：将 date 按周/月截断，先 sum(daily_growth) 聚合到周/月粒度，再做排名，输出"本周涨粉TOP10 UP主"
2. 涨粉速率分级：用 CASE WHEN 将日增量分为"高速增长(>1000)"、"稳定增长(100-1000)"、"低速增长(<100)"、"掉粉(负数)"四档，统计各类UP主占比
3. 异常掉粉检测：设置阈值 where daily_growth < -500，筛选单日掉粉超过500的UP主，可能是出了负面舆情需要PR介入

七、知识点总结

考点	说明
LAG(expr, offset)	获取前N行数据，用于环比计算、前后对比
RANK() OVER	跳跃排名，并列值同名次、下一名跳跃
子查询 + 窗口函数	窗口函数在SELECT中计算，需子查询才能对外层WHERE可见
NULL值过滤	窗口函数产生NULL时，排名前需显式排除，避免结果污染

八、建表语句和数据插入

点击展开 DDL & DML

create table t1_up_fans (
    up_id    string comment 'UP主ID',
    date     string comment '统计日期',
    fans_cnt bigint comment '当日粉丝数'
) comment 'UP主粉丝表';

insert into t1_up_fans values
('UP001','2023-03-01',10000),('UP001','2023-03-02',10500),
('UP001','2023-03-03',11200),('UP002','2023-03-01',50000),('UP002','2023-03-02',50800);

📱关注公众号

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

💬加群交流

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