如何用SQL实现基于每日粉丝变化量计算历史总粉丝数？

现有每日粉丝增减数据如下：

date	follower
23-10-2022	1
22-10-2022	0
21-10-2022	1
20-10-2022	2

已知总粉丝数为250，需要转换为每日的累计粉丝总数，预期输出：

date	follower
23-10-2022	251
22-10-2022	250
21-10-2022	249
20-10-2022	247

实现思路 #

从预期结果可推导规律：

• 最新日期的粉丝总数 = 给定总粉丝数 + 最新日期的粉丝增减量
• 历史日期的粉丝总数 = 后一天的粉丝总数 - 当前日期的粉丝增减量

我们可以通过窗口函数计算每个日期到最新日期的增减量总和，结合最新日期的总数推导每日粉丝数。

SQL代码（通用版本） #

假设数据表名为daily_followers，包含date（日期）和follower（当日粉丝增减量）两列：

SELECT 
    date,
    -- 计算最新日期的粉丝总数：给定总粉丝数 + 最新日期的增减量
    (250 + (SELECT follower FROM daily_followers WHERE date = (SELECT MAX(date) FROM daily_followers)))
    -- 减去从当前日期到最新日期的增减量总和，再加当前日期的增减量得到当日总数
    - SUM(follower) OVER (ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    + follower AS follower
FROM daily_followers
-- 按日期从新到旧排序，匹配预期输出格式
ORDER BY date DESC;

处理日期字符串的版本（针对字符串格式日期） #

如果date列是dd-mm-yyyy格式的字符串，需先转换为日期类型确保排序正确，以MySQL为例：

SELECT 
    date,
    (250 + (SELECT follower FROM daily_followers WHERE STR_TO_DATE(date, '%d-%m-%Y') = (SELECT MAX(STR_TO_DATE(date, '%d-%m-%Y')) FROM daily_followers)))
    - SUM(follower) OVER (ORDER BY STR_TO_DATE(date, '%d-%m-%Y') ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    + follower AS follower
FROM daily_followers
ORDER BY STR_TO_DATE(date, '%d-%m-%Y') DESC;

代码解释 #

1. 最新日期总数计算：通过子查询获取最新日期的增减量，与给定的总粉丝数250相加，得到最新日期的粉丝总数（示例中为251）。
2. 累计增减量计算：窗口函数SUM(follower) OVER (...)计算从当前日期到最新日期的所有增减量之和。
3. 当日总数推导：用最新日期的总数减去上述累计和，再加上当前日期的增减量，即可得到当日的粉丝总数。

内容的提问来源于stack exchange，提问作者Likhitha Reddy