with cur_date as
(select max(date(end_time)) as cur from tb_user_video_log),

#近一个月发布的视频
video_release as
(select distinct ti.video_id
from  tb_video_info ti
join tb_user_video_log t on ti.video_id = t.video_id
where date(release_time) between date_sub((select cur from cur_date),interval 29 day) and (select cur from cur_date))
#主查询
select video_id,cast((wanbo*100+dianzan*5+pinglun*3+zhuanfa*2)*xinxian as signed) as hot_index 
from
(select ti.video_id,
count(if(timestampdiff(second,start_time,end_time)>=duration,1,null))/count(start_time) as wanbo,
sum(if_like) as dianzan,
sum(if_retweet) as zhuanfa,
count(comment_id) as pinglun,
1/(timestampdiff(day,max(date(end_time)),(select cur from cur_date))+1) as xinxian

from tb_user_video_log ti
join tb_video_info t on ti.video_id = t.video_id
where ti.video_id in (select video_id from video_release)
group by ti.video_id)b
order by hot_index desc
limit 3

三刷