sql - 如何计算postgres的指数移动平均线？

Question

我正在尝试在 postgres 上实现指数移动平均线 (EMA)，但是当我检查文档并思考它时，我尝试得越多，我就越困惑。

EMA(x) 的公式是:

EMA(x1) = x1EMA(xn) = α * xn + (1 - α) * EMA(xn-1)

这对于聚合器来说似乎是完美的，保留最后计算元素的结果正是这里必须要做的。然而，聚合器产生一个单一的结果(如 reduce 或 fold)，这里我们需要一个结果列表(一列)(如 map)。我一直在检查过程和函数是如何工作的，但据我所知，它们只产生一个输出，而不是一列。我见过很多过程和函数，但我无法真正弄清楚这与关系代数如何相互作用，尤其是在做像 EMA 这样的事情时。

到目前为止，我还没有在互联网上搜索过。但是 EMA 的定义非常简单，我希望有可能将这个定义转化为适用于 postgres 且简单高效的东西，因为在我的上下文中迁移到 NoSQL 会过度。

谢谢。

PD:这里你可以看到一个例子:
https://docs.google.com/spreadsheet/ccc?key=0AvfclSzBscS6dDJCNWlrT3NYdDJxbkh3cGJ2S2V0cVE

Answer 1

您可以定义自己的聚合函数，然后将其与窗口规范一起使用以获取每个阶段的聚合输出而不是单个值。

因此，聚合是一个状态，一个转换函数用于为每一行修改该状态，以及一个可选的终结函数，用于将状态转换为输出值。对于像这样的简单情况，只需一个转换函数就足够了。

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

这给了我:

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

这些数字似乎与您添加到问题中的电子表格相符。

此外，您还可以定义函数以将 alpha 作为参数从语句中传递:

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

此外，这个函数实际上非常简单，根本不需要在 plpgsql 中，但可以只是一个 sql 函数，尽管您不能在其中一个函数中按名称引用参数:

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