sql——滑动窗口

普通窗口函数：

sum( __ ) over(partition by __ oder by __ )

avg( __ ) over(partition by __ oder by __ )

count( __ ) over(partition by __ oder by __ )

rank() over(partition by __ oder by __ )

row_number() over(partition by __ oder by __ )

滑动窗口函数： (只计算某些行的聚合)

在两个点之间滑动计算，用以下语句指定起点和终点:

sum(__)   over(partition by __ order by __
               ROWS BWTWEEN <start> AND <finish> )

<strat>和<finish>关键字如下：

2 PRECEDING   #指定前2行
2 FOLLOWING   #指定后2行
UNBOUNDED PRECEDING  #前面所有行
YNBOUNDED FOLLOWING  #后面所有行
CURRENT ROW   #当前行

eg: SQL160 国庆期间每类视频点赞量和转发量

with t as(
    select 
    tag
    ,date_format(start_time,'%Y-%m-%d') daytime
    ,sum(if_like) like_cnt 
    ,sum(if_retweet) retweet_cnt
    from tb_user_video_log a 
    left join tb_video_info b
    on a.video_id = b.video_id
    where datediff('2021-10-03',date_format(start_time,'%Y-%m-%d')) < 9
    group by tag,date_format(start_time,'%Y-%m-%d')
)
select tag,daytime,like_cnt,retweet_cnt,
sum(like_cnt) over(partition by tag order by daytime rows 6 preceding),
max(retweet_cnt) over(partition by tag order by daytime rows 6 preceding),
sum(like_cnt) over(partition by tag order by daytime),
max(retweet_cnt) over(partition by tag order by daytime)
from t 

tag	day	like_cnt	retweet_cnt	sum_like	sum_retweet	sum_like6	sum_retweet6
旅游	2021-09-26	0	1	1	2	1	2
旅游	2021-09-27	1	0	2	2	2	2
旅游	2021-09-28	0	1	2	2	2	2
旅游	2021-09-29	0	1	2	2	2	2
旅游	2021-09-30	1	1	3	2	3	2
旅游	2021-10-01	2	1	5	2	5	2
旅游	2021-10-02	1	3	5	3	6	3
旅游	2021-10-03	1	0	6	3	7	3