**最大值max窗口函数 分别以start_time和end_time为标准 竟然结果不同... 这个题告诫我要早睡😅 **

select video_id,round((sum(TIMESTAMPDIFF(second,start_time,end_time)>=duration)/count(1)*100+sum(if_like)*5+sum(comment_id is not null)*3+sum(if_retweet)*2)/(TIMESTAMPDIFF(day,max_part_time,max_time)+1)) as hot_index 
from 
(select t.video_id,start_time,end_time,if_follow,if_like,if_retweet,comment_id,duration,release_time,max(DATE(end_time))over() as max_time ,max(date(end_time))over(partition by video_id) as max_part_time
from tb_user_video_log t join tb_video_info t1 using(video_id)) t
where release_time between date_add(max_time,interval -29 day) and max_time
group by video_id
order by hot_index desc
limit 3