也不算总结帖,至多算个小结帖
由近期看到的帖子想到的,本主题只讨论数字
1、Oracle的计算精度是多少?
Oracle的数字精度最多是38位,计算精度当然也就这么多了,否则如何表示计算结果?呵呵
Oracle对数字可用number(p, s)来统一表示,相关基础知识请看此贴: http://www.itpub.net/viewthread. ... p%3Bfilter%3Ddigest
2、Oracle计算误差是怎么产生的?
计算误差多数情况下是由于进制转换产生的,这跟Oracle没有关系,是计算机与生俱来就存在的现象
众所周知,计算机存储最终是表现为一堆0和1,也就是二进制表示法
对于任何一个十进制整数,无论正负,我们总是很容易将其表示为一个二进制的数
但是对于一个小数,就没那么简单了:十进制0.5可以轻松表示为二进制的0.1,但是十进制的0.1,二进制该怎么表示?
因为0.1=1/power(2,4)+1/power(2,5)+1/power(2,8)+.......,转换为二进制就是0.00011001.......
一个十进制的有限不循环小数,在二进制里竟然成了一个无限小数了
而由于精度的限制,这个“无限”不可能一直进行下去,必然会出现截断
这就会导致Oracle用一个非常接近十进制0.1的二进制数去表示十进制的0.1,这就产生了误差
这会导致在一些计算中,该误差就会显现出来,而在另外一些计算中,误差有可能出现相互抵消,从而不会表现出来
3、误差如何处理?
误差避免是很难的,我们需要做的,实际上是对误差的修正
Oracle对此提供的函数有round、trunc、floor和ceil,不过后二者只能返回整数,所以这里我们着重关注前二者
在帖子 http://www.itpub.net/thread-1345933-1-1.html 中提到的问题,就是由于精度造成的误差
在这里可以估计到,的确是由于精度的误差累积,造成了结果不一致
> select round(n,2) x, n,round(n/1.17*1.17,2) y, n-14.595 c1, n/1.17*
1.17-14.595 c2 from (select 1.39*10.5 n from dual);
X N Y C1 C2
---------- ---------- ---------- ---------- ----------
14.6 14.595 14.59 0 -1.000E-38
已用时间: 00: 00: 00.01
从这里可以看出,在 /1.17*1.17 后,确实产生了误差
round(n, 2),实际上是看小数点后第三位到底是啥
在 /1.17*1.17 后,小数点后第三位还是5么?
> select round(n,3) x, trunc(n,3) tx, n, round(n/1.17*1.17,3) y, trunc
(n/1.17*1.17,3) ty, n-14.595 c1, n/1.17*1.17-14.595 c2 from (select 1.39*10.5 n
from dual);
X TX N Y TY C1 C2
---------- ---------- ---------- ---------- ---------- ---------- ----------
14.595 14.595 14.595 14.595 14.594 0 -1.000E-38
已用时间: 00: 00: 00.01
可以看到,Y在trunc后,小数点后是4,所以在round到小数点后第二位的时候,4自然而然就被舍去了
对于这里所提到的精度造成的误差问题,暂时还没想到解决办法,上面所做的,仅仅是解释
但是在其他一些场合,用round可以防止误差造成的影响,例如:
如果一个数是阶乘数,那么显示1,否则,显示0.
通常,我们会这么做:
首先,10以内的阶乘可以这么算(不包括10):
> select power(10,sum(logrn)over(order by logrn)) jc from (select log
(10,rownum) logrn from dual connect by rownum<10);
JC
----------
1
2
6
24
120
720
5040
40320
362880
已选择9行。
简单起见,我们按题目意思,先检测10以内哪些数是阶乘数
> select n.num, count(j.jc) flag from (select power(10,sum(logrn)over
(order by logrn)) jc from (select log(10,rownum) logrn from dual connect by rownum
<10)) j right outer join (select rownum num from dual connect by rownum<10) n
on j.jc=n.num group by n.num order by n.num;
NUM FLAG
---------- ----------
1 1
2 1
3 0
4 0
5 0
6 0
7 0
8 0
9 0
已选择9行。
嘿,1和2是阶乘数,但6却不是了,为毛?好意外哦!
老办法,我们用减法来做个检测,由于阶乘是整数,因而可以辅助使用floor和ceil来判断
> select jc-6, floor(jc), ceil(jc) from (select power(10,sum(logrn))
jc from (select log(10,rownum) logrn from dual connect by rownum<4));
JC-6 FLOOR(JC) CEIL(JC)
---------- ---------- ----------
2.0000E-38 6 7
可见,阶乘的计算是存在一定误差的
trunc也可以检测,但必须设置相应合理的截断处才能看到效果,如下:
> select jc-6 m, trunc(jc,4)-6 t4, trunc(jc,40)-6 t40 from (select power(10,sum(logrn)) jc from (select log(10,rownum) logrn from dual connect by rownum<4));
M T4 T40
---------- ---------- ----------
2.0000E-38 0 2.0000E-38
知道了这些信息后,我们就可以从容处理刚才意外的地方了
> select n.num, count(j.jc) flag from (select round(power(10,sum(logrn)
over(order by logrn))) jc from (select log(10,rownum) logrn from dual connect
by rownum<10)) j right outer join (select rownum num from dual connect by rownum
<10) n on j.jc=n.num group by n.num order by n.num;
NUM FLAG
---------- ----------
1 1
2 1
3 0
4 0
5 0
6 1
7 0
8 0
9 0
已选择9行。
关于此处阶乘的算法,更多的请见http://www.itpub.net/viewthread.php?tid=434561
4、小结
知其然也知其所以然,是为学习之道。希望从此贴中能给你带来一些方法与思路,从而不会被今后类似的问题所困扰。
各个函数的使用详见Oracle的手册,没手册的可以去搜索。最后这个例子的sql也许有些难懂,但没关系,理解思路就行了
没事多在itpub Oracle开发版遛达遛达,仨月后你也就是高手了
[本帖最后由 lastwinner 于 2010-9-8 00:21 编辑]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29867/viewspace-672923/,如需转载,请注明出处,否则将追究法律责任。