近一个月发布的视频中热度最高的top3视频

明确题意：

请找出近一个月发布的视频中热度最高的top3视频。

假设热度计算公式简化为：热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；新鲜度=1/(最近无播放天数+1)； 结果中热度保留为整数，并按热度降序排序。

问题分解：

• 计算每个视频的各项指标：

  • 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)；
  • 在每一行上追加当前日期列：

      LEFT JOIN (
          SELECT MAX(DATE(end_time)) as cur_date FROM tb_user_video_log
      ) as t_max_date ON 1
  

  • 按视频id分组：GROUP BY video_id
  • 计算各指标：
    • 播放进度：AVG(IF(TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0)) as comp_play_rate
    • 点赞量：SUM(if_like) as like_cnt
    • 评论量：COUNT(comment_id) as comment_cnt
    • 转发量：SUM(if_retweet) as retweet_cnt
    • 最近被播放日期：MAX(DATE(end_time)) as recently_end_date
    • 发布日期：MAX(DATE(release_time)) as release_date
    • 当前日期（非分组列，加MAX避免语法错误）：MAX(cur_date) as cur_date
  • 分组后筛选，筛选近30天的记录：HAVING TIMESTAMPDIFF(DAY, release_date, cur_date) < 30

• 计算每个视频的热度：(100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt) / (TIMESTAMPDIFF(DAY, recently_end_date, cur_date) + 1) as hot_index

• 保留为整数：ROUND(x, 0)

• 取热度top3高的视频：ORDER BY hot_index DESC LIMIT 3

细节问题：

• 表头重命名：as
• 按热度倒序排序：ORDER BY hot_index

完整代码：

SELECT video_id,
    ROUND((100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt)
        / (TIMESTAMPDIFF(DAY, recently_end_date, cur_date) + 1), 0) as hot_index
FROM (
    SELECT video_id,
        AVG(IF(
            TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0
        )) as comp_play_rate,
        SUM(if_like) as like_cnt,
        COUNT(comment_id) as comment_cnt,
        SUM(if_retweet) as retweet_cnt,
        MAX(DATE(end_time)) as recently_end_date,  -- 最近被播放日期
        MAX(DATE(release_time)) as release_date,  -- 发布日期
        MAX(cur_date) as cur_date  -- 非分组列，加MAX避免语法错误
    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_max_date ON 1
    GROUP BY video_id
    HAVING TIMESTAMPDIFF(DAY, release_date, cur_date) < 30
) as t_video_info
ORDER BY hot_index DESC
LIMIT 3;