hive窗口函数计算累加值

累加值

1.数据源：

select*from
    stu_score
orderby
    score;

2.函数使用：

select
    name,
    score,sum(score)over(orderby score range between2precedingand2following) s1,-- 当前行的score值加减2的范围内的所有行sum(score)over(orderby score rowsbetween2precedingand2following) s2,-- 当前行+前后2行，一共5行sum(score)over(orderby score range betweenunboundedprecedingandunboundedfollowing) s3,-- 全部行，不做限制sum(score)over(orderby score rowsbetweenunboundedprecedingandunboundedfollowing) s4,-- 全部行，不做限制sum(score)over(orderby score) s5,-- 第一行到当前行（和当前行相同score值的所有行都会包含进去）sum(score)over(orderby score rowsbetweenunboundedprecedingandcurrentrow) s6,-- 第一行到当前行（和当前行相同score值的其他行不会包含进去，这是和上面的区别）sum(score)over(orderby score rowsbetween3precedingandcurrentrow) s7,-- 当前行+往前3行sum(score)over(orderby score rowsbetween3precedingand1following) s8,--当前行+往前3行+往后1行sum(score)over(orderby score rowsbetweencurrentrowandunboundedfollowing) s9 --当前行+往后所有行from
    stu_score
orderby 
    score;

hive开窗函数中range和rows的区别

• rows是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无关，只与排序后的行号相关，就是我们常规理解的那样。
• range是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range范围。简要：如果当前行的值有重复的，range会默认把重复的值加一块，rows是按照行号来，是哪一行就是哪一行

select id
,sum(id)over(orderby id) default_sum
,sum(id)over(orderby id range betweenunboundedprecedingandcurrentrow) range_sum
,sum(id)over(orderby id rowsbetweenunboundedprecedingandcurrentrow) rows_sum
,sum(id)over(orderby id range between1precedingand2following) range_sum1
,sum(id)over(orderby id rowsbetween1precedingand2following) rows_sum1
from tmp

不加行号默认是按照range，id都为1的话，

sum(id) over(order by id range between unbounded preceding and current row) range_sum

会把1的加一块

计算窗体第一条和最后一条的值

select pt_month,sum(amount) pay_amount,first_value(sum(amount))over(orderby pt_month ROWSBETWEEN1PRECEDINGAND1FOLLOWING) first_amount,last_value(sum(amount))over(orderby pt_month ROWSBETWEEN1PRECEDINGAND1FOLLOWING) last_amount
from data_chushou_pay_info
where pt_month between'2017-01'and'2017-11'and state=0groupby pt_month;