Convert Excel formula (PRODUCT) to MySQL query

Question

wondering if anyone can help me convert the following Excel formula to MySQL query:

Excel: =PRODUCT(1+A1:A7)-1

MySQL data:

id | data
---------------
1  | -1.64
2  | 1.38
3  | 0
4  | 0
5  | -1.52
6  | 0
7  | -1.78

Result should equal -0.207936

Answer 1

This is how you can do it. I sure hope someone can improve on it cause it is awful.

with data(id, val) as(
select 1,-1.64 from dual union all
select 2,1.38 from dual union all
select 3,0.00 from dual union all
select 4,0.00 from dual union all
select 5,-1.52 from dual union all
select 6,0.00 from dual union all
select 7,-1.78 from dual
),
products as(
select Replace(LISTAGG(val+1, '*') within group (order by val),',','.')  chain
from data)

SELECT o.val-1 AS product_value
FROM products p
OUTER APPLY(
   SELECT *
   FROM XMLTABLE('/ROWSET/ROW/*' 
        passing dbms_xmlgen.getXMLType('SELECT ' || p.chain || ' FROM dual')
        COLUMNS val NUMBER PATH '.')
   WHERE p.chain IS NOT NULL
) o;

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c12284b5165da61de8ed418cdf1d6cd7

Answer 2

a prettier solution

with data(id, val) as(
select 1,-1.64 from dual union all
select 2,1.38 from dual union all
select 3,0.00 from dual union all
select 4,0.00 from dual union all
select 5,-1.52 from dual union all
select 6,0.00 from dual union all
select 7,-1.78 from dual
),
neg(val , modifier) as(
select exp(sum(ln(abs(val+1)))), case when mod(count(*),2) = 0 then 1 Else -1 end
from data
where val+1 <0
)
,
pos(val) as (
select exp(sum(ln(val+1)))
from data
where val+1 >=0
)
select (select val*modifier from neg)*(select val from pos)-1 from dual