Skip to Main Content
  • Questions

  • Update current row witrh values from previous row

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Peter.

Asked: February 15, 2018 - 5:21 pm UTC

Last updated: February 20, 2018 - 1:20 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi,
I'm searching for a solution to solve update with a single SQL statement instead of a PL/SQL procedure:

create table test (stock_date DATE, stock NUMBER(5), stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5));
INSERT INTO test VALUES ('010118',100,10,20,null);
INSERT INTO test VALUES ('020118',90,5,5,null);
INSERT INTO test VALUES ('030118',80,0,90,null);
INSERT INTO test VALUES ('040118',-10,2,5,null);
INSERT INTO test VALUES ('050118',-13,20,5,null);
INSERT INTO test VALUES ('060118',2,10,30,null);
COMMIT;


That results to this tabele.

STOCK_DA      STOCK   STOCK_IN  STOCK_OUT  STOCK_VAL
-------- ---------- ---------- ---------- ----------
01.01.18        100         10         20           
02.01.18         90          5          5           
03.01.18         80          0         90           
04.01.18        -10          2          5           
05.01.18        -13         20          5           
06.01.18          2         10         30


Now I want to calculate the stock_val column beginning with the initial value from stock from the first row as the first value for stock_val.
For all other columns the new stock_val should be
stock_val[n] := GREATEST(stock_val[n-1]+stock_in[n-1]-stock_out[n-1]
The problem is: I have to do this row by row, because the stock_val must not get lower than zero! That means you cannot sum up all above lines for the current line.

This is a PL/SQL procedure doing the calculation:

declare
  minDate DATE;
  curStock NUMBER(5);
begin
  SELECT min(stock_date) INTO minDate FROM test;
  SELECT stock INTO curStock FROM test WHERE stock_date = minDate;
  --
  for rec in (select stock_date, stock_in, stock_out FROM test ORDER BY stock_date) LOOP
    update test set stock_val = curStock
     where stock_date = rec.stock_date;
    curStock := GREATEST((curStock+rec.stock_in-rec.stock_out),0);
  end loop;
  commit;

end;
/


The result must look like that:

STOCK_DA      STOCK   STOCK_IN  STOCK_OUT  STOCK_VAL
-------- ---------- ---------- ---------- ----------
01.01.18        100         10         20        100
02.01.18         90          5          5         90
03.01.18         80          0         90         90
04.01.18        -10          2          5          0
05.01.18        -13         20          5          0
06.01.18          2         10         30         15


Thanks a lot,
Peter

and Chris said...

Sadly I don't think this is possible in a single update statement. At least, not in a way that's efficient!

What you could do is calculate the running total with model or recursive with. Then bulk collect the results of that and do a forall update:

create table test (
  stock_date DATE, stock NUMBER(5), stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5)
);
alter session set nls_date_format = 'ddmmyyyy';
INSERT INTO test VALUES ('010118',100,10,20,null);
INSERT INTO test VALUES ('020118',90,5,5,null);
INSERT INTO test VALUES ('030118',80,0,90,null);
INSERT INTO test VALUES ('040118',-10,2,5,null);
INSERT INTO test VALUES ('050118',-13,20,5,null);
INSERT INTO test VALUES ('060118',2,10,30,null);
COMMIT;

select stock_date, stock, stock_in, stock_out, val 
from   test
model 
  dimension by ( row_number() over (order by stock_date) rn )
  measures ( stock_date, stock, stock_in, stock_out, stock_val val )
  rules (
    val[any] = greatest(stock[cv()-1]+stock_in[cv()-1]-stock_out[cv()-1], 0),
    val[1] = stock[cv()]
  );

STOCK_DATE   STOCK   STOCK_IN   STOCK_OUT   VAL   
01010018         100         10          20   100 
02010018          90          5           5    90 
03010018          80          0          90    90 
04010018         -10          2           5     0 
05010018         -13         20           5     0 
06010018           2         10          30     2 

declare
  type test_arr is table of test%rowtype index by pls_integer;
  test_rws test_arr;
