hive中if和coalesce 去除null值,case when
来源:互联网 发布:golang sleep 编辑:程序博客网 时间:2024/06/05 10:39
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 countryWHEN '中国' THEN '亚洲'WHEN '印度' THEN '亚洲'WHEN '日本' THEN '亚洲'WHEN '美国' THEN '北美洲'WHEN '加拿大' THEN '北美洲'WHEN '墨西哥' THEN '北美洲'ELSE '其他' ENDFROM Table_AGROUP BY CASE countryWHEN '中国' THEN '亚洲'WHEN '印度' THEN '亚洲'WHEN '日本' THEN '亚洲'WHEN '美国' THEN '北美洲'WHEN '加拿大' THEN '北美洲'WHEN '墨西哥' THEN '北美洲'ELSE '其他' END;
同样的,我们也可以用这个方法来判断工资的等级,并统计每一等级的人数。SQL代码如下;
SELECTCASE 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_AGROUP BYCASE 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;
阅读全文
0 0
- hive中if和coalesce 去除null值,case when
- hive中if和coalesce 去除null值,case when
- case when 和 coalesce
- hive中使用case、if:一个region统计业务(hive条件函数case、if、COALESCE语法介绍:CONDITIONAL FUNCTIONS IN HIVE)
- hive 的条件判断(if、coalesce、case)
- hive 的条件判断(if、coalesce、case)
- hive 的条件判断(if、coalesce、case)
- hive 的判断条件(if、coalesce、case)
- hive 的条件判断(if、coalesce、case)
- sql控制流程语句case when/if/ifnull/null if
- sqlserver CASE WHEN 中 NULL 值判断方法
- count, sum和 if,case when
- MySQL upate if和case when语法
- 2、if、case when和循环语句
- is null nvl coalesce || case when then else end dbms_random.value() escape
- hive中null和'','NULL'
- oracle中if/else、decode函数、case when
- mysql中case、when、if、then的用法
- Maven的安装并在IDEA环境中用maven打包部署
- PCA算法原理介绍
- ESP8266网页配置参数——html
- 实用Redis操作类
- 初识Linux下的C语言
- hive中if和coalesce 去除null值,case when
- Windows下搭建python2.7 爬虫框架Scrapy
- Netflix是如何实现视频安全下载离线播放的?(上)
- package-cleanup
- 结构体数据类型
- 单机redis基本安装
- 不得不知 云计算入门必备的60条术语
- 初识C程序与Linux交互
- 备忘:访问多网段多路由