Home » SQL & PL/SQL » SQL & PL/SQL » Calculate the exponenial moving average
Calculate the exponenial moving average [message #378439] Tue, 30 December 2008 07:50 Go to next message
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 #378443 is a reply to message #378439] Tue, 30 December 2008 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Calculate the exponenial moving average [message #378717 is a reply to message #378439] Wed, 31 December 2008 19:38 Go to previous messageGo to next message
coleing
Messages: 213
Registered: 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 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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: Invailad row id , ora-01410
Next Topic: Is it possible.
Goto Forum:
  


Current Time: Tue Dec 06 12:30:07 CST 2016

Total time taken to generate the page: 0.20066 seconds