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) ------------ 24I 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 3628800There 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:

- ln(x*y) = ln(x)+ln(y)
- x = exp(ln(x))
So to compute product we need only the SUM aggregate/analytic function LN function and EXP function:

x

_{1}*x_{2}*…*x_{n}=(rule 2) exp(ln(x_{1}*x_{2}*…*x_{n})) = (rule 1) exp(ln(x_{1})+ln(x_{2})+…+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 3628800A 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.

January 12th, 2011 1:20 pm

Model example:

select

val

from dual

MODEL

dimension by ( 1 x )

measures (1 val)

rules

iterate( 4 ) — n_factorial

(

val[1] = val[1] * ( ITERATION_NUMBER + 1 )

)

;

January 12th, 2011 11:47 pm

Zukus, nice example for the model clause, but I would prefer group by for aggreagation

Rob van Wijk has great articles about model clause http://rwijk.blogspot.com/.

I’ve read them all, but still.. I use that clause very very rarely