题解 | 每类视频近一个月的转发量/率 —— W1uSeven7 -- NO.01

对我来说，这道题两个难点：

1. 如何找到最新日期
2. 如何表示近三十天

法一（自己写的，用到了窗口函数，写复杂了）

SELECT 
    tbi.tag,
    SUM(tuv.if_retweet) AS retweet_cnt,
    ROUND(AVG(tuv.if_retweet), 3) AS retweet_rate
FROM (
    -- 最近30天内的视频
    SELECT video_id, if_retweet
    FROM (
        SELECT 
            video_id,
            if_retweet,
            end_time
        FROM tb_user_video_log
    ) AS t1
    WHERE DATE_ADD(end_time, INTERVAL 30 DAY) > (
        -- 获取最新视频的 end_time
        SELECT end_time
        FROM (
            SELECT 
                ROW_NUMBER() OVER(ORDER BY end_time DESC) AS ranking,
                end_time
            FROM tb_user_video_log
        ) AS t2
        WHERE ranking = 1
    )
) AS tuv
INNER JOIN tb_video_info AS tbi
    ON tuv.video_id = tbi.video_id
GROUP BY tbi.tag
ORDER BY retweet_rate DESC;

法二（经过GPT老师的优化）

SELECT 
    tbi.tag,
    SUM(tuv.if_retweet) AS retweet_cnt,
    ROUND(AVG(tuv.if_retweet), 3) AS retweet_rate
FROM (
    SELECT video_id, if_retweet
    FROM (
        SELECT 
            video_id,
            if_retweet,
            end_time
        FROM tb_user_video_log
    ) t
    -- 更简洁，少了一层子循环，直呼妙啊妙啊
    WHERE DATE_ADD(end_time, INTERVAL 30 DAY) > (SELECT end_time FROM tb_user_video_log ORDER BY end_time DESC LIMIT 1)
) tuv
INNER JOIN tb_video_info AS tbi
    ON tuv.video_id = tbi.video_id
GROUP BY tbi.tag
ORDER BY retweet_rate DESC;

法三（来自题解中最高赞的那位大佬，这里也引用一下，直呼天才）

SELECT b.tag, SUM(if_retweet) retweet_cnt, ROUND(SUM(if_retweet)/COUNT(*), 3) retweet_rate
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
ON a.video_id = b.video_id
WHERE DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(a.start_time)) <= 29
GROUP BY b.tag
ORDER BY retweet_rate desc

看了一些大佬的优质题解，针对我遇到的难点，再小结一下：

• 如何找最新日期：

1. select max(end_time) FROM tb_user_video_log（最快的,返回的结果就是最新日期）
2. 按日期降序排序后的子查询（只输出一行，也比较巧妙）
3. 窗口函数（要两层子循环，较为复杂）

• 如何表示近三十天：

1. DATE_ADD(end_time,INTERVAL 29 DAY) >= 最新日期
2. DATEDIFF(最新日期，end_time) <= 29

这是我在牛客写的第一篇题解，附上今天看到的一句话，很喜欢：

将军着金甲，银枪起落沾黄花，知秋天下

SELECT FROM SP ZHAO YUN