SQL Server求和应为零却得到极小浮点值的问题排查

问题现象 #

在SQL Server中对QTY字段执行滚动求和（SUM() OVER子句）或直接SUM求和时，得到结果为-3.5527136788005E-15，但手动计算对应数据的总和应为0。

原因分析 #

• 浮点数精度误差：QTY字段被强制转换为float类型，而float属于二进制浮点类型，无法精确表示所有十进制小数（比如0.1这类数值在二进制中是无限循环的近似值）。当多个这样的微小误差累积后，原本应为0的总和就会出现极小的非零值（-3.5527136788005E-15本质是接近0的极小负数）。
• 运算放大误差：QTY的计算涉及乘法、除法的组合运算，每一步运算都会引入或放大浮点精度误差，最终求和时这些误差被集中显现出来。

解决思路 #

1. 替换为精确小数类型 #

将QTY的类型从float改为decimal（或numeric，SQL Server中二者功能一致），根据业务需求指定合适的精度和小数位数，确保计算结果能被精确存储：

CAST(
    (CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.AMOUNT
    * (CASE WHEN ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END)
    / (CASE WHEN ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END)
AS decimal(18,6)) QTY  -- 可根据实际业务调整精度和小数位

2. 对求和结果四舍五入 #

如果无法修改字段类型，可对最终求和结果进行四舍五入，将极小的误差值近似为0：

• 直接求和场景：

SELECT ROUND(SUM(QTY), 6)  -- 保留6位小数，可按需调整
FROM (...) XX 
WHERE CODE='91.KI10.0045'
  AND HAREKET_OZEL_KODU=''
  AND CAPIWHOUSE='4'

• 滚动求和场景：

SELECT
    XX.*,
    ROUND(SUM(QTY) OVER(order by DATE_, FICHENO, STFICHELNNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 6) as TOTALQTY
FROM (...) XX 
WHERE CODE='91.KI10.0045'
  AND HAREKET_OZEL_KODU=''
  AND CAPIWHOUSE='4'
ORDER BY DATE_

3. 调整运算顺序减少误差 #

在计算QTY时，尽量先执行乘法再做除法（业务逻辑允许的前提下），或者将除法转换为乘以倒数的形式，减少分步运算带来的误差累积。

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