题解 | #国庆期间每类视频点赞量和转发量#

SELECT * FROM(
    SELECT tag, dt, 
    SUM(like_cnt) over (partition by tag order by dt rows 6 preceding) sum_like_cnt_7d,
    MAX(retweet_cnt) over (partition by tag order by dt rows 6 preceding) max_retweet_cnt_7d
    FROM (
        SELECT tag, DATE_FORMAT(start_time,'%Y-%m-%d') dt, 
        SUM(if_like) like_cnt, SUM(if_retweet) retweet_cnt
        FROM tb_user_video_log JOIN tb_video_info
        ON tb_user_video_log.video_id=tb_video_info.video_id
        GROUP BY tag, dt) t1
    ) t2
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC, dt

1. 思路是先算出每一天的总点赞量和总转发量，然后用窗口函数统计一周内的总点赞量和最大转发量。

2. 窗口函数的补充学习：<窗口函数> OVER (partition by ... order by... rows ...) 在rows后面，常用的参数有以下几个： （1）PRECEDING (之前) ：其将框架指定为“截止到之前N行” 如 rows 3 preceding 表示自身（当前记录）这一行 + 该记录之前的3行，共四行数据。 （2）FOLLOWING(之后)：其将框架指定为“截止到之后N行” 如 rows 3 following 表示自身（当前记录）这一行 + 该记录之后的3行，共四行数据。 （3）当然，可以同时使用preceding 和following，用以选取当前记录前后的行作为汇总对象： rows between 3 preceding and 3 following上面参数表示选取的数据范围是：当前记录前的三行 —— 当前记录后的三行，共7行数据。

资料来源：https://zhuanlan.zhihu.com/p/237292843

3. 容易错误的点：此题要加一层select * 在外面然后再加条件where dt BETWEEN '2021-10-01' AND '2021-10-03'，而不是在第二层加条件where dt BETWEEN '2021-10-01' AND '2021-10-03'。这样才能保证计数时不会遗漏10-01至10-03号的数据。