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

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

这个题学到了三个点：

1⃣️date_format函数如果是%y则是两位数，如果是%Y就是四位数

2⃣️case when可以多次用，直接并列when即可

3⃣️有个疑问：为什么这里不用窗口函数计算粉丝数不行，想了一下应该是求和之后再对author求和