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

SELECT DISTINCT video_id, 
ROUND((100*(watch/play_v)+5*like_num+3*comment_num+2*retweet_num)*(1/(DATEDIFF(maxdate,groupmaxdate)+1))) AS hot_index
FROM(
    SELECT *,
    MAX(simdate) OVER () AS maxdate,
    MAX(simdate) OVER (PARTITION BY video_id) AS groupmaxdate,
    SUM(IF(viewtime>=duration,1,0)) OVER (PARTITION BY video_id) AS watch,
    COUNT(video_id) OVER (PARTITION BY video_id) AS play_v
    FROM(
            SELECT ul.video_id, vi.duration,
            DATE_FORMAT(ul.end_time, '%Y-%m-%d') AS simdate,
            TIME_TO_SEC(TIMEDIFF(ul.end_time, ul.start_time)) AS viewtime,
            SUM(if_like) OVER (PARTITION BY video_id) AS like_num,
            SUM(if_retweet) OVER (PARTITION BY video_id) AS retweet_num,
            COUNT(comment_id) OVER (PARTITION BY video_id) AS comment_num
            FROM tb_user_video_log ul
            JOIN tb_video_info vi USING(video_id)
            WHERE DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log) , vi.release_time) <=29
            ) AS t
    ) AS t2
ORDER BY hot_index DESC
LIMIT 3

知识点速记：WHERE DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log) , vi.release_time) <=29

理解题目本身比写出代码更复杂