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

WITH t1 AS (
    SELECT
    DATE(MAX(end_time)) AS max_date,
    DATE_ADD(DATE(MAX(end_time)), INTERVAL -29 DAY) AS min_date
    FROM tb_user_video_log
),
t2 AS (
    SELECT tu.video_id,
           tu.start_time,
           tu.end_time,
           tu.if_like,
           tu.comment_id,
           tu.if_retweet,
           if(TIMESTAMPDIFF(SECOND, start_time, end_time) / tv.duration >= 1, 1, 0 ) AS ratio,
           max_date
    FROM tb_user_video_log tu
        JOIN t1
    LEFT JOIN tb_video_info tv
    ON tu.video_id = tv.video_id
    WHERE DATE(end_time) BETWEEN min_date AND max_date AND
          DATE (release_time) BETWEEN min_date AND max_date
),
t3 AS (
    SELECT
    video_id,
    SUM(ratio) / count(video_id) AS wanbo_ratio,
    SUM(if_like) AS sum_like,
    SUM(IF(comment_id IS NULL,0,1)) AS sum_comment,
    SUM(if_retweet) AS sum_retweet,
    TIMESTAMPDIFF (DAY ,MAX(DATE (end_time)), MAX(max_date)) no_wanbo
    FROM t2
    GROUP BY video_id
),
t4 AS (
    SELECT
    video_id,
    100 * wanbo_ratio + 5 * sum_like + 3 * sum_comment + 2 * sum_retweet AS hot_left,
    1 / (no_wanbo + 1) AS hot_right
    FROM t3
)
SELECT t4.video_id,
       ROUND(hot_left * hot_right,0) AS hot_index
       FROM t4
        LEFT JOIN tb_video_info tv
ON tv.video_id = t4.video_id
ORDER BY hot_index DESC
LIMIT 3;

# 这道题关键在于近一个月发布的视频，因此发布时间要进行过滤的！