1

I would like to perform a simple calculation as follows

Dataset

SR      value_one      result 
1         null          0.99 
2          1            0.99*1 = 0.99 
3         0.75          0.99*0.75 = 0.7425 
4         0.75          0.7425*0.75 = 0.556875 
5          1            0.556875*1 = 0.556875 
6          1            0.556875*1 = 0.556875 
7          1            0.556875*1 = 0.556875 
8          1            0.556875*1 = 0.556875 
9          1            0.556875*1 = 0.556875 
10         1            0.556875*1 = 0.556875

The results is dependent on the previous one for SR >= 2 while always starts with 0.99.

Looping while performing a calculation. Database Oracle.

szakwani
  • 386
  • 3
  • 14
  • 1
    possible duplicate of [Not getting full series multiplication (product) in CONNECT-BY query](http://stackoverflow.com/questions/27955093/not-getting-full-series-multiplication-product-in-connect-by-query) – Noel Apr 01 '15 at 07:48
  • not a duplicate, the function on the link accumulates the multiplied results – szakwani Apr 01 '15 at 08:45

1 Answers1

2

If you wanted to calculate a running sum, it would be easy to do using analytic functions:

with d as (
  select 1 as sr, cast(null as number) as value_one from dual union all 
  select 2 as sr, 1 as value_one from dual union all 
  select 3 as sr, 0.75 as value_one from dual union all 
  select 4 as sr, 0.75 as value_one from dual union all 
  select 5 as sr, 1 as value_one from dual union all 
  select 6 as sr, 1 as value_one from dual union all 
  select 7 as sr, 1 as value_one from dual union all 
  select 8 as sr, 1 as value_one from dual union all 
  select 9 as sr, 1 as value_one from dual union all
  select 10 as sr, 1 as value_one from dual 
)
select d.*, sum(nvl(value_one, 0.99)) over (order by sr)
from d;

Unfortunately, there is no "PRODUCT" aggregate function which we could use here, so we have to make a detour using EXP and LN (see https://stackoverflow.com/a/3912248/1230592):

with d as (
  select 1 as sr, cast(null as number) as value_one from dual union all 
  select 2 as sr, 1 as value_one from dual union all 
  ..
)
select d.*, nvl(exp (sum (ln (value_one)) over (order by sr)), 1) * 0.99
from d;

This should give you the desired results

Community
  • 1
  • 1
guthy
  • 326
  • 1
  • 4
  • For a documentation of analytic functions, see http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174 – guthy Apr 01 '15 at 09:51
  • In this case you are multiplying every number by 0.99 as nvl(exp (sum (ln (value_one)) over (order by sr)), 1) * 0.99 – szakwani Apr 01 '15 at 10:55
  • 1
    That's because the whole sequence starts with 0.99 (see question). Without it, the first result would be 1. – guthy Apr 01 '15 at 11:07