Hive常用函数case when 练习(二)

员工工资组成表 gz.txt
uid jb jj tc deptno(员工id, 基本工资, 奖金, 提成 ,部门编号)

1,2000,3000,1500,1
2,5000,500,1000,2
3,1500,1000,3000,2
4,3000,6000,8000,3
5,1500,2000,1800,1
6,2500,1000,1900,1

部门表 bm.txt

1,销售
2,技术
3,行政

员工信息表 yg.txt
uid name gender age

1,zs,M,28
2,ww,F,36
3,zl,F,48
4,pp,M,44
5,wb,M,32
6,TQ,F,32

建表, 导入数据

员工工资组成表 gz

 drop  table gz ;
create table gz(
uid int,
jb int,
jj int,
tc int,
deptno int
)
row format delimited fields terminated by ",";
load data local inpath "/doit16/gz.txt" into table gz;

部门表 bm

drop  table bm ;
create table bm(
deptno string ,
name string
)
row format delimited fields terminated by ",";
load data local inpath "/doit16/bm.txt" into table bm;

员工信息表 yg

create table yg(
uid int,
name string,
gender string,
age int
)
row format delimited fields terminated by ",";
load data local inpath "/doit16/yg.txt" into table yg;

需求：

1. 求出公司中每个员工的姓名 和 三类收入中最高的那种收入的类型
2. 求出公司中每个岗位的薪资总和
3. 求出公司中每个岗位不同性别员工薪资总和
4. 求出公司中不同性别、不同年龄阶段（20-30,31-40,41-50）的员工薪资总和

解决：
1. 求出公司中每个员工的姓名 和 三类收入中最高的那种收入的类型
先使用greatest函数获取最高的收入 ，使用case when 来判断最高的收入是哪种收入

select
uid,
greatest(jb,jj,tc) greatest_sal,
case greatest(jb,jj,tc)
when jb then 'jb'
when jj then 'jj'
when tc then 'tc'
end greatest_sal_type
from
gz

上面的查询就求出了员工id 和员工对应的最高工资类型，然后就可以和员工信息表join，得到员工姓名了

select
yg.uid,yg.name,t.greatest_sal_type
from
(select
uid,
greatest(jb,jj,tc) greatest_sal,
case greatest(jb,jj,tc)
when jb then 'jb'
when jj then 'jj'
when tc then 'tc'
end greatest_sal_type
from
gz) t
join
yg
on yg.uid = t.uid

2.每个岗位的薪资总和
gz表按岗位分组，然后薪资汇总，最后和bm表join，得到部门名字

  select
bm.name,
t.total_sal
from
(select 
deptno,
sum(jj+jb+tc) total_sal
from
gz
group by deptno) t
join
bm
on t.deptno = bm.deptno

3. 求出公司中每个岗位不同性别员工薪资总和
按照岗位和性别分组，再组内聚合

select
bm.name,t2.gender,t2.dept_gender_sum_sal
from
bm
join
(select
deptno,
gender,
sum(jj+jb+tc) dept_gender_sum_sal
from
(select
gz.*,
yg.gender
from
gz
join
yg
on gz.uid = yg.uid) t
group by deptno,gender) t2
on t2.deptno = bm.deptno

查询结果：

4.求出公司中不同性别、不同年龄阶段（20-30,31-40,41-50）的员工薪资总和
首先需要对员工的年龄划分为对应的阶段，划分为对应的年龄阶段后就可以再分组，然后组内聚合

select
gender ,
age_range,
sum(jj+jb+tc) total_sal
from
(select
*,
case 
when age >= 20 and age <= 30 then "20-30" 
when age > 30 and age <= 40 then "31-40" 
when age > 40 and age <= 50 then "41-50"
else "others"
end  age_range 
from
yg) t
join
gz
on t.uid = gz.uid
group by gender ,age_range

查询结果：