题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
https://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
SELECT a.video_id,
ROUND(
(100 * SUM(IF(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) >= duration, 1, 0)) / COUNT(*)
+ 5 * SUM(if_like)
+ 3 * COUNT(comment_id)
+ 2 * SUM(if_retweet))
/ (DATEDIFF(max_date, DATE_FORMAT(MAX(end_time), '%Y-%m-%d')) + 1)
, 0) AS hot_index
FROM tb_user_video_log a
JOIN (
SELECT video_id,
duration,
max_date
FROM (
SELECT video_id,
duration,
DATE_FORMAT(release_time, '%Y-%m-%d') AS release_time,
(SELECT DATE_FORMAT(MAX(end_time), '%Y-%m-%d') FROM tb_user_video_log) AS max_date
FROM tb_video_info
) t
WHERE release_time >= DATE_SUB(max_date, INTERVAL 29 DAY)
) b
ON a.video_id=b.video_id
GROUP BY a.video_id
ORDER BY hot_index DESC
LIMIT 3
题目没有交代清楚的计算口径 :
最近播放日期以end_time-结束观看时间为准 : 这句话的意思是以事实表 tb_user_video_log 的最大播放时间最为统计的截至日期
最近无播放天数: 这句话的意思是用上面的 统计截至日期 减去 该视频最后播放的日期 就是 最近无播放天数
本题就是主要就是考察MySQL时间函数的用法:
1. DATE_FORMAT -- 时间格式化
2. DATE_SUB( yyyy-MM-dd, INTERVAL 29 DAY) -- 减去多少天
3. TIMEDIFF -- 两个时间相差多少
4. TIME_TO_SEC -- 时间转换成秒
