题目主要信息

• 2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
• 结果按创作者ID、总粉丝数量升序

问题拆解

• 2021年(一定要记得是2021年的)
• 每个创作者每月涨粉率=(加粉量-掉粉量)/播放量
• 每个创作者截止当月的总粉丝数量

1. 把用户-视频互动表tb_user_video_log与短视频信息表tb_video_info连接起来，得到每个创作者视频被观看后的粉丝情况tb

SELECT 
	vi.author,
	DATE_FORMAT(uvl.start_time,'%Y-%m') MONTH,
	CASE WHEN if_follow=1 THEN 1
		WHEN if_follow=2 THEN -1
		ELSE 0
		END fans_cnt --利用case when 来表示粉丝情况，2相当于粉丝数-1
FROM tb_user_video_log uvl
LEFT JOIN tb_video_info vi
ON uvl.video_id=vi.video_id
WHERE YEAR(start_time)=2021 --2021年的情况

2. 根据表tb对author,month进行分组，得到每个创作者每个月的涨粉数量month_fans以及涨粉率fans_growth_rate。得到每个创作者每个月的涨粉率以及新增粉丝数量表 t

	  SELECT 
		tb.author,
		tb.month,
		ROUND(SUM(fans_cnt)/COUNT(*),3) fans_growth_rate, -- count(*)表示每个创作者每个月视频总播放量
		SUM(fans_cnt) month_fans -- 对每个创作者每个月粉丝数量进行求和
	  FROM tb
	  GROUP BY tb.author,tb.month
      ORDER BY tb.author,tb.month

3.根据表t，利用 SUM() OVER() 窗口函数得到每个作者截止当月的总粉丝数量total_fans

 SELECT 
 	t.author,
    t.month,
    t.fans_growth_rate,
    SUM(month_fans) over(PARTITION BY  author 
                         ORDER BY author,month) total_fans
  FROM t
	ORDER BY t.author,t.total_fans 

代码

  SELECT t.author,t.month,t.fans_growth_rate,
  SUM(month_fans) over(PARTITION BY  t.author ORDER BY t.author,t.month) total_fans
  FROM(
	  SELECT 
		tb.author,
		tb.month,
		ROUND(SUM(fans_cnt)/COUNT(*),3) fans_growth_rate,
		SUM(fans_cnt) month_fans
	  FROM(
		   SELECT 
			vi.author,
			DATE_FORMAT(uvl.start_time,'%Y-%m') MONTH,
			CASE WHEN if_follow=1 THEN 1
			WHEN if_follow=2 THEN -1
			ELSE 0
			END fans_cnt
		  FROM tb_user_video_log uvl
		  LEFT JOIN tb_video_info vi
		  ON uvl.video_id=vi.video_id
          WHERE YEAR(start_time)=2021
		  ) tb
	  GROUP BY tb.author,tb.month
          ORDER BY author,tb.month
      ) t
	ORDER BY t.author,total_fans