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

判空函数之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)