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

1.问题： 计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 涨粉率=(加粉量-掉粉量)/播放量。结果按创作者ID、总粉丝量升序排序。

2.思路：

(1)计算涨粉率，涨粉率=(加粉量-掉粉量)/播放量

加粉量：sum(if(if_follow=1,1,0))

掉粉量：sum(if(if_follow=2,1,0))

播放量：count(author) group BY month(start_time),author

当月总粉丝量：sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)) over(partition by author order by date_format(start_time,'%Y-%m'))

(2)2021年、每个创作者每月、按创作者ID、总粉丝量升序排序

2021年：where year(xx）=2021

每个创作者每月：group by

按创作者ID、总粉丝量升序排序：order by

3.解题代码：

SELECT b.author,date_format(start_time,'%Y-%m') month,
    round((sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)))/count(author),3) fans_growth_rate,
    sum(sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0))) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
FROM tb_user_video_log a
    left join tb_video_info b
    on a.video_id=b.video_id
where year(start_time)=2021
GROUP BY author,date_format(start_time,'%Y-%m')
order BY author,total_fans