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

with t as (
select author, tb_video_info.video_id, date_format(start_time,"%Y-%m") as month, case
when if_follow = 2 then -1
else if_follow
end as if_follow
from tb_video_info
left join tb_user_video_log
on tb_user_video_log.video_id = tb_video_info.video_id
where year(start_time)=2021
)

select author, month, 
# 1. 2021年里每个创作者每月的涨粉率
round(sum(if_follow)/count(if_follow),3) as fans_growth_rate,
# 2. 2021年里每个创作者截止当月的总粉丝量
sum(sum(if_follow)) over(partition by author order by month) as total_fans
from t
group by author, month
order by author, total_fans

每个创作者截止当月的总粉丝量：按时间累加每个创作者每月的总粉丝数

即 ( sum(每个创作者每月的总粉丝数 ) over(partition by 创作者 order by month))

每个创作者每月的总粉丝数 = sum(if_follow) group by author, month

每个创作者截止当月的总粉丝量 = sum(每个创作者每月的总粉丝数) over(partition by author order by month)

= sum(sum(if_follow)) over(partition by author order by month) group by author, month