判空函数之mysql中ifnull函数和hive中if函数及nvl函数介绍

た入场券 2022-12-07 12:03 485阅读 0赞

# 判空函数之mysql中ifnull函数和hive中if函数及nvl函数介绍 #

先说说，在mysql中，ifnull函数的用法，其表达式如下：

IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

举个应用场景，比如某一个字段定义为int类型，其默认值为0，但是在sql查询过程中，有可能出现为null，这个时候，我们就可以用ifnull来判断一下，如果结果为null,就给默认值0,下面给出一个实例：

## 1、MySQL的ifnull例子 ##

> MySQL \[testdb\]> select ifnull(22>333,11);  
> \+-------------------+  
> | ifnull(22>333,11) |  
> \+-------------------+  
> |                 0 |  
> \+-------------------+  
> 1 row in set (0.00 sec)
> 
> MySQL \[testdb\]> select ifnull(22<333,11);  
> \+-------------------+  
> | ifnull(22<333,11) |  
> \+-------------------+  
> |                 1 |  
> \+-------------------+  
> 1 row in set (0.00 sec)
> 
> MySQL \[testdb\]> select ifnull(null,11);  
> \+-----------------+  
> | ifnull(null,11) |  
> \+-----------------+  
> |              11 |  
> \+-----------------+  
> 1 row in set (0.00 sec)
> 
> MySQL \[testdb\]> select ifnull(66,11);  
> \+---------------+  
> | ifnull(66,11) |  
> \+---------------+  
> |            66 |  
> \+---------------+  
> 1 row in set (0.00 sec)

## 2、MySQL的if 例子 ##

> MySQL \[testdb\]> select if(1>2,3,4);  
> \+-------------+  
> | if(1>2,3,4) |  
> \+-------------+  
> |           4 |  
> \+-------------+  
> 1 row in set (0.00 sec)
> 
>   
> MySQL \[testdb\]> select if(1>2,3,5);  
> \+-------------+  
> | if(1>2,3,5) |  
> \+-------------+  
> |           5 |  
> \+-------------+  
> 1 row in set (0.00 sec)
> 
> MySQL \[testdb\]> select if(1<2,3,5);  
> \+-------------+  
> | if(1<2,3,5) |  
> \+-------------+  
> |           3 |  
> \+-------------+  
> 1 row in set (0.00 sec)
> 
> MySQL \[testdb\]> select if(1,3,5);  
> \+-----------+  
> | if(1,3,5) |  
> \+-----------+  
> |         3 |  
> \+-----------+  
> 1 row in set (0.00 sec)
> 
> MySQL \[testdb\]> select if(10,3,5);  
> \+------------+  
> | if(10,3,5) |  
> \+------------+  
> |          3 |  
> \+------------+  
> 1 row in set (0.00 sec)
> 
>   
> MySQL \[testdb\]> select if(0,3,5);  
> \+-----------+  
> | if(0,3,5) |  
> \+-----------+  
> |         5 |  
> \+-----------+  
> 1 row in set (0.00 sec)
> 
> MySQL \[testdb\]> select if(null,3,5);  
> \+--------------+  
> | if(null,3,5) |  
> \+--------------+  
> |            5 |  
> \+--------------+  
> 1 row in set (0.00 sec)

然而，**在hive写HQL的时候，ifnull函数是不存在的，但是，可以使用if(col is not null, col, default\_value)来实现ifnull的功能，如果col不为null，返回col的值，否则返回default\_value。**

那上面的实例就可以改成如下：

## 3、hive中不支持 ifnull的 ##

> hive> select ifnull(1>2,111);  
> FAILED: SemanticException \[Error 10011\]: Line 1:7 Invalid function 'ifnull'

## 4、hive中支持if的例子 ##

> hive> select if(1>3,11,33);  
> OK  
> 33  
> Time taken: 1.44 seconds, Fetched: 1 row(s)
> 
> hive> select if(1<3,11,33);  
> OK  
> 11  
> Time taken: 0.339 seconds, Fetched: 1 row(s)
> 
> hive> select if(22,11,33);  
> FAILED: SemanticException \[Error 10016\]: Line 1:10 Argument type mismatch '22': The first argument of function IF should be "boolean", but "int" is found
> 
> hive> select if(null,11,33);  
> OK  
> 33  
> Time taken: 0.324 seconds, Fetched: 1 row(s)  
> hive> select if(not null,11,33);  
> FAILED: ClassCastException org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableVoidObjectInspector cannot be cast to org.apache.hadoop.hive.serde2.objectinspector.primitive.BooleanObjectInspector

## 5、hive中支持nvl ##

> hive> select nvl(null,3);  
> OK  
> 3  
> Time taken: 0.302 seconds, Fetched: 1 row(s)  
> hive> select nvl(5,3);  
> OK  
> 5  
> Time taken: 0.311 seconds, Fetched: 1 row(s)

## 6、查看hive中所有函数和desc function函数定义 ##

show functions;  
1）、查看if函数的定义

> hive> desc function if;  
> OK  
> IF(expr1,expr2,expr3) - If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.  
> Time taken: 0.009 seconds, Fetched: 1 row(s)

### 2）、查看nvl函数的定义 ###

> hive> desc function nvl;  
> OK  
> nvl(value,default\_value) - Returns default value if value is null else returns value  
> Time taken: 0.006 seconds, Fetched: 1 row(s)

### 3）、查看其他函数的定义 ###

> hive> desc function collect\_list;  
> OK  
> collect\_list(x) - Returns a list of objects with duplicates  
> Time taken: 0.005 seconds, Fetched: 1 row(s)
> 
> hive> desc function collect\_set;  
> OK  
> collect\_set(x) - Returns a set of objects with duplicate elements eliminated  
> Time taken: 0.005 seconds, Fetched: 1 row(s)
> 
> hive> desc function year;  
> OK  
> year(param) - Returns the year component of the date/timestamp/interval  
> Time taken: 0.009 seconds, Fetched: 1 row(s)

## 7、desc function extended 获得函数定义和用法 ##

> hive> desc function extended case;  
> OK  
> CASE a WHEN b THEN c \[WHEN d THEN e\]\* \[ELSE f\] END - When a = b, returns c; when a = d, return e; else return f  
> Example:  
>  SELECT  
>  CASE deptno  
>    WHEN 1 THEN Engineering  
>    WHEN 2 THEN Finance  
>    ELSE admin  
>  END,  
>  CASE zone  
>    WHEN 7 THEN Americas  
>    ELSE Asia-Pac  
>  END  
>  FROM emp\_details  
> Time taken: 0.006 seconds, Fetched: 13 row(s)  
> hive> desc function case;  
> OK  
> CASE a WHEN b THEN c \[WHEN d THEN e\]\* \[ELSE f\] END - When a = b, returns c; when a = d, return e; else return f  
> Time taken: 0.006 seconds, Fetched: 1 row(s)  
>