SQL查询COVID死亡占比时Death_percentage列全为0的问题排查

你遇到的问题是整数除法导致的取整结果：如果total_deaths和total_cases都是整数类型，SQL执行除法运算时会直接舍弃小数部分，只保留整数。比如3/100会得到0，再乘以100还是0。

修正方案 #

可以通过以下几种方式解决：

1. 将其中一个操作数转换为浮点类型 #

在除法前把total_deaths或total_cases转换成浮点型，强制SQL执行浮点除法：

SELECT
    date,
    location,
    total_cases,
    total_deaths,
    (CAST(total_deaths AS FLOAT)/total_cases)*100 AS Death_percentage
FROM CovidDeaths
ORDER BY 1,2

MySQL环境下也可以用更简洁的方式：

SELECT
    date,
    location,
    total_cases,
    total_deaths,
    (total_deaths * 1.0 / total_cases)*100 AS Death_percentage
FROM CovidDeaths
ORDER BY 1,2

2. 先乘100再做除法 #

先将分子乘以100，再除以分母，避免整数取整问题：

SELECT
    date,
    location,
    total_cases,
    total_deaths,
    (total_deaths * 100)/total_cases AS Death_percentage
FROM CovidDeaths
ORDER BY 1,2

额外注意 #

如果total_cases存在0值，需要处理除以0的错误，可使用NULLIF规避：

SELECT
    date,
    location,
    total_cases,
    total_deaths,
    (CAST(total_deaths AS FLOAT)/NULLIF(total_cases, 0))*100 AS Death_percentage
FROM CovidDeaths
ORDER BY 1,2

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