Home » SQL & PL/SQL » SQL & PL/SQL » Calculate EMA (10g)
Calculate EMA [message #380183] Fri, 09 January 2009 07:23 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hi,

How can I calculate an exponental moving average (EMA) with the formula below?

EMA(t) = 0.1 * Px(t) + (1 - 0.1) * EMA(t-1)

That means that subsequent EMA's shall be calculated by the actual Price Px(t) and the EMA of the previous day EMA(t-1).
The first EMA is 9 in the example below.

Thanks for your help,
Stefan

create table T1 (ID varchar2(20), Px varchar2(20), EMA varchar2(20));
	INSERT INTO T1 VALUES (1,10,9);
	INSERT INTO T1 VALUES (2,12,null);
	INSERT INTO T1 VALUES (3,11,null);
	INSERT INTO T1 VALUES (4,14,null);
	INSERT INTO T1 VALUES (5,15,null);
	INSERT INTO T1 VALUES (6,12,null);
	INSERT INTO T1 VALUES (7,16,null);
	INSERT INTO T1 VALUES (8,18,null);
	INSERT INTO T1 VALUES (9,19,null);
	INSERT INTO T1 VALUES (10,17,null);
	INSERT INTO T1 VALUES (11,15,null);
	INSERT INTO T1 VALUES (12,18,null);
	INSERT INTO T1 VALUES (13,19,null);
	INSERT INTO T1 VALUES (14,20,null);
	INSERT INTO T1 VALUES (15,22,null);
	INSERT INTO T1 VALUES (16,25,null);
	INSERT INTO T1 VALUES (17,29,null);
	INSERT INTO T1 VALUES (18,30,null);
	INSERT INTO T1 VALUES (19,21,null);
	INSERT INTO T1 VALUES (20,23,null);
	INSERT INTO T1 VALUES (21,22,null);
	INSERT INTO T1 VALUES (22,26,null);
	INSERT INTO T1 VALUES (23,36,null);
	INSERT INTO T1 VALUES (24,37,null);
	INSERT INTO T1 VALUES (25,31,null);
	INSERT INTO T1 VALUES (26,29,null);

[Updated on: Fri, 09 January 2009 07:24]

Report message to a moderator

Re: Calculate EMA [message #380192 is a reply to message #380183] Fri, 09 January 2009 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select id, px, ema
  2  from (select id, px, ema, row_number() over (order by id) rn from t1)
  3  model
  4    dimension by (rn)
  5    measures (id, px, ema)
  6    rules (
  7      ema[ANY] = 
  8        case
  9        when id[cv()-1] is null then ema[cv()]
 10        else 0.1*px[cv()]+(1-0.1)*nvl(ema[cv()-1],0)
 11        end
 12    )
 13  /
        ID         PX        EMA
---------- ---------- ----------
         1         10          9
         2         12        9.3
         3         11       9.47
         4         14      9.923
         5         15    10.4307
         6         12   10.58763
         7         16  11.128867
         8         18 11.8159803
         9         19 12.5343823
        10         17  12.980944
        11         15 13.1828496
        12         18 13.6645647
        13         19 14.1981082
        14         20 14.7782974
        15         22 15.5004676
        16         25 16.4504209
        17         29 17.7053788
        18         30 18.9348409
        19         21 19.1413568
        20         23 19.5272211
        21         22  19.774499
        22         26 20.3970491
        23         36 21.9573442
        24         37 23.4616098
        25         31 24.2154488
        26         29 24.6939039

26 rows selected.

Regards
Michel
Re: Calculate EMA [message #380197 is a reply to message #380192] Fri, 09 January 2009 08:29 Go to previous message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Excellent, thank you very much for your help!
Stefan
Previous Topic: Multiple Column Selection from Table in a Function
Next Topic: DISABLE CONSTRAINTS
Goto Forum:
  


Current Time: Thu Feb 06 14:32:49 CST 2025