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

1. 计算2021年里每个创作者每月的<涨粉率>及<截止当月的总粉丝量>
2. 涨粉率=(加粉量 - 掉粉量) / 播放量
3. 结果按创作者ID、总粉丝量升序排序

• <加粉量><掉粉量>取决于if_follow的状态，如果if_follow为1，则增粉，如果if_follow为2则掉粉，如果if_follow为0则状态不变。多情况条件选择时，首选case when语句。首先将各状态转换成粉丝数量的增减

case 
when if_follow=1 then 1
when if_follow=2 then -1
else 0 end

• 对于一个创作者而言，计算净粉丝量，按照author分组，用sum()即可

sum(case 
when if_follow=1 then 1
when if_follow=2 then -1
else 0 end) net_cnt

... ...

group by author

• 每个创作者的播放量，按照author分组，使用count计算

select i.author,DATE_FORMAT(u.start_time,'%Y-%m') month,
sum(case 
when if_follow=1 then 1
when if_follow=2 then -1
else 0 end) net_cnt,-- 净粉丝量
count(i.author) play_cnt -- 总播放量
from tb_video_info i
left join tb_user_video_log u
on i.video_id=u.video_id
where year(u.start_time)=2021 -- 筛选出2021年数据
group by i.author,DATE_FORMAT(u.start_time,'%Y-%m') -- 每个作者 每月

• 提及累计，最容易想到的就是窗口函数。sum(net_cnt) over (partition by author order by month)
• 涨粉率，很容易计算。保留3位小数，使用round(float,3)

select t.author,t.month,
round(t.net_cnt/t.play_cnt,3) fans_growth_rate,
sum(net_cnt) over (PARTITION by t.author order by t.month) total_fans
FROM
(select i.author,DATE_FORMAT(u.start_time,'%Y-%m') month,
sum(case 
when if_follow=1 then 1
when if_follow=2 then -1
else 0 end) net_cnt,
count(i.author) play_cnt
from tb_video_info i
left join tb_user_video_log u
on i.video_id=u.video_id
where year(u.start_time)=2021
group by i.author,DATE_FORMAT(u.start_time,'%Y-%m'))t
order by t.author,total_fans;