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

select author, date_format(start_time,"%Y-%m") month, 
round(sum(case when if_follow = 0 then 0
         when if_follow = 1 then 1
         else -1
         end) / count(author),3) fans_growth_rate,
sum(sum(case when if_follow = 0 then 0
         when if_follow = 1 then 1
         else -1
         end)) over (partition by author order by date_format(start_time,"%Y-%m")) total_fans
from tb_user_video_log log 
left join tb_video_info info on log.video_id=info.video_id
where year(start_time)=2021
group by author, month
order by author, total_fans  

注意点：

1. 时间的设置：data_format(time,"%Y-%m")表示（xxxx(年)-xx(月)）
2. 计算涨粉量的方法: 利用sum()+ case when解决：
1. sum(

case when if_follow = 1 then 1 % 当if_follow等于1时代表增加1个粉丝

when if_follow = 0 then 0 % 不涨粉也不脱粉

else -1 %脱粉

end）

3. 计算每个月的粉丝（本质上是逐月的累加算法）：
1. 处理累加我们是通过 sum() over (partition by order by) 来实现的
2. 在实际的实现中，我们利用 partition by author 来对累加算法的对象进行明确，即累加的对象是author
3. order by date_format(start_time,"%Y-%m") 来实现对每个月粉丝量的累加
4. 最后总体模块：
1. sum(

sum(case when if_follow = 0 then 0

when if_follow = 1 then 1

else -1 end))

over (partition by author order by date_format(start_time,"%Y-%m"))