CODEWITHSUNDEEP
sqloracle-databasecumulative-sumoracle19c
Divya
Divya

Reputation: 19

Cumulative calculation/ Calculation based on previous row data in Oracle 19c

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

Data -

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

I have tried using lag, with, join etc and also looked upon internet but unable to arrive at a solution.

Thanks in advance!

Upvotes: 1

Views: 382

Answers (1)

MT0
MT0

Reputation: 167972

If you have the successive rows with amounts: a1, a2, a3, ...

Then the calc value for the successive rows is:

  1. a1 × (40 ÷ 51)
  2. a1 × (41 ÷ 52) + a2 × (40 ÷ 51)
  3. a1 × (42 ÷ 53) + a2 × (41 ÷ 52) + a3 × (40 ÷ 51)
  4. ...

You can use this (and the fact that your data has successive days) to calculate the progression with the query:

SELECT t2.dt,
       t2.name,
       t2.amount,
       SUM(POWER(4/5, t2.dt - t1.dt) * t1.amount / 5) AS calc
FROM   test t1
       INNER JOIN test t2
       ON (t1.name = t2.name AND t1.dt <= t2.dt)
GROUP BY
       t2.dt,
       t2.name,
       t2.amount
ORDER BY
       t2.name,
       t2.dt;

You could also use the MODEL clause:

SELECT dt,
       name,
       amount,
       calc
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY dt) AS rn
  FROM   test t
)
MODEL
  PARTITION BY (name)
  DIMENSION BY (rn)
  MEASURES ( dt, amount, 0 AS calc)
  RULES (
    calc[1] = amount[1]/5,
    calc[rn>1] = (amount[cv(rn)] + 4*calc[cv(rn)-1])/5
  )

Which, for your sample data, both output:

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

db<>fiddle here

Upvotes: 2

Related Questions