sql - 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.