BigQuery中ROUND函数输出存在非精确舍入问题咨询

这个问题其实是浮点数二进制存储的固有特性加上BigQuery分布式执行的特性共同导致的，我来给你拆解清楚：

1. 浮点数的精度本质问题 #

首先，weight_pounds字段是FLOAT64类型（你可以用SELECT typeof(weight_pounds) FROM bigquery-public-data.samples.natality LIMIT 1验证）。这类二进制浮点数没办法精确表示所有十进制小数，比如你要保留两位小数的0.01，在二进制里是一个无限循环的小数，存储的时候只能近似表示。

当你对单个weight_pounds执行ROUND(...,2)时，看起来是得到了两位小数，但底层存储的还是一个近似值。当你对这些近似值做SUM的时候，微小的误差会被累积放大，最终就会出现.XX000001或者.XX99999这类看起来“没舍干净”的结果。

2. 分布式执行导致误差位置不固定 #

BigQuery是分布式计算引擎，每次执行查询时，可能会把数据拆分到不同的worker节点上并行计算，然后再汇总结果。不同的执行计划下，数据的分组、求和顺序会发生变化——而浮点数的加法是不满足结合律的（因为误差的累积顺序不同），所以每次执行后，误差会出现在不同的数值上，之前正常的数值可能这次又出现误差，反之亦然。

怎么解决这个问题？ #

如果需要精确的十进制小数计算，建议改用NUMERIC类型来处理：

• 先把weight_pounds转换为NUMERIC，再做ROUND和SUM：

SELECT 
  year,month,day, 
  sum(round(CAST(weight_pounds AS NUMERIC),2))as total_pounds, 
  count(*) as cnt 
FROM `bigquery-public-data.samples.natality` 
group by 1,2,3 
order by 1,2,3

NUMERIC是十进制浮点类型，专门用来精确表示十进制小数，能完美避免二进制浮点数的精度问题，求和后就会得到精确的两位小数结果。

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