运行指定SQL代码时cases_percentage字段返回NULL的技术求助

运行以下SQL查询时，cases_percentage字段始终返回NULL值：

SELECT   location,
         date,
         population,
         total_cases,
         (try_cast(total_cases as DECIMAL(10,2))/nullif(try_cast(population AS DECIMAL(10,2)),0))*100.0 AS cases_percentage
FROM     portfolioproject..coviddeaths
WHERE    location ='India'
ORDER BY 1,
         2 

已尝试上述代码，但问题依旧。

排查与解决方案 #

1. 优先修正数据精度问题

印度人口超过13亿，而DECIMAL(10,2)的最大取值为99999999.99，无法容纳10位数的人口数据，导致TRY_CAST(population AS DECIMAL(10,2))返回NULL，最终整个计算式结果为NULL。

调整精度到足够大的范围，比如DECIMAL(15,2)或DECIMAL(18,2)：

SELECT   location,
         date,
         population,
         total_cases,
         (TRY_CAST(total_cases AS DECIMAL(15,2))/NULLIF(TRY_CAST(population AS DECIMAL(15,2)),0))*100.0 AS cases_percentage
FROM     portfolioproject..coviddeaths
WHERE    location ='India'
ORDER BY 1,2

2. 处理字段空值或非数字内容

• 如果total_cases存在NULL或空字符串，TRY_CAST会返回NULL，可通过COALESCE设置默认值：

  SELECT   location,
           date,
           population,
           total_cases,
           (TRY_CAST(COALESCE(total_cases, '0') AS DECIMAL(15,2))/NULLIF(TRY_CAST(population AS DECIMAL(15,2)),0))*100.0 AS cases_percentage
  FROM     portfolioproject..coviddeaths
  WHERE    location ='India'
  ORDER BY 1,2
  

• 若population本身为NULL，同样用COALESCE处理：

  SELECT   location,
           date,
           population,
           total_cases,
           (TRY_CAST(COALESCE(total_cases, '0') AS DECIMAL(15,2))/NULLIF(TRY_CAST(COALESCE(population, '0') AS DECIMAL(15,2)),0))*100.0 AS cases_percentage
  FROM     portfolioproject..coviddeaths
  WHERE    location ='India'
  ORDER BY 1,2
  

3. 排查无法转换的异常数据

查询哪些记录的total_cases或population无法转为数字：

-- 检查total_cases异常记录
SELECT location, date, total_cases
FROM portfolioproject..coviddeaths
WHERE location = 'India' 
  AND TRY_CAST(total_cases AS DECIMAL(15,2)) IS NULL
  AND total_cases IS NOT NULL
  AND total_cases != ''

-- 检查population异常记录
SELECT location, date, population
FROM portfolioproject..coviddeaths
WHERE location = 'India' 
  AND TRY_CAST(population AS DECIMAL(15,2)) IS NULL
  AND population IS NOT NULL
  AND population != ''

找到异常数据后，可根据业务需求清洗数据或跳过这类记录。

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