Hive条件函数

最新推荐文章于 2024-05-29 17:57:09 发布

原创最新推荐文章于 2024-05-29 17:57:09 发布 · 594 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Hive

Hive 专栏收录该内容

34 篇文章

订阅专栏

一 条件函数

1、coalesce:从左到右返回地一个不为null的值。

2、case...when...:条件表达式

CASE a WHEN b THEN c

WHEN d THEN e

ELSE f

END

二 实战

1、coalesce应用

2、给员工涨工资，总裁涨1000，经理涨800，其他涨400

hive> select ename,empjob,sal,case empjob when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+800 else sal+400 end from emp;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201709011948_0003, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201709011948_0003
Kill Command = /opt/hadoop-1.2.1/libexec/../bin/hadoop job  -kill job_201709011948_0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2017-09-01 20:38:32,027 Stage-1 map = 0%,  reduce = 0%
2017-09-01 20:38:52,814 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.22 sec
2017-09-01 20:39:00,968 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 4.22 sec
MapReduce Total cumulative CPU time: 4 seconds 220 msec
Ended Job = job_201709011948_0003
MapReduce Jobs Launched: 
Job 0: Map: 1   Cumulative CPU: 4.22 sec   HDFS Read: 635 HDFS Write: 440 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 220 msec
OK
Tome	null	5000.0	5400.0
Tome	null	5000.0	5400.0
SMITH	CLERK	4800.0	5200.0
ALLEN	SALESMAN	5600.0	6000.0
WARD	SALESMAN	5250.0	5650.0
JONES	MANAGER	9075.0	9875.0
MARTIN	SALESMAN	5250.0	5650.0
BLAKE	MANAGER	8850.0	9650.0
CLARK	MANAGER	8450.0	9250.0
SCOTT	ANALYST	7000.0	7400.0
KING	PRESIDENT	12111.0	13111.0
TURNER	SALESMAN	5500.0	5900.0
ADAMS	CLERK	5100.0	5500.0
JAMES	CLERK	4950.0	5350.0
FORD	ANALYST	7000.0	7400.0
MILLER	CLERK	5300.0	5700.0
Time taken: 61.021 seconds, Fetched: 16 row(s)