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

select author
        ,date_format(start_time,'%Y-%m') AS month
        ,round((sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0)))/count(start_time),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*/
        ,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')) AS total_fans
from tb_video_info ti
inner join tb_user_video_log tl on ti.video_id = tl.video_id
where year(start_time) = 2021
group by author
        ,date_format(start_time,'%Y-%m')
order by author
        ,total_fans

每个作者当前月累计的粉丝数：开窗函数sum(每月粉丝数) over(partition by author order by month)。以作者分组，按照月份排序，对每月粉丝数量进行求和sum（）。相当于每月粉丝数sum(if(if_follow=1,1,0))-sum(if(if_follow=2,1,0))是group by author,month随之产生的字段，在group by这个基础上用sum( )over( )对这个字段进行开窗