Product of n Rows

Question

From this data (assuming the number of rows is not known in advance):

with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4)
select * from q1;
/*
C1
--
 2 
 1 
 4 
 1
*/

I want the product of the c1 column from all the rows. Something like the results of SUM(c1) only I want each value multiplied by they others rather than added. In this case that would be 2 * 1 * 4 * 1 = 8.

/*
X1
--
 8
*/

Data could contain negative numbers and zero, which can be simulated using:

with q1 as (select mod(ora_hash(level),5)-1 c1 from dual connect by level <=4)
select * from q1;

or

with q1 as (select mod(ora_hash(level),5)-3 c1 from dual connect by level <=4)
select * from q1;

I know this could be done with custom aggregate function, but am interested in native approaches.

Answer 1

For sufficiently small aggregate products, you can use the old trick of summing the logarithms and them exponentiating the result

SQL> ed
Wrote file afiedt.buf

  1  with q1
  2    as (select mod(ora_hash(level),5) c1
  3          from dual
  4       connect by level <=4)
  5  select exp(sum(ln(c1)))
  6*   from q1
SQL> /

EXP(SUM(LN(C1)))
----------------
               8

Since you're using 11.2, it's a bit more verbose (though someone may be able to figure out a simpler version) but you can also use recursive common table expressions

SQL> ed
Wrote file afiedt.buf

  1  with
  2  q1 as (select level l, mod(ora_hash(level),5) c1
  3           from dual
  4        connect by level <= 4),
  5  num(n, c1, running_product)
  6  as
  7  (
  8    select 1 as N,
  9           null as c1,
 10           1 as running_product
 11      from dual
 12    union all
 13    select N+1,
 14           q1.c1,
 15           (q1.c1)*running_product
 16      from num
 17           join q1 on (num.N = q1.l)
 18  )
 19  select running_product
 20    from (select num.*,
 21                 rank() over (order by N desc) rnk
 22            from num)
 23*  where rnk = 1
SQL> /

RUNNING_PRODUCT
---------------
              8

Answer 2

Justin inspired me to attempt a version using the MODEL clause. This is my first use of the clause, so I'm open to any constructive criticism. I created three CTEs to test negative numbers and zero.

with q1 as (select level N, mod(ora_hash(level),5) c1 from dual connect by level <=4),
     q2 as (select N, c1-1 c1 from q1),
     q3 as (select N, c1-3 c1 from q1)
select c1 from q1
model return updated rows dimension by (N) measures (c1) 
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
   c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
)
union all
select c1 from q2
model return updated rows dimension by (N) measures (c1) 
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
   c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
)
union all
select c1 from q3
model return updated rows dimension by (N) measures (c1) 
rules iterate (999) until (c1[iteration_number+1] IS NULL) (
   c1[1] = c1[1] * NVL(c1[iteration_number+2],1)
);

Here is a fleshed out version of the log solution that handles negative and zero values.

with q1 as (select mod(ora_hash(level),5)  c1 from dual connect by level <=4),
     q2 as (select c1-1 c1 from q1),
     q3 as (select c1-3 c1 from q1)
select 'q1',  case when sum(case when c1 < 0 then -1 
   when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
   decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1  
   from q1
union all   
select 'q2', case when sum(case when c1 < 0 then -1 
   when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
   decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1  
   from q2
union all   
select 'q3', case when sum(case when c1 < 0 then -1 
   when c1 > 0 then 1 else 0 end) >= 0 then 1 else -1 end *
   decode(min(abs(c1)),0,0,Round(exp(sum(ln(abs(nullif(c1,0))))))) x1  
   from q3
; 

Finally, here is a recursive CTE similar to Justins, but slightly simpler.

with q1 as (select level N, mod(ora_hash(level),5) c1 from dual connect by level <=4),
     q2 as (select N, c1-1 c1 from q1),
     q3 as (select N, c1-3 c1 from q1),
     x1 (N, Product) as (
        select 1 N, 1 Product from dual
        union all
        select x1.N+1 N, q1.c1 * x1.Product Product from x1 
        join q1 on (x1.N = q1.N)
        ),
     x2 (N, Product) as (
        select 1 N, 1 Product from dual
        union all
        select x2.N+1 N, q2.c1 * x2.Product Product from x2
        join q2 on (x2.N = q2.N)
        ),
     x3 (N, Product) as (
        select 1 N, 1 Product from dual
        union all
        select x3.N+1 N, q3.c1 * x3.Product Product from x3
        join q3 on (x3.N = q3.N)
        )
select Product from (select N, Product, max(N) OVER () MaxN from x1) where N = MaxN
union all
select Product from (select N, Product, max(N) OVER () MaxN from x2) where N = MaxN
union all
select Product from (select N, Product, max(N) OVER () MaxN from x3) where N = MaxN;