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;
窗口帧只能用在聚合函数