select v.video_id,
round((100*avg(if(timestampdiff(second,u.start_time,u.end_time)<v.duration,0,1))+5*sum(u.if_like)+3*count(u.comment_id)+2*sum(u.if_retweet))/(1+datediff(max(max(u.end_time)) over(), max(u.end_time))),0) hot_index
from tb_user_video_log u
left join
tb_video_info v
on u.video_id=v.video_id
where datediff((select max(end_time) from tb_user_video_log),v.release_time)<=29
group by v.video_id
order by hot_index desc
limit 0,3
# select datediff('2021-09-05','2021-09-06') 返回-1

  • 是否大多数
  • 使用两层max分配全局最大值
  • 新鲜度的计算着重复习
  • timestampdiff是顺序是start_time, end_time
  • datediff的顺序是end_time,start_time