题解 | #mysql5.7每个创作者每月的涨粉率及#

select a.author,a.month,fans_growth_rate,b.fans as total_fans from 
(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
from tb_user_video_log as a left join tb_video_info as b using(video_id)
where year(start_time)=2021
group by 1,2) as a
left join
(select author,date_format(a.month,'%Y-%m') as month,sum(fans) as fans from 
(select distinct date(date_format(start_time,'%Y-%m-01')) as month from tb_user_video_log
where year(start_time)=2021) as a
cross join
(select date(date_format(start_time,'%Y-%m-01')) as month, author,
sum(if(if_follow=2,-1,if_follow)) as fans
from tb_user_video_log as a left join tb_video_info as b using(video_id)
where year(start_time)=2021
group by 1,2) as b
where a.month>=b.month
group by author,a.month) as b
on a.author=b.author and a.month=b.month
order by a.author,b.fans;