Referencing the value of the previous calculcated value in Oracle

Question

How can one reference a calculated value from the previous row in a SQL query? In my case each row is an event that somehow manipulates the same value from the previous row.

The raw data looks like this:

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

Lets say each Eventtype=OUT should half the previous row totalcharge in a column called Remaincharge:

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

I've already tried the LAG analytic function but that does not allow me to get a calculated value from the previous row. Tried something like this:

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

But this didn't work because remaingcharge could not be found.

Any ideas how to achieve this? Would need a analytics function that can give me the the cumulative sum but given a function instead with access to the previous value.

Thank you in advance!

Update problem description

I'm afraid my example problem was to general, here is a better problem description:

What remains of totalcharge is decided by the ratio of outqty/(previous remainqty).

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

In your case you could work out the first value using the FIRST_VALUE() analytic function and the power of 2 that you have to divide by with RANK() in a sub-query and then use that. It's very specific to your example but should give you the 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 haven't partitioned by anything because you've got nothing in your raw data to partition by...

Answer 2

A variation on Ben's answer to use a windowing clause, which seems to take care of 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
);

Except it gives 19.2 instead of 12.8 for the third row, but that's what your formula suggests it should be:

   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 it goes from 60 to zero in two steps, with another non-OUT record 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's an assumption that the remaining quantity is consistent and you can effectively track a running total of what has gone before, but from the data you've shown that looks plausible. The inner query calculates that running total for each row, and the outer query does the calculation; that could be condensed but is hopefully clearer like this...

Answer 3

Ben's answer is the better one (will probably perform 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 think it's easier to read

Here is a demo