取得Hive的第三高薪 [英] Retrieve 3rd MAX salary in Hive

问题描述

我是新手.我有以下Employee表.

I'm a novice. I have the following Employee table.

ID  Name  Country  Salary  ManagerID

我使用以下方法检索了第三高薪.

I retrieved the 3rd max salary using the following.

select name , salary From (
    select name, salary from 
    employee sort by salary desc limit 3)
    result sort by salary limit 1;

该如何执行以显示每个国家/地区的第三最高薪水?我们可以使用OVER(PARTITION BY country)吗?我尝试使用手动窗口和分析"语言进行查找，但发现它很难理解.请帮忙！

How to do the same to display 3rd max salary for each country? can we use OVER (PARTITION BY country)? I tried looking in the languageManual Windowing and Analytics but I'm finding it difficult to understand. Please help!

推荐答案

使用窗口功能，您肯定走在正确的轨道上. row_number()是在此处使用的很好的功能.

You're definitely on the right track with windowing functions. row_number() is a good function to use here.

select name, salary
from (
  select name
    , salary
    , row_number() over (partition by country order by salary desc) idx
  from employee ) x
where idx = 3

当您按薪水订购时，请确保它是数字类型，否则将无法正确排序.

when you order by salary, make sure that it is a numerical type, or it will not be sorted correctly.

这篇关于取得Hive的第三高薪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！