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 1000+ 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 Connor 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)
Is this answer out of date? If it is, please let us know via a Comment