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

注意：1、inner和left连接的区别

2、case when 外面可以加sum

3、在粉丝变化数的基础上求每个月的粉丝总数，可以用sum的窗口函数

SUM(fans_situation) OVER(PARTITION BY author ORDER BY month) AS total_fans

注意这里和子查询中的group by不一样的是没有对momth进行聚合，所以sum算的是累计

SELECT
A.author AS author,
A.month AS month,
ROUND(fans_situation / total_play, 3) AS fans_growth_rate,
SUM(fans_situation) OVER(PARTITION BY author ORDER BY month) AS total_fans
FROM
(SELECT
b.author AS author,
DATE_FORMAT(a.start_time,'%Y-%m') AS month,
# 粉丝变化量
SUM(
CASE
WHEN a.if_follow = 1 THEN 1
WHEN a.if_follow = 2 THEN -1
ELSE 0
END) AS fans_situation,
# 播放量
COUNT(*) AS total_play
FROM
tb_user_video_log AS a
INNER JOIN
tb_video_info AS b
ON
a.video_id = b.video_id
WHERE
YEAR(a.start_time) = 2021
AND YEAR(a.end_time) = 2021
GROUP BY
author, month) AS A
ORDER BY
author, total_fans