Rows指的是行的范围,相当于是物理上的行数限制
Range指的是行可以根据行逻辑上的限制,对窗口内的内容限制
比如近几天,近几个等等
sum(id) over(order by id range between 1 preceding and 2 following)
窗口大小设置为该分区内小于本记录id-1和id+2天的窗口
order by id,所以要看id的值,并对key(id进行range操作),即[id-1, id+2]。注意是闭区间
相同key的窗口函数的值是相同的,尽管不在同一行,已经不在考虑行,只考虑key的值
看一些示例:
select
user_id
,buy_date
,amount
,datediff(buy_date,'2021-01-01') as key
,count(1) over(PARTITION by user_id order by datediff(buy_date,'2021-01-01') RANGE between 6 PRECEDING and CURRENT row) as cnt
,sum(amount) over(PARTITION by user_id order by datediff(buy_date,'2021-01-01') RANGE between 6 PRECEDING and CURRENT row) as amount
from (
select '101' as user_id, '2021-01