sql - periodos - medias moviles exponenciales trading

¿Cómo calcular una media móvil exponencial en postgres?

Question

Estoy tratando de implementar un promedio móvil exponencial (EMA) en postgres, pero a medida que reviso la documentación y pienso en ello, cuanto más me esfuerzo, más confundido estoy.

La fórmula para EMA(x) es:

EMA(x₁) = x₁ EMA(x_n) = α * x_n + (1 - α) * EMA(x_n-1)

Parece ser perfecto para un agregador, ya que mantener el resultado del último elemento calculado es exactamente lo que debe hacerse aquí. Sin embargo, un agregador produce un solo resultado (como reducir o plegar) y aquí necesitamos una lista (una columna) de resultados (como mapa). He estado comprobando cómo funcionan los procedimientos y las funciones, pero AFAIK produce una sola salida, no una columna. He visto muchos procedimientos y funciones, pero realmente no puedo entender cómo interactúa esto con el álgebra relacional, especialmente cuando hago algo como esto, un EMA.

No he tenido suerte buscando en los Internets hasta ahora. Pero la definición para un EMA es bastante simple, espero que sea posible traducir esta definición en algo que funcione en postgres y que sea simple y eficiente, porque pasar a NoSQL será excesivo en mi contexto.

Gracias.

PD: aquí puedes ver un ejemplo:
https://docs.google.com/spreadsheet/ccc?key=0AvfclSzBscS6dDJCNWlrT3NYdDJxbkh3cGJ2S2V0cVE

Answer 1

Este tipo de consulta se puede resolver con un CTE recursivo: pruebe:

with recursive cte as (
select n, x ema from my_table where n = 1
union all
select m.n, alpha * m.x + (1 - alpha) * cte.ema
from cte
join my_table m on cte.n = m.n - 1
cross join (select ? alpha) a)
select * from cte;

Answer 2

Puede definir su propia función agregada y luego utilizarla con una especificación de ventana para obtener la salida agregada en cada etapa en lugar de un solo valor.

Entonces, un agregado es una parte del estado y una función de transformación para modificar ese estado para cada fila, y opcionalmente una función de finalización para convertir el estado a un valor de salida. Para un caso simple como este, solo una función de transformación debería ser suficiente.

create function ema_func(numeric, numeric) returns numeric
  language plpgsql as $$
declare
  alpha numeric := 0.5;
begin
  -- uncomment the following line to see what the parameters mean
  -- raise info ''ema_func: % %'', $1, $2;
  return case
              when $1 is null then $2
              else alpha * $2 + (1 - alpha) * $1
         end;
end
$$;
create aggregate ema(basetype = numeric, sfunc = ema_func, stype = numeric);

lo que me da:

steve@steve@[local] =# select x, ema(x, 0.1) over(w), ema(x, 0.2) over(w) from data window w as (order by n asc) limit 5;
     x     |      ema      |      ema      
-----------+---------------+---------------
 44.988564 |     44.988564 |     44.988564
   39.5634 |    44.4460476 |    43.9035312
 38.605724 |   43.86201524 |   42.84396976
 38.209646 |  43.296778316 |  41.917105008
 44.541264 | 43.4212268844 | 42.4419368064

Estos números parecen coincidir con la hoja de cálculo que agregó a la pregunta.

Además, puede definir la función para pasar alfa como un parámetro de la declaración:

create or replace function ema_func(state numeric, inval numeric, alpha numeric)
  returns numeric
  language plpgsql as $$
begin
  return case
         when state is null then inval
         else alpha * inval + (1-alpha) * state
         end;
end
$$;

create aggregate ema(numeric, numeric) (sfunc = ema_func, stype = numeric);

select x, ema(x, 0.5 /* alpha */) over (order by n asc) from data

Además, esta función es en realidad tan simple que no necesita estar en plpgsql en absoluto, sino que puede ser solo una función SQL, aunque no puede referirse a los parámetros por nombre en uno de ellos:

create or replace function ema_func(state numeric, inval numeric, alpha numeric)
  returns numeric
  language sql as $$
select case
       when $1 is null then $2
       else $3 * $2 + (1-$3) * $1
       end
$$;

Answer 3

--$1 Stock code
--$2 exponential;
create or replace function fn_ema(text,numeric)
    returns numeric as
    $body$
    declare
        alpha numeric := 0.5;
        var_r record;
        result numeric:=0;
        n int;
        p1 numeric;
    begin
        alpha=2/(1+$2);
        n=0;
        for var_r in(select *
        from stock_old_invest
        where code=$1  order by stock_time desc)
        loop
            if n>0 then
                result=result+(1-alpha)^n*var_r.price_now;
            else 
                p1=var_r.price_now;
            end if;
            n=n+1;
        end loop;
        result=alpha*(result+p1);
        return result;
    end
    $body$
    language plpgsql volatile
    cost 100;
    alter function fn_ema(text,numeric)
    owner to postgres;