题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
select author ,month ,fans_growth_rate ,sum(inc_fo) over(partition by author order by month) total_fans from ( select author ,left(start_time,7) month ,round(sum(inc_fo) / count(1),3) fans_growth_rate ,round(sum(inc_fo),3) inc_fo from ( select log.video_id video_id ,start_time ,author ,case when if_follow = 1 then 1 when if_follow = 0 then 0 when if_follow = 2 then -1 else -100000 end inc_fo from tb_user_video_log log left join tb_video_info info on log.video_id = info.video_id ) a group by 1,2 ) b where left(month,4) = '2021' order by 1,4
我们的目的是分析2021年各位作者的粉丝增长情况。通过计算每个月的粉丝增长率以及累计粉丝数,我们能够清楚地看到每位作者在2021年各月的粉丝动态变化。
首先,代码的核心在于从数据库中获取与作者、视频相关的数据,并通过一系列操作最终得到作者的月度粉丝增长率和累计粉丝数。这些数据主要来自两个表:tb_user_video_log
和 tb_video_info
。
代码的主要逻辑:
- 数据提取和处理:首先,从 tb_user_video_log 表中提取 video_id、start_time 和 author。通过 CASE 语句,将 if_follow 字段的值转换为增减粉丝数的指标 inc_fo,其中:if_follow = 1 表示粉丝增加,因此 inc_fo 为 1。if_follow = 0 表示没有变化,inc_fo 为 0。if_follow = 2 表示粉丝减少,inc_fo 为 -1。其他异常情况设定为一个极小值,用于标记无效数据。
- 数据聚合:将上述结果按作者和月份分组,计算出每个月的粉丝增长总数 inc_fo,并进一步计算平均粉丝增长率 fans_growth_rate,保留三位小数。
- 窗口函数:关键部分是使用了一个窗口函数 sum(inc_fo) over(partition by author order by month),它累积计算每位作者在每个月的总粉丝数,并按月份顺序累加。这部分是代码的核心,帮助我们跟踪作者的粉丝数随着时间的变化。
- 数据过滤和排序:最后,只保留2021年的数据,并按作者和累计粉丝数进行排序,方便我们观察各个作者的表现。
总体来说,这段代码通过窗口函数和聚合操作,详细计算了2021年各位作者的粉丝增长情况,提供了一个清晰的粉丝增长趋势。
#sql##mysql##sql练习日常#