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

# 根据最近播放日期限制最近一个月的视频
# 计算热度，需要先计算视频完播率和新鲜度
# 完播率：完成播放次数/被播放次数  完成播放：实际时长大于等于理论时长  被播放：有记录
# 新鲜度：1/最近无播放天数+1  最近无播放天数：最近播放日期往前推最近的播放日期的间隔天数 
# 点赞数、转发数：SUM
# 评论数：COUNT

# 创建临时表，提取视频id，实际播放时长，理论播放时长，播放完日期，是否点赞，是否转发，评论id，全局最大的日期，每个视频的最大日期，视频发布日期
# 排序
WITH temp_0 AS(
    SELECT video_id, TIMESTAMPDIFF(SECOND, start_time, end_time) do_duration, duration, DATE_FORMAT(end_time, '%Y-%m-%d') end_date, if_like, if_retweet, comment_id, MAX(DATE_FORMAT(end_time, '%Y-%m-%d')) OVER() MAX_date, MAX(DATE_FORMAT(end_time, '%Y-%m-%d')) OVER(PARTITION BY video_id ORDER BY video_id) id_max_date, DATE_FORMAT(release_time, '%Y-%m-%d') release_date
    FROM tb_user_video_log
    JOIN tb_video_info USING(video_id)
    ORDER BY video_id, end_date
    ),
    temp_1 AS(
	# 提取视频id，计算完成观看的次数，有看过的记录，点赞数，转发数，评论数，每个视频的最近未播放天数
    SELECT video_id, SUM(IF(do_duration >= duration, 1, 0)) all_watch, COUNT(*) any_watch, SUM(if_like) like_cnt, SUM(if_retweet) re_cnt, SUM(IF(comment_id, 1, 0)) com_cnt, MAX(DATEDIFF(MAX_date, id_max_date)) day_cnt
    FROM temp_0
	# 筛选发布日期符合要求的，
    WHERE release_date BETWEEN DATE_SUB(MAX_date, INTERVAL 29 DAY) AND MAX_date
    GROUP BY video_id
    ORDER BY video_id
    )

# 主查询，根据公式写
SELECT video_id, 
    ROUND((100 * (all_watch/any_watch) + 5 * like_cnt + 3 * com_cnt + 2 * re_cnt) * (1/(day_cnt + 1)), 0) hot_index
FROM temp_1
ORDER BY hot_index DESC
# 别忘了限制3条
LIMIT 3

踩的坑：1.是发布日期的约束条件，之前写成了每个视频的最近播放日期

2.限制前三条，没注意

喜欢写临时表，一步步走，封装到里面