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

with temp as (
    select author,substr(end_time,1,7) as month_id,if_follow 
    from tb_user_video_log tuvl 
    left join tb_video_info tvi on tuvl.video_id = tvi.video_id
    where year(end_time) = 2021
) ,
temp1 as (
select author,month_id,
    cast(sum(if(if_follow=1,1,if(if_follow=0,0,-1)))/count(*) as decimal(10,3)) as fans_growth_rate,
    sum(if(if_follow=1,1,if(if_follow=0,0,-1))) as total_fans
from temp
group by author,month_id  )
select author,month_id as month,max(fans_growth_rate) as fans_growth_rate ,
    sum(total_fans) over(partition by author order by month_id) as total_fans
    from temp1
    group by author,month
    order by author,total_fans

解题思路：

1：首先明确计算的指标：第一个是每月涨粉率，第二个是累积粉丝量。

2：这没有每月的时间，所以需要先对时间进行修改，可以通过创建临时表，也可以join操作，然后通过修改的时间按月分组计算涨粉率:，也可把每月的粉丝量计算出来

3：累积粉丝量意味着需要计算之前月份的数据，这里想到累积相加的方法，采用sum(粉丝量) over(partition by 用户 order by 月份) 然后可以按照月份 一直进行相加。