BigQuery中幂运算转BigNumeric后减法结果不符合预期

1. power()默认返回浮点数，存不下超大整数的精确值 #

几乎所有SQL引擎里，power(2, 63)返回的都是双精度浮点数，这种类型的有效精度只有53位二进制位。简单说：

• 大于2^53的整数，双精度浮点数没法精确区分相邻值，比如2^63和2^63 -1在浮点数里是同一个数。
• 所以你算power(2,63)-1的时候，结果和power(2,63)完全一样，转成bignumeric也救不回来，自然减法得0而不是预期的10000。

2. 你搞反了运算和转类型的顺序 #

你的写法是先做浮点数减法power(2,63)-1，再转bignumeric——但精度已经丢失了，转类型根本没用。正确姿势是先把power()的结果转成bignumeric，再做减法，这样全程用高精度数值运算，不会丢精度。

修正后的代码 #

select '2^63 - 1', cast(power(2, 63) as bignumeric) - 1 union all
select '2^63', cast(power(2, 63) as bignumeric) union all
select '2^63 with diff of 10k', cast(power(2, 63) as bignumeric) - (cast(power(2, 63) as bignumeric) - 10000) union all
select '2^64 - 1', cast(power(2, 64) as bignumeric) - 1 union all
select '2^64', cast(power(2, 64) as bignumeric) union all
select '2^64 - 10000', cast(power(2, 64) as bignumeric) - 10000 union all
select '2^64 with diff of 10k', cast(power(2, 64) as bignumeric) - (cast(power(2, 64) as bignumeric) - 10000)

更稳妥的替代方案 #

如果你的SQL引擎支持，直接写2^63的精确数值（比如9223372036854775808）来替代power()函数，彻底绕开浮点数的坑，比如：

select '2^63 -1', cast(9223372036854775808 as bignumeric) -1

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