begin
  select stock_date, stock, stock_in, stock_out, val 
  bulk collect into test_rws 
  from test
  model 
    dimension by ( row_number() over (order by stock_date) rn )
    measures ( stock_date, stock, stock_in, stock_out, stock_val val )
    rules (
      val[any] = greatest(stock[cv()-1]+stock_in[cv()-1]-stock_out[cv()-1], 0),
      val[1] = stock[cv()]
    );
    
  forall i in 1 .. test_rws.count 
    update test
    set    stock_val = test_rws(i).stock_val
    where  stock_date = test_rws(i).stock_date;
end;
/

select * from test;

STOCK_DATE   STOCK   STOCK_IN   STOCK_OUT   STOCK_VAL   
01010018         100         10          20         100 
02010018          90          5           5          90 
03010018          80          0          90          90 
04010018         -10          2           5           0 
05010018         -13         20           5           0 
06010018           2         10          30           2


Or dump the results in a temporary table and do an insert-select from that.

You'll need to test whether this performs better than your current PL/SQL method.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Using MERGE

Racer I., February 16, 2018 - 2:44 pm UTC

Hi,

This seems to work. Maybe unsuited if it creates too big a transaction :

alter session enable parallel dml;

MERGE /*+ PARALLEL(16) */ INTO test t
USING (
select /*+ PARALLEL(16) */ ROW_ID, stock_date, stock, stock_in, stock_out, val 
from   test t
model 
  dimension by ( row_number() over (order by stock_date) rn )
  measures ( t.ROWID ROW_ID, stock_date, stock, stock_in, stock_out, stock_val val )
  rules (
    val[any] = greatest(stock[cv()-1]+stock_in[cv()-1]-stock_out[cv()-1], 0),
    val[1] = stock[cv()]
  )) s
ON (s.ROW_ID = t.ROWID)
WHEN MATCHED THEN UPDATE SET stock_val = s.val;
commit;


You can also write your own SUM_FLOOR-analytics-function :

https://docs.oracle.com/database/121/SQLRF/statements_6005.htm#SQLRF01304

I did that once, but lost the code...

regards,
Chris Saxon
February 16, 2018 - 4:32 pm UTC

Fantastic, nicely done! Completely forgot about merge...

Correction

Racer I., February 16, 2018 - 2:58 pm UTC

Hi,

I meant this :

https://docs.oracle.com/database/121/ADDCI/aggr_functions.htm#ADDCI2120

regards,

Igor, February 16, 2018 - 4:35 pm UTC

Am I missing anything?
SQL> select * from test;

STOCK_DA      STOCK   STOCK_IN  STOCK_OUT  STOCK_VAL
-------- ---------- ---------- ---------- ----------
01010018        100         10         20
02010018         90          5          5
03010018         80          0         90
04010018        -10          2          5
05010018        -13         20          5
06010018          2         10         30

6 rows selected.

SQL> update test t set stock_val = (select v from (select stock_date,
  2  nvl(greatest(lag(stock+stock_in-stock_out) over (order by stock_date), 0), stock) v
  3  from test) t1 where t.stock_date=t1.stock_date);

6 rows updated.

SQL> select * from test;

STOCK_DA      STOCK   STOCK_IN  STOCK_OUT  STOCK_VAL
-------- ---------- ---------- ---------- ----------
01010018        100         10         20        100
02010018         90          5          5         90
03010018         80          0         90         90
04010018        -10          2          5          0
05010018        -13         20          5          0
06010018          2         10         30          2

6 rows selected.

SQL>

Chris Saxon
February 16, 2018 - 6:14 pm UTC

It works, but you full scanning the table once per row:

---------------------------------------------------------------------------------------   
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
---------------------------------------------------------------------------------------   
|   0 | UPDATE STATEMENT     |      |      1 |        |      0 |00:00:00.01 |      63 |   
|   1 |  UPDATE              | TEST |      1 |        |      0 |00:00:00.01 |      63 |   
|   2 |   TABLE ACCESS FULL  | TEST |      1 |      6 |      6 |00:00:00.01 |       7 |   
|*  3 |   VIEW               |      |      6 |      6 |      6 |00:00:00.01 |      42 |   
|   4 |    WINDOW SORT       |      |      6 |      6 |     36 |00:00:00.01 |      42 |   
|   5 |     TABLE ACCESS FULL| TEST |      6 |      6 |     36 |00:00:00.01 |      42 |   
---------------------------------------------------------------------------------------


