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

https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84

```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解决：
3. sum(
4. case when if_follow = 1 then 1 % 当if_follow等于1时代表增加1个粉丝

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

else -1 %脱粉

end）

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