Product() aggregate function

Question

While explaining some concepts over CTE, was asked with a cute question.. can we find the multiplication of rows , while we always concentrate on the SUM() from a fresher. That gave me a thought! Is it ever possible with just an SQL. I was also considering the maximum number precision we can even support, since the product may be so huge.

Having said, we cant write our own aggregate function.(can we?) I am thinking is it ever possible with SQL only.

I thought of 2*3 is like adding 2 , 3 times to itself.. but when the set is huge.. I cant make it possible as it may be cumbersome.

Aonther possibility is log (a+b) = log a * log b; and Anti logarithm, gives you the result. This is what i managed to respond. For Oracle, I would have a function using EXECUTE IMMEDIATE.

Can we actually multiply itself. Which will be more beautiful SQL-ly.. This is for pure learning experience.

Answer 1

The logarathm/power approach is the generally used approach. For Oracle, that is:

select exp(sum(ln(col)))
from table;

I don't know why the original database designers didn't include PRODUCT() as an aggregation function. My best guess is that they were all computer scientists, with no statisticians. Such functions are very useful in statistics, but they don't show up much in computer science. Perhaps they didn't want to deal with overflow issues, that such a function would imply (especially on integers).

By the way, this function is missing from most databases, even those that implement lots of statistical aggregation functions.

edit:

Oy, the problem of negative numbers makes it a little more complicated:

select ((case when mod(sum(sign(col)), 2) = 0 then 1 else -1 end) *
        exp(sum(ln(abs(col))))
       ) as product

I am not sure of a safe way in Oracle to handle 0s. This is a "logical" approach:

select (case when sum(case when col = 0 then 1 else 0 end) > 0
             then NULL
             when mod(sum(sign(col)), 2) = 0
             then exp(sum(ln(abs(col)))
             else - exp(sum(ln(abs(col)))
        end) 
       ) as product

The problem is that the database engine might get an error on the log before executing the case statement. That happens to be how SQL Server works. I'm not sure about Oracle.

Ah, this might work:

select (case when sum(case when col = 0 then 1 else 0 end) > 0
             then NULL
             when mod(sum(sign(col)), 2) = 0
             then exp(sum(ln(case when col <> 0 then abs(col) end)))
             else - exp(sum(ln(case when col <> 0 then abs(col) end)))
        end) 
       ) as product

It returns NULL when there is a 0.

Answer 2

In Oracle you can create your own aggregate function,
please take a look at this working example: http://sqlfiddle.com/#!4/ee247/1
It is based on example from documentation:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/aggr_functions.htm

create type ProductImpl as object
(
  product NUMBER, 
  static function ODCIAggregateInitialize(sctx IN OUT ProductImpl) return number,
  member function ODCIAggregateIterate(self IN OUT ProductImpl, value IN number) return number,
  member function ODCIAggregateTerminate(self IN ProductImpl, returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT ProductImpl, ctx2 IN ProductImpl) return number
);
/

create or replace type body ProductImpl is 
static function ODCIAggregateInitialize(sctx IN OUT ProductImpl) 
return number is 
begin
  sctx := ProductImpl(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT ProductImpl, value IN number) return number is
begin
  self.Product := self.Product * value;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN ProductImpl, 
    returnValue OUT number, flags IN number) return number is
begin
  returnValue := self.Product;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT ProductImpl, ctx2 IN ProductImpl) return number is
begin
  self.Product := self.Product * ctx2.Product;
  return ODCIConst.Success;
end;
end;
/

CREATE OR REPLACE FUNCTION Product (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING ProductImpl;
/