hive详细笔记(七)-Hive之case when语法详解(附带讲解视频 )

最新推荐文章于 2024-01-29 17:03:50 发布

置顶白眼黑刺猬

最新推荐文章于 2024-01-29 17:03:50 发布

阅读量1.9w

点赞数 16

分类专栏：大数据之HIVE 文章标签：大数据 hive

本文链接：https://blog.csdn.net/qq_37933018/article/details/106878128

版权

大数据之HIVE 专栏收录该内容

14 篇文章

订阅专栏

免费视频教程 https://www.51doit.com/ 或者联系博主微信 17710299606

1 基本语法

语法一

CASE 字段 

WHEN 值1 THEN 值1

[WHEN 值2 THEN 值2]*

[ELSE 值] 

END

语法二

CASE 

WHEN 条件表达式 THEN 值1

[WHEN 条件表达式 [and or] 条件表达式THEN 值2]*

[ELSE 值] 

END

2 实例一

2.1 数据和需求

数据

悟空   A   男
娜娜   A   男
宋宋   B   男
凤姐   A   女
热巴   B   女
慧慧   B   女

求出不同部门男女各多少人。结果如下：

dpt 男女
A 2 1
B 1 2

2.2 建表导入数据

create table tb_case_when_demo(
name string ,
dname string ,
gender string 
)
row format delimited fields terminated by "\t" ;

load data local inpath "/hive/data/casedemo.txt" into table tb_case_when_demo ;
0: jdbc:hive2://linux01:10000> select * from tb_case_when_demo ;
OK
+-------------------------+--------------------------+---------------------------+
| tb_case_when_demo.name  | tb_case_when_demo.dname  | tb_case_when_demo.gender  |
+-------------------------+--------------------------+---------------------------+
| 悟空                      | A                        | 男                         |
| 娜娜                      | A                        | 男                         |
| 宋宋                      | B                        | 男                         |
| 凤姐                      | A                        | 女                         |
| 热巴                      | B                        | 女                         |
| 慧慧                      | B                        | 女                         |
+-------------------------+--------------------------+---------------------------+

2.3 case when实现一

select
dname ,
sum(case gender when '男' then 1 else 0 end)  as m_cnts ,
sum(case gender when '女' then 1 else 0 end)  as f_cnts 
from
tb_case_when_demo
group by dname ;

+--------+---------+---------+
| dname  | m_cnts  | f_cnts  |
+--------+---------+---------+
| A      | 2       | 1       |
| B      | 1       | 2       |
+--------+---------+---------+

2.4 case when实现二

select
dname ,
sum(case when gender='男' then 1 else 0 end) as m_cnts ,
sum(case when gender='女' then 1 else 0 end) as f_cnts ,
case when dname='A' then '教学部' else '后勤部' end   as ch
from
tb_case_when_demo
group by dname ;

+--------+---------+---------+------+
| dname  | m_cnts  | f_cnts  |  ch  |
+--------+---------+---------+------+
| A      | 2       | 1       | 教学部  |
| B      | 1       | 2       | 后勤部  |
+--------+---------+---------+------+

2.5 补充if语法

2) if 参数一条件判断  参数二 成立的结果  参数三不成立的结果
0: jdbc:hive2://linux01:10000> select if(1=1,1,0);
OK
+------+
| _c0  |
+------+
| 1    |
+------+
1 row selected (0.142 seconds)
0: jdbc:hive2://linux01:10000> select if(1=2,1,0);
OK
+------+
| _c0  |
+------+
| 0    |
+------+

2.6 if实现上面的需求

select
dname,
sum(1) cnts ,
sum(if(gender='男', 1 , 0)) as  M_CNTS ,
sum(if(gender='女', 1 , 0)) as  F_CNTS 
from
tb_case_when_demo
group by  dname ;

+--------+-------+---------+---------+
| dname  | cnts  | m_cnts  | f_cnts  |
+--------+-------+---------+---------+
| A      | 3     | 2       | 1       |
| B      | 3     | 1       | 2       |
+--------+-------+---------+---------+

3 练习

3.1 数据

gz.txt 用户工资组成表
uid jb jj tc deptno
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

3.2 建表导入数据

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 "/hive/data/gz.txt" into table gz;


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

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


0: jdbc:hive2://linux01:10000> select * from gz ;
OK
+---------+--------+--------+--------+------------+
| gz.uid  | gz.jb  | gz.jj  | gz.tc  | gz.deptno  |
+---------+--------+--------+--------+------------+
| 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          |
+---------+--------+--------+--------+------------+
6 rows selected (0.223 seconds)
0: jdbc:hive2://linux01:10000> select * from bm ;
OK
+------------+----------+
| bm.deptno  | bm.name  |
+------------+----------+
| 1          | 销售       |
| 2          | 技术       |
| 3          | 行政       |
+------------+----------+
3 rows selected (0.2 seconds)
0: jdbc:hive2://linux01:10000> select * from yg ;
OK
+---------+----------+------------+---------+
| yg.uid  | yg.name  | yg.gender  | yg.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      |
+---------+----------+------------+---------+

