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

# 1）截止当月的总粉丝量：窗口函数  SUM(fans_turnover)OVER(PARTITION BY author ORDER BY month)  total_fans
#   如果起始值为0, 窗口函数的作用就是累计每一段窗口的变化量; 
# if_follow 2 -1 fans 
# if_follow 1 +1 fans
# 有两个sum是因为是针对每个月的变化量sum(fans_turnover) 累计; 例如:
# 一月变化量是2，二月变化量是4，那么累积两个月的变化量终值就是6
# 2）只要出现在互动表，就表示一次观看；播放量+1
# COUNT(id) 
# GROUP BY author, month  #每个创作者每月
# WHERE YEAR(start_time) = 2021
# 3）涨粉率= 变化量／播放量：fans_turnover／COUNT(id)
# 4）求变化量fans_turnover:
#     SUM(CASE WHEN if_follow = 2 THEN  -1 
#              WHEN if_follow = 1 THEN  1
#              ELSE 0 END)

# 解法1:
SELECT author, DATE_FORMAT(start_time, "%Y-%m") month,
ROUND(SUM(CASE WHEN if_follow = 2 THEN  -1 
      WHEN if_follow = 1 THEN  1
      ELSE 0 END)/ COUNT(a.id),3) fans_growth_rate,
SUM(SUM(CASE WHEN if_follow = 2 THEN  -1 
         WHEN if_follow = 1 THEN  1
         ELSE 0 END)) OVER(PARTITION BY author ORDER BY DATE_FORMAT(start_time, "%Y-%m")) total_fans 
FROM tb_user_video_log a JOIN tb_video_info b
ON a.video_id = b.video_id
WHERE YEAR(start_time) = "2021"
GROUP BY author, DATE_FORMAT(start_time, "%Y-%m")
ORDER BY author, total_fans

# 解法2:
SELECT author, DATE_FORMAT(start_time, "%Y-%m") month,
ROUND(SUM(fans_turnover)/ COUNT(a.id),3) fans_growth_rate, 
# 问题就在于，这里计算总观看量时，不能把if_follow = 0的部分去掉；所以有三个union看起来不简洁
SUM(SUM(fans_turnover)) OVER(PARTITION BY author ORDER BY DATE_FORMAT(start_time, "%Y-%m")) total_fans
FROM
    (
    SELECT id, video_id, start_time, 1 AS fans_turnover
    FROM tb_user_video_log
    WHERE if_follow = 1 AND YEAR(start_time) = "2021"
    UNION 
    SELECT id, video_id, start_time, -1 AS fans_turnover
    FROM tb_user_video_log
    WHERE if_follow = 2 AND YEAR(start_time) = "2021"
    UNION
    SELECT id, video_id, start_time, 0 AS fans_turnover
    FROM tb_user_video_log
    WHERE if_follow = 0 AND YEAR(start_time) = "2021"
    ) a JOIN tb_video_info b
    ON a.video_id = b.video_id
GROUP BY author, DATE_FORMAT(start_time, "%Y-%m")
ORDER BY author, total_fans