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

题目分析

首先热度拆分成几个小指标，其中前几个指标都是比较简单就可以得到，新鲜度这个指标是第一个难点，需要通过DATEDIFF函数获取当前日期和该tag下最近播放日期的差值。

第二个难点就是获取当前日期，这里直接可以把(SELECT max(end_time) FROM tb_user_video_log)做为一个整体做为当前日期然后代入到其他板块中。

SELECT video_id,
ROUND((100*t1.finish+5*t1.dz+3*t1.pl+2*zf)/(t2.day_new+1),0) hot_index
FROM
(SELECT video_id,COUNT(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,video_id,null))/ COUNT(video_id) AS finish,
COUNT(if(if_like=1,if_like,null)) AS dz,
COUNT(if(if_retweet=1,if_retweet,null)) AS zf,
COUNT(comment_id) AS pl,
release_time
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
GROUP BY video_id 
HAVING DATEDIFF((SELECT max(end_time) FROM tb_user_video_log),release_time)<=29) t1
JOIN 
(SELECT video_id, DATEDIFF((SELECT max(end_time) FROM tb_user_video_log),MAX(end_time)) AS day_new FROM tb_user_video_log
GROUP BY video_id) t2
USING(video_id)
ORDER BY hot_index DESC LIMIT 3