SQL查询电商流失数据集Null值返回0的问题求助

问题背景 #

我在SQL中查询电商流失（Ecommerce Churn）数据集的Null值，确认数据集中存在Null值，但查询结果始终返回0。已两次重新上传数据验证更新是否正确，结果依旧。在Python中可正常显示Null值，但SQL查询存在问题，查询代码如下：

SELECT 'Tenure' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE Tenure IS NULL
UNION
SELECT 'WarehouseToHome' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE warehousetohome IS NULL
UNION
SELECT 'HourSpendonApp' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE hourspendonapp IS NULL
UNION
SELECT 'OrderAmountHikeFromLastYear' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE orderamounthikefromlastyear IS NULL 
UNION
SELECT 'CouponUsed' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE couponused IS NULL 
UNION
SELECT 'OrderCount' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE ordercount IS NULL 
UNION
SELECT 'DaySinceLastOrder' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE daysincelastorder IS NULL;

潜在原因排查 #

• 非标准Null值存在：Python识别的"Null"可能是字符串形式（比如'NULL'、'NaN'、空字符串''），而非SQL原生的NULL值。可以先查询这些类Null值：

  SELECT 'Tenure' as ColumnName, 
         COUNT(CASE WHEN Tenure IS NULL OR Tenure = 'NULL' OR Tenure = 'NaN' OR TRIM(Tenure) = '' THEN 1 END) AS NullLikeCount
  FROM ecomm.eccomchurn
  UNION ALL
  SELECT 'WarehouseToHome' as ColumnName, 
         COUNT(CASE WHEN WarehouseToHome IS NULL OR WarehouseToHome = 'NULL' OR WarehouseToHome = 'NaN' OR TRIM(WarehouseToHome) = '' THEN 1 END) AS NullLikeCount
  FROM ecomm.eccomchurn;
  -- 其他列可按此格式替换查询
  

• 列名/表名匹配问题：部分SQL数据库（如PostgreSQL）对带引号的列名区分大小写，确认你的列名拼写、大小写与表结构完全一致。可以查询表结构验证：

  -- MySQL/MariaDB
  DESCRIBE ecomm.eccomchurn;
  -- PostgreSQL
  SELECT column_name FROM information_schema.columns WHERE table_schema = 'ecomm' AND table_name = 'eccomchurn';
  

• 数据类型转换异常：如果列是数值类型，Python中的NaN在导入SQL时可能被自动转为0或其他默认值，而非NULL。可以查询数值列的异常值：

  SELECT 'Tenure' as ColumnName, COUNT(*) AS ZeroCount FROM ecomm.eccomchurn WHERE Tenure = 0;
  

• 操作对象不匹配：确认查询的ecomm.eccomchurn是Python中查看的同一张表，可能存在同名的测试表或不同环境下的表。

优化查询建议 #

可以用更高效的方式一次性统计所有列的Null值，避免多次UNION：

SELECT 
  'Tenure' AS ColumnName, COUNT(*) - COUNT(Tenure) AS NullCount
FROM ecomm.eccomchurn
UNION ALL
SELECT 'WarehouseToHome', COUNT(*) - COUNT(WarehouseToHome)
FROM ecomm.eccomchurn
UNION ALL
SELECT 'HourSpendonApp', COUNT(*) - COUNT(HourSpendonApp)
FROM ecomm.eccomchurn
UNION ALL
SELECT 'OrderAmountHikeFromLastYear', COUNT(*) - COUNT(OrderAmountHikeFromLastYear)
FROM ecomm.eccomchurn
UNION ALL
SELECT 'CouponUsed', COUNT(*) - COUNT(CouponUsed)
FROM ecomm.eccomchurn
UNION ALL
SELECT 'OrderCount', COUNT(*) - COUNT(OrderCount)
FROM ecomm.eccomchurn
UNION ALL
SELECT 'DaySinceLastOrder', COUNT(*) - COUNT(DaySinceLastOrder)
FROM ecomm.eccomchurn;

注：COUNT(column)会自动忽略NULL值，用总行数减去非NULL行数即可得到NULL数量

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