If and coalesce in hive remove null value, case when

Ref http://blog.csdn.net/mtj66/article/details/52629876

###################################### If usage select * FROM (select *,IF (B.name was null, True,false) as Bo from Test1 A left joins Test2 B on A.name =b.name) T3;


T3.name t3.age t3._col2 t3.id t3.bo
Lucy + NULL NULL True
Lily Lily 1 False
Jim + NULL NULL True
Henry NULL null True
Time taken:11.266 seconds, Fetched:4 row (s)


####################################### COALESCE Usage
SELECT * FROM (SELECT *, COALESCE (B.name are null, true) as bool from Test1 a left joins Test2 B on A.name =b.name) T3;
Ok
T3.name t3.age t3._col2 t3.id t3.bool
Lucy + NULL NULL True
Lily Lily 1 False
Jim + NULL NULL True
Henry NULL null True
Time taken:10.651 seconds, Fetched:4 row (s)




CONDITIONAL FUNCTIONS in HIVE
Hive supports three types of conditional functions. These functions is listed below:

IF (Test Condition, True value, False value)
The IF condition evaluates the "test condition" and IF the "test condition" is true, then it returns the "true Value". Otherwise, it
Returns the False Value.
Example:if (1=1, ' working ', ' not working ') returns ' working '

COALESCE (Value1,value2,...)


The COALESCE function returns the fist not NULL value from the list of values. If all of the values in the list is NULL, then it retur
NS NULL.
EXAMPLE:COALESCE (null,null,5,null,4) returns 5

Case Statement

The syntax for the case statement is:
case [Expression]
When Condition1 then RESULT1
When Condition2 then RESULT2
...
When Conditionn then RESULTN
ELSE result
END
Here expression is optional. It is the value of comparing to the list of conditions. (Ie:condition1, Condition2, ... cond
Itionn).


All the conditions must is of same datatype. Conditions is evaluated in the order listed. Once a condition is found to be true, the
Case statement would return the result and not evaluate the conditions any further.


All the results must is of same datatype. This is the value returned once a condition are found to be true.


IF no condition is found to being true, then the case statement would return the value in the ELSE clause. If the ELSE clause is omitted
And no condition is found to being true, then the case statement would return NULL


Example:


Case Fruit
When ' Apple ' and ' the owner is Apple '
When "orange" then "the owner is orange"
ELSE ' It is another Fruit '
END
The other form of case is


Case
When Fruit = "Apple" then "the owner is Apple"
When Fruit = "Orange" Then "the owner is orange"
ELSE ' It is another Fruit '

END

SELECT  SUM (population), case country when '
China ' Then ' Asia ' when '
India ' then     ' Asia ' when '
Japan ' Then     ' Asia '
When ' us ' then ' North America ' when '
Canada ' then ' North America ' when
' Mexico ' then  ' North America '
Else ' other ' END
from< C16/>table_a GROUP by case country when '
China ' Then ' Asia ' when '
India ' then     ' Asia ' when
' Japan ' Then     ' Asia '
When ' us ' then ' North America ' when
' Canada ' Then ' North America ' when
' Mexico ' then  ' North America ' else '
other ' END;

Similarly, we can use this method to judge the salary level, and to count the number of each level. The SQL code is as follows;

SELECT case when
salary <= "1" when
salary > Salary <= and  "2" when
salary > Salary <=  3 ' when
salary > Salary <= and ' 4 '
ELSE NULL END Salar Y_class,
COUNT (*) from table_a GROUP by case when
salary <= and ' 1 ' when
salary > and salary <=  2 ' when
salary > Salary <=, then  ' 3 ' when
salary > Salary <= and 4 '
ELSE NULL END;