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

结论：

在这种环境下没有“正确”或“不正确”的功效,它们都是正确的而且很洪流平上取决于实现(执行算术运算时行使的选项).

参考文献：

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!