AWS Athena中varchar转double计算体重差出现浮点精度异常求助

原因 #

• 二进制浮点的精度缺陷：double属于二进制浮点数类型，它只能精确表示有限的十进制小数。像0.1、0.2这类常见的十进制小数，在二进制中是无限循环的近似值，转换为double后本身就带有微小误差。当两个带误差的数值相减时，误差会被放大，最终输出超长的浮点小数结果。
• 字符串转double的额外误差：你的体重数据原本是varchar类型，转换为double时，即使原始字符串是精确的十进制数值，也可能因为二进制浮点的存储特性引入精度损失，进一步加剧计算后的误差。

解决办法 #

改用decimal（Athena中与numeric等价）类型进行转换和计算，它是专门为精确表示十进制数值设计的，完全适配体重这类需要精确小数的场景。

修改后的查询语句：

SELECT t1.user_id, t1.sample_id, t1.weight, 
  cast(t1.weight AS decimal(10,2)) - cast(t2.weight AS decimal(10,2)) AS weight_loss
FROM my_table t1
JOIN my_table t2 ON t1.user_id = t2.user_id AND t1.sample_id - 1 = t2.sample_id
ORDER BY t1.user_id, t1.sample_id

其中decimal(10,2)表示总位数为10、小数位为2，你可以根据实际体重的数值范围调整参数（比如体重是三位数加一位小数，就用decimal(4,1)）。

如果只是想格式化输出结果，也可以用round()函数对计算结果取整，但这种方式只是表面处理，不如直接用decimal类型转换从根源上解决精度问题。

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