Hive条件函数

来源：互联网发布：java基本数据类型大小编辑：程序博客网时间：2024/06/05 11:18

一 条件函数

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 = 1Launching Job 1 out of 1Number of reduce tasks is set to 0 since there's no reduce operatorStarting Job = job_201709011948_0003, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201709011948_0003Kill Command = /opt/hadoop-1.2.1/libexec/../bin/hadoop job  -kill job_201709011948_0003Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 02017-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 sec2017-09-01 20:39:00,968 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 4.22 secMapReduce Total cumulative CPU time: 4 seconds 220 msecEnded Job = job_201709011948_0003MapReduce Jobs Launched: Job 0: Map: 1   Cumulative CPU: 4.22 sec   HDFS Read: 635 HDFS Write: 440 SUCCESSTotal MapReduce CPU Time Spent: 4 seconds 220 msecOKTomenull5000.05400.0Tomenull5000.05400.0SMITHCLERK4800.05200.0ALLENSALESMAN5600.06000.0WARDSALESMAN5250.05650.0JONESMANAGER9075.09875.0MARTINSALESMAN5250.05650.0BLAKEMANAGER8850.09650.0CLARKMANAGER8450.09250.0SCOTTANALYST7000.07400.0KINGPRESIDENT12111.013111.0TURNERSALESMAN5500.05900.0ADAMSCLERK5100.05500.0JAMESCLERK4950.05350.0FORDANALYST7000.07400.0MILLERCLERK5300.05700.0Time taken: 61.021 seconds, Fetched: 16 row(s)

阅读全文

0 0