select h.video_id,

round((100*h.wanbolv+5*h.dianzan_cnt+3*comment_cnt+2*retweet_cnt)/

(h.wubofangtianshu+1),0) as hot_index

from

(#计算需要的各种指标

select h1.video_id,sum(if(h2.miaoshu>=h1.duration,1,0))/count(h2.end_time) as wanbolv,sum(h2.if_like) as dianzan_cnt,

sum(if(h2.comment_id is not null,1,0)) as comment_cnt,

sum(h2.if_retweet) as retweet_cnt,

datediff('2021-10-03',max(h2.end_time)) as wubofangtianshu

from

(#找出近一个月发布的视频

select video_id,duration  

from tb_video_info

where datediff('2021-10-03',date_format(release_time,'%Y-%m-%d'))<30

) as h1

inner join

(#播放视频的各个指标

select video_id,date_format(end_time,'%Y-%m-%d') as end_time,

timestampdiff(second,start_time,end_time) as miaoshu,

if_like,if_retweet,comment_id

from tb_user_video_log

) as h2 on h1.video_id=h2.video_id

group by h1.video_id

) as h

group by h.video_id

order by round((100*h.wanbolv+5*h.dianzan_cnt+3*comment_cnt+2*retweet_cnt)/

(h.wubofangtianshu+1),0) desc