前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >注意!SQL中的NULL

注意!SQL中的NULL

作者头像
数据森麟
发布2021-01-08 14:30:46
8750
发布2021-01-08 14:30:46
举报
文章被收录于专栏:数据森麟

越发觉得取数之前的“预处理”非常重要,其中最核心的一点是检查数据的准确性。大的方向有两种,其一,确认数据本身无错乱,其二,保障取数业务逻辑准确。

第一种比较繁琐、耗时,但是好处理(习惯对结果做一下统计值分布可以减少很多异常)。第二种不是SQL执行过程中报错,而是返回的结果和你需要的不太一样。今天主要聊一下取数分析中容易忽略的点,尤其是SQL中的NULL值。

1、空值JOIN时导致数据丢失

创建案例数据表
代码语言:javascript
复制
CREATE TABLE IF NOT EXISTS tmp_test_3
(
id_1 INT,
col_1 VARCHAR(255),
col_2 VARCHAR(255)
);


CREATE TABLE IF NOT EXISTS tmp_test_4
(
id_2 INT,
col_3 VARCHAR(255),
col_4 VARCHAR(255)
);


INSERT INTO tmp_test_3(id_1, col_1, col_2) VALUES (1,'a',null);
INSERT INTO tmp_test_3(id_1, col_1, col_2) VALUES (2,'b','join_key_1');
INSERT INTO tmp_test_3(id_1, col_1, col_2) VALUES (3,'c','join_key_2');


INSERT INTO tmp_test_4(id_2, col_3, col_4) VALUES (1,'a',null);
INSERT INTO tmp_test_4(id_2, col_3, col_4) VALUES (2,'b','join_key_1');
INSERT INTO tmp_test_4(id_2, col_3, col_4) VALUES (3,'c','join_key_2');

查看下tmp_test_3、tmp_test_4两个案例表的数据(分布是类似的)

现在有个业务,部分数据存在tmp_test_3表,有一些存在tmp_test_4表,假设要得到两个表中的数据,需要这两个表按col_2、col_4列JOIN连接。

代码语言:javascript
复制
SELECT 
  * 
FROM 
  tmp_test_3 t_a 
Left JOIN 
  tmp_test_4 t_b 
on 
  t_a.col_2 = t_b.col_4 ;

执行一下上面的语句,会得到什么结果。

结果显示是col_2和col_4为空的数据是丢失了的。

为什么?

直接说原因:在tmp_test_3和tmp_test_4表中用于join的列存在NULL值,而NULL和任何值做比较都是返回的NULL(即不能对NULL进行!=、=、>、<等判断,返回是NULL)。

Coalesce真香函数,将空值替换成一个默认值。

代码语言:javascript
复制
SELECT 
  * 
FROM 
  tmp_test_3 t_a 
JOIN 
  tmp_test_4 t_b 
on 
  COALESCE(t_a.col_2 ,'aaa')=  COALESCE(t_b.col_4 ,'aaa')  

这样就可以把tmp_test_3中包含NULL的数据记录和tmp_test_4表中的NULL数据记录JOIN起来。但是这里有个小问题是他会把这些NULL记录全部匹配,所以实际应用中可以按照业务需求来做取舍。

2、聚合运算时遇到NULL值

以下是教导主任的302班学生数学成绩表,对应了学生名字和成绩。

代码语言:javascript
复制
CREATE TABLE IF NOT EXISTS tmp_score_baoqi_1
(
col_name VARCHAR(255),
col_core int
);

INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('a',null);
INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('b',86);
INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('c',78);
INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('d',65);  

你验证数据的时候发现有学生a的成绩是空的(没参加考试),心里美滋滋的预处理并且开始执行如下脚本。

代码语言:javascript
复制
SELECT
 avg(IFNULL(col_core, 0 )  ) 
FROM
 tmp_score_baoqi_1
  
-- 返回57.2500

结果返回:这学期教导主任的302班学生数学平均成绩是57.25分,四舍五入为58分,不及格。

很好,执行结果也出来了,也不报错,但是教导主任却生气了,质疑怎么可能他的班上学生数学成绩不及格,需要你核查。

经过排查你发现,原来你做预处理的时候把没参加考试的学生a缺少的数学成绩也算在内,用数值0代替NULL,严重影响了最终成绩。

这个小例子想说明的就是做聚合运算时要注意NULL值,一定要清楚count、sum、avg函数对NULL的处理:

avg

代码语言:javascript
复制
SELECT
 avg(col_core),avg(IFNULL(col_core, 0 )  ) 
FROM
 tmp_score_baoqi_1
  
-- 返回76.33、57.2500

count

代码语言:javascript
复制
SELECT
 count(1),count(*),count(col_core)
FROM
 tmp_score_baoqi_1
  
-- 返回4、4、3

sum:可以对单个列求和,也可以对多个列运算后求和忽略NULL值,且当对多个列运算求和时,如果运算的列中任意一列的值为NULL,则忽略这行的记录。

补充一条,DISTINCT、ORDER BY、GROUP BY 遇到NULL值视为相等,较好理解,不做数据测试。

请务必注意细节,大概率能决定成败,本节完。

代码语言:javascript
复制
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-01-01,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据森麟 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1、空值JOIN时导致数据丢失
    • 创建案例数据表
    • 2、聚合运算时遇到NULL值
    领券
    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档