plsql cumulative multiplication

Question

I have a query that looks like this:

SELECT SUM(CAPITAL_RETURN) OVER (PARTITION BY PORTF_CODE, 
                                 EXTRACT(MONTH FROM END_DATE) ORDER BY END_DATE
                                 ) AS CUM_CAPITAL_RET,
 FROM
 ....

This is fine for creating a cumulative sum column, but I now need to create a cumulative multiplication column.

Is this possible?

Thanks

Answer 1

1. You could write a user-defined aggregate function. Here is an example:

   SQL> create or replace type t_multiply as object
     2    (
     3    total number,
     4  
     5    static function ODCIAggregateInitialize(nctx IN OUT t_multiply )
     6    return number,
     7  
     8    member function ODCIAggregateIterate(self IN OUT t_multiply ,
     9                                         value number)
    10    return number,
    11  
    12    member function ODCIAggregateTerminate(self IN t_multiply,
    13                                           retVal OUT  number,
    14                                           flags IN number)
    15    return number,
    16  
    17    member function ODCIAggregateMerge(self IN OUT t_multiply,
    18                                       ctx2 IN t_multiply)
    19    return number
    20  )
    21  /
   
    Type created
   
    SQL> create or replace type body t_multiply
      2  is
      3  
      4   static function ODCIAggregateInitialize(nctx IN OUT t_multiply)
      5   return number
      6   is
      7   begin
      8     nctx := t_multiply(1);
      9     return ODCIConst.Success;
     10   end;
     11  
     12   member function ODCIAggregateIterate(self IN OUT t_multiply,
     13                                        value IN number)
     14   return number
     15   is
     16  
     17   begin
     18    total := total * value;
     19    return ODCIConst.Success;
     20   end;
     21  
     22   member function ODCIAggregateTerminate(self IN t_multiply,
     23                                          retVal OUT number,
     24                                          flags IN number)
     25   return number
     26   is
     27   begin
     28     retval := total;
     29     return ODCIConst.Success;
     30   end;
     31  
     32   member function ODCIAggregateMerge(self IN OUT t_multiply,
     33                                      ctx2 IN t_multiply)
     34   return number
     35   is
     36   begin
     37     return ODCIConst.Success;
     38   end;
     39  end;
     40  /
   
     Type body created
   
     SQL> CREATE OR REPLACE FUNCTION multiply(input in number)
       2  RETURN number
       3  PARALLEL_ENABLE AGGREGATE USING t_multiply;
       4  /
   
     Function created
   

   Demonstration:

     SQL> with t1(col1, col2) as(
       2    select 1, 1 from dual union all
       3    select 1, 2 from dual union all
       4    select 1, 3 from dual union all
       5    select 1, 4 from dual
       6  )
       7  select col1
       8       , sum(col2) over(partition by col1 order by col2) as sum1
       9       , multiply(col2) over(partition by col1 order by col2) as mult
      10    from t1
      11  ;
   
        COL1       SUM1       MULT
      ---------- ---------- ----------
         1          1          1
         1          3          2
         1          6          6
         1         10         24 
   

2. You could use model clause to achieve desired result:

    SQL> with t1(col1, col2) as(
      2    select 1, 1 from dual union all
      3    select 1, 2 from dual union all
      4    select 1, 3 from dual union all
      5    select 1, 4 from dual
      6  )
      7  select c1
      8       , col2
      9       , mult
     10   from t1
     11   model
     12   partition by (col1 as c1)
     13   dimension by (row_number() over(order by col2) rn)
     14   measures(col2, 1 as mult)
     15   rules(
     16     mult[rn] = nvl(mult[cv() - 1], 1) * col2[cv()]
     17   )
     18  ;
   
        C1       COL2       MULT
     ---------- ---------- ----------
        1          1          1
        1          2          2
        1          3          6
        1          4         24
   

Answer 2

To put @DavidAldridge's suggestion into code (and also taking care to handle negative values) might look something like this:

WITH T AS
(
    SELECT 2 AS X FROM DUAL
    UNION ALL
    SELECT -3 AS X FROM DUAL
    UNION ALL
    SELECT 4 AS X FROM DUAL
)
SELECT
    CASE
        -- If there are any zeroes, then the result is zero.
        WHEN MAX(CASE WHEN X = 0 THEN 1 END) > 0 THEN 0
        -- Otherwise, if there is an even (or zero) number of 
        -- negative numbers, the result is positive.
        WHEN MOD(COUNT(CASE WHEN X < 0 THEN 1 END), 2) = 0 THEN 1
        -- Otherwise, the result is negative.
        ELSE -1
    END
    *
    EXP
    (
        SUM
        (
            CASE
                WHEN X = 0 THEN 0
                ELSE LN(ABS(X))
            END              
        )
    )
FROM
    T;

One thing to watch out for, though, is that there may be a greater risk of rounding errors with this method than with methods (like those ones suggested by @NicholasKrasnov) that use multiplication directly.

Answer 3

The classical way of doing this is by taking the exponent of the sum of the logarithms of the values, taking care to handle negatives zeroes correctly.