题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
http://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
- select
- author,
- date_format(start_time,'%Y-%m') month,
- round(sum(case when if_follow = 1 then 1
- when if_follow = 2 then -1
- else 0 end) /count(start_time), 3) 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')) total_fans
- from
- tb_user_video_log join tb_video_info using(video_id)
- where year(start_time) = 2021
- group by author, month
- order by author, total_fans
- 注意的几个点：
- 窗口函数中order by的内容要和date_format(start_time,'%Y-%m') month一样，
- 为了求得总的粉丝量，所以需要用窗口函数将粉丝数量作者和月份进行划分
- 可以在sum（）里面加case when直接进行计算，这样就不需要用子查询了
