注意！SQL中的NULL

大家好，我是寶器！

越發(fā)覺得取數(shù)之前的“預(yù)處理”非常重要，其中最核心的一點是檢查數(shù)據(jù)的準確性。大的方向有兩種，其一，確認數(shù)據(jù)本身無錯亂，其二，保障取數(shù)業(yè)務(wù)邏輯準確。

第一種比較繁瑣、耗時，但是好處理（習(xí)慣對結(jié)果做一下統(tǒng)計值分布可以減少很多異常）。第二種不是SQL執(zhí)行過程中報錯，而是返回的結(jié)果和你需要的不太一樣。今天主要聊一下取數(shù)分析中容易忽略的點，尤其是SQL中的NULL值。

1、空值JOIN時導(dǎo)致數(shù)據(jù)丟失

創(chuàng)建案例數(shù)據(jù)表

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兩個案例表的數(shù)據(jù)（分布是類似的）

現(xiàn)在有個業(yè)務(wù)，部分數(shù)據(jù)存在tmp_test_3表，有一些存在tmp_test_4表，假設(shè)要得到兩個表中的數(shù)據(jù)，需要這兩個表按col_2、col_4列JOIN連接。

SELECT?
??*?
FROM?
??tmp_test_3?t_a?
Left?JOIN?
??tmp_test_4?t_b?
on?
??t_a.col_2?=?t_b.col_4?;

執(zhí)行一下上面的語句，會得到什么結(jié)果。

結(jié)果顯示是col_2和col_4為空的數(shù)據(jù)是丟失了的。

為什么？

直接說原因：在tmp_test_3和tmp_test_4表中用于join的列存在NULL值，而NULL和任何值做比較都是返回的NULL（即不能對NULL進行！=、=、>、<等判斷，返回是NULL）。

Coalesce真香函數(shù)，將空值替換成一個默認值。

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的數(shù)據(jù)記錄和tmp_test_4表中的NULL數(shù)據(jù)記錄JOIN起來。但是這里有個小問題是他會把這些NULL記錄全部匹配，所以實際應(yīng)用中可以按照業(yè)務(wù)需求來做取舍。

2、聚合運算時遇到NULL值

以下是教導(dǎo)主任的302班學(xué)生數(shù)學(xué)成績表，對應(yīng)了學(xué)生名字和成績。

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);??

你驗證數(shù)據(jù)的時候發(fā)現(xiàn)有學(xué)生a的成績是空的（沒參加考試），心里美滋滋的預(yù)處理并且開始執(zhí)行如下腳本。

SELECT
?avg(IFNULL(col_core,?0?)??)?
FROM
?tmp_score_baoqi_1
??
--?返回57.2500

結(jié)果返回：這學(xué)期教導(dǎo)主任的302班學(xué)生數(shù)學(xué)平均成績是57.25分，四舍五入為58分，不及格。

很好，執(zhí)行結(jié)果也出來了，也不報錯，但是教導(dǎo)主任卻生氣了，質(zhì)疑怎么可能他的班上學(xué)生數(shù)學(xué)成績不及格，需要你核查。

經(jīng)過排查你發(fā)現(xiàn)，原來你做預(yù)處理的時候把沒參加考試的學(xué)生a缺少的數(shù)學(xué)成績也算在內(nèi)，用數(shù)值0代替NULL，嚴重影響了最終成績。

這個小例子想說明的就是做聚合運算時要注意NULL值，一定要清楚count、sum、avg函數(shù)對NULL的處理：

avg：

SELECT
?avg(col_core),avg(IFNULL(col_core,?0?)??)?
FROM
?tmp_score_baoqi_1
??
--?返回76.33、57.2500

count：

SELECT
?count(1),count(*),count(col_core)
FROM
?tmp_score_baoqi_1
??
--?返回4、4、3

sum：可以對單個列求和，也可以對多個列運算后求和忽略NULL值，且當(dāng)對多個列運算求和時，如果運算的列中任意一列的值為NULL，則忽略這行的記錄。

補充一條，DISTINCT、ORDER BY、GROUP BY 遇到NULL值視為相等，較好理解，不做數(shù)據(jù)測試。

請務(wù)必注意細節(jié)，大概率能決定成敗，本節(jié)完。