14 October 2011

Calculating Exponential Moving Average with a Function in Oracle

There is much to be said for the recent features in Oracle that allow a function to pose as a table - and also enable that function to accept a weakly typed REF CURSOR. A while back I wrote a small function that would calculate the Exponential Moving Average based on a SQL statement as input:

create or replace
FUNCTION CALC_EMA (dataset SYS_REFCURSOR, days NUMBER)
RETURN EMATypeSet PIPELINED
IS
  l_no          NUMBER;
  l_value       NUMBER;
  l_ema         NUMBER;
  emarow        EMAType;
  l_prev_ema    NUMBER := 0;
  l_cnt         INTEGER := 0;
BEGIN
  LOOP
    FETCH dataset
     INTO l_no, l_value;
    
    EXIT WHEN dataset%NOTFOUND;

    l_cnt := l_cnt + 1;
   
    IF (l_cnt = 1) THEN
      l_prev_ema := l_value;
    END IF;

    l_ema := (l_value - l_prev_ema) * 2/(days + 1) + l_prev_ema;
   
    emarow := EMAType(l_no, l_value, l_ema);
   
    PIPE ROW (emarow);

    l_prev_ema := l_ema;
  END LOOP;
 
  CLOSE dataset;
END CALC_EMA;


This enables me to do the following to get an EMA data set that I can use to for instance plot a graph (I am a big fan of EJS Charting):

select *
from  table (calc_ema(CURSOR(select rownum, value from ( select close value from security_day_prices where security_id=p_SecurityId order by value_date)), p_EMADays));


1 comment:

raj said...

Hi Morten

i tried to use this function on sampel table. its not working.

SQL> desc testsma
Name Null? Type
----------------------------------------- -------- --------

LDATE DATE
PRICE NUMBER

select *
from table (calc_ema(CURSOR(select rownum, price from ( select price from testsma order by ldate)), 20));