SQL基础能力-SQL中的null处理

chankay
2,118 阅读5分钟

这是我参与8月更文挑战的第5天，活动详情查看：8月更文挑战

SQL中的null值处理经常是ETL中最常见的异常处理方式。首先由于数据源可能来自不同的业务域，各域的业务开发在定义表结构的时候通常都会允许null值的存在，一个是在业务上存在意义，另外一种干脆就是作为default值直接写入数据。这就要求数据开发以及测试同学在实际的开发测试过程中，需要对null值的业务含义进行判断，或者进行特殊处理。

SQL 中的 NULL 值是什么？

NULL 在数据库中表示缺失值或未知值。业务开发在定义表结构的时候通常都会允许null值的存在，一个是在业务上存在意义,甚至是建表的时候默认设置的默认值。另外，在SQL中使用聚合函数，若匹配不到数据，则结果也会存在null值的情况。如

SELECT SUM(1) from DUAL WHERE 1 =2;

在实际开发中，遇到如上的不满足条件的情况下，产生的null或许会直接影响后续计算，也需要根据情况进行处理。值得注意的是空值不是任何特定数据类型的一部分，它是一种灵活的数据类型，代表什么都没有，可以放在任何数据类型的列中，无论是 string、int、blob 还是 CLOB 数据类型。

为了克服这个问题，我们通常在ET数据处理过程中就进行检查，针对无意义的空值进行过滤或者替换操作。

etl脚本中经常可见如下的null值处理。

select 
       ifnull(product_category, '') as product_category,
       ifnull(user_name_create, '') as user_name_create,
       ifnull(user_name_picking, '') as user_name_picking,
       ifnull(user_name_package, '') as user_name_package,
       ifnull(user_name_knight, '') as user_name_knight,
from table

SQL 中的 NULL 处理

功能说明
ISNULL()帮助我们用所需的值替换 NULL 值。 
IFNULL()允许我们在值为 NULL 时返回第一个值，否则返回第二个值。 
COALESCE()帮助我们返回参数中的第一个非空值。 
NVL()帮助用用户提供的所需值替换 NULL 值。

在现在的SQL中基本由上面四种处理方式，我们先来看看最常用的ifnull

ifnull

ifnull 接受两个参数，其中第一个参数为判断的字段或者表达式，而第二个参数为要替换的值。当第一个参数的值不为null是，则返回其值。若计算结果为null，则返回第二个参数。

mysql 中使用

mysql> SELECT IFNULL(`name`,'kong') as `name` from (select 1 as `id`,'test' as `name` UNION SELECT 2 as `id`,null as `name`) t;
+------+
| name |
+------+
| test |
| kong |
+------+
2 rows in set (0.00 sec)

这里我们可以看到所有与NULL对应的值都被自动替换为'kong'

spark 中使用

spark-sql> SELECT IFNULL(`name`,'kong') as `name` from (select 1 as `id`,'test' as `name` UNION SELECT 2 as `id`,null as `name`) t;
test
kong
Time taken: 6.339 seconds, Fetched 2 row(s)

flink 中使用 由于flink 中无法直接使用如上语句来定义表，所以通过连接数据表来进行展示

Flink SQL> select * from testsuit;
+----+-------------+--------------------------------+
| op | testsuit_id |                     project_id |
+----+-------------+--------------------------------+
| +I |          72 |                             26 |
| +I |          73 |                             26 |
| +I |          74 |                             26 |
| +I |          75 |                             26 |
| +I |          76 |                             26 |
| +I |          77 |                             26 |
| +I |         116 |                             26 |
| +I |         119 |                             28 |
| +I |         127 |                              1 |
| +I |         129 |                              1 |
| +I |         130 |                           null |
+----+-------------+--------------------------------+
Received a total of 11 rows

使用ifnull进行替换查看输出

image.png

怎么回事？null怎么没有被过滤？认真检查一下数据库，原来在进行复制的时候，是我手动写入了个null值，此时的null是字符串‘null’而非真正的一无所有。在重新设置以后

image.png

这么一来，ifnull在spark、flink、MySQL中的用法一致。

ISNULL

isnull 主要接收一个参数，其可以是具体字段，也可以是表达式，判断参数的结果若为null，则返回1（true），非null则返回0(false)。具体的差异请看下列具体案例

依然使用之前的案例来查看结果

MySQL

image.png

spark

image.png

flink 中不支持次函数

image.png

这里需要注意，isnull可能由于场景较低，在三中引擎中的支持度都不一样。flink完全不支持该函数，而spark中返回的值是布尔类型，MySQL则返回了0、1来区分结果。

COALESCE

coalesce 可以接受多个参数，其用法为返回其参数中第一个非空表达式。如果所有参数均为 NULL，则 COALESCE 返回 NULL。这时候需要稍微修改一下我们的测试语句

SELECT coalesce(`name`,null,'3') as `name` from (select 1 as `id`,'test' as `name` UNION SELECT 2 as `id`,null as `name`) t;

返回结果如下

image.png

可以看到，连续过滤了null值，将3的值返回。

spark中使用

image.png

flink 中使用

image.png

coalesce中所有参数均为null的情况

image.png

image.png

image.png

NVL

nvl 与ifnull 功能类型，也是接受两个参数，当第一个值非null，则返回第一个参数， 当第一个值为null，则返回第二个表达式的值

MySQL中不支持nvl函数

spark 使用

image.png

flink中也不支持该函数

image.png

总结：

本文主要介绍了在现有的ETL中，null值的出现是难以避免的，如何加以小心的处理成为后续清洗工作的重点。在此之外还讨论了一些函数，如 IFNULL()、ISNULL()、COALESCE() 和 NVL()，它们对 NULL 值进行操作。借助每个函数的示例，我们已经了解了用法和语法以及不同殷勤的不同支持度。

  1. ifnull 三种引擎均支持，且效果一致。
  2. isnull 三种引擎的支持程度不一致，MySQL与spark返回类型不一样，而flink直接不支持。
  3. coalesce 三种引擎支持度一样。
  4. nvl作为Oracle的方言，在三种引擎中只有spark支持了。
avatar
文章
阅读
粉丝