Calculate the exponenial moving average [message #378439] |
Tue, 30 December 2008 07:50  |
steffeli
Messages: 112 Registered: July 2006
|
Senior Member |
|
|
Hello,
I want to calculate the exponential moving average (EMA) of daily price differences (IX_DIFF). Is there a more general solution for this than the code below? Is it possible to write the EMA with a loop in that way that I can write a code for 100 steps instead of the 14 steps below without writing 100 rows of code?
A appreciate your help!
Thanks, Stefan
create table T1 as
select a.*,
+ power(1-2/(14+1),1) * Lag(a.IX_DIFF,1) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),2) * Lag(a.IX_DIFF,2) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),3) * Lag(a.IX_DIFF,3) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),4) * Lag(a.IX_DIFF,4) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),5) * Lag(a.IX_DIFF,5) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),6) * Lag(a.IX_DIFF,6) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),7) * Lag(a.IX_DIFF,7) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),8) * Lag(a.IX_DIFF,8) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),9) * Lag(a.IX_DIFF,9) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),10) * Lag(a.IX_DIFF,10) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),11) * Lag(a.IX_DIFF,11) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),12) * Lag(a.IX_DIFF,12) over (partition by a.PRODUCT order by a.ID)
+ power(1-2/(14+1),13) * Lag(a.IX_DIFF,13) over (partition by a.PRODUCT order by a.ID)
as EMA
from T0 a
|
|
|
|
Re: Calculate the exponenial moving average [message #378523 is a reply to message #378443] |
Tue, 30 December 2008 19:23   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Sounds like a job for the MODEL clause. I haven't used it, however it seems you need some iterative logic and MODEL is apparently great at that.
There are a few MODEL experts here who might chip in, otherwise check out the doco.
Ross Leishman
|
|
|
Re: Calculate the exponenial moving average [message #378703 is a reply to message #378523] |
Wed, 31 December 2008 09:08   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Id us a row generator and use rows instead of columns. Someting like this:
Quote: | BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for HPUX: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production
5 rows selected.
|
SQL> select level
2 from dual
3 connect by level <= 10
4 /
LEVEL
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Since we can easily generate any list of numbers we want, and you wanted to do power for 100 steps, we can generate a list from 1 to 100, then use it.
select b.intgr
,power(1-2/(14+1),b.intgr) * Lag(a.IX_DIFF,b.intgr) over (partition by a.PRODUCT order by a.ID) ix_diff_power_of_i
from TO a
,(
select level intgr
from dual
connect by level <= 100
) b
/
Since you neglected to take the time to post a create table with insert statements for sample data, I have decided not to take the time to create a real table and test this code with it. You will have to do it and fix any syntax or logic issues.
Good luck, Kevin
|
|
|
|
|