SQLite中GROUP BY分组后百分比计算结果异常问题排查

问题场景 #

处理SQLite数据库中的碰撞事件数据集，需按日期统计每日重伤人数占受伤总人数的百分比。首先成功统计每日重伤和受伤总数的SQL语句：

SELECT collision_date, SUM(severe_injury_count),SUM(injured_victims)
FROM collisions c
GROUP BY collision_date
LIMIT 50,100;

但尝试计算百分比时，多条语句均返回0或NULL，比如：

SELECT 
   collision_date, 
   SUM(severe_injury_count/injured_victims) AS chance_being_sever_injured,
   SUM(severe_injury_count),
   SUM(injured_victims),
   (severe_injury_count/injured_victims)*100,
   (SUM(severe_injury_count)/SUM(injured_victims))*100
FROM collisions c 
GROUP BY collision_date;

已做排查操作 #

• 确认字段类型为INT，尝试CAST转换类型
• 排除NULL值、用COALESCE将NULL替换为0
• 补充统计每日记录数确认数据有效性
• 转换collision_date字段类型
• 尝试使用FORMAT函数格式化结果

以上操作均未解决问题，百分比结果仍为0或NULL。

问题根源 #

误将SQLite语法与MySQL完全等同，忽略了两者在整数运算规则上的差异：SQLite中整数间的除法会直接截断小数部分，导致小于1的结果被转为0。

正确解法 #

在计算前将其中一个数值转换为浮点型，触发浮点运算即可得到正确百分比。两种常用写法：

写法1：通过CAST转换类型 #

SELECT 
   collision_date,
   SUM(severe_injury_count) AS total_severe,
   SUM(injured_victims) AS total_injured,
   (CAST(SUM(severe_injury_count) AS REAL) / CAST(SUM(injured_victims) AS REAL)) * 100 AS severe_injury_percent
FROM collisions c
WHERE injured_victims != 0 -- 避免除零错误
GROUP BY collision_date;

写法2：用1.0触发浮点转换（简化版） #

SELECT 
   collision_date,
   SUM(severe_injury_count) AS total_severe,
   SUM(injured_victims) AS total_injured,
   (SUM(severe_injury_count) * 1.0 / SUM(injured_victims)) * 100 AS severe_injury_percent
FROM collisions c
WHERE injured_victims != 0
GROUP BY collision_date;

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