hive window 开窗函数

一、窗口函数

聚合函数：
    sum()
    min()
    max()
    avg()

排序函数：
    rank()
    dens_rank()
    row_number()
    ntile()

统计比较函数：
    lead()
    lag()
    first_value()

二、窗口大小

partition by 
order by 

三、窗口边界

n preceding
n following
current row
unbounded preceding
unbounded following

四、rows between

select
    name,
    date,
    timestamp,
    value,
    -- 前1行_当前行
    sum(value) over(partition by name order by timestamp rows between 1 preceding and current row) as sum_number_row1,
    -- 前1行_后1行
    sum(value) over(partition by name order by timestamp rows between 1 preceding and 1 following) as sum_number_row2,
    -- 第1行_当前行
    sum(value) over(partition by name order by timestamp rows between unbounded preceding and current row) as sum_number_row3,
    -- 前2行_前1行
    sum(value) over(partition by name order by timestamp rows between 2 preceding and 1 preceding) as sum_number_row4
from table

四、range between

select
    name,
    date,
    timestamp,
    value,
    -- 当前行时间戳近3天数据
    sum(value) over(partition by name order by timestamp range between 3*24*3600 preceding and current row) as sum_value
from t