SQL学习（3）：大厂真题实例-涨粉率和总粉丝量

SQL学习（3）：大厂真题实例-涨粉率和总粉丝量

题目来源：《牛客题霸：SQL大厂真题》： 01 某音短视频 SQL4 每个创作者每月的涨粉率和截至当前的总粉丝量

1.题目描述

用户-视频互动表tb_user_video_log：

uid-用户ID, 
video_id-视频ID, 
start_time-开始观看时间（精确至秒）, 
end_time-结束观看时间（精确至秒）, 
if_follow-是否关注, 
if_like-是否点赞, 
if_retweet-是否转发, 
comment_id-评论ID

短视频信息表tb_video_info

video_id-视频ID, 
author-创作者ID, 
tag-类别标签, 
duration-视频时长（秒）, 
release_time-发布时间

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

注：涨粉率=(加粉量 - 掉粉量) / 播放量，保留3位小数。结果按创作者ID、总粉丝量升序排序。if_follow-是否关注为1，表示用户观看视频中关注了视频创作者；为0，表示此次互动前后关注状态未发生变化；为2，表示本次观看过程中取消了关注。 输出结果包括author、month(YY-MM形式)、fans_growth_rate、total_fans。

2.问题分析

1.代码框架：

SELECT author, month, fans_grwth_rate, total_fans
FROM tb_user_video_log JOIN tb_video_info USING (video_id)
WHERE YEAR(start_time)='2021'
GROUP BY author, month
ORDER BY author ASC, total_fans ASC

2.拆解补充

• month：保留年-月，SUBSTRING(start_time, 1,7)
• 涨粉率：
  本质是if_follow的求平均，外套ROUND，因此公式外壳为ROUND(AVG(涨粉量),3)。
  每个月的涨粉量通过SUM IF进行，2时为-1，其他时候原值不变，表示为SUM(IF(if_follow=2,-1,if_follow))。
  总结为ROUND(AVG(SUM(IF(if_follow=2,-1,if_follow))),3)
  -总粉丝数：
  先计算每月涨粉量，上一点已经完成，为SUM(IF(if_follow=2,-1,if_follow))
  然后每个月的总粉丝数等于之前所有月份涨粉量累加，因此使用开窗函数SUM(涨粉量) OVER(PARTITION BY author ORDER BY month)
  总结为

  SUM(SUM(IF(if_follow=2,-1,if_follow))) 
  OVER(PARTITION BY author ORDER BY substring(start_time,1,7))
  

  需要注意，这里的ORDER BY需要和整体GROUP BY相同，并且不能够使用重命名month，否则报错。

3.完整代码

SELECT
  author,
  substring(start_time, 1,7) month,
  ROUND(AVG(IF(if_follow = 2, -1, if_follow)), 3) fans_growth_rate,
  SUM(SUM(IF(if_follow = 2, -1, if_follow))) OVER(
    PARTITION BY author
    ORDER BY
      substring(start_time,1,7)
  ) total_fans
FROM
  tb_user_video_log
  JOIN tb_video_info USING (video_id)
WHERE
  YEAR(start_time) = '2021'
GROUP BY author, month
ORDER BY author ASC,total_fans ASC

3.知识点总结

1. 时间变化函数和子查询

时间变化函数和子查询结合时，要求子查询只能返回1列
而如果输入参数为列名是可行的
例如:表table

id	time
1	2021-01-01
2	2021-01-03
3	2021-01-03

DATEDIFF((SELECT time FROM table WHERE id=1),time)返回-2，-2两行
而DATEDIFF((SELECT time FROM table WHERE id IN (2,3)),time)则会报错，因为子查询和time都返回了多行

2. 开窗函数

<窗口函数> OVER (PARTITION BY <列名> ORDER BY <列名>)

3.开窗函数和GROUP BY

4.SQL语句运行顺序