Hive窗口帧(滑动窗口)

Hive窗口帧(滑动窗口)

测试用例数据testWins.txt

111	69	class1
113	74	class1
216	74	class1
112	80	class1
215	82	class1
212	83	class1
211	93	class1
115	93	class1
213	94	class1
114	94	class1
214	94	class1
124	70	class2
121	74	class2
223	74	class2
222	78	class2
123	78	class2
224	80	class2
225	85	class2
122	86	class2
221	99	class2

create table testWins(
id string,
score bigint,
clazz string
)
row format delimited fields terminated by '\t';

从本地加载数据到hive表中

load data local inpath '/usr/local/data/testWins.txt' into table testwins;

验证表数据

select * from testwins;

开窗函数

按性别分组查询成绩从高到低的学生信息

select *,
row_number() over(partition by clazz order by score) as num,
rank() over(partition by clazz order by score) as num2
from testwins;

//row_number：无并列排名
//dense_rank：有并列排名，并且依次递增
//rank：有并列排名，不依次递增

TopN

select * from (select *,
row_number() over(partition by clazz order by score desc) as a
from testwins) as b where a <= 3;

思考怎么求前三名的平均分

窗口帧

假设窗口大小只能处理三条数据

滑动窗口

在每一条记录 加上前一条 加上后一条 求个平均数

rows格式1：前两行+后两行
avg(score) over(partition by clazz order by score desc rows between 1 preceding and 1 following)

select *,
row_number() over(partition by clazz order by score desc) as num,
rank() over(partition by clazz order by score desc) as num2,
avg(score) over(partition by clazz order by score desc rows between 1 preceding and 1 following) as avg1
from testwins;

rows格式2：前n行到当前行
max(score) over(partition by clazz order by score desc rows between 2 preceding
and current row)

rows格式3：当前行到末尾
max(score) over(partition by clazz order by score desc rows between current row and unbounded following)

求前三行最大值

select *,
row_number() over(partition by clazz order by score desc) as num,
rank() over(partition by clazz order by score desc) as num2,
avg(score) over(partition by clazz order by score desc rows between 1 preceding and 1 following) as avg1,
max(score) over(partition by clazz order by score desc rows between 2 preceding
and current row) as max1
from testwins;

窗口帧只能用在聚合函数