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:
- 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:
x1*x2*…*xn =(rule 2) exp(ln(x1*x2*…*xn)) = (rule 1) exp(ln(x1)+ln(x2)+…+ln(xn))
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.
. Recently I received a new feedback on my