Oracle中实现连乘（二）

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

Oracle中实现连乘（一）：http://yangtingkun.itpub.net/post/468/466369

 

 

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

网友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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-604867/