题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

SUM 聚合窗口函数时对author PARTITION BY,对month ORDER BY ,算的是每个月的累积粉丝，不是全部日期的累积粉丝

WITH t_fan AS (SELECT
b.author,DATE_FORMAT(a.start_time,'%Y-%m') AS month,
ROUND((COUNT(IF(if_follow=1,1,NULL)) - COUNT(IF(if_follow=2,1,NULL)) )/COUNT(*),3) AS fans_growth_rate,
COUNT(IF(if_follow=1,1,NULL))-COUNT(IF(if_follow=2,1,NULL)) AS total_fan
FROM tb_user_video_log a
JOIN tb_video_info b
USING(video_id)
WHERE YEAR(start_time) ='2021'
GROUP BY b.author,DATE_FORMAT(a.start_time,'%Y-%m'))

SELECT
author,month,fans_growth_rate,
SUM(total_fan) OVER(PARTITION BY author ORDER BY month) AS total_fans
FROM t_fan
ORDER BY author,total_fans;