编写 SQL 和 Hive 查询以打印每个国家/地区支付第二高工资的年份？

Question

编写SQL和HIVE查询以打印每个国家/地区支付第二高工资的年份？。

请提供下表的查询

country,salary,year
india,1000,2017
japan,2000,2017
germany,1500,2017
india,1250,2018
japan,500,2018
china,955,2017
japan,850,2019
china,1150,2018
india,1250,2019

Answer 1

类似于：

select 
t.*
from (
select
tbl.*,
row_number() over(partition by country order by salary desc) rn
from 
tbl
) t
where 
t.rn = 2

Answer 2

最大的问题是如何处理关系。据推测，你的意思是第二高的不同工资。在这种情况下，您专门寻找dense_rank()窗口函数：

select t.*
from (select t.*,
dense_rank() over (partition by country order by salary desc) as seqnum
from t
) t
where t.seqnum = 2;

现在，这方面的挑战是，在平局的情况下，它可能会返回不止一排。如果你特别想要一行，那么：

select t.*
from (select t.*,
dense_rank() over (partition by country order by salary desc) as ranking,
row_number() over (partition by country, salary order by country) as seqnum
from t
) t
where t.ranking = 2 and seqnum = 1;