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

SELECT * 
FROM(
SELECT tv.tag, DATE(start_time) AS dt, 
SUM(SUM(if_like)) OVER(PARTITION BY tag ORDER BY DATE(start_time) ROWS 6 PRECEDING) AS sum_like_cnt_7d, # 每7行为一个窗口框架进行数据累加，否则，默认为一行一行向下累加
MAX(SUM(if_retweet)) OVER(PARTITION BY tag ORDER BY DATE(start_time) ROWS 6 PRECEDING) AS 
max_retweet_cnt_7d # 每7行为一个窗口框架进行数据最大值查找，否则，默认为一行一行查找最大值
FROM tb_user_video_log AS tu
LEFT OUTER JOIN tb_video_info AS tv
ON tu.video_id = tv.video_id
WHERE DATE(start_time) <= '2021-10-3' AND DATE(start_time) >= '2021-09-25'
GROUP BY tv.tag, dt) AS t

WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC, dt;

学到了聚合窗口函数不仅单行运算，还可以指定滑动行运算