Calculating Oracle sql math using current and previous row values

Question

I have the following table:

ID  A   B   C
1   1   23  22
2   2   19  20
3   5   18  15
4   3   12  12

Columns A and B are constants. I want to calculate column C using the following formula:

for the first row

 C = B - A

thereafter

 C = previous(C) - A

I want to achieve this using only oracle SQL.

Answer 1

If you put your data in the form

    A    B       C
   a1   b1   b1 - a1     == b1 - a1
   a2   b2   c1 - a2     == b1 - a1 - a2 
   a3   b3   c2 - a3     == b1 - a1 - a2 - a3
   a4   b4   c3 - a4     == b1 - a1 - a2 - a3 - a4
   ...
   an   bn   cn-1 - an   == b1 - a1 - a2 - ... - an

you can easily find a solution in analytic functions, for example

select A,
       B, 
       first_value(B) over(order by id) - sum(A) over (order by id) C
  from myTable   

Please check the actual order (I put order by id)

Answer 2

Take a look at Oracle analytic functions. https://oracle-base.com/articles/misc/lag-lead-analytic-functions

C, , LAG.

, , C . 3 C C@row2 - A@row3 = C@row1 - (A@row3 + A@row2) = C@row1 + A@row1 - sum(a @ row1,2,3), sql,

Oracle sql,

SELECT ID,A,B, (LAG(C_+A,RN-1,0) OVER (ORDER BY ID)) - SUM(A) OVER (ORDER BY ID) as c FROM(
  SELECT ID, A, B, B-A AS C_, ROWNUM RN FROM TEST
)

TEST ID, A, B

ID  A   B   C
1   1   23  22
2   2   19  20
3   5   18  15
4   3   12  12