题目：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

要求条件：

1）仅限2021年

2）结果按创作者ID、总粉丝量升序排序

指标：

1）涨粉率 = (加粉量 - 掉粉量) / 播放量

2）字段“if_follow” —— 1代表点击关注，0代表未点击关注，2为取消关注

解题思路

第一步：确认2021年中，每个作者、在每一个月中的实际涨粉量 & 播放量，才能求出每个作者在每个月的转粉率

这一步很简单，只需要联结两个表即可，对作者、月份进行GROUP BY。其中唯一需要注意的是，因为字段if_follow的值只有0、1、2，而非可直接用于计算的-1（掉粉）、0（无变化）、1（涨粉），所以这里需要用到CASE WHEN表达式。

SELECT i.author, 
       DATE_FORMAT(l.start_time, '%Y-%m') AS momth, 
       /* 每个作者在每个月的涨粉量为 SUM(CASE if_follow WHEN...),
       而播放量计算就更简单，在表tb_video_info中，字段“author”中，作者id出现多少行就是多少次播放量，
       最后，对两者相除的结果进行四舍五入，保留3位数即可*/
       ROUND(SUM(CASE l.if_follow WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 
       ELSE NULL END) / COUNT(author), 3) AS fans_growth_rate
       
FROM tb_video_info AS i INNER JOIN tb_user_video_log AS l
ON i.video_id = l.video_id

WHERE YEAR(l.start_time) = 2021
GROUP BY i.author, DATE_FORMAT(l.start_time, '%Y-%m')

第二步 2-1：计算每个作者、每个月的总涨粉量

这一步稍微有点复杂，但也不难。

首先，需要知道：如“截止当前的XX量”这种指标，其实都是累计值的计算，累计值的计算一般可以用聚合函数与窗口函数的结合来解决。

所以，在计算这个累计值之前，需要确认的指标是：每个月的总涨粉量 —— 之后再对这个每月总涨粉量进行累计计算。如此一来，就很清晰了，再为总涨粉量select出一个新字段即可

SELECT i.author, 
       DATE_FORMAT(l.start_time, '%Y-%m') AS month, 
       ROUND(SUM(CASE l.if_follow WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 
       ELSE NULL END) / COUNT(author), 3) AS fans_growth_rate,
       /* 每月的总涨粉量，上面已经使用过了，只需要为其单独创建一个字段即可*/
      SUM(CASE l.if_follow WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 ELSE NULL END) AS monthly_growth
      
FROM tb_video_info AS i INNER JOIN tb_user_video_log AS l
ON i.video_id = l.video_id

WHERE YEAR(l.start_time) = 2021
GROUP BY i.author, DATE_FORMAT(l.start_time, '%Y-%m')

第二步 2-2：基于2-1，计算每个作者、截至当前月的总涨粉量（“总涨粉量”就是题目所说的粉丝量）

这一步很简单了，如上述，需要结合聚合函数与开窗函数来解决，把2-1所创建的table命名为t，再从该表中选取字段来进行计算。直接上代码：

SELECT t.author, t.month, t.fans_growth_rate,
		/* 使用SUM()来计算累计即可，需要对author进行分组、月份进行降序，来计算每个作者在每个月中
        所累积的总涨粉量即可 */
       SUM(t.monthly_growth) OVER (PARTITION BY t.author ORDER BY t.month ASC) AS total_fans
FROM

    (SELECT i.author, 
           DATE_FORMAT(l.start_time, '%Y-%m') AS month, 
           ROUND(SUM(CASE l.if_follow WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 
           ELSE NULL END) / COUNT(author), 3) AS fans_growth_rate,
           /* 每月的总涨粉量，上面已经使用过了，只需要为其单独创建一个字段即可*/
          SUM(CASE l.if_follow WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 ELSE NULL END) AS monthly_growth

    FROM tb_video_info AS i INNER JOIN tb_user_video_log AS l
    ON i.video_id = l.video_id

    WHERE YEAR(l.start_time) = 2021
    GROUP BY i.author, DATE_FORMAT(l.start_time, '%Y-%m')) AS t
    
ORDER BY t.author ASC, total_fans ASC 

其实到第二步已经结束了，但是！其实本道题可以写的更加直接 ——

可以看到2-2中，计算每月的累计值，实际上用的是SUM(t.monthly_growth) OVER ...来解决的，而monthly_growth 实际上又是：

SUM(CASE l.if_follow WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 ELSE NULL END)

因此，我们能不能直接一次性查询，而不进行嵌套呢？——of course！请看如下代码：

SELECT i.author, 
       DATE_FORMAT(l.start_time, '%Y-%m') AS momth,
       ROUND(SUM(CASE l.if_follow 
           WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 
           ELSE NULL END) / COUNT(author), 3) AS fans_growth_rate,
           /* 可以看到这里有两层SUM()，最里层的SUM就是计算每月涨粉量，而最外层的SUM()，
           则是用于开窗函数的SUM()，其所运用的对象正是最里面的SUM() */
        SUM(SUM(CASE l.if_follow 
           WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN -1 
           ELSE NULL END)) OVER (PARTITION BY i.author ORDER BY DATE_FORMAT(l.start_time, '%Y-%m') ASC) AS total_fans
FROM tb_video_info AS i INNER JOIN tb_user_video_log AS l
ON i.video_id = l.video_id
WHERE YEAR(l.start_time) = 2021
GROUP BY i.author, DATE_FORMAT(l.start_time, '%Y-%m')
ORDER BY i.author ASC, total_fans ASC;