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 |
- over() 此时每一行的窗口都是所有的行
SELECT id,name,num,
sum(num) over() sum1 from aa
- 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
- over(partition by name) 每一行根据 name来区分窗口
SELECT id,name,num,
sum(num) over(partition by name) sum1 from aa
- 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
- over( order by id range between 1 preceding and 1 following ) range 代表范围 preceding 向前 following 向后 窗口范围当前行前后一行
- 讲解:
CURRENT ROW:当前行
n PRECEDING:往前n行数据 -----------》n为int类型
n FOLLOWING:往后n行数据 -----------》n为int类型
UNBOUNDED:起点
UNBOUNDED PRECEDING 表示从前面的起点,
UNBOUNDED FOLLOWING表示到后面的终点
SELECT id,name,num,
sum(num) over( order by id range between 1 preceding and 1 following ) sum1 from aa
- 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