That may be slow on large data sets!

Simple is good

Racer I., February 19, 2018 - 8:07 am UTC

Hi,

The last solution can be combined with MERGE :

alter session enable parallel dml;

MERGE /*+ PARALLEL(16) */ INTO test t
USING (
SELECT t.ROWID ROW_ID, NVL(GREATEST(LAG(stock + stock_in - stock_out) OVER (ORDER BY stock_date), 0), stock) val FROM test t) s
ON (s.ROW_ID = t.ROWID)
WHEN MATCHED THEN UPDATE SET stock_val = s.val;

commit;

The sum_floor version is probably only necessary if you only have stock_in/out (or even (stock_in-stock_out), i.e. only changes) and the assumption that the initial stock is 0 and have to get the same result (i.e. no current stock for each row).

regards
Chris Saxon
February 19, 2018 - 10:33 am UTC

Good point, thanks

mismatch in the rules

Rajeshwaran, Jeyabal, February 19, 2018 - 2:09 pm UTC

Team,

The stated requirement is like this:

Now I want to calculate the stock_val column beginning with the initial value from stock from the first row as the first value for stock_val.
For all other columns the new stock_val should be
stock_val[n] := GREATEST(stock_val[n-1]+stock_in[n-1]-stock_out[n-1]

but the rule defined in the model clause is like this:

val[any] = greatest(stock[cv()-1]+stock_in[cv()-1]-stock_out[cv()-1], 0),

Instead of this

stock[cv()-1]

don't it should like this?

stock_val[cv()-1]

so the existing code, produce the output like this:

demo@ORA12C> select stock_date, stock, stock_in, stock_out, val
  2    from test
  3    model
  4      dimension by ( row_number() over (order by stock_date) rn )
  5      measures ( stock_date, stock, stock_in, stock_out, stock_val val )
  6      rules (
  7        val[any] = greatest(stock[cv()-1]+stock_in[cv()-1]-stock_out[cv()-1], 0),
  8        val[1] = stock[cv()]
  9      );

STOCK_DATE       STOCK   STOCK_IN  STOCK_OUT        VAL
----------- ---------- ---------- ---------- ----------
01-JAN-2018        100         10         20        100
01-FEB-2018         90          5          5         90
01-MAR-2018         80          0         90         90
01-APR-2018        -10          2          5          0
01-MAY-2018        -13         20          5          0
01-JUN-2018          2         10         30          2

6 rows selected.


with the code fix in the rules section, the results look like this.

demo@ORA12C> select *
  2  from test
  3  model
  4    dimension by (row_number() over(order by stock_date) n)
  5    measures( stock_date,stock, stock_in, stock_out, stock_val)
  6    rules(
  7      stock_val[n=1] = stock[cv()] ,
  8      stock_val[n>1] order by n = greatest( stock_val[cv()-1] +
  9                            stock_in[cv()-1] -
 10                            stock_out[cv()-1] , 0) )
 11  /

         N STOCK_DATE       STOCK   STOCK_IN  STOCK_OUT  STOCK_VAL
---------- ----------- ---------- ---------- ---------- ----------
         1 01-JAN-2018        100         10         20        100
         2 01-FEB-2018         90          5          5         90
         3 01-MAR-2018         80          0         90         90
         4 01-APR-2018        -10          2          5          0
         5 01-MAY-2018        -13         20          5          0
         6 01-JUN-2018          2         10         30         15

6 rows selected.

demo@ORA12C>


BTW, analytics doesn't return what we needed (due to the inter-column calculations on stock_val column).

demo@ORA12C> SELECT t.*,
  2    NVL(GREATEST(LAG(stock + stock_in - stock_out) OVER (ORDER BY stock_date), 0), stock) val
  3  FROM test t
  4  /

STOCK_DATE       STOCK   STOCK_IN  STOCK_OUT  STOCK_VAL        VAL
----------- ---------- ---------- ---------- ---------- ----------
01-JAN-2018        100         10         20                   100
01-FEB-2018         90          5          5                    90
01-MAR-2018         80          0         90                    90
01-APR-2018        -10          2          5                     0
01-MAY-2018        -13         20          5                     0
01-JUN-2018          2         10         30                     2

6 rows selected.

Chris Saxon
February 19, 2018 - 5:35 pm UTC

Ahh yes, good catch. I knew there was a reason I went with model in the first place! ;)

