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。