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

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) sum_retweet_cnt_7d
FROM (SELECT tag,DATE_FORMAT(start_time,'%Y-%m-%d') as dt,sum(if_like) like_cnt ,SUM(if_retweet) retweet_cnt FROM tb_user_video_log
JOIN tb_video_info using(video_id)
WHERE DATE_FORMAT(start_time,'%Y-%m-%d') BETWEEN '2021-09-25' and '2021-10-03'
GROUP BY dt,tag
) as t 
)as t1
WHERE dt BETWEEN '2021-10-01' and '2021-10-03'
ORDER BY tag desc,dt

SUM(like_cnt)over(PARTITION by tag ORDER BY dt rows 6 preceding ) //获取当前行+前6行的和

MAX(retweet_cnt)over(PARTITION by tag ORDER BY dt rows 6 preceding) // 获取当前行+前6行的最大值

preceding：向前    following：向后，这两个窗口函数可以实现滑窗求和（指定rows范围）或者指定范围内数据求和（指定range范围）

sum(score) over(order by score range between 2 preceding and 2 following) s1, -- 当前行的score值加减2的范围内的所有行

sum(score) over(order by score rows between 2 preceding and 2 following) s2, -- 当前行+前后2行，一共5行

sum(score) over(order by score range between unbounded preceding and unbounded following) s3, -- 全部行，不做限制

sum(score) over(order by score rows between unbounded preceding and unbounded following) s4, -- 全部行，不做限制

sum(score) over(order by score) s5, -- 第一行到当前行（和当前行相同score值的所有行都会包含进去）

sum(score) over(order by score rows between unbounded preceding and current row) s6, -- 第一行到当前行（和当前行相同score值的其他行不会包含进去，这是和上面的区别）

sum(score) over(order by score rows between 3 preceding and current row) s7, -- 当前行+往前3行

sum(score) over(order by score rows between 3 preceding and 1 following) s8, --当前行+往前3行+往后1行

sum(score) over(order by score rows between current row and unbounded following) s9 --当前行+往后所有行