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

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

https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84

select author, date_format(start_time,"%Y-%m") month, 
round(sum(case when if_follow = 0 then 0
         when if_follow = 1 then 1
         else -1
         end) / count(author),3) fans_growth_rate,
sum(sum(case when if_follow = 0 then 0
         when if_follow = 1 then 1
         else -1
         end)) over (partition by author order by date_format(start_time,"%Y-%m")) total_fans
from tb_user_video_log log 
left join tb_video_info info on log.video_id=info.video_id
where year(start_time)=2021
group by author, month
order by author, total_fans  



注意点:

  1. 时间的设置:data_format(time,"%Y-%m")表示(xxxx(年)-xx(月))
  2. 计算涨粉量的方法: 利用sum()+ case when解决:
  3. sum(
  4. case when if_follow = 1 then 1 % 当if_follow等于1时代表增加1个粉丝

    when if_follow = 0 then 0 % 不涨粉也不脱粉

    else -1 %脱粉

    end)

  5. 计算每个月的粉丝(本质上是逐月的累加算法):
  6. 处理累加我们是通过 sum() over (partition by order by) 来实现的
  7. 在实际的实现中,我们利用 partition by author 来对累加算法的对象进行明确,即累加的对象是author
  8. order by date_format(start_time,"%Y-%m") 来实现对每个月粉丝量的累加
  9. 最后总体模块:
  10. sum(

sum(case when if_follow = 0 then 0

when if_follow = 1 then 1

else -1 end))

over (partition by author order by date_format(start_time,"%Y-%m"))

全部评论

相关推荐

和光同尘cc:给我发就算了,还敢恶心上交✌🏻
点赞 评论 收藏
分享
点赞 收藏 评论
分享
牛客网
牛客企业服务