题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
-- 选择视频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个最高热度的视频