Radino’s blog

Few months ago I needed an product aggregate function. It’s strange that Oracle does not have such functionality. I think it’s pretty trivial.
Here is it is:

CREATE OR REPLACE TYPE product_impl AS OBJECT
(
  product NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT product_impl) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT product_impl,
                                       VALUE IN NUMBER) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT product_impl,
                                     ctx2 IN product_impl) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT product_impl,
                                         returnvalue OUT NUMBER,
                                         flags       IN NUMBER) RETURN NUMBER
)
/

CREATE OR REPLACE TYPE BODY product_impl IS
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT product_impl) RETURN NUMBER IS
  BEGIN
    ctx := product_impl(1);
    RETURN ODCIConst.Success;
  END ODCIAggregateInitialize;

  MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT product_impl,
                                       VALUE IN NUMBER) RETURN NUMBER IS
  BEGIN
    IF VALUE IS NOT NULL THEN
      SELF.product := SELF.product * VALUE;
    END IF;
    RETURN ODCIConst.Success;
  END ODCIAggregateIterate;

  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT product_impl,
                                     ctx2 IN product_impl) RETURN NUMBER IS
  BEGIN
    SELF.product := SELF.product * ctx2.product;
    RETURN ODCIConst.Success;
  END ODCIAggregateMerge;

  MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT product_impl,
                                         returnvalue OUT NUMBER,
                                         flags       IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnvalue := SELF.product;
    RETURN ODCIConst.Success;
  END ODCIAggregateTerminate;
END;
/

CREATE OR REPLACE FUNCTION product(x IN NUMBER) RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING product_impl;
/

DROP TABLE prod_test;

CREATE TABLE prod_test(val number);

INSERT INTO prod_test VALUES (1);

INSERT INTO prod_test VALUES (2); 

INSERT INTO prod_test VALUES (3); 

INSERT INTO prod_test VALUES (4);

COMMIT;

SELECT product(val) FROM prod_test;

PRODUCT(VAL)
------------
          24

I have never mention it, but a user-defined aggregate function may be used also as an analytic function. This is an example for n! computation:

SELECT
  level,
  product(level) over(order by level) n_factorial
FROM dual
CONNECT BY LEVEL < 100;

     LEVEL N_FACTORIAL
---------- -----------
         1           1
         2           2
         3           6
         4          24
         5         120
         6         720
         7        5040
         8       40320
         9      362880
        10     3628800

There is another way how to compute a product without any additional aggregate function. This is possible by using a 2 simple logarithm rules. Let’s use natural logarithms:

1. ln(x*y) = ln(x)+ln(y)
2. x = exp(ln(x))

So to compute product we need only the SUM aggregate/analytic function LN function and EXP function:

x₁*x₂*…*x_n =(rule 2) exp(ln(x₁*x₂*…*x_n)) = (rule 1) exp(ln(x₁)+ln(x₂)+…+ln(x_n))

SELECT
  n,
  exp(n_log_sum) n_factorial
FROM (SELECT
            LEVEL n,
            SUM(ln(LEVEL)) over(ORDER BY LEVEL) n_log_sum
          FROM dual
          CONNECT BY LEVEL <= 10);

         N N_FACTORIAL
---------- -----------
         1           1
         2           2
         3           6
         4          24
         5         120
         6         720
         7        5040
         8       40320
         9      362880
        10     3628800

A word of caution here: You should test this logarithmic approach! I didn’t do it. It may be less precise, because you are computing natural logarithms and also you are adding this real numbers together. And also you should test the performance of this.
Maybe I’ll do it in some of my next post.

UPDATE [13.1.2011]
Of course, logarithm is not defined for non-positive values, so see this article how to implement product which is aware of this.

Tags: aggregation, product