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

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

 Name(Signature) Description Return Type 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差不多，都是处理单个列的判断查询结果

hive> select if(a=a,’bbbb’,111) fromlxw_dual;

bbbb

hive> select if(1<2,100,200) fromlxw_dual;

200

2.nvl对于null值的判断

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

1

3.非空查找函数: COALESCE

hive> select COALESCE(null,’aaa’,50)from lxw_dual;

aaa

3.条件件判断函数case  A when  B THEN C ,case和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是一起的，用于处理单个列的查询结果

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

 select *  from ( 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')  b 查询结果： 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

07-10 3万+

08-09 2万+
04-03 7万+
02-14 3253
07-18 4529
03-25 17万+
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie