If函数:if和case差不多,都是处理单个列的查询结果
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull
实际测试:
hive>
>
> select if(1=1,'bbbb',111) from default.dual limit 1;
OK
bbbb
Time taken: 0.362 seconds, Fetched: 1 row(s)
hive> select if(1<2,'bbbb',111) from default.dual limit 1;
OK
bbbb
Time taken: 0.159 seconds, Fetched: 1 row(s)
非空查找函数: COALESCE
语法: COALESCE(T v1, T v2, …)
返回值: T
说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL
hive> select COALESCE(null,'aaa',50)from default.dual limit 1;
OK
aaa
Time taken: 0.102 seconds, Fetched: 1 row(s)
hive> select COALESCE(1,'aaa',50)from default.dual limit 1;
OK
1
Time taken: 0.132 seconds, Fetched: 1 row(s)