Skip to Main Content
  • Questions
  • Cumulative calculation/ calculation based on previous row data in oracle

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Divya.

Asked: August 30, 2021 - 7:55 pm UTC

Last updated: September 06, 2021 - 2:51 am UTC

Version: 19c

Viewed 100+ times

You Asked

Hi,

Could you please provide suggestions on how to implement cumulative calculations on a column to calculate a value based on data in same column but previous row.

For instance: Considering below table and records

DB - Oracle 19c

Data -

<Code/>

CREATE TABLE TEST ( DT DATE, NAME VARCHAR2(10), AMOUNT NUMBER(10,3));

insert into TEST values ( to_date( '01-jan-2021'), 'apple', 198.95 );
insert into TEST values ( to_date( '02-jan-2021'), 'apple', 6.15 );
insert into TEST values ( to_date( '03-jan-2021'), 'apple', 4.65 );
insert into TEST values ( to_date( '04-jan-2021'), 'apple', 20.85 );
insert into TEST values ( to_date( '01-jan-2021'), 'banana', 80.5 );
insert into TEST values ( to_date( '02-jan-2021'), 'banana', 9.5 );
insert into TEST values ( to_date( '03-jan-2021'), 'banana', 31.65 );

Desired Output:

DT NAME AMOUNT CALC
--------------------------------------
1-Jan-21 apple 198.95 39.79
2-Jan-21 apple 6.15 33.062
3-Jan-21 apple 4.65 27.380
4-Jan-21 apple 20.85 26.074
.
.
.
1-Jan-21 banana 80.5 16.1
2-Jan-21 banana 9.5 14.78
3-Jan-21 banana 31.65 18.14
.
.
.


I need to write a query to obtain CALC for each record (grouped by NAME) based on below formula:

((CALC of prev day record * 4)+ AMOUNT of current record )/ 5

i.e for APPLE
for 1-jan-21, CALC = ((0*4)+198.95)/5 = 39.79 -------> since it is 1st record, have taken 0 as CALC of prev day record
for 2-jan-21, CALC = ((39.79*4)+6.15)/5= 33.062 -----> prev CALC is considered from 1-jan-21 - 39.79 and 6.15 from current row
for 3-jan-21, CALC = ((33.062*4)+4.65)/5= 27.380 and so on

For BANANA
1-jan-21, CALC = ((0*4)+80.5)/5=16.1
1-jan-21, CALC = ((16.1*4)+9.5)/5=14.78
etc

Thanks in advance!

with LiveSQL Test Case:

and we said...

A little recursive SQL should do the trick

SQL> with t as
  2  ( select
  3      test.*,
  4      row_number() over ( partition by name order by dt ) as seq
  5    from test
  6  ),
  7  results(name, dt, amount, calc ,seq) as
  8  (
  9    select name, dt, amount, amount/5 calc, seq
 10    from t
 11    where seq = 1
 12    union all
 13    select t.name, t.dt, t.amount, ( 4 * results.calc + t.amount ) / 5, t.seq
 14    from t, results
 15    where t.seq - 1 = results.seq
 16    and  t.name = results.name
 17  )
 18  select * from results
 19  order by name, seq;

NAME       DT            AMOUNT       CALC        SEQ
---------- --------- ---------- ---------- ----------
apple      01-JAN-21     198.95      39.79          1
apple      02-JAN-21       6.15     33.062          2
apple      03-JAN-21       4.65    27.3796          3
apple      04-JAN-21      20.85   26.07368          4
banana     01-JAN-21       80.5       16.1          1
banana     02-JAN-21        9.5      14.78          2
banana     03-JAN-21      31.65     18.154          3



Rating

  (7 ratings)

Comments

MODEL Clause

Rajeshwaran Jeyabal, August 31, 2021 - 7:32 am UTC

