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

select video_id,round((100*playrate+5*likenum+3*commentnum+2*retweetnum)/(reno+1),0) as hot_index
from(
SELECT video_id,count(if(dur>=duration,end_time,null))/count(end_time) as playrate,
count(if(if_like=1,if_like,null))as likenum,count(comment_id)as commentnum,
count(if(if_retweet=1,if_retweet,null))as retweetnum,
DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), MAX(DATE(end_time))) as reno
from(select* 
from(SELECT a.video_id,a.end_time,TIMESTAMPDIFF(SECOND,
start_time,end_time)as dur,if_like,if_retweet,comment_id,duration,release_time
from tb_user_video_log a left join tb_video_info b on a.video_id=b.video_id)c
where DATEDIFF(date((SELECT max(end_time) from tb_user_video_log)),date(release_time))<=29
)d
group by video_id)e
order by hot_index desc
limit 3