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

WITH t1 AS(
SELECT tag, dt
        ,SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum_like_cnt_7d
        ,MAX(retweet_cnt) OVER(PARTITION BY tag ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS max_retweet_cnt_7d
FROM(
    SELECT tag
        ,DATE(start_time) AS dt
        ,SUM(if_like) AS like_cnt
        ,SUM(if_retweet) AS retweet_cnt
    FROM tb_video_info
    JOIN tb_user_video_log USING(video_id)
    WHERE start_time BETWEEN "2021-09-25" AND "2021-10-04"
    GROUP BY tag, dt
) data1
)

SELECT *
FROM t1
WHERE dt BETWEEN "2021-10-01" AND "2021-10-03"
ORDER BY tag DESC, dt

• start_time BETWEEN "2021-09-25" AND "2021-10-04",使用该函数时必须设置为"2021-10-04"，不能是"2021-10-03"，因为涉及到日期时间与日期判断包含关系时，日期自动被补充为：日期 00：00，因此设置为"2021-10-04"，筛选4号零点及前的数据