窗口函数over.md

over()
over() 函数在Hive中运用广泛，通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围
准备数据导入表中

id    name    num
1    a    1
2    b    3
3    a    5
4    c    9
5    b    3

》》1：over() 此时每一行的窗口都是所有的行

SELECT id,name,num,
sum(num) over() sum1 from aa

》》2：over(order by id) id=1的窗口只有一行，id=2的窗口包括id=1,id=2

SELECT id,name,num,
sum(num) over(order by id) sum1 from aa

》》3：over(partition by name) 每一行根据 name来区分窗口

SELECT id,name,num,
sum(num) over(partition by name) sum1 from aa

》》4：over(partition by name order by id) 每一行根据 name来区分窗口,再根据order by 取具体的范围

SELECT id,name,num,
sum(num) over(partition by name order by id) sum1 from aa

》》5：over( order by id range between 1 preceding and 1 following ) range 代表范围 preceding 向前 following 向后 窗口范围当前行前后一行

SELECT id,name,num,
sum(num) over( order by id range between 1 preceding and 1 following ) sum1 from aa

》》6：over( order by id range between CURRENT ROW AND 1 following ) 窗口范围当前行和后面一行

SELECT id,name,num,
sum(num) over( order by id range between CURRENT ROW AND  1 following ) sum1 from aa