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

WITH q0 as (
SELECT 
author,
date_format(start_time,'%Y-%m') month,
avg(case WHEN if_follow=2 THEN -1 
ELSE if_follow END) fans_growth_rate,
sum(case WHEN if_follow=2 THEN -1 
ELSE if_follow END) cur_fans
FROM tb_user_video_log  l
LEFT join tb_video_info t ON l.video_id	= t.video_id	
WHERE YEAR(start_time)='2021'

GROUP BY author,date_format(start_time,'%Y-%m')
)
SELECT
    author,
    month,
    round(fans_growth_rate,3),
    sum(cur_fans) OVER(PARTITION BY author ORDER BY month) total_fans
FROM q0
ORDER BY 1,4
-- 按创作者ID、总粉丝量升序排序。

求创作者涨粉量和每月粉丝量

结果只与作者相关与视频信息无关。

1. 链接表，where限制时间为2021，group by时间
2. sum case when 求得每月粉丝量加减
3. 开窗函数sum求得当月和当月前的粉丝量合