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

SELECT video_id
        ,ROUND((100*rate + 5*like_num + 3*comment_num + 2*retweet_num)*fresh_rate, 0) AS hot_index
FROM(
    SELECT video_id
            ,AVG(IF(TIMESTAMPDIFF(second, start_time, end_time) >= duration, 1, 0)) AS rate
            ,SUM(if_like) AS like_num
            ,COUNT(comment_id) AS comment_num
            ,SUM(if_retweet) AS retweet_num
            ,1/(DATEDIFF(DATE((SELECT MAX(end_time)
                            FROM tb_user_video_log))
                        ,MAX(DATE(end_time)))+1) AS fresh_rate
    FROM tb_video_info
    JOIN tb_user_video_log USING(video_id)
    WHERE DATEDIFF(DATE((SELECT MAX(end_time)
                        FROM tb_user_video_log))
                    ,DATE(release_time)) <= 29
    GROUP BY video_id
) data1
ORDER BY hot_index DESC
LIMIT 3

• 借鉴：2022-05-08 21:16 和2022-05-09 21:15使用timestampdiff的话就是0，相差不到一天，用datediff话是1，后者只返回二者相差的天数。
• 注意TIME总会去考虑日期中的时间，因此需要考虑时间时应该使用TIMESTAMPDIFF，不需要考虑时间计算日期差值时，则采用DATEDIFF