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

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