如何在hive中找到每个部门薪水最高的前三名员工？

ovfsdjhp 于 2021-05-27 发布在 Hadoop

关注(0)|答案(1)|浏览(510)

name, salary, department
joe, 95000, Manager
walter, 26500, HR
Adam, 36520,Developer
Sam, 38910, Developer
Romie, 68457,Manager
oliver, 69545, Developer
Mahesh, 89542, Developer
Ricky, 96845,HR
Sara, 84756,Manager
Anand, 36597, HR
Kevin, 98457, HR
Ram, 63547, Manager

输出应为`

Developer, Mahesh, 89542
Developer, oliver, 69545
Developer, Sam, 38910
HR, Kevin, 98457
HR, Ricky, 96845
HR, Anand, 36597
Manager, joe, 95000
Manager, Sara, 84756
Manager, Romie, 68457

`

我希望所有的记录都应该像给定的输出一样打印出来，并且应该在配置单元中运行。

hadoop hiveql

来源：https://stackoverflow.com/questions/59114270/how-to-find-top-3-employee-in-every-department-having-higest-salary-in-hive

1条答案

按热度按时间

q9yhzks01#

select dept, name, salary from (
    select name, salary, dept, row_number() over(partition by dept order by salary desc) as rn 
    from employee_sal
    ) emp 
where emp.rn<4;

结果如下所示： name salary dept Mahesh 89542 Developer oliver 69545 Developer Sam 38910 Developer Kevin 98457 HR Ricky 96845 HR Anand 36597 HR joe 95000 Manager Sara 84756 Manager Romie 68457 Manager

赞(0）回复(0）举报 2021-05-27

我来回答

如何在hive中找到每个部门薪水最高的前三名员工？

1条答案

相关问题

热门标签

最新问答