Home » SQL & PL/SQL » SQL & PL/SQL » Calculate EMA (10g)
Calculate EMA Fri, 09 January 2009 07:23
 steffeli Messages: 112Registered: 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: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 steffeli Messages: 112Registered: 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: Wed Jul 26 18:07:54 CDT 2017

Total time taken to generate the page: 0.09767 seconds