Home » SQL & PL/SQL » SQL & PL/SQL » Calculate the exponenial moving average
Calculate the exponenial moving average Tue, 30 December 2008 07:50
 steffeli Messages: 112Registered: 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?

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 #378443 is a reply to message #378439] Tue, 30 December 2008 08:02
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Post a Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Explain with words what you want to achieve instead of posting an invalid statement.

Regards
Michel
Re: Calculate the exponenial moving average [message #378523 is a reply to message #378443] Tue, 30 December 2008 19:23
 rleishman Messages: 3727Registered: 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: 2102Registered: 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
Re: Calculate the exponenial moving average [message #378717 is a reply to message #378439] Wed, 31 December 2008 19:38
 coleing Messages: 213Registered: February 2008 Senior Member
maybe this will help?

http://www.java2s.com/Code/Oracle/Analytical-Functions/PerformingaMovingAverage.htm
Re: Calculate the exponenial moving average [message #378733 is a reply to message #378717] Thu, 01 January 2009 00:38
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 coleing wrote on Thu, 01 January 2009 02:38 maybe this will help? http://www.java2s.com/Code/Oracle/Analytical-Functions/PerformingaMovingAverage.htm

I don't think so.

Regards
Michel

 Previous Topic: subtype INTEGER Next Topic: dates and null
Goto Forum:

Current Time: Sat Aug 19 17:41:49 CDT 2017

Total time taken to generate the page: 0.05036 seconds