3.3 练习

3.3.1求出公司中每个员工的姓名和三类收入中最高的那种收入的类型 greatest

方式一

select
name ,
greatest(jb, jj,tc) max_gz ,
case when greatest(jb, jj,tc) = jb then 'jb'
when greatest(jb, jj,tc) = tc then 'tc'
when greatest(jb, jj,tc) = jj then 'jj'
else "_" 
end  as  gz_category
from
(select
yg.name ,
gz.jb ,
gz.jj ,
gz.tc
from
yg
join
gz
on
yg.uid = gz.uid)t ;
+-------+---------+--------------+
| name  | max_gz  | gz_category  |
+-------+---------+--------------+
| zs    | 3000    | jj           |
| ww    | 5000    | jb           |
| zl    | 3000    | tc           |
| pp    | 8000    | tc           |
| wb    | 2000    | jj           |
| TQ    | 2500    | jb           |
+-------+---------+--------------+

方式二

select
yg.uid ,
yg.name ,
t.max_gz ,
t.category
from
yg 
join
(select
uid ,
greatest(jb , jj ,tc)  as max_gz ,
case when greatest(jb , jj ,tc) = jb then 'jb'
when greatest(jb , jj ,tc) = jj then 'jj' 
when greatest(jb , jj ,tc) = tc then 'tc'  
end  as  category 
from
gz) t 
on t.uid = yg.uid ;

+---------+----------+-----------+-------------+
| yg.uid  | yg.name  | t.max_gz  | t.category  |
+---------+----------+-----------+-------------+
| 1       | zs       | 3000      | jj          |
| 2       | ww       | 5000      | jb          |
| 3       | zl       | 3000      | tc          |
| 4       | pp       | 8000      | tc          |
| 5       | wb       | 2000      | jj          |
| 6       | TQ       | 2500      | jb          |
+---------+----------+-----------+-------------+

3.3.2求出公司中每个岗位的薪资总和

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

+------------+----------+-----------+
| bm.deptno  | bm.name  | t.sum_gz  |
+------------+----------+-----------+
| 2          | 技术       | 12000     |
| 3          | 行政       | 17000     |
| 1          | 销售       | 17200     |
+------------+----------+-----------+

3.3.3求出公司中每个岗位不同性别员工薪资总和

select
bm_name , 
gender ,
sum(jb+jj+tc)  bm_gender_gz
from
(select
yg.uid ,
yg.name  yg_name,
yg.gender ,
gz.jb ,
gz.jj ,
gz.tc ,
bm.name as bm_name
from
yg
join
gz
join
bm
on yg.uid = gz.uid 
and gz.deptno = bm.deptno) t
group by bm_name , gender  ;

+----------+---------+---------------+
| bm_name  | gender  | bm_gender_gz  |
+----------+---------+---------------+
| 销售       | F       | 5400          |
| 行政       | M       | 17000         |
| 销售       | M       | 11800         |
| 技术       | F       | 12000         |
+----------+---------+---------------+

3.3.4求出公司中不同性别、不同年龄阶段（20-30,31-40,41-50）的员工薪资总和

with t1 as (
select
*,
case 
when age >=20 and  age<30 then '20~30'
when age >=30 and age <40 then '30~40'
when age >=40 and age <50 then '40~50'
else  '退休'
end as  stage 
from
yg
)

select 
t1.stage ,
t1.gender, 
sum(jb+jj+tc) stage_zg
from
t1 
join
gz
on
gz.uid = t1.uid
group by t1.stage , t1.gender  ;

+-----------+------------+-----------+
| t1.stage  | t1.gender  | stage_zg  |
+-----------+------------+-----------+
| 30~40     | F          | 11900     |
| 40~50     | M          | 17000     |
| 30~40     | M          | 5300      |
| 20~30     | M          | 6500      |
| 40~50     | F          | 5500      |
+-----------+------------+-----------+

补充 : with 用法

WITH 
t1 as (SELECT * FROM Table1) ,
t2 as (SELECT * FROM Table2) 
SELECT t1.*
FROM t1
LEFT JOIN t2 ON t1.id=t2.id ;

例如

with t1 as (select
			yg.uid ,
			yg.name  yg_name,
			yg.gender ,
			gz.jb ,
			gz.jj ,
			gz.tc ,
			bm.name as bm_name
			from
			yg
			join
			gz
			join
			bm
			on yg.uid = gz.uid 
			and gz.deptno = bm.deptno)
	t2 as  (select .......)
    t3 as  (select ......)
select t1  join t2 ...

https://apppunf4gqb9193.h5.xiaoeknow.com/v1/course/column/p_5fa2648ee4b01f764d88acc5?type=3