hive 窗口分析函数 over：以 student表为例 求年龄top2

学生表student：

hive> select * from student;
OK
student.id      student.name    student.sex     student.age     student.dept
95002   刘晨    女      19      IS
95017   王风娟  女      18      IS
95018   王一    女      19      IS
95013   冯伟    男      21      CS
95014   王小丽  女      19      CS
95019   邢小丽  女      19      IS
95020   赵钱    男      21      IS
95003   王敏    女      22      MA
95004   张立    男      19      IS
95012   孙花    女      20      CS
95010   孔小涛  男      19      CS
95005   刘刚    男      18      MA
95006   孙庆    男      23      CS
95007   易思玲  女      19      MA
95008   李娜    女      18      CS
95021   周二    男      17      MA
95022   郑明    男      20      MA
95001   李勇    男      20      CS
95011   包小柏  男      18      MA
95009   梦圆圆  女      18      MA
95015   王君    男      18      MA

求：每个部门年龄最大的2个人的信息。

方法1：

先开窗，增加rank列。

hive> select id,name,sex,age,dept,row_number() over(partition by dept order by age desc) as rank  from student;
OK
id      name    sex     age     dept    rank
95006   孙庆    男      23      CS      1
95013   冯伟    男      21      CS      2
95001   李勇    男      20      CS      3
95012   孙花    女      20      CS      4
95014   王小丽  女      19      CS      5
95010   孔小涛  男      19      CS      6
95008   李娜    女      18      CS      7
95020   赵钱    男      21      IS      1
95002   刘晨    女      19      IS      2
95004   张立    男      19      IS      3
95019   邢小丽  女      19      IS      4
95018   王一    女      19      IS      5
95017   王风娟  女      18      IS      6
95003   王敏    女      22      MA      1
95022   郑明    男      20      MA      2
95007   易思玲  女      19      MA      3
95015   王君    男      18      MA      4
95011   包小柏  男      18      MA      5
95009   梦圆圆  女      18      MA      6
95005   刘刚    男      18      MA      7
95021   周二    男      17      MA      8

接下来可以先把数据存入另一个表，就比较容易求了。

create table student_kaichuang as
select id,name,sex,age,dept,row_number() over(partition by dept order by age desc) as rank  from student;

select * from student_kaichuang;
OK
student_kaichuang.id    student_kaichuang.name  student_kaichuang.sex   student_kaichuang.age   student_kaichuang.deptstudent_kaichuang.rank
95006   孙庆    男      23      CS      1
95013   冯伟    男      21      CS      2
95001   李勇    男      20      CS      3
95012   孙花    女      20      CS      4
95014   王小丽  女      19      CS      5
95010   孔小涛  男      19      CS      6
95008   李娜    女      18      CS      7
95020   赵钱    男      21      IS      1
95002   刘晨    女      19      IS      2
95004   张立    男      19      IS      3
95019   邢小丽  女      19      IS      4
95018   王一    女      19      IS      5
95017   王风娟  女      18      IS      6
95003   王敏    女      22      MA      1
95022   郑明    男      20      MA      2
95007   易思玲  女      19      MA      3
95015   王君    男      18      MA      4
95011   包小柏  男      18      MA      5
95009   梦圆圆  女      18      MA      6
95005   刘刚    男      18      MA      7
95021   周二    男      17      MA      8

接下来从student_kaichuang这个表里取数据：

hive> select id,name,sex,age,dept from student_kaichuang where rank<=2;
OK
id      name    sex     age     dept
95006   孙庆    男      23      CS
95013   冯伟    男      21      CS
95020   赵钱    男      21      IS
95002   刘晨    女      19      IS
95003   王敏    女      22      MA
95022   郑明    男      20      MA

方法2：

一步到位：

select id,name,sex,age,dept from 
(select id,name,sex,age,dept,
row_number() over(partition by dept order by age desc) as rank  
from student) d
where d.rank <= 2;
OK
id      name    sex     age     dept
95006   孙庆    男      23      CS
95013   冯伟    男      21      CS
95020   赵钱    男      21      IS
95002   刘晨    女      19      IS
95003   王敏    女      22      MA
95022   郑明    男      20      MA