SQL统计：如何在COUNT去重时排除空值？

问题描述 #

你当前使用以下SQL统计唯一人员、配偶及子女数量，但当配偶或子女姓名为空时，这些空值会被错误计入统计，不符合预期：

select
    count(distinct(ssn))
    , count (distinct(ssn + spousename))
    , count distinct((ssn + spousename + kidname))

测试数据：

SSN        Person Spouse Child 

111-11.... John             (no spouse no kid) 

222-22.....Jane  Jim   Jack   

333-33.....Jerry       Jack (no spouse) 
333-33.....Jerry       Jill (no spouse second kid) 

444-44..... John Judy       (no kid)

预期结果：统计4个唯一人员、2位非空配偶、3名非空子女（仅统计非空的唯一组合）。

解决方案 #

通过case语句判断字段是否为空，为空时返回null（count(distinct)会自动忽略null值），仅对非空字段进行组合去重统计。修改后的SQL如下：

select
    count(distinct SSN) as unique_persons,
    count(distinct case when Spouse is not null then concat(SSN, Spouse) end) as unique_spouse_entries,
    count(distinct case when Child is not null then concat(SSN, Child) end) as unique_child_entries
from your_table_name;

说明 #

• 唯一人员统计：直接使用count(distinct SSN)即可，因为SSN作为人员标识不会为空。
• 配偶统计：仅当Spouse非空时，才将SSN与配偶姓名组合后去重统计，空值记录会被排除。
• 子女统计：仅当Child非空时，将SSN与子女姓名组合后去重统计，空值记录会被排除。

执行上述查询后，将得到符合预期的结果：4个唯一人员、2位配偶、3名子女。

内容的提问来源于stack exchange，提问作者MYTWO