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

Oracle has exposed these ODCI (Oracle Data Cartridge Interface) methods to do the Aggregate! Reference:

• ODCIAggregateDelete() - Removes an input value from the current group.
• ODCIAggregateInitialize() - Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter.
• ODCIAggregateIterate() - Iterates through input rows by processing the input values, updating and then returning the aggregation context.
• ODCIAggregateMerge() - Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate.
• ODCIAggregateTerminate() - Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory.
• ODCIAggregateWrapContext() Integrates all external pieces of the current aggregation context to make the context self-contained.

Code For PRODUCT() Aggregate function :

CREATE OR REPLACE type PRODUCT_IMPL
AS
  object
  (
    result NUMBER,
    static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,
                                         value IN NUMBER)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateTerminate( self IN PRODUCT_IMPL,
                                            returnValue OUT NUMBER,
                                            flags IN NUMBER)
    RETURN NUMBER,
    member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,
                                       ctx2 IN PRODUCT_IMPL )
    RETURN NUMBER );
  /
  /* 1.Initializes the computation by initializing the aggregation context—the rows over which aggregation is performed: */
CREATE OR REPLACE type body PRODUCT_IMPL
IS
  static FUNCTION ODCIAggregateInitialize(sctx IN OUT PRODUCT_IMPL)
  RETURN NUMBER
IS
BEGIN
  sctx := PRODUCT_IMPL(1);
  RETURN ODCIConst.Success;
END;
/* 2.Iteratively processes each successive input value and updates the context: */
member FUNCTION ODCIAggregateIterate(self  IN OUT PRODUCT_IMPL,
                                     value IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  self.result := value * self.result;
  RETURN ODCIConst.Success;
END;
member FUNCTION ODCIAggregateTerminate(
    self IN PRODUCT_IMPL,
    returnValue OUT NUMBER,
    flags IN NUMBER)
  RETURN NUMBER
IS
BEGIN
  returnValue := self.result;
  RETURN ODCIConst.Success;
END;
member FUNCTION ODCIAggregateMerge(self IN OUT PRODUCT_IMPL,
                                   ctx2 IN PRODUCT_IMPL)
  RETURN NUMBER
IS
BEGIN
  self.result := self.result;
  RETURN ODCIConst.Success;
END;
END;
/

/* Create A function using the PRODUCT_IMPL implementation we did above */
CREATE OR REPLACE FUNCTION product(input NUMBER)
RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING PRODUCT_IMPL;
/

Results:

SELECT group_name,product(num) FROM product_test GROUP BY group_name;

Mahesh  -60000
Mahesh_1    9

Answer 2

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 3

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;
/