ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中实现连乘(二)

Oracle中实现连乘(二)

原创 Linux操作系统 作者:yangtingkun 时间:2009-06-01 23:02:50 0 删除 编辑

Oracle有连加的聚集函数SUM,但是没有一个连乘的函数。

Oracle中实现连乘(一):http://yangtingkun.itpub.net/post/468/466369

 

 

在上一篇文章的最后给出了利用指数和对数的方法,配合简单的SUM就得到了连乘的结果。上文也提到了,这种方法唯一的缺点是,在处理大数据量计算的时候,可能由于LOGPOWER运算,而引入一些小的误差。

网友raiseup.net提出置疑,认为“直接用SQL函数组合的话,精度损失不是唯一的缺点,比较要命的是无法处理0和负数的数据。”

首先从数学的角度讲,这种看法是正确的,不过这个问题其实很容易就可以通过变通的方法来解决。

先说0的问题,只要连乘的数中包含了0,那么最终的结果一定是0,基于这一点考虑,修改SQL如下:

SQL> WITH T AS (SELECT ROWNUM RN FROM TAB)
  2  SELECT POWER(10, SUM(LOG(10, RN))) MULTI
  3  FROM T;

     MULTI
----------
    362880

SQL> WITH T AS (SELECT ROWNUM - 1 RN FROM TAB)
  2  SELECT POWER(10, SUM(LOG(10, RN))) MULTI
  3  FROM T;
SELECT POWER(10, SUM(LOG(10, RN))) MULTI
                             *
2 行出现错误:
ORA-01428:
参数 '0' 超出范围


SQL> WITH T AS (SELECT ROWNUM RN FROM TAB)
  2  SELECT DECODE(SUM(DECODE(RN, 0, 1, 0)), 0, 1, 0)
  3   * POWER(10, SUM(LOG(10, DECODE(RN, 0, 1, RN)))) MULTI
  4  FROM T;

     MULTI
----------
    362880

SQL> WITH T AS (SELECT ROWNUM - 1 RN FROM TAB)
  2  SELECT DECODE(SUM(DECODE(RN, 0, 1, 0)), 0, 1, 0)
  3   * POWER(10, SUM(LOG(10, DECODE(RN, 0, 1, RN)))) MULTI
  4  FROM T;

     MULTI
----------
         0

只需要对0进行简单的处理,就可以解决问题。查询的被乘数的含义是:对0的个数进行SUM,如果SUM的结果是0,说明连乘的数中不包含0,则被乘数为1,不改变乘数的结果。如果SUM的结果不为0,说明连乘的数中包含了一个以上的0,那么这时将被乘数设置为0,不管后面计算的结果是什么,最终的结果都返回0,需要注意,由于0不能进行LOG操作,因此在后面处理的时候需要进行转变。

对于负数的处理其实也很简单,只需要记录将负数按照正数来处理,并记录出现负数的次数就可以了:

SQL> WITH T AS (SELECT ROWNUM - 2 RN FROM TAB)
  2  SELECT DECODE(SUM(DECODE(RN, 0, 1, 0)), 0, 1, 0)
  3   * POWER(10, SUM(LOG(10, DECODE(RN, 0, 1, RN)))) MULTI
  4  FROM T;
 * POWER(10, SUM(LOG(10, DECODE(RN, 0, 1, RN)))) MULTI
                         *
3 行出现错误:
ORA-01428:
参数 '-1' 超出范围


SQL> WITH T AS (SELECT ROWNUM - 2 RN FROM TAB)
  2  SELECT POWER(-1, MOD(SUM(DECODE(SIGN(RN), -1, 1, 0)), 2))
  3   * DECODE(SUM(DECODE(RN, 0, 1, 0)), 0, 1, 0)
  4   * POWER(10, SUM(LOG(10, DECODE(RN, 0, 1, ABS(RN))))) MULTI
  5  FROM T
  6  ;

     MULTI
----------
         0

SQL> WITH T AS (SELECT ROWNUM - 2 RN FROM TAB)
  2  SELECT POWER(-1, MOD(SUM(DECODE(SIGN(RN), -1, 1, 0)), 2))
  3   * DECODE(SUM(DECODE(RN, 0, 1, 0)), 0, 1, 0)
  4   * POWER(10, SUM(LOG(10, DECODE(RN, 0, 1, ABS(RN))))) MULTI
  5  FROM T
  6  WHERE RN != 0;

     MULTI
----------
     -5040

SQL> WITH T AS (SELECT ROWNUM - 3 RN FROM TAB)
  2  SELECT POWER(-1, MOD(SUM(DECODE(SIGN(RN), -1, 1, 0)), 2))
  3   * DECODE(SUM(DECODE(RN, 0, 1, 0)), 0, 1, 0)
  4   * POWER(10, SUM(LOG(10, DECODE(RN, 0, 1, ABS(RN))))) MULTI
  5  FROM T
  6  WHERE RN != 0;

     MULTI
----------
      1440

第一个被乘数就是计算连乘的数字中,负数出现的个数,并根据这个个数判断最终结果的正负,而在计算连乘的时候,则只计算数值绝对值的连乘,不考虑正负数。

其实就是对上一篇文章中SQL的一个简单的变形,这篇文章单独写出来,目的其实是想说明最基础的东西有了,如何利用基础的东西来实现自己的功能。

SQL实现如此,DBA不少的工作也是如此,Oracle提供了大量的基础的功能,DBA如何根据Oracle提供的功能来进行定制、修改,最终实现自己想要的功能,这不仅需要DBA对于Oracle提供的基础功能有详细的了解,还需要DBA自己大胆设想,做到Tom提到的think out of the box

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-604867/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10470274