Multiply with Previous Value from One colum in Oracle SQL-CodePudding

I have the following result, which is easily calculated in Excel, but how to do it in Oracle, the result is the following, based on a previous select and comes from one column,

  Result from select   Expected result
  1.62590                 
  0.60989                0.991620151 
  0.83859                0.831562742

the result is based on 1.62590 * 0.60989 = 0.991620151, 1.62590 * 0.60989 * 0.83859 = 0.831562742

CodePudding user response：

You can use:

SELECT id,
       result,
       EXP(SUM(LN(result)) OVER (ORDER BY id)) AS expected
FROM   table_name;

Note: Use any other column instead of id to give the appropriate ordering or, if your rows are already ordered, use the ROWNUM pseudo-column instad of id.

Which, for the sample data:

CREATE TABLE table_name (id, Result) AS
SELECT 1, 1.62590 FROM DUAL UNION ALL
SELECT 2, 0.60989 FROM DUAL UNION ALL
SELECT 3, 0.83859 FROM DUAL;

Outputs:

ID	RESULT	EXPECTED
1	1.6259	1.62590000000000000000000000000000000001
2	.60989	.9916201510000000000000000000000000000026
3	.83859	.8315627424270900000000000000000000000085

fiddle

CodePudding user response：

One option is to use a recursive CTE; it, though, expects that sample data can be sorted, somehow, so I added the ID column which starts with 1, while other values are incremented by 1:

Sample data:

SQL> with
  2  test (id, col) as
  3    (select 1, 1.62590 from dual union all
  4     select 2, 0.60989 from dual union all
  5     select 3, 0.83859 from dual
  6    ),

Query begins here:

  7  product (id, col, prod) as
  8    (select id, col, col
  9     from test
 10     where id = 1
 11     union all
 12     select t.id, t.col, t.col * p.prod
 13     from test t join product p on p.id   1 = t.id
 14    )
 15  select id,
 16         round(prod, 10) result
 17  from product;

        ID     RESULT
---------- ----------
         1     1,6259
         2 ,991620151
         3 ,831562742

SQL>

CodePudding user response：

You can use a MODEL clause:

SELECT *  
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, result FROM table_name)
MODEL
  DIMENSION BY (rn)
  MEASURES ( result, 0 AS expected)
  RULES (
    expected[rn] = result[cv()] * COALESCE(expected[cv()-1], 1)
)
order by rn;

Which, for the sample data:

CREATE TABLE table_name (id, Result) AS
SELECT 1, 1.62590 FROM DUAL UNION ALL
SELECT 2, 0.60989 FROM DUAL UNION ALL
SELECT 3, 0.83859 FROM DUAL;

Outputs:

RN	RESULT	EXPECTED
1	1.6259	1.6259
2	.60989	.991620151
3	.83859	.83156274242709

fiddle