Hive内嵌函数大全：条件判断函数IF,COALESCE,CASE WHEN

绝地灬酷狼 2022-05-30 06:42 194阅读 0赞

### 1.Hive所有关于条件判断内嵌函数 ###

<table> 
 <thead> 
 <tr> 
 <td style="vertical-align:middle;"> Return Type </td> 
 <th> Name(Signature) </th> 
 <th> Description </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> T </td> 
 <td> if(boolean testCondition, T valueTrue, T valueFalseOrNull) </td> 
 <td> Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise. 如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull ，（valueTrue，valueFalseOrNull为泛型）&nbsp; if( 100 is not null , 'a=100','a=99' )&nbsp; 结果：a=100 if( null,100,00 ) 结果：00 </td> 
 </tr> 
 <tr> 
 <td colspan="1">T</td> 
 <td colspan="1">nvl(T value, T default_value)</td> 
 <td colspan="1">Returns default value if value is null else returns value (as of HIve&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2288" rel="nofollow">0.11</a>). 如果value值为NULL就返回default_value,否则返回value </td> 
 </tr> 
 <tr> 
 <td> T </td> 
 <td> COALESCE(T v1, T v2, ...) </td> 
 <td> Returns the first v that is not NULL, or NULL if all v's are NULL. 返回第一非null的值，如果全部都为NULL就返回NULL &nbsp;如：COALESCE (NULL,44,55)=44/strong&gt; </td> 
 </tr> 
 <tr> 
 <td> T </td> 
 <td> CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END </td> 
 <td> When a = b, returns c; when a = d, returns e; else returns f. 如果a=b就返回c,a=d就返回e，否则返回f &nbsp;如CASE 4 WHEN 5 &nbsp;THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4 </td> 
 </tr> 
 <tr> 
 <td> T </td> 
 <td> CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END </td> 
 <td> When a = true, returns b; when c = true, returns d; else returns e. 如果a=ture就返回b,c=&nbsp;ture就返回d，否则返回e &nbsp;如：CASE WHEN &nbsp;5&gt;0 &nbsp;THEN 5 WHEN 4&gt;0 THEN 4 ELSE 0 END 将返回5；CASE WHEN &nbsp;5&lt;0 &nbsp;THEN 5 WHEN 4&lt;0 THEN 4 ELSE 0 END 将返回0 </td> 
 </tr> 
 <tr> 
 <td colspan="1">boolean</td> 
 <td colspan="1">isnull( a )</td> 
 <td colspan="1">Returns true if a is NULL and false otherwise. 如果a为null就返回true，否则返回false </td> 
 </tr> 
 <tr> 
 <td colspan="1">boolean</td> 
 <td colspan="1">isnotnull ( a )</td> 
 <td colspan="1">Returns true if a is not NULL and false otherwise. 如果a为非null就返回true，否则返回false </td> 
 </tr> 
 </tbody> 
</table>

### 2.常用条件判断函数演示 ###

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

语法: if(boolean testCondition, T valueTrue, T valueFalse Or Null)

说明:  当条件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.nvl对于null值的判断**

语法：nvl(T value, T default\_value)

说明：如果value值为NULL就返回default\_value,否则返回value,**主要功能就是实现null值替换。**

hive> select nvl(principal,1)  from mydb.aaaaa

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

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

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

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

aaa

**3.条件件判断函数case  A when  B THEN C ,case和when是分开的**

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

说明：**如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f。注意这种when的判断条件可以有很多个。**

举例：

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 when是一起的，用于处理单个列的查询结果**

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

说明：如果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>

**5. is null和is not null**

语法与说明：比较简单， a is null, a is not null ,**如果a为非null就返回true，否则返回false**