如何在Postgres上计算指数移动平均？

Question

我正在尝试在Postgres上实现指数移动平均值（EMA），但是随着我查阅文档并思考，我越尝试越感到困惑。

EMA(x)的公式如下：

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

对于聚合器来说，保留上一个计算元素的结果似乎非常完美。然而，聚合器只产生一个单一的结果（作为reduce或fold），而在这里我们需要一个结果列表（作为map）（即一列）。我一直在查看过程和函数的工作原理，但据我所知，它们只产生一个单一的输出，而不是一列。我已经看过很多过程和函数，但我无法真正弄清楚这与关系代数如何交互，尤其是像这样做EMA时。

到目前为止，我在互联网上搜索并没有什么收获。但是，EMA的定义相当简单，我希望能够将此定义翻译成在Postgres中有效且简单的内容，因为在我的情况下转向NoSQL会过度。

谢谢。

附：这里您可以看到一个示例：
https://docs.google.com/spreadsheet/ccc?key=0AvfclSzBscS6dDJCNWlrT3NYdDJxbkh3cGJ2S2V0cVE

- Trylks

---

发布一个示例表格源代码和期望的结果。这可以帮助到您。 - dani herrera

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

- araqnid

---

+1 我也有类似的想法，只是没有那么详细。 - Erwin Brandstetter

这是计算每个子列表的每一行产生结果的聚合吗？因为它看起来是使用聚合器到第n行，返回结果，然后再回到第0行重新计算到第n+1行的聚合。有没有办法使用累加或一些静态变量（如C语言中）使其只需计算一次？谢谢。 - Trylks

不，它使用了累积值。如果您取消注释带有“raise info”命令的查询，您将能够看到该函数仅针对每个行输出调用一次。Postgresql在每行上输出状态值（如果有finalfunc定义，则会调用该值来将状态转换为输出值）。 - araqnid

我刚刚检查了一下所需时间，对于示例数据集，它所需的时间比另一个选项少了5倍。在更复杂的情况下，我有更多的问题需要解决，但我认为它们值得开一个新话题。谢谢。 - Trylks

创建聚合还似乎有一个选项可以使用msvfunc和msffunc移动聚合，建议修改您的答案以包括这些选项，以防OP使用移动窗口。 - PirateApp

Answer 2

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

- mansel

Answer 3

这种类型的查询可以通过使用递归CTE来解决 - 尝试使用以下方法：

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;

- user359040

---

我冒昧地进行了一些小修补。删除了前导的; - 这在tSQL中是必需的，但在PostgreSQL中不是。格式化代码。改进了JOIN条件。从CTE中递增一个值比从表中递减所有值更快。（我实际上在pg 9.0上运行了一个快速测试来验证。） - Erwin Brandstetter

任何基于交叉连接的解决方案对于大型数据集来说都太慢了。请改用窗口函数。 - Pavel Stehule

如果我没记错的话，这个假设n是一个索引（因此是n-1），因为如果它是一个日期或者我们有来自示例的有序表，那么这个方法就不会起作用，对吧？我还没有完全理解这个。 - Trylks

@Trylks：在这个答案中，n、x和alpha与你的问题中完全相同，因为这是你提供的唯一数据结构指示。 - user359040

抱歉，n 表示某种排序，但我并不是指数据库中有一个名为 n 的字段，其中包含行的排序作为序数。递归查询很酷，我不知道 postgres 支持它们，非常感谢您启发性的答案。 - Trylks