Spanner与BigQuery的ROUND()函数返回非预期值问题咨询

问题原因 #

这个现象是IEEE 754双精度浮点数（即Spanner和BigQuery中默认的FLOAT64类型）的固有特性，不属于产品功能故障，也不是操作错误：

• 十进制小数在转换为二进制存储的FLOAT64时，大部分无法实现完全精确的表示，你测试用到的34.092136在转换为FLOAT64后本身就存在极其微小的精度偏差
• ROUND()函数是基于已经存在偏差的二进制值进行计算，最终就会出现你观测到的和十进制预期不符的舍入结果，因为Spanner和BigQuery的FLOAT64都遵循标准IEEE 754实现，所以测试结果完全一致。

解决方案 #

你可以通过以下几种方式获得符合十进制预期的计算结果：

1. 改用精确十进制类型计算
   Spanner和BigQuery都提供了十进制存储的NUMERIC类型（更高精度可选BIGNUMERIC），不存在二进制浮点数的精度损失，先将输入值转换为该类型后再做舍入即可：

-- 通用写法，Spanner/BigQuery均适用
SELECT ROUND(CAST(34.092136 AS NUMERIC), 8)
-- 输出结果为预期的34.092136

2. 浮点数微小偏移修正
   如果业务逻辑限制必须使用FLOAT64类型，可以在舍入前添加一个远小于计算精度的偏移量抵消精度误差，该方法仅适合临时应急使用：

SELECT ROUND(34.092136 + 1e-12, 8)

3. 输出时格式化处理
   如果仅需要符合预期的展示结果，可以直接使用字符串格式化函数指定小数位数，自动处理精度偏差：

-- Spanner写法
SELECT FORMAT("%.8f", 34.092136)
-- BigQuery写法同理
SELECT FORMAT("%0.8f", 34.092136)

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