题解 | #近一个月发布的视频中热度最高的top3视频#

#需求：近一个月发布的视频中热度最高的top3视频
#输出：video_id、hot_index(热度)
#要求：时间范围限制在最大播放日期的近一个月内发布的视频；最近播放日期以end_time为准，向前推29天为一个月；热度保留整数，降序输出
#热度=(视频完播率*100+点赞数*5+评论数*3+转发数*2)*(1/(最近无播放天数+1))
#拆分问题：视频完播率：sum(if(timestampdiff(second,start_time,end_time)>duration,1,0))/count(*)+group by video_id
#点赞数：sum(if_like)
#评论数:count(comment_id) count计数会自动剔除null值
#转发数:sum(if_retweet)
#最近无播放天数:比对两个日期之差，一个是所有记录的最近播放时间，一个是视频的最近播放时间，两个时间的差就是该video_id的最近无播放天数；datediff(date(select max(end_time) from tb_user_video_log),max(date(end_time)))+group by video_id
#近一个月发布的视频:与查最近无播放天数逻辑相同，datediff(date(select max(end_time) from tb_user_video_log),date(release_time))<=29
#热度最高的top3:order by hot_index desc limit 3
select video_id,round((tt1*100+tt2*5+tt3*3+tt4*2)*(1/(tt5+1)),0) hot_index
from(
  select video_id,sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(*) tt1,
  sum(if_like) tt2,count(comment_id) tt3,sum(if_retweet) tt4,
  datediff(date((select max(end_time) from tb_user_video_log)),date(max(end_time))) tt5
  from tb_user_video_log join tb_video_info using(video_id)
  where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<=29
  group by 1
) t1
order by 2 desc
limit 3