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

-- 选择视频ID和热度指数
SELECT
    video_id, -- 视频ID
    ROUND(
        (
            100 * comp_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt
        ) / hot_days, -- 计算热度指数
        0 -- 四舍五入到最接近的整数
    ) AS hot_index
FROM
    (
        SELECT
            a.video_id, -- 视频ID
            AVG(
                TIMESTAMPDIFF (SECOND, start_time, end_time) >= b.duration
            ) AS comp_rate, -- 计算视频完整观看率
            SUM(a.if_like) AS like_cnt, -- 喜欢数总和
            COUNT(a.comment_id) AS comment_cnt, -- 评论总数
            SUM(a.if_retweet) AS retweet_cnt, -- 转发数总和
            MAX(DATE (a.end_time)) AS end_date, -- 最后观看日期
            MAX(DATE (c.cur_date)), -- 当前日期的最大日期
            DATEDIFF (
                MAX(DATE (c.cur_date) + 1),
                MAX(DATE (a.end_time))
            ) AS hot_days -- 热度天数
        FROM
            tb_user_video_log a
            JOIN tb_video_info b ON a.video_id = b.video_id
            JOIN (
                SELECT
                    MAX(end_time) AS cur_date
                FROM
                    tb_user_video_log
            ) c ON 1 = 1
        WHERE
            DATEDIFF ( c.cur_date,b.release_time) <= 29 -- 视频发布时间与当前日期的差不超过29天
        GROUP BY
            a.video_id -- 按视频ID分组
    ) t
ORDER BY
    hot_index DESC -- 按热度指数降序排序
LIMIT
    3;

 -- 限制结果返回前3个最高热度的视频