数据仓库-hive 窗口函数中的rows和range的区分

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