Reference to the value of the previous calculated value in Oracle

Question

How can I refer to the calculated value from the previous row in the SQL query? In my case, each row is an event that somehow controls the same value from the previous row.

Raw data is as follows:

  Eventno Eventtype Totalcharge
 3 ACQ 32
 2 OUT NULL
 1 OUT NULL

Suppose that each Eventtype = OUT should have half the total charge of the previous row in the Remaincharge column:

  Eventno Eventtype Totalcharge Remaincharge
 3 ACQ 32 32
 2 OUT NULL 16
 1 OUT NULL 8

I already tried the LAG analytic function, but this does not allow me to get the calculated value from the previous line. Tried something like this:

LAG(remaincharge, 1, totalcharge) OVER (PARTITION BY ...) as remaincharge

But this did not work, because remaingcharge could not be found.

Any ideas how to achieve this? You will need an analytics function that can give me a cumulative amount, but get access to the previous value instead.

Thank you in advance!

Description of upgrade problem

I am afraid that my approximate problem was general, here is the best description of the problem:

What remains of the total charge is determined by the ratio outqty / (previous residual value).

  Eventno Eventtype Totalcharge Remainqty Outqty
 4 ACQ 32 100 0
 3 OTHER NULL 100 0
 2 OUT NULL 60 40
 1 OUT NULL 0 60

  Eventno Eventtype Totalcharge Remainqty Outqty Remaincharge
 4 ACQ 32 100 0 32
 3 OTHER NULL 100 0 32 - (0/100 * 32) = 32
 2 OUT NULL 60 40 32 - (40/100 * 32) = 12.8
 1 OUT NULL 0 60 12.8 - (60/60 * 12.8) = 0

Answer 1

Ben's answer to using a windowing clause that seems to care about your updated requirements:

 select eventno, eventtype, totalcharge, remainingqty, outqty, initial_charge - case when running_outqty = 0 then 0 else (running_outqty / 100) * initial_charge end as remainingcharge from ( select eventno, eventtype, totalcharge, remainingqty, outqty, first_value(totalcharge) over (partition by null order by eventno desc) as initial_charge, sum(outqty) over (partition by null order by eventno desc rows between unbounded preceding and current row) as running_outqty from t42 ); 

Also, it gives 19.2 instead of 12.8 for the third line, but this is what your formula suggests:

  EVENTNO EVENT TOTALCHARGE REMAININGQTY OUTQTY REMAININGCHARGE ---------- ----- ----------- ------------ ---------- --------------- 4 ACQ 32 100 0 32 3 OTHER 100 0 32 2 OUT 60 40 19.2 1 OUT 0 60 0 

If I add another split so that it starts from 60 to zero in two steps, with another record outside OUT in the mix, too:

  EVENTNO EVENT TOTALCHARGE REMAININGQTY OUTQTY REMAININGCHARGE ---------- ----- ----------- ------------ ---------- --------------- 6 ACQ 32 100 0 32 5 OTHER 100 0 32 4 OUT 60 40 19.2 3 OUT 30 30 9.6 2 OTHER 30 0 9.6 1 OUT 0 30 0 

There is an assumption that the remaining amount is consistent, and you can effectively track the current amount of what was before, but from the data you showed it looks plausible. The internal query calculates that the total is performed for each row, and the external query performs the calculation; which can be compressed, but hopefully will be clearer like this ...

Answer 2

In your case, you can work out the first value using the analytic function FIRST_VALUE () and power 2, which you must split with with RANK () in the subquery and then use this. This is very specific to your example, but should give you a general idea:

 select eventno, eventtype, totalcharge , case when eventtype <> 'OUT' then firstcharge else firstcharge / power(2, "rank" - 1) end as remaincharge from ( select a.* , first_value(totalcharge) over ( partition by 1 order by eventno desc ) as firstcharge , rank() over ( partition by 1 order by eventno desc ) as "rank" from the_table a ) 

Here's a SQL Fiddle to demonstrate. I didn’t partition anything because you don’t have anything in your raw data for partitioning ...

Answer 3

The answer to the answer is the best (it may work better), but you can also do it like this:

 select t.*, (connect_by_root Totalcharge) / power (2,level-1) Remaincharge from the_table t start with EVENTTYPE = 'ACQ' connect by prior eventno = eventno + 1; 

I find it easier to read

Here is a demo