hive rows between和range between使用

create table tmp
(
   name string,
   class tinyint,
   cooperator_name string,
   score tinyint
)
row format delimited fields terminated by '|';

adf|3|测试公司1|45
asdf|3|测试公司2|55
cfe|2|测试公司2|74
3dd|3|测试公司5|n
fda|1|测试公司7|80
gds|2|测试公司9|92
ffd|1|测试公司10|95
dss|1|测试公司4|95
ddd|3|测试公司3|99
gf|3|测试公司9|99

rows between

rows between ... and ...控制窗口函数的范围
unbounded 无边界
preceding 往前
following 往后
unbounded preceding 往前所有行
2 preceding 往前2行
unbounded following 往后所有行
2 following 往后2行
current row 当前行

--从(无边界)第一行到当前行进行累加
select score,sum(score) over(order by score rows between unbounded preceding and current row) as s from tmp; 
--从前2行到当前行进行累加
select score,sum(score) over(order by score rows between 2 preceding and current row) as s from tmp; 
--前2行+当前行+后面所有行进行累加
select score,sum(score) over(order by score rows between 2 preceding and unbounded following) as s from tmp; 
--当前行到后一行进行累加
select score,sum(score) over(order by score rows between current row and 1 following) as s from tmp;
--第一行到当前行进行累加,如果当前行的值和后一行的值相同的话,当前行的累加结果取的是后一行的累加结果(和当前行相同score值的所有行都会包含进去)
select score,sum(score) over(order by score) as s from tmp;

range between

range between ... and ...按照列值限制窗口的大小

 --比如当前行score的值为200,窗口分区内score值满足200-100 到 200+200,(100-400)这个范围的所有值进行累加
sum(score) over(order by score range between 100 preceding and 200 following)

--score值满足当前行score值+1条件的所有行累加
select score,sum(score) over(order by score range between current row and 1 following) as s from tmp;