题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
http://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
题目要求求：
热度=(a视频完播率+b点赞数+c评论数+d转发数)*新鲜度；
新鲜度=1/(最近无播放天数+1)； 当前配置的参数a,b,c,d分别为100、5、3、2。
视频完播率：
sum(if(TIMESTAMPDIFF(second,tu.start_time,tu.end_time)>=tv.duration,1,0))/count(tu.id)
点赞数：sum(tu.if_like)
评论数： count(tu.comment_id)
转发数： sum(tu.if_retweet)
新鲜度：
1/（datediff((Select max(end_time) from tb_user_video_log),max(tu.end_time))+1）
最近一个月发布的视频：
datediff((Select max(end_time) from tb_user_video_log),tv.release_time) <=29
完整代码：
SELECT video_id,round((100*a+5*b+3*c+2*d)/(e+1),0) hot_index
from (
SELECT tu.video_id,sum(if(TIMESTAMPDIFF(second,tu.start_time,tu.end_time)>=tv.duration,1,0))/count(tu.id) a,sum(tu.if_like) b,
count(tu.comment_id) c,sum(tu.if_retweet) d,datediff((Select max(end_time) from tb_user_video_log),max(tu.end_time)) e
from tb_user_video_log tu JOIN tb_video_info tv USING(video_id)
where datediff((Select max(end_time) from tb_user_video_log),tv.release_time) <=29
group by tu.video_id
)t
order by hot_index desc
limit 3
