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

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

Return Type	Name(Signature)	Description
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise. 如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull ，（valueTrue，valueFalseOrNull为泛型）  if( 100 is not null , 'a=100','a=99' )  结果：a=100 if( null,100,00 ) 结果：00
T	nvl(T value, T default_value)	Returns default value if value is null else returns value (as of HIve 0.11). 如果value值为NULL就返回default_value,否则返回value
T	COALESCE(T v1, T v2, ...)	Returns the first v that is not NULL, or NULL if all v's are NULL. 返回第一非null的值，如果全部都为NULL就返回NULL  如：COALESCE (NULL,44,55)=44/strong>
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	When a = b, returns c; when a = d, returns e; else returns f. 如果a=b就返回c,a=d就返回e，否则返回f  如CASE 4 WHEN 5  THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	When a = true, returns b; when c = true, returns d; else returns e. 如果a=ture就返回b,c= ture就返回d，否则返回e  如：CASE WHEN  5>0  THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5；CASE WHEN  5<0  THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0
boolean	isnull( a )	Returns true if a is NULL and false otherwise. 如果a为null就返回true，否则返回false
boolean	isnotnull ( a )	Returns true if a is not NULL and false otherwise. 如果a为非null就返回true，否则返回false

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

         1

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是分开的

语法: CASE 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

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匹配的结果属于一个新的字段

select name ,id , case when id <=1235  then 'low salary' when id >1235 and id <=1236 then 'middle' else 'very high' end  from mytest_staff_info_demo4_cp1 where statis_date='20180228' 查询结果： name                id                    _c2 'account1'        1234         low salary 'account2'        1235         low salary 'account3'        1235         low salary 'account4'        1236         middle 'account5'        1237         very high

5. is null和is not null

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