Oracle SQL舍入不当行为

我遇到了使用Oracle sql进行binary_double舍入的奇怪行为. binary_double值应该根据 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 ),(round( cast (0.1000065 as binary_double ),6)) from dual
  union
  select 3,(round( cast (0.2000075 as binary_double ),(round( cast (0.2000065 as binary_double ),6)) from dual
  union
  select 4,(round( cast (0.3000075 as binary_double ),(round( cast (0.3000065 as binary_double ),6)) from dual
  union
  select 5,(round( cast (0.4000075 as binary_double ),(round( cast (0.4000065 as binary_double ),6)) from dual
  union
  select 6,(round( cast (0.5000075 as binary_double ),(round( cast (0.5000065 as binary_double ),6)) from dual
  union
  select 7,(round( cast (0.6000075 as binary_double ),(round( cast (0.6000065 as binary_double ),6)) from dual
  union
  select 8,(round( cast (0.7000075 as binary_double ),(round( cast (0.7000065 as binary_double ),6)) from dual
  union
  select 9,(round( cast (0.8000075 as binary_double ),(round( cast (0.8000065 as binary_double ),6)) from dual
  union
  select 10,(round( cast (0.9000075 as binary_double ),(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 ),(round( cast (0.100065 as binary_double ),5)) from dual
  union
  select 13,(round( cast (0.200075 as binary_double ),(round( cast (0.200065 as binary_double ),5)) from dual
  union
  select 14,(round( cast (0.300075 as binary_double ),(round( cast (0.300065 as binary_double ),5)) from dual
  union
  select 15,(round( cast (0.400075 as binary_double ),(round( cast (0.400065 as binary_double ),5)) from dual
  union
  select 16,(round( cast (0.500075 as binary_double ),(round( cast (0.500065 as binary_double ),5)) from dual
  union
  select 17,(round( cast (0.600075 as binary_double ),(round( cast (0.600065 as binary_double ),5)) from dual
  union
  select 18,(round( cast (0.700075 as binary_double ),(round( cast (0.700065 as binary_double ),5)) from dual
  union
  select 19,(round( cast (0.800075 as binary_double ),(round( cast (0.800065 as binary_double ),5)) from dual
  union
  select 20,(round( cast (0.900075 as binary_double ),(round( cast (0.900065 as binary_double ),5)) from dual;

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

SELECT (round( CAST (0.0000065 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位数,其中二进制表示中的一系列数字开始不同,但是如何在这样的早期阶段看到差异？

解决方法

让我们看看第一个例子,因为其他的几乎相似：

双精度IEEE 754中的0.0000075表示为7.50000000000000019000643072808E-6

0.0000065表示为6.49999999999999959998360846147E-6

当你将两者都四舍五入时 – 前者变为8e-6,后者变为6e-6

没有“一致”的行为,因为不同的数字被分解为2的除数.

因此,即使你做SELECT 0.0000065 FROM DUAL并且看到0.0000065结果 – 它不是以二进制形式在内部表示的,它已经“破碎”并且比这个数字少了一小部分.然后在输出格式化期间为您舍入.

双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小于5

1.00006499999999998173905169097E-1 == 0.100006499999999998173905169097舍入6到0.100006,因为下一个数字是4,即小于5.我看到与实际结果的差异.老实说,我这里没有很好的解释.我怀疑这是一个神谕问题,因为：

> 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

Oracle SQL舍入不当行为

解决方法

猜你在找的Oracle相关文章