Hive中窗口函数的使用

准备测试表

CREATE TABLE over_test(name STRING, month int,cnt int) row format delimited fields Terminated by ',' lines terminated by '\n' STORED AS textfile;

LOAD DATA LOCAL INPATH '/opt/data/over_test.txt' INTO TABLE over_test;

数据

夏明,1,10
夏明,2,20
夏明,3,30
夏明,4,40
夏明,5,50
夏明,6,60
夏明,7,70
夏明,8,80
夏明,9,90
夏明,10,100
夏明,11,30
夏明,12,60
无言,1,20
无言,2,10
无言,3,50
无言,4,70
无言,5,60
无言,6,70
无言,7,30
无言,8,10
无言,9,20
无言,10,300
无言,11,200
无言,12,888

---

分组排序

---

select * ,
row_number() over(partition by name order by cnt asc) as rmp，
rank() over(partition by name order by cnt asc) as rp,
dense_rank() over(partition by name order by cnt asc) as drp 
 from over_test;

---

分组累计求和

---

select * ,
sum(cnt) over(partition by name order by month asc) as sum_cnt 
from over_test;

---

分组求平均数

---

select * ,
avg(cnt) over(partition by name order by month asc) as avg_cnt 
from over_test;

---

分组滑动

---

select * ,
sum(cnt) over(partition by name order by month asc rows between 1 preceding and 1 following) as sum_cnt ,
avg(cnt) over(partition by name order by month asc rows between 1 preceding and 1 following) as avg_cnt 
from over_test;

 

参考官网资料

LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundation