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

明确题意：

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

问题分解：

关联用户-视频互动表和短视频信息表：join tb_user_video_log tuvl on tvi.video_id = tuvl.video_id

筛选2021年的记录：where year(start_time) = 2021

按创作者和月份分组：group by author,month

计算涨粉率：sum(case if_follow when 1 then 1 when 2 then -1 else 0 end) / count(*)

计算截至当月的总粉丝量：sum(sum(case if_follow when 1 then 1 when 2 then -1 else 0 end)) over(partition by author order by date_format(start_time,'%Y-%m'))

细节问题：

按创作者ID、总粉丝量升序排序：order by author,total_fans

保留三位小数：round(x,3)

select author,
    date_format(start_time,'%Y-%m') month,
    round(sum(case if_follow
        when 1 then 1
        when 2 then -1
        else 0
        end) / count(*), 3) fans_growth_rate,
    sum(sum(case if_follow
        when 1 then 1
        when 2 then -1
        else 0
        end)) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans # 累加粉丝数需要开窗
from tb_video_info tvi
join tb_user_video_log tuvl on tvi.video_id = tuvl.video_id
where year(start_time) = 2021
group by author,month
order by author,total_fans;