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

解题思路：

1 先把关键列列出来：作者，年月份，涨粉，掉粉这些比较好统计的

关于涨粉，掉粉计算：配合group by + sum(if()) 或者cast when 直接计算

2 难点在于粉丝累计数量，这里是需要开分，sum() over(partition by 作者 order by 年月）进行第二步计算

答案如下：

select
c.author,
c.month,
cast( ((c.gz + c.qg) / c.guank)  as decimal(10,3) ) as rate,
sum(c.gz) over( partition by c.author order by c.month ) + sum(c.qg) over ( partition by c.author order by c.month ) as total
from
(select
b.author,
date_format(a.start_time,'%Y-%m') as month,
sum(if((a.if_follow = '1'),1,0)) as gz,
sum(if((a.if_follow = '2'),-1,0)) as qg,
count(1) as guank
from tb_user_video_log a 
left join tb_video_info b
on a.video_id=b.video_id
where year(a.start_time) = '2021'
group by b.author,date_format(a.start_time,'%Y-%m'))c
order by c.author, total