hive中if和coalesce 去除null值,case when

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

###################################### if usage

select * from (select *,if (b.name is null ,true,false ) as bo from test1 a  left join test2 b on a.name =b.name ) t3;


t3.name t3.age  t3._col2        t3.id   t3.bo
lucy    18      NULL    NULL    true
lily    10      lily    1       false
jim     16      NULL    NULL    true
henry   19      NULL    NULL    true
Time taken: 11.266 seconds, Fetched: 4 row(s)


####################################### COALESCE usage
select * from (select *, COALESCE(b.name is null ,true ) as bool from test1 a  left join test2 b on a.name =b.name ) t3;
OK
t3.name t3.age  t3._col2        t3.id   t3.bool
lucy    18      NULL    NULL    true
lily    10      lily    1       false
jim     16      NULL    NULL    true
henry   19      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 are 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 the values in the list are 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 that you are comparing to the list of conditions. (ie: condition1, condition2, ... cond
itionn).


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


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


IF no condition is found to be true, then the case statement will return the value in the ELSE clause. If the ELSE clause is omitted
 and no condition is found to be true, then the case statement will return NULL


Example:


CASE Fruit
  WHEN 'APPLE' THEN '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 '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END
FROM    Table_A
GROUP BY CASE country
WHEN '中国'     THEN '亚洲'
WHEN '印度'     THEN '亚洲'
WHEN '日本'     THEN '亚洲'
WHEN '美国'     THEN '北美洲'
WHEN '加拿大'  THEN '北美洲'
WHEN '墨西哥'  THEN '北美洲'
ELSE '其他' END;

同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下； 

SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM    Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;