SQL中的重复值与null值处理

数据准备

-- 建表
CREATE TABLE IF NOT EXISTS TEST01.A
(
     ID                      VARCHAR(50)               COMMENT 'ID号'       -- 01
    ,NUMS                    INT                       COMMENT '数字'       -- 02
    ,NAME                    VARCHAR(50)               COMMENT '名字'       -- 03

)
COMMENT 'A表'
STORED AS PARQUET
;

-- 插数
INSERT INTO TEST01.A (ID,NUMS,NAME) VALUES ('01',1,NULL);
INSERT INTO TEST01.A (ID,NUMS,NAME) VALUES ('02',2,'');
INSERT INTO TEST01.A (ID,NUMS,NAME) VALUES ('03',3,'c');

-- 删数
DELETE FROM TEST01.A WHERE ID = '04';
-- 删表
DROP TABLE IF EXISTS TEST01.A;

null空值处理

空值 ‘’

1. 只有字符型字段可以插入’‘，数值型字段无法插入’’
2. ''可以认为是正常的字符
3. 注意关联字段为‘’的情况

空值 null

count与distinct

SELECT COUNT(`NAME`) FROM TEST01.A;                                 -- 2 不算null 但算‘’
SELECT COUNT(DISTINCT `NAME`)  FROM TEST01.A;                       -- 2 不算null 但算‘’
SELECT DISTINCT `NAME` FROM TEST01.A;                               -- 3 null ''都算
SELECT `NAME`  FROM TEST01.A GROUP BY `NAME`;                       -- 3 null ''都算
SELECT COUNT(1) FROM (SELECT `NAME`  FROM TEST01.A)T;               -- 3 null ''都算
SELECT COUNT(1) FROM (SELECT DISTINCT `NAME` FROM TEST01.A)T;       -- 3 null ''都算
SELECT COUNT(`NAME`) FROM (SELECT DISTINCT `NAME` FROM TEST01.A)T;  -- 2 不算null 但算‘’

count 不保留空值mull，保留重复值。（针对空值null处理，去掉空值null）
distinct 保留空值null，所有重复空值合并，所有重复值合并。（针对重复值处理，合并重复值）
count+distinct 不保留空值null，所有重复值合并。（针对空值null处理，去掉空值null；针对重复值处理，合并重复值）

综上：只要count(字段名)，会忽略null

union与distinct

-- 01
SELECT `NAME` FROM TEST01.A
UNION
SELECT `NAME` FROM TEST01.B;                 -- 3 null ''都算
-- 02
SELECT DISTINCT `NAME` FROM TEST01.A
UNION
SELECT DISTINCT `NAME` FROM TEST01.B;        -- 3 null ''都算
-- 03
SELECT COUNT(1) FROM (                       -- 3 null ''都算
SELECT DISTINCT `NAME` FROM TEST01.A
UNION
SELECT DISTINCT `NAME` FROM TEST01.B         -- 3 null ''都算
)T;

sum与null

sum(列) 是对所有列的值求和。

• 如果没查到数据，sum的值为null
• 如果查到的数据这一列值为null，sum的值为null
• 如果查到数据有null，也有不是null的，那么sum的值为所有非空值的和

重复值处理

group by、distinct与row_number互换

• 下面2种写法结果一样

-- 写法01
SELECT DISTINCT ID,`NAME`
FROM A;

-- 写法02
SELECT ID,`NAME`
FROM A
GROUP BY ID,`NAME`;

• 下面2种写法结果一样

-- 写法01
SELECT COUNT(DISTINCT ID)
FROM A;

-- 写法02
SELECT COUNT(1)
(SELECT ID
FROM A
GROUP BY ID
) B
WHERE B.ID IS NOT NULL;

• 下面3种写法结果不同

-- 写法01
SELECT COUNT(DISTINCT ID)
FROM A;

-- 写法02 与 写法01 意义不同，但有其他用途
SELECT COUNT(ID)
FROM A
GROUP BY ID;

-- 写法03
-- 可以找出那些重复出现的行id
SELECT ID,COUNT(ID)
FROM A
GROUP BY ID
HAVING COUNT(ID)>1;

• 下面2种写法结果一样

-- 写法01
SELECT COUNT(DISTINCT ID)
FROM A;

-- 写法02
SELECT COUNT(1)
(SELECT ID
FROM A
GROUP BY ID
) B
WHERE B.ID IS NOT NULL;

• 下面2种写法结果一样

-- 写法01
SELECT COUNT(DISTINCT COALESCE(ID,'NULL'))
FROM A;

-- 写法02
SELECT COUNT(1)
(SELECT ID
FROM A
GROUP BY ID
) B;

• 下面2种写法结果一样

-- 写法01
-- 会显示出所有去重后的id
-- count(id)=0，表示该id为null
SELECT ID,COUNT(ID) FROM A GROUP BY ID;

-- 写法02
-- 显示去重后的id，以及此时id对应的name
SELECT ID,`NAME` FROM
(SELECT ID,`NAME`,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN FROM A
) B
WHERE RN = 1;