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

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

http://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84

【理解题意】

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

特别提示：每月按照start_time来划分

【解题思路】

1、表关联，短视频信息表left join视频互动信息表（因为以创作者author为核心进行计算，要保证每个author都出现在表里）

2、筛选出2021年的数据。where year(start_time)='2021'

3、本题难点：计算涨粉率

3.1 计算净粉丝量 = 加粉量 - 掉粉量

<加粉量><掉粉量>取决于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

3.2 计算总播放量

每个创作者的播放量，按照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') -- 每个作者 每月

4、计算累计粉丝量，及涨粉率，保留3位小数

提及累计，最容易想到的就是窗口函数。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;