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

SELECT tv.author, DATE_FORMAT(start_time, '%Y-%m') AS month, 
ROUND(SUM(CASE 
WHEN tu.if_follow = 1 THEN 1
WHEN tu.if_follow=2 THEN -1
ELSE 0
END) / COUNT(*), 3) AS fans_growth_rate, # 求涨粉率
SUM(SUM(CASE 
WHEN tu.if_follow = 1 THEN 1
WHEN tu.if_follow=2 THEN -1
ELSE 0 
END)) OVER(PARTITION BY tv.author ORDER BY DATE_FORMAT(start_time,'%Y-%m')) AS total_fans # 求总粉丝量 使用聚合窗口函数
FROM tb_user_video_log AS tu
LEFT OUTER JOIN tb_video_info AS tv
ON tu.video_id = tv.video_id # 进行表联结
WHERE YEAR(start_time) = 2021
GROUP BY tv.author, DATE_FORMAT(start_time, '%Y-%m') # 按作者、日期进行分组
ORDER BY tv.author, total_fans;