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

6 篇文章 0 订阅

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 (
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 (
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 (
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 (
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 (
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 (
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 (
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 (
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 (
from bi_edw.edw_crm_jd_sales_tmp
order by recid
limit 4
)a
)
select * from tt

• 1
点赞
• 5
收藏
觉得还不错? 一键收藏
• 打赏
• 0
评论
01-12 182
08-03 310
08-06 526
08-03 462
04-01
08-21
08-02 100

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。