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

select author,

date_format(start_time,'%Y-%m') as month,

round(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)/(count(tb_user_video_log.video_id)),3) as fans_growth_rate,

sum(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)) over(partition by author order by date_format(start_time,'%Y-%m')) as total_fans

from tb_user_video_log join tb_video_info on tb_user_video_log.video_id=tb_video_info.video_id

where year(start_time)=2021

group by author, date_format(start_time,'%Y-%m')

order by author, total_fans asc

注释

1. 日期函数：date_format(date,format)
2. 条件判断函数：case when
3. 数学函数：round（x，y）
4. 窗口函数 sum()over()