but why not a simple model clause like this for these sort of inter-row calculation requirements?
demo@XEPDB1> select *
  2  from test
  3  model
  4      partition by (name)
  5      dimension by (dt)
  6      measures( 0 calc, amount )
  7      rules(
  8          calc[dt] = ( (presentnnv(calc[cv()-1],calc[cv()-1],0) *4) + amount[cv()] )/5 )
  9  order by name,dt
 10  /

NAME       DT                CALC     AMOUNT
---------- ----------- ---------- ----------
apple      01-JAN-2021      39.79     198.95
apple      02-JAN-2021     33.062       6.15
apple      03-JAN-2021    27.3796       4.65
apple      04-JAN-2021   26.07368      20.85
banana     01-JAN-2021       16.1       80.5
banana     02-JAN-2021      14.78        9.5
banana     03-JAN-2021     18.154      31.65

7 rows selected.

Connor McDonald
September 01, 2021 - 2:11 am UTC

If you have a problem that you want to solve with SQL, and you use a MODEL clause...then now you have two problems :-)

Seriously though, whilst the MODEL clause is very cool, its usage in the community is very rare, and thus long term maintainability by developers is harder.

Divya, August 31, 2021 - 5:28 pm UTC

Thank you so much for the solution :)
Connor McDonald
September 01, 2021 - 2:11 am UTC

glad we could help

Model Clause?

Divya, September 02, 2021 - 11:34 am UTC

Thanks Rajeshwaran for inputs!
Your code is working perfect when the dates in the input are consecutive, but considering there are no records for weekends, output isn't as expected :( How could we handle this?
Connor McDonald
September 03, 2021 - 6:43 am UTC

Add a row_number in the same way I did with the recursive and dimension by that.

Model Clause

Rajeshwaran Jeyabal, September 03, 2021 - 10:29 am UTC

Just added this two rows to your table to make the dates appear non-consecutive.
insert into TEST values ( to_date( '07-jan-2021'), 'apple', 23.64 );
insert into TEST values ( to_date( '08-jan-2021'), 'banana', 27.65 );

Then as mentioned by Connor, add row_number to the dimension by clause, then final code will be like this:
demo@XEPDB1> select * from test order by 2,1;

DT          NAME           AMOUNT
----------- ---------- ----------
01-JAN-2021 apple          198.95
02-JAN-2021 apple            6.15
03-JAN-2021 apple            4.65
04-JAN-2021 apple           20.85
07-JAN-2021 apple           23.64
01-JAN-2021 banana           80.5
02-JAN-2021 banana            9.5
03-JAN-2021 banana          31.65
08-JAN-2021 banana          27.65

9 rows selected.

demo@XEPDB1> select *
  2  from test
  3  model
  4      partition by (name)
  5      dimension by ( row_number() over(partition by name order by dt) r)
  6      measures( 0 calc, amount,dt )
  7      rules(
  8          calc[any] order by r = ( (presentnnv(calc[cv()-1],calc[cv()-1],0) *4) + amount[cv()] )/5 )
  9  order by name,dt
 10  /

NAME                R       CALC     AMOUNT DT
---------- ---------- ---------- ---------- -----------
apple               1      39.79     198.95 01-JAN-2021
apple               2     33.062       6.15 02-JAN-2021
apple               3    27.3796       4.65 03-JAN-2021
apple               4   26.07368      20.85 04-JAN-2021
apple               5  25.586944      23.64 07-JAN-2021
banana              1       16.1       80.5 01-JAN-2021
banana              2      14.78        9.5 02-JAN-2021
banana              3     18.154      31.65 03-JAN-2021
banana              4    20.0532      27.65 08-JAN-2021

9 rows selected.

demo@XEPDB1>

this MODEL clause is fully documented, @ https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/sql-modeling-data-warehouses.html#GUID-538F78AA-9BF3-46F2-93D1-39A8739B3237 so read through and customize the code as per your requirements.

Thank you Rajeshwaran, appreciate it! :)

Divya, September 04, 2021 - 3:57 pm UTC


Connor McDonald
September 06, 2021 - 2:51 am UTC

+1

More to Explore

Analytics

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