Home » SQL & PL/SQL » SQL & PL/SQL » Calculating a row value from the previous row value in a query statement (Oracle 9.2)
Calculating a row value from the previous row value in a query statement [message #350586] 
Thu, 25 September 2008 08:48 
milaan66
Messages: 12 Registered: March 2007

Junior Member 


I HAVE A NEED TO CALCULATE A NEXT ROW VALUE FROM THE PREVIOUS ROW VALUE IN A QUERY STATEMENT. IF THERE ARE 10 ROWS RETURNED FROM A QUERY STATEMENT, THE 2ND ROW VALUE IS CALCULATED BASED ON THE VALUE OF 1ST ROW, 3RD ROW VALUE IS CALCULATED BASED ON THE VALUE OF 2ND ROW AND SO ON….THE LAST ROW VALUE IS CALCULATED ON THE VALUE OF SECOND LAST ROW. HOW TO WRITE A QUERY FOR THIS?
For example:
ROW VALUE
R1 N1
R2 N2(N*N1)
R3 N3(N*N2)
.
.
.
R10 N10(N*N9)





Re: Calculating a row value from the previous row value in a query statement [message #350610 is a reply to message #350586] 
Thu, 25 September 2008 10:38 
milaan66
Messages: 12 Registered: March 2007

Junior Member 


My appologies for not following the forum guidelines.
Here is what I want to do?
Table:
SQL> Create t1 (a varchar2(10), x number);
Table created.
SQL> insert into t1 values ('abc', 5);
1 row created.
SQL> insert into t1 values ('abcd', 6);
1 row created.
SQL> insert into t1 values ('xyz', 8);
1 row created.
SQL> insert into t1 values ('yze', 1);
1 row created.
SQL> select t1.a, t1.x, 0.3*t1.x+(10.3)*t.avg ewma
2 from (select avg(x) avg from t1) t, t1;
A X EWMA
  
abc 5 5
abcd 6 5.3
xyz 8 5.9
yze 1 3.8
4 rows selected.
What I want is to apply different formula for EWMA from row # 2 onwards. The formula would be 0.3*t1.x+(10.3)*ewma (of previous row. Multiply by EWMA of previous row instead by AVG.
Expected result set:
A X EWMA
  
abc 5 5
abcd 6 5.3
xyz 8 6.11
yze 1 4.577
Hope I have explained well now.
Thanks,
Milaan
[Edit MC: add code tags, do it yourself next time, check with Preview button]
[Updated on: Thu, 25 September 2008 10:48] by Moderator Report message to a moderator













Re: Calculating a row value from the previous row value in a query statement [message #350807 is a reply to message #350610] 
Fri, 26 September 2008 08:08 

Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome

Senior Member 


Could a custom aggregate function do what are you looking for?
It should work on 9i too.
Processing ...
select id,val,0.3*avg*product(val) over (order by id) as ewma
from (
select t.*,avg(val) over() as avg
from t
)
Query finished, retrieving results...
ID VAL EWMA
  
1 4 6,6
2 7 46,2
3 8 369,6
4 4 1478,4
5 8 11827,2
6 8 94617,6
7 8 756940,8
8 3 2270822,4
9 2 4541644,8
10 3 13624934,4
10 row(s) retrieved
And this is what you would need
CREATE OR REPLACE
TYPE T_PRODUCT_AGG AS OBJECT (
g_number number,
STATIC FUNCTION ODCIAggregateInitialize (
sctx IN OUT T_PRODUCT_AGG
)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate (
self IN OUT T_PRODUCT_AGG,
value IN number
)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate (
self IN T_PRODUCT_AGG,
returnValue OUT number,
flags IN NUMBER
)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge (
self IN OUT T_PRODUCT_AGG,
ctx2 IN T_PRODUCT_AGG
)
RETURN NUMBER
);
/
CREATE OR REPLACE
TYPE BODY T_PRODUCT_AGG IS
STATIC FUNCTION ODCIAggregateInitialize (
sctx IN OUT T_PRODUCT_AGG
)
RETURN NUMBER IS
temp clob;
BEGIN
sctx := T_PRODUCT_AGG(1);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate (
self IN OUT T_PRODUCT_AGG,
value IN number
)
RETURN NUMBER IS
BEGIN
g_number := SELF.g_number * nvl(value,1);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate (
self IN T_PRODUCT_AGG,
returnValue OUT number,
flags IN NUMBER
)
RETURN NUMBER IS
BEGIN
returnValue := SELF.g_number;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge (
self IN OUT T_PRODUCT_AGG,
ctx2 IN T_PRODUCT_AGG
)
RETURN NUMBER IS
BEGIN
g_number := self.g_number * ctx2.g_number;
RETURN ODCIConst.Success;
END;
END;
/
CREATE OR REPLACE FUNCTION product(
x number
) RETURN number
DETERMINISTIC AGGREGATE USING T_PRODUCT_AGG;
/
For explanations:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1004572
Bye Alessandro
[Updated on: Fri, 26 September 2008 08:13] Report message to a moderator




Re: Calculating a row value from the previous row value in a query statement [message #350858 is a reply to message #350839] 
Fri, 26 September 2008 14:18 

Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome

Senior Member 


Michel Cadot wrote on Fri, 26 September 2008 19:16 
I don't see the relation between OP problem (the final result) and your query.

Quote:  I want is to apply different formula for EWMA from row # 2 onwards. The formula would be 0.3*t1.x+(10.3)*ewma (of previous row. Multiply by EWMA of previous row instead by AVG.

I forgot to add 10.3 but, if it wasn't there (as I was thinking before coming back here to read it better) that formula would be
first_ewma = 0.3 * t1.x
ewma = 0.3*t1.x*prev_ewma
ewma^1 = 0.3 * t1.x^1
ewma^2 = 0.3 * t1.x^2 * ewma^1
...
ewma^n = 0.3 * t1.x^n * ewma^(n1) = 0.3 * t1.x^n * [0.3*t1.x^(n1)]*....*[0.3 * t1.x^1] = product(0.3*t1.x)
And I missed to put 0.3 inside the function.
But now, considering that missed (10.3). This can't be done as easily as before and I don't have enough time to analyze it.
If the OP would clarify his requirements giving us the exact recursive formula in mathematical terms. Something like first values and formulas to calculate successive ones(base steps and recursive steps). With that it would be possible to analyze it and see if there is an equivalent numerical succession.
Bye Alessandro
[Updated on: Fri, 26 September 2008 14:23] Report message to a moderator




Re: Calculating a row value from the previous row value in a query statement [message #350867 is a reply to message #350858] 
Fri, 26 September 2008 14:48 
milaan66
Messages: 12 Registered: March 2007

Junior Member 


Thank you all for your effort. It has given a good insight but am still struggling... Here is the formula I want to apply:
For row#1 : lambda*column1+(1lambda)*(average of column1) = EWMA of row#1
For row#2 : lambda*column1+(1lambda)*(row#1 ewma)
For row#3 : lambda*column1+(1lambda)*(row#2 ewma)
.
.
For row#N : lambda*column1+(1lambda)*(row#n1 ewma)
Where 
lambda is some constant value.
EWMA is the result of this calculation for that row.
Hope this is clear.
thank you Milaan
[Updated on: Fri, 26 September 2008 14:48] Report message to a moderator



Re: Calculating a row value from the previous row value in a query statement [message #350890 is a reply to message #350867] 
Fri, 26 September 2008 19:44 

Alessandro Rossi
Messages: 166 Registered: September 2008 Location: Rome

Senior Member 


I probably found a way.
This one took more than an hour of concentration, I had to remember a techique I didn't use since the time of the math course at the university.
Let's begin then.
If we consider the average as ewma#0 and as value#0 too, we can say:
ewma#n=lambda*value#n+(1lambda)*ewma#(n1)
so
ewma#n(lambda*value#n)=(1lambda)*ewma#(n1)=
=(1lambda)*(ewma#(n1)(lambda*value#(n1))+(lambda*value#(n1))) =
=(1lambda)*((1lambda)*ewma#(n2)+(lambda*value#(n1))) =
....
=(1lambda)*(...(1lambda)*ewma#0+(lambda*value#1)...) =
=(1lambda)^n*ewma#0+(1lambda)^(n1)*value#1+...+(1lambda)*value#(n1) =
=(1lambda)^n*value#0+(1lambda)^(n1)*value#1+...+(1lambda)*value#(n1)
and finally
ewma#n=(1lambda)^n*ewma#0+(1lambda)^(n1)*value#1+...+(1lambda)^2*value#(n2)+(1lambda)*value#(n1)+(lambda*value#n) =
=lambda*value#i+summatory(for i,from 0,to n,of lambda*value#(i1)*(1lambda)^(ni))
That should be evaluated as (Not tested)
with comp as(
selct 0 as id,avg(val) as val
from t
union all
select id,val
from t
)
select a.id,a.val,sum(:lambda*b.val*pow(1:lambda),a.idb.id)+a.val
from comp a
join comp b on (
a.id < b.id
)
group by a.id,a.val
/
Bye Alessadro
[Updated on: Fri, 26 September 2008 19:59] Report message to a moderator



Re: Calculating a row value from the previous row value in a query statement [message #350960 is a reply to message #350890] 
Sun, 28 September 2008 04:41 

Michel Cadot
Messages: 64618 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


Nice analysis.
Modifying your query (for instance, first val (id 0) is not average which is first ewma...):
SQL> create table t (id integer, val integer);
Table created.
SQL> insert into t
2 select level, round(dbms_random.value(2,10))
3 from dual connect by level <= 10
4 /
10 rows created.
SQL> select * from t order by 1;
ID VAL
 
1 3
2 3
3 4
4 9
5 5
6 6
7 7
8 4
9 4
10 8
10 rows selected.
SQL> select avg(val) from t;
AVG(VAL)

5.3
1 row selected.
SQL> var lambda number
SQL> exec :lambda := 0.3
PL/SQL procedure successfully completed.
SQL> col ewma format 990.00
SQL> with
2 data as (
3 select 0 id, avg(val)/:lambda val from t
4 union all
5 select id, val from t
6 )
7 select a.id, a.val,
8 sum(:lambda * power(1:lambda,a.idb.id) * b.val) ewma
9 from data a, data b
10 where b.id <= a.id
11 and a.id > 0
12 group by a.id, a.val
13 order by a.id
14 /
ID VAL EWMA
  
1 3 4.61
2 3 4.13
3 4 4.09
4 9 5.56
5 5 5.39
6 6 5.58
7 7 6.00
8 4 5.40
9 4 4.98
10 8 5.89
10 rows selected.
Regards
Michel



Re: Calculating a row value from the previous row value in a query statement [message #351073 is a reply to message #350960] 
Mon, 29 September 2008 03:44 

Michel Cadot
Messages: 64618 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


Just by curiosity, I also made it with custom aggregate function.
First I create a type containing the current value, lambda value (which might be different for each row) and the ewma initial value (id=0 in the previous query).
SQL> create or replace type ewma_value as object (
2 value number,
3 lambda number,
4 first number
5 )
6 /
Type created.
Then I create the aggregate function:
SQL> Create or replace type ewma_type as object (
2 ewma number,
3 coef number,
4 first number,
5
6 static function ODCIAggregateInitialize (sctx IN OUT ewma_type) return number,
7
8 member function ODCIAggregateIterate (
9 self IN OUT ewma_type,
10 value IN ewma_value)
11 return number,
12
13 member function ODCIAggregateTerminate (
14 self IN ewma_type,
15 returnValue OUT number,
16 flags IN number)
17 return number,
18
19 member function ODCIAggregateMerge (
20 self IN OUT ewma_type,
21 ctx2 IN ewma_type)
22 return number
23
24 );
25 /
Type created.
SQL> Create or replace type body ewma_type is
2
3 static function ODCIAggregateInitialize (sctx IN OUT ewma_type)
4 return number
5 is
6 begin
7 sctx := ewma_type (0,1,null);
8 return ODCIConst.Success;
9 end;
10
11 member function ODCIAggregateIterate (
12 self IN OUT ewma_type,
13 value IN ewma_value)
14 return number
15 is
16 begin
17 if self.first is null then self.first := value.first; end if;
18 self.ewma := value.lambda * value.value + (1value.lambda) * self.ewma;
19 self.coef := self.coef * (1value.lambda);
20 return ODCIConst.Success;
21 end;
22
23 member function ODCIAggregateTerminate (
24 self IN ewma_type,
25 returnValue OUT number,
26 flags IN number)
27 return number
28 is
29 begin
30 returnValue := ewma + nvl(self.first,0) * self.coef;
31 return ODCIConst.Success;
32 end;
33
34 member function ODCIAggregateMerge (
35 self IN OUT ewma_type,
36 ctx2 IN ewma_type)
37 return number
38 is
39 begin
40 raise_application_error (20001, 'Not supported');
41 return ODCIConst.Error;
42 end;
43
44 end;
45 /
Type body created.
SQL> Create or replace function ewma (input ewma_value)
2 return number
3 aggregate using ewma_type;
4 /
Function created.
Finally I use it:
SQL> with ave as (select avg(val) ave from t)
2 select id, val,
3 ewma(ewma_value(val,0.3,ave)) over (order by id) ewma
4 from t, ave
5 order by id
6 /
ID VAL EWMA
  
1 3 4.61
2 3 4.13
3 4 4.09
4 9 5.56
5 5 5.39
6 6 5.58
7 7 6.00
8 4 5.40
9 4 4.98
10 8 5.89
10 rows selected.
Now I want to compare performances between the 2 ways with a table of 1000 rows (I executed several times each query and post here the fastest ones):
SQL> truncate table t;
Table truncated.
SQL> insert into t
2 select level, round(dbms_random.value(2,10))
3 from dual connect by level <= 1000
4 /
1000 rows created.
SQL> select count(*) from t;
COUNT(*)

1000
1 row selected.
SQL> var lambda number
SQL> exec :lambda := 0.3
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> set timing on
SQL> with
2 data as (
3 select 0 id, avg(val)/:lambda val from t
4 union all
5 select id, val from t
6 )
7 select a.id, a.val,
8 sum(:lambda * power(1:lambda,a.idb.id) * b.val) ewma
9 from data a, data b
10 where b.id <= a.id
11 and a.id > 0
12 group by a.id, a.val
13 order by a.id
14 /
1000 rows selected.
Elapsed: 00:00:01.75
Execution Plan

Plan hash value: 2686348104

 Id  Operation  Name  Rows  Bytes  Cost (%CPU) Time 

 0  SELECT STATEMENT   1  52  15 (54) 00:00:01 
 1  TEMP TABLE TRANSFORMATION      
 2  LOAD AS SELECT      
 3  UNIONALL      
 4  SORT AGGREGATE   1  13   
 5  TABLE ACCESS FULL  T  1000  13000  3 (0) 00:00:01 
 6  TABLE ACCESS FULL  T  1000  26000  3 (0) 00:00:01 
 7  SORT GROUP BY   1  52  9 (56) 00:00:01 
 8  MERGE JOIN   50100  2544K 6 (34) 00:00:01 
 9  SORT JOIN   1001  26026  3 (34) 00:00:01 
* 10  VIEW   1001  26026  2 (0) 00:00:01 
 11  TABLE ACCESS FULL  SYS_TEMP_0FD9D6618_116BB3  1001  26026  2 (0) 00:00:01 
* 12  SORT JOIN   1001  26026  3 (34) 00:00:01 
 13  VIEW   1001  26026  2 (0) 00:00:01 
 14  TABLE ACCESS FULL  SYS_TEMP_0FD9D6618_116BB3  1001  26026  2 (0) 00:00:01 

Predicate Information (identified by operation id):

10  filter("A"."ID">0)
12  access(INTERNAL_FUNCTION("B"."ID")<=INTERNAL_FUNCTION("A"."ID"))
filter(INTERNAL_FUNCTION("B"."ID")<=INTERNAL_FUNCTION("A"."ID"))
Note

 dynamic sampling used for this statement
Statistics

3 recursive calls
12 db block gets
24 consistent gets
7 physical reads
612 redo size
31824 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
1000 rows processed
SQL> with ave as (select avg(val) ave from t)
2 select id, val,
3 ewma(ewma_value(val,0.3,ave)) over (order by id) ewma
4 from t, ave
5 order by id
6 /
1000 rows selected.
Elapsed: 00:00:00.04
Execution Plan

Plan hash value: 314794421

 Id  Operation  Name  Rows  Bytes  Cost (%CPU) Time 

 0  SELECT STATEMENT   1000  39000  6 (0) 00:00:01 
 1  WINDOW SORT   1000  39000  6 (0) 00:00:01 
 2  NESTED LOOPS   1000  39000  6 (0) 00:00:01 
 3  VIEW   1  13  3 (0) 00:00:01 
 4  SORT AGGREGATE   1  13   
 5  TABLE ACCESS FULL T  1000  13000  3 (0) 00:00:01 
 6  TABLE ACCESS FULL  T  1000  26000  3 (0) 00:00:01 

Note

 dynamic sampling used for this statement
Statistics

0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
31682 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
Aggregate function is far far better.
What happens when number of rows increases?

 Nb Rows  1000  2000  3000  4000  5000 

 1st query  1.75  7.40  16.96  31.78  51.39 
 2nd query  0.04  0.09  0.14  0.17  0.21 

Aggregate function is strictly linear when the first query is more than quadratic (increase like the square of number of rows).
(Note that an (unique) index on id column does not change anything as it is not used.)
Regards
Michel



Re: Calculating a row value from the previous row value in a query statement [message #351092 is a reply to message #351073] 
Mon, 29 September 2008 04:55 

Michel Cadot
Messages: 64618 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


Now with model clause (in 10.2) we get:
SQL> select id, val, ewma
2 from t
3 model
4 unique dimension
5 return all rows
6 reference ave
7 on (select 0 id, avg(val) ave from t)
8 dimension by (id)
9 measures (ave)
10 dimension by (id)
11 measures (val, to_number(null) ewma)
12 rules update sequential order
13 ( ewma[ANY] order by id = :lambda * val[cv()] + (1:lambda) * nvl(ewma[cv()1],ave[0]) )
14 order by id
15 /
ID VAL EWMA
  
1 3 4.61
2 3 4.13
3 4 4.09
4 9 5.56
5 5 5.39
6 6 5.58
7 7 6.00
8 4 5.40
9 4 4.98
10 8 5.89
10 rows selected.
And the performances array becomes (in seconds):

 Nb Rows  1000  2000  3000  4000  5000  10000  100000  1000000 

 1st query  1.75  7.40  16.96  31.78  51.39  203.39   
 2nd query  0.04  0.09  0.14  0.17  0.21  0.54  4.10  40.10 
 3rd query  0.04  0.07  0.12  0.15  0.17  0.25  1.30  12.50 

The power of MODEL!
Regards
Michel
[Updated on: Mon, 29 September 2008 12:23] Report message to a moderator







Re: Calculating a row value from the previous row value in a query statement [message #361009 is a reply to message #350586] 
Mon, 24 November 2008 12:38 
milaan66
Messages: 12 Registered: March 2007

Junior Member 


I am working with the same data set and query (with the MODEL clause)as described in this thread. Really, the only difference being the "ID" column is defined as VARCHAR2 and actually have values as text. In other words, if ID column as text value in lieu of pure numbers, it (cv() function) does not work.
I hope I have made myself clear. If not, please let me know and I will post the data set that I am working with but that is the only difference.
Thanks,
Milaan




Re: Calculating a row value from the previous row value in a query statement [message #361014 is a reply to message #350586] 
Mon, 24 November 2008 14:02 
milaan66
Messages: 12 Registered: March 2007

Junior Member 


Here you go... my appologies again if it does not indent properly as I don't know how to indent. If it does n't that please be assured that I am right now reading the forum guide to find if it mentions how to indent?
thank you
Milaan
SQL> create table t (id varchar2(20), val integer);
Table created.
SQL> insert into t values ('abc', 3);
1 row created.
SQL> insert into t values ('abcd', 5);
1 row created.
SQL> insert into t values ('abcde', 6)
2 /
1 row created.
SQL> insert into t values ('xyz', 3);
1 row created.
SQL> insert into t values ('a12345', 4);
1 row created.
SQL> insert into t values ('abc12', 3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID VAL
 
abc 3
abcd 5
abcd 6
xyz 3
a12345 4
abc12 3
6 rows selected.
SQL> var lambda number
SQL> exec :lambda := 0.3
PL/SQL procedure successfully completed.
select id, val, ewma
from t
model
unique dimension
return all rows
reference ave
on (select 0 id, avg(val) ave from t)
dimension by (id)
measures (ave)
dimension by (id)
measures (val, to_number(null) ewma)
rules update sequential order
( ewma[1] order by id = :lambda * val[cv()] + (1:lambda) * nvl(ewma[cv()1],ave[0]) )
order by id
/
( ewma[ANY] order by id = :lambda * val[cv()] + (1:lambda) * nvl(ewma[cv()1],ave[0]) )
*
ERROR at line 13:
ORA01722: invalid number
Is there a way to get ewma with the id as text?
Thanks,
Milaan
[Updated on: Mon, 24 November 2008 14:20] Report message to a moderator



Re: Calculating a row value from the previous row value in a query statement [message #361015 is a reply to message #361014] 
Mon, 24 November 2008 14:41 

Michel Cadot
Messages: 64618 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


Just introduced a new column as number:
SQL> select id, val, ewma
2 from (select row_number() over (order by id) rn,
3 id, val
4 from t)
5 model
6 unique dimension
7 return all rows
8 reference ave
9 on (select 0 rn, '' id, avg(val) ave from t)
10 dimension by (rn)
11 measures (id, ave)
12 dimension by (rn)
13 measures (id, val, to_number(null) ewma)
14 rules update sequential order
15 ( ewma[ANY] order by id = :lambda * val[cv()] + (1:lambda) * nvl(ewma[cv()1],ave[0]) )
16 order by id
17 /
ID VAL EWMA
  
a12345 4 4
abc 3 3.7
abc12 3 3.49
abcd 5 3.943
abcde 6 4.5601
xyz 3 4.09207
6 rows selected.
Regards
Michel




Goto Forum:
Current Time: Tue Mar 28 07:04:48 CDT 2017
Total time taken to generate the page: 0.10192 seconds
