sql - Oracle SQL 舍入不当行为

Question

我遇到了 binary_double 的奇怪行为使用 Oracle SQL 进行舍入。 binary_double值应四舍五入 half even根据 documentation ，但是在使用以下查询进行测试时，似乎存在一些不一致之处。 以下所有查询都应给出相同的最后一位数字 ，分别为 0.x00008 和 0.x00006(四舍五入为 6 位)或 0.x0008 和 0.x0006(四舍五入为 5 位)，其中 x 位于 (0,1,2,3,4,5,6, 7,8,9)。 问题是他们没有 .任何有助于理解为什么舍入结果取​​决于分隔符点后的第一个数字和/或原始数字中的位数的任何帮助，我们表示感谢。

select 1,(round( cast (0.0000075 as binary_double ) ,6)), (round( cast (0.0000065 as binary_double ) ,6)) from dual
  union
  select 2,(round( cast (0.1000075 as binary_double ) ,6)), (round( cast (0.1000065 as binary_double ) ,6)) from dual
  union
  select 3,(round( cast (0.2000075 as binary_double ) ,6)), (round( cast (0.2000065 as binary_double ) ,6)) from dual
  union
  select 4,(round( cast (0.3000075 as binary_double ) ,6)), (round( cast (0.3000065 as binary_double ) ,6)) from dual
  union
  select 5,(round( cast (0.4000075 as binary_double ) ,6)), (round( cast (0.4000065 as binary_double ) ,6)) from dual
  union
  select 6,(round( cast (0.5000075 as binary_double ) ,6)), (round( cast (0.5000065 as binary_double ) ,6)) from dual
  union
  select 7,(round( cast (0.6000075 as binary_double ) ,6)), (round( cast (0.6000065 as binary_double ) ,6)) from dual
  union
  select 8,(round( cast (0.7000075 as binary_double ) ,6)), (round( cast (0.7000065 as binary_double ) ,6)) from dual
  union
  select 9,(round( cast (0.8000075 as binary_double ) ,6)), (round( cast (0.8000065 as binary_double ) ,6)) from dual
  union
  select 10,(round( cast (0.9000075 as binary_double ) ,6)), (round( cast (0.9000065 as binary_double ) ,6)) from dual
  union
  select 11,(round( cast (0.000075 as binary_double ) ,5)), (round( cast (0.000065 as binary_double ) ,5)) from dual
  union
  select 12,(round( cast (0.100075 as binary_double ) ,5)), (round( cast (0.100065 as binary_double ) ,5)) from dual
  union
  select 13,(round( cast (0.200075 as binary_double ) ,5)), (round( cast (0.200065 as binary_double ) ,5)) from dual
  union
  select 14,(round( cast (0.300075 as binary_double ) ,5)), (round( cast (0.300065 as binary_double ) ,5)) from dual
  union
  select 15,(round( cast (0.400075 as binary_double ) ,5)), (round( cast (0.400065 as binary_double ) ,5)) from dual
  union
  select 16,(round( cast (0.500075 as binary_double ) ,5)), (round( cast (0.500065 as binary_double ) ,5)) from dual
  union
  select 17,(round( cast (0.600075 as binary_double ) ,5)), (round( cast (0.600065 as binary_double ) ,5)) from dual
  union
  select 18,(round( cast (0.700075 as binary_double ) ,5)), (round( cast (0.700065 as binary_double ) ,5)) from dual
  union
  select 19,(round( cast (0.800075 as binary_double ) ,5)), (round( cast (0.800065 as binary_double ) ,5)) from dual
  union
  select 20,(round( cast (0.900075 as binary_double ) ,5)), (round( cast (0.900065 as binary_double ) ,5)) from dual;

底线是这个:
为什么在以下查询中，两个值之间存在差异:

SELECT (round( CAST (0.0000065 AS BINARY_DOUBLE ) ,6)), (round( cast (0.1000065 as binary_double ) ,6)) FROM dual;

遵循@zerkms 的建议，我 convert将数字转换为二进制格式，然后我得到:

0.0000065 -> 6.49999999999999959998360846147E-6
0.1000065 -> 1.00006499999999998173905169097E-1

查询将此四舍五入到 6 位数字。令人惊讶的是，对我来说，我看到四舍五入的结果是:

0.0000065 -> 0.000006 (execute the query above to see this)
0.1000065 -> 0.100007 (execute the query above to see this)

这是为什么？我可以理解，如果我尝试四舍五入到 > 12 位，二进制表示中的一系列数字开始不同，但是为什么在这么早的阶段差异变得可见？

Answer 1

让我们看一下第一个示例，因为其他示例非常相似:
0.0000075在 double IEEE 754 中表示为 7.50000000000000019000643072808E-60.0000065显示为 6.49999999999999959998360846147E-6
当您将两者都四舍五入时 - 前者变为 8e-6 ，后者6e-6
没有“一致”行为，因为不同的数字被分解为 2 的除数不同。

所以，即使你这样做 SELECT 0.0000065 FROM DUAL并查看 0.0000065结果 - 这不是它在内部以二进制形式表示的方式，它已经“ splinter ”并且比那个数字少了一小部分。然后在输出格式期间为您四舍五入。

双IEEE 754提供15-16 significant digits .因此，出于输出目的，它们变为:7.500000000000000e-6和 6.499999999999999e-6舍入为 6.5e-6
UPD :
6.49999999999999959998360846147E-6 == 0.00000649999999999999959998360846147 .如果将其四舍五入 6 - 它等于 0.000006 ，因为后面跟着 4小于 51.00006499999999998173905169097E-1 == 0.100006499999999998173905169097由 6 舍入到 0.100006 ，因为下一个数字是 4 ，即小于 5 .我看到了与实际结果的差异。老实说，我在这里没有很好的解释。我怀疑这是一个 oracle 问题，因为:

C#“按预期”运行:http://ideone.com/Py9aer

Go 也“按预期”运行:http://ideone.com/OEJBoA

Python 也“按预期”运行:http://ideone.com/I0ADOR

Javascript(在控制台中):parseFloat(0.1000065).toFixed(6) // 0.100006

UPD 2 :

在与 Skype 聊天中的同事进行了更多研究之后，我得到了一个很好的例子，结果取决于所选择的舍入模式:

flock.core> (import '[org.apache.commons.math3.util Precision])

flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_CEILING)
0.100007
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_DOWN)
0.100006
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_UP)
0.100007
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_HALF_DOWN)
0.100006
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_HALF_EVEN)
0.100006
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_HALF_UP)
0.100007
flock.core> (Precision/round (Double. 0.1000065) 6 BigDecimal/ROUND_FLOOR)
0.100006

结论 :

在这种情况下没有“正确”或“不正确”的结果，它们都是正确的，并且在很大程度上取决于实现(+执行算术运算时使用的选项)。

引用:

在线十进制到 IEEE 754 双转换器:0.0000065和 0.0000075

http://en.wikipedia.org/wiki/Floating_point#Internal_representation