SELECT video_id,ROUND((100*yes_watch_rate+5*like_cnt+3*com_cnt+2*ret_cnt)/(1+TIMESTAMPDIFF(DAY,recently_end_date,cur_date)),0) AS hot_index 
FROM (SELECT video_id,
AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)-duration>=0,1,0)) AS yes_watch_rate,
SUM(if_like) AS like_cnt,
SUM(if_retweet) AS ret_cnt,
SUM(IF(comment_id IS NOT NULL,1,0)) AS com_cnt,
MAX(DATE(end_time)) AS recently_end_date,
MAX(DATE(release_time)) AS release_date,
MAX(cur_date) AS cur_date
FROM tb_user_video_log JOIN tb_video_info USING(video_id) LEFT JOIN (SELECT MAX(DATE(end_time)) AS cur_date FROM tb_user_video_log) AS t ON 1
GROUP BY video_id
HAVING TIMESTAMPDIFF(DAY,release_date,cur_date)<30
) AS t1
ORDER BY hot_index DESC
LIMIT 3;

本题收获：

1.SELECT中要不是聚合键，要不是聚合函数，如果实在不能满足，那么可以考虑将常量写成聚合函数的形式

2.ON 1表示无论如何都连接的意思，不用管联结键

3.TIMESTAMPDIFF计算时间戳之差更加全面

4.必要时可用三表联结

5.第9和第10行代码相呼应，因为需要用到cur_time参与运算，所以在FROM中联结上