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

SELECT 
    tag,
    dt,
    sum_like_cnt_7d,
    max_retweet_cnt_7d
FROM (
    SELECT 
        tag,
        DATE_FORMAT(start_time, '%Y-%m-%d') AS dt,
        SUM(SUM(if_like)) OVER (
            PARTITION BY tag 
            ORDER BY DATE_FORMAT(start_time, '%Y-%m-%d') 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS sum_like_cnt_7d,
        MAX(SUM(if_retweet)) OVER (
            PARTITION BY tag 
            ORDER BY DATE_FORMAT(start_time, '%Y-%m-%d') 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS max_retweet_cnt_7d
    FROM tb_user_video_log 
    JOIN tb_video_info USING(video_id)
    WHERE DATEDIFF('2021-10-03', DATE_FORMAT(start_time, '%Y-%m-%d')) < 9
    GROUP BY 
        DATE_FORMAT(start_time, '%Y-%m-%d'),
        tag
) AS t1
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC, dt;

本题要点

1.该题需要利用两次窗口函数：数据范围需要包括向前或向后所用到的记录范围

（1）7日总点赞量SUM

（2）7日内单日最大转发量MAX

2.在主查询中，再对当前时间进行限制。