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

梦里梦外; 2023-06-16 13:52 11阅读 0赞

### hive 窗口分析函数 over ###

*   *   *   *  学生表student：
             *   *  求：每个部门年龄最大的2个人的信息。
                 *  方法1：
                 *  方法2：

#### 学生表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