Oracle sql math calculation 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

after that

 C = previous(C) - A

I want to achieve this using only oracle SQL.

Answer 1

If you put your data in the form of

    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, e.g.

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

Please, check actual order (I've put order by id)