hive窗口函数计算累加值

累加值

1.数据源：

select
   *
from
    stu_score
order by
    score;

2.函数使用：

select
    name,
    score,
    sum(score) over(order by score range between 2 preceding and 2 following) s1, -- 当前行的score值加减2的范围内的所有行
    sum(score) over(order by score rows between 2 preceding and 2 following) s2, -- 当前行+前后2行，一共5行
    sum(score) over(order by score range between unbounded preceding and unbounded following) s3, -- 全部行，不做限制
    sum(score) over(order by score rows between unbounded preceding and unbounded following) s4, -- 全部行，不做限制
    sum(score) over(order by score) s5, -- 第一行到当前行（和当前行相同score值的所有行都会包含进去）
    sum(score) over(order by score rows between unbounded preceding and current row) s6, -- 第一行到当前行（和当前行相同score值的其他行不会包含进去，这是和上面的区别）
    sum(score) over(order by score rows between 3 preceding and current row) s7, -- 当前行+往前3行
    sum(score) over(order by score rows between 3 preceding and 1 following) s8, --当前行+往前3行+往后1行
    sum(score) over(order by score rows between current row and unbounded following) s9 --当前行+往后所有行
from
    stu_score
order by 
    score;

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

• rows是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无关，只与排序后的行号相关，就是我们常规理解的那样。

• range是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range范围。
  简要：如果当前行的值有重复的，range会默认把重复的值加一块，rows是按照行号来，是哪一行就是哪一行

select id
,sum(id) over(order by id) default_sum
,sum(id) over(order by id range between unbounded preceding and current row) range_sum
,sum(id) over(order by id rows between unbounded preceding and current row) rows_sum
,sum(id) over(order by id range between 1 preceding and 2 following) range_sum1
,sum(id) over(order by id rows between 1 preceding and 2 following) 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(order by pt_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) first_amount,last_value(sum(amount))over(order by pt_month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) last_amount
from data_chushou_pay_info
where pt_month between '2017-01' and '2017-11' and state=0
group by pt_month;