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;

Usage Link to heading

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
));

Originally published at https://jensenmo.blogspot.com/2011/10/calculating-exponential-moving-average.html