Hive查询以查找哪个月是按部门支付的最高薪水 [英] Hive query to find which month is highest paid salary by department

问题描述

0
down vote
favorite
my input

Department     Jan_sal      Feb_sal         Mar_sal

civil            1            5               5
mech             2            7               2
civil            3            8               9
mech             6            4               4
mech             5            6               6
Elec             4            5               3
mech             8            5               5
Elec             8            5               4
Expected output

Civil       Mar
Mech      Feb
Elec       Jan

我尝试过的事情：



我是hive的新人。



但我试着写子查询，我试过下面

What I have tried:

Im new in hive.

But im trying to write subquery and i tried below

select    department
         ,sort_array
          (
              array
              (
                  struct(-sum(Jan_sal),'Jan')
                 ,struct(-sum(Feb_sal),'Feb')
                 ,struct(-sum(Mar_sal),'Mar')
              )
          )[0].col2

from      mytable

group by  department

但我得到的错误如--->

but im getting error like --->

Argument type mismatch ''mar_sal'': Argument 1 of function SORT_ARRAY must be array<PRIMITIVE>, but array<struct<col1:bigint,col2:string>> was found.

推荐答案

这里是上述问题的解决方案

here is the solution for the above problem

select d.department,
       case
       when (d.maxJan>=d.maxFeb)
       and (d.maxJan>=d.maxMarch)
       then 'Jan'
       when (d.maxFeb>=d.maxJan)
       and (d.maxFeb>=d.maxMarch)
       then 'Feb'       
       when (d.maxMarch>=d.maxJan)
       and (d.maxMarch>=d.maxFeb)
       then 'March'
       else 'null'
       end as month 
       from (select department,max(jan_sal) maxJan,max(feb_sal) maxFeb,max(march_sal) maxMarch from mytable group by department)d;

这篇关于Hive查询以查找哪个月是按部门支付的最高薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！