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

WITH
    iinfo AS (
        SELECT
            video_id,
            DATE_FORMAT (end_time, '%Y-%m-%d') AS dt,
            SUM(if_like) AS if_like,
            SUM(if_retweet) AS if_retweet
        FROM
            tb_user_video_log
        GROUP BY
            video_id,
            DATE_FORMAT (end_time, '%Y-%m-%d')
    )
select
    *
from
    (
        SELECT
            info.tag,
            uv.dt,
            SUM(uv.if_like) OVER (
                PARTITION BY
                    info.tag
                ORDER BY
                    uv.dt ROWS BETWEEN 6 PRECEDING
                    AND CURRENT ROW
            ) AS sum_like_cnt_7d,
            MAX(uv.if_retweet) OVER (
                PARTITION BY
                    info.tag
                ORDER BY
                    uv.dt ROWS BETWEEN 6 PRECEDING
                    AND CURRENT ROW
            ) AS max_retweet_cnt_7d
        FROM
            iinfo uv
            LEFT JOIN tb_video_info info ON uv.video_id = info.video_id
    ) b
WHERE
    dt BETWEEN '2021-10-01' AND '2021-10-03'
order by
    tag desc,
    dt

1. 解决滑动窗口问题 先需要求出每个日期的完整前七日信息 之后合并后再进行一次Filter Date
2. 求向上滑动的方法

#1. ROWS 滑动 ROWS BETWEEN and Current Row
SUM(uv.if_like) OVER (
                PARTITION BY info.tag
  				ORDER BY uv.dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 
            ) AS sum_like_cnt_7d,
			
#2. Range 滑动
Sum() over ( order by uv.dt range between interval '7 Day' AND CURRENT ROW )