Hive rows between ,range between 的理解跟案例

unbounded preceding 起始行
unbounded following 结尾行
current row 当前行
1 preceding 当前行的前1行
1 following 当前行的后1行

首先这是数据格式

1.当前行到最后一行的汇总（逐行递减）

with tt as(

select a.*,sum(mainid)over(rows between current row and unbounded following) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

2.第一行跟当前行的汇总（累加）

with tt as(

select a.*,sum(mainid)over(rows between unbounded preceding and current row ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

3.首行跟最后一行的汇总（sum()）

with tt as(

select a.*,sum(mainid)over(rows between unbounded preceding and unbounded following ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

4.第一行跟当前行的后两行的和

with tt as(

select a.*,sum(mainid)over(rows between unbounded preceding and 2 following ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

5. 当前行前两行到最后一行的和

with tt as(

select a.*,sum(mainid)over(rows between 2 preceding and unbounded following ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

6.第一行到当前行后一行的和

with tt as(

select a.*,sum(mainid)over(rows between unbounded preceding and 1 following ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

7. 当前行前一行到最后一行的和

with tt as(

select a.*,sum(mainid)over(rows between 1 preceding and unbounded following ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

Rnage  between

1.mainid 数值在   当前行的 mainid-1 ， mainid+3 范围内的和

with tt as(

select a.*,sum(mainid)over(order by mainid range between 1 preceding and 3 following ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：

 2.mainid 数值在   当前行的 值 ， mainid+4 范围内的和

with tt as(

select a.*,sum(mainid)over(order by mainid range between current row and 4 following ) as sums
from (
select recid,buyerid,mainid 
from bi_edw.edw_crm_jd_sales_tmp
order by recid 
limit 4
)a
)
select * from tt

结果：