时间戳

首页 > 解决方案 > 通过在 hive 中使用 case 语句向薪水列添加额外金额

hive - 通过在 hive 中使用 case 语句向薪水列添加额外金额

问题描述

下面的例子是为了我的学习目的。实际上我正在尝试在 emp 表中的工资列中添加一些不同的金额，其中通过使用案例语句在每个部门中具有最高薪水的人

使用下面的查询我检索了每个部门的最高薪水

select * from (select rank() over (partition by job order by salary desc ) as rank,* from emp) as a where rank=1

a.rank  a.emp_no        a.ename a.job   a.mgr_id        a.date_of_joining       a.salary        a.bonus a.dept_no
1       7788    SCOTT   ANALYST 7566    09-DEC-1982     3000    NULL    20
1       7902    FORD    ANALYST 7566    3-DEC-1981      3000    NULL    20
1       7934    MILLER  CLERK   7782    23-JAN-1982     1300    NULL    10
1       7566    JONES   MANAGER 7839    2-APR-1981      2975    NULL    20
1       7839    KING    PRESIDENT       NULL    17-NOV-1981     5000    NULL    10
1       7499    ALLEN   SALESMAN        7698    20-FEB-1981     1600    300     30

预期输出：

我想根据条件在工资列中添加不同的金额。

a.emp_no        a.job   a.salary
7788    ANALYST 3300
7902    ANALYST 3300
7934    CLERK   1800
7566    MANAGER 3275
7839    PRESIDENT       5200
7499    SALESMAN        2100

这里是下面的查询，在下面的查询中更正哪里做错了

select updated_salary,result.emp_no,result.ename,
case when result.salary>1000 and result.salary<=2000 then 'result.salary+500'
    when result.salary>2001 and result.salary<=4000 then 'result.salary+300' when result.salary>=4001  then 'result.salary+200' end as final_result
 from (select * from (select rank() over (partition by job order by  salary desc ) as rank,* from emp) as a where rank=1) as result;`

标签： hivehiveql

解决方案

试试这个 -

--Create table script
create table employee ( emp_no int, job string, salary int);

--Inserting dummy Data 
insert into employee (emp_no,job,salary) values (7788,"ANALYST",3300);
insert into employee (emp_no,job,salary) values (7902,"ANALYST",3100);
insert into employee (emp_no,job,salary) values (7934,"CLERK",1800);
insert into employee (emp_no,job,salary) values (7930,"CLERK",1500);
insert into employee (emp_no,job,salary) values (7566,"MANAGER",3275);
insert into employee (emp_no,job,salary) values (7839,"PRESIDENT",5200);
insert into employee (emp_no,job,salary) values (7499,"SALESMAN",2100);

--Updated script (issue with passing the salary as string )

select salary,emp_no,job,
case when salary>1000 and salary<=2000 then salary+500 
when salary>2001 and salary<=4000 then salary+300 
when salary>=4001  then salary+200 end as final_result 
from (select * from (select rank() over (partition by job order by  salary desc ) as rank,* from employee) as a where rank=1) as b
;

推荐阅读