Home » SQL & PL/SQL » SQL & PL/SQL » Calculate EMA (10g)
Calculate EMA [message #380183] |
Fri, 09 January 2009 07:23  |
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   |
 |
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
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 14:32:49 CST 2025
|