CODEWITHSUNDEEP
sqloracle-database
Prats
Prats

Reputation: 21

Calculate results based on multiplying previous row column

I have spent some time to figure out the solutions using lag functionality as well as max functions. But this works fine when the operation is a sum or diff and not for multiplication.

Below is the original table:

id  a   b       c
1   100 0.02    0
2       0.030   0
3       0.040   0
4       0.05    0

Expected result:

id  a   b       c
1   100 0.02    102
2       0.030   105.06
3       0.040   109.2624
4       0.05    114.72552

Where c is

case when id =1 then (a + a*b)
else prev row of c(Lag c) +prev row of c(Lag c) * b

exception for id = 1: 

c = (a + a*b)= 100 +100*0.02  = 102

id = 2:

c = 102 +102*0.03  = 105.06

id = 3:

c = 105.06 +105.06*0.04  = 109.2624

Tried multiple queries using lag and max functions. Able to do this easily in excel unlike sql.

Thank you for your help in advance!

Upvotes: 2

Views: 680

Answers (2)

Gilmar Vaz
Gilmar Vaz

Reputation: 102

Sum:

--drop table test

create table test(
  id numeric identity(1,1)
 ,a numeric
 ,b numeric
)

insert into test (a,b)values(100, 2)
insert into test (a,b)values(null,  3)
insert into test (a,b)values(null,  4)
insert into test (a,b)values(null,  5)

select id, a, b,
SUM( case when id = (select top 1 id from test) then (a+b) else b end) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as result
from test

enter image description here

Multiplication:

select id, a, b,
 exp(SUM( case when id = (select top 1 id from test) then log(a*b) else log(b) end) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) as result
from test

enter image description here

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

One method is a recursive CTE. Another method is to implement a PRODUCT() window function. Fortunately, you can do this using some arithmetic.

So, the multiplication factor is:

select t.*,
       exp(sum(log(1 + b)) over (order by id)) as factor
from t;

We can then do the complete arithmetic as:

select t.*,
       max(a) over () * exp(sum(log(1 + b)) over (order by id)) as c
from t;

As written, this should work in both Oracle and SQL Server, your original tags.

Here is a SQL Fiddle.

Upvotes: 2

Related Questions