to model or not to model

Racer I., February 20, 2018 - 12:22 pm UTC

Hi,

I wish Oracle allowed analytics to use self reference so I wouldn't need to learn the model clause (intimidating).

My reference to manual aggregates comes from this :
http://www.oracle-developer.net/display.php?id=215

Apparently some versions can be solved with CONNECT BY (like factorials) because you only need the level.
I used to think recursive WITH was just a replacement for CONNECT BY but this shows it is truly self referencing and can do things CONNECT BY can't (I think) :
drop table test;
create table test (stock_date DATE, stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5));
--create table test (stock_date DATE, stock NUMBER(5), stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5));
INSERT INTO test VALUES (TO_DATE('010118', 'MMDDRR'),100,0,null);
INSERT INTO test VALUES (TO_DATE('020118', 'MMDDRR'),10,20,null);
INSERT INTO test VALUES (TO_DATE('030118', 'MMDDRR'),5,5,null);
INSERT INTO test VALUES (TO_DATE('040118', 'MMDDRR'),0,90,null);
INSERT INTO test VALUES (TO_DATE('050118', 'MMDDRR'),2,5,null);
INSERT INTO test VALUES (TO_DATE('060118', 'MMDDRR'),20,5,null);
INSERT INTO test VALUES (TO_DATE('070118', 'MMDDRR'),10,30,null);
COMMIT;

WITH 
NumberedRows AS (
  SELECT ROW_NUMBER() OVER(ORDER BY stock_date) Pos, stock_date, t.ROWID Row_ID, stock_in - stock_out change
  FROM test t),
SumFloor(Pos, stock_date, Row_ID, change, total) AS (
  SELECT Pos, stock_date, Row_ID, 0 change, change total 
  FROM NumberedRows r
  WHERE Pos = 1
  UNION ALL
  SELECT r.Pos, r.stock_date, r.Row_ID, r.change, GREATEST(s.total + r.change, 0) total 
  FROM   SumFloor s
    JOIN NumberedRows r ON (s.Pos + 1 = r.Pos))
SELECT *
FROM SumFloor
ORDER BY Pos

       POS STOCK_DATE ROW_ID                 CHANGE      TOTAL
---------- ---------- ------------------ ---------- ----------
         1 01.01.18   AAQZtoAAAAAD9xeAAA          0        100
         2 01.02.18   AAQZtoAAAAAD9xeAAB        -10         90
         3 01.03.18   AAQZtoAAAAAD9xeAAC          0         90
         4 01.04.18   AAQZtoAAAAAD9xeAAD        -90          0
         5 01.05.18   AAQZtoAAAAAD9xeAAE         -3          0
         6 01.06.18   AAQZtoAAAAAD9xeAAF         15         15
         7 01.07.18   AAQZtoAAAAAD9xeAAG        -20          0

Had to remove the misleading stock column (not just for the negative values but the 90 to 80 change (from 5=5) was wrong too) and combine in/out into change to get a clearer picture. I also shifted the changes to the date whose total they affect.

can still be MERGEd I expect.
performance vs. model : my bet is on model.

regards,

Chris Saxon
February 20, 2018 - 1:20 pm UTC

I'm not sure how self-referencing analytics would work in general. How do you handle window clauses that span many rows?

Nice work with recursive with - yes you can still merge this.

Recursive with allows you to specify depth or breadth first search, which connect by doesn't. You can also define a cycle on any of your columns, instead of only those in the parent/child relationship as with connect by.

For performance, you'd have to check on your data set. Model struggles on large numbers of rows.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.