[数据仓库]hive常用函数之条件判断函数IF,COALESCE,CASE

深藏阁楼爱情的钟 2022-05-10 07:26 175阅读 0赞

**1.If函数:if和case差不多，都是处理单个列的查询结果**

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

返回值: T

说明:  当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

举例：**if（条件表达式，结果1，结果2）相当于java中的三目运算符,只是if后面的表达式类型可以不一样。**

hive> select if(a=a,’bbbb’,111) fromlxw\_dual;

bbbb

hive> select if(1<2,100,200) fromlxw\_dual;

200

**2.非空查找函数: COALESCE**

语法: COALESCE(T v1, T v2, …)

返回值: T

说明:  返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

hive> select COALESCE(null,’aaa’,50)from lxw\_dual;

aaa

**3.条件件判断函数：这种CASE A和if差不多，条件查询（常用）**

语法: C**ASE a WHEN b THEN c \[WHENd THEN e\]\* \[ELSE f\] END**

返回值: T

说明：**如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f**

举例：

hive> Select case 100 when 50 then 'tom'when 100 then 'mary' else 'tim' end from lxw\_dual;

mary

hive> Select case 200 when 50 then 'tom'when 100 then 'mary' else 'tim' end from lxw\_dual;

tim

**4.条件判断函数：这种case，case后面没有表达式，用于处理单个列的查询结果**

语法: **CASE WHEN a THEN b \[WHEN cTHEN d\]\* \[ELSE e\] END**

返回值: T

说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e

举例：

hive> select case when 1=2 then 'tom'when 2=2 then 'mary' else 'tim' end from lxw\_dual;

mary

hive> select case when 1=1 then 'tom'when 2=2 then 'mary' else 'tim' end from lxw\_dual;

tom

**实战演示：case匹配的结果属于一个新的字段**

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> select *&nbsp; from ( select name ,id ,case when id &lt;=1235&nbsp; then 'low salary' when id &gt;1235 and id &lt;=1236 then 'middle' else 'very high' end&nbsp; from mytest_staff_info_demo4_cp1 where statis_date='20180228') &nbsp;b 查询结果： name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;_c2 'account1'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; low salary 'account2'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; low salary 'account3'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; low salary 'account4'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1236&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; middle 'account5'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1237&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; very high </td> 
 </tr> 
 </tbody> 
</table>