当天时间的逻辑不是很好得出

select video_id,
round((100 * ratio + 5 * fun + 3 * comment_cnt + 2 * retweet) / (1 + lastest_day), 0)
from (
select t1.video_id, 
sum(case when timestampdiff(second, t1.start_time, t1.end_time) < t2.duration 
    then 0 else 1 end) / count(1)  ratio,
sum(t1.if_like) fun,
sum(t1.if_retweet)  retweet,
count(t1.comment_id)  comment_cnt,
min(datediff(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), date(t1.end_time))) lastest_day
from tb_user_video_log t1 
left join tb_video_info t2
on t1.video_id = t2.video_id
where  DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29
group by 1 
    ) t
ORDER BY 2 DESC LIMIT 3