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

SELECT
    t2.video_id,
    CAST((100*t2.finish_rate + 5*t2.like_count + 3*t2.comment_count + 2*t2.retweet_count) * t2.fresh AS SIGNED) AS hot_index
FROM
    (
        SELECT
            t1.video_id,
            global_max_date.max_date,
            1 / (DATEDIFF(global_max_date.max_date,MAX(DATE(t1.end_time))) + 1) AS fresh,
            SUM(t1.if_finish) / COUNT(1) AS finish_rate,
            SUM(t1.if_like) AS like_count,
            COUNT(t1.comment_id) AS comment_count,
            SUM(t1.if_retweet) AS retweet_count
        FROM
            (
                SELECT
                    tblog.video_id,
                    tbinfo.release_time,
                    tblog.start_time,
                    tblog.end_time,
                    tblog.if_like,
                    tblog.if_retweet,
                    tblog.comment_id,
                    IF(TIMESTAMPDIFF(SECOND,tblog.start_time,tblog.end_time) >= tbinfo.duration, 1, 0) AS if_finish
                FROM
                    tb_user_video_log tblog
                JOIN tb_video_info tbinfo ON tblog.video_id = tbinfo.video_id
            ) t1
        CROSS JOIN (
            SELECT MAX(DATE(end_time)) AS max_date
            FROM tb_user_video_log
        )global_max_date
        WHERE DATE(t1.release_time) BETWEEN DATE_SUB(global_max_date.max_date,INTERVAL 29 DAY) AND global_max_date.max_date 
        GROUP BY video_id,global_max_date.max_date  -- 确保GROUP BY包含所有非聚合列
    ) t2
ORDER BY hot_index DESC
LIMIT 3;

无需开窗，使用最简单的思路