如何计算postgres的指数移动平均线？ [英] How to calculate an exponential moving average on postgres?

问题描述

我正在尝试在postgres上实现指数移动平均线（EMA），但是当我查看文档并对其进行思考时，我越努力越想越困惑。

EMA（x）的公式为：

 EMA（x  1 ）= x  1  
 EMA（x  n ）=α * x  n  +（1-α）* EMA（x  n-1 ）
 

对于聚合器来说似乎是完美的，保留最后计算的元素的结果正是在这里必须要做的。但是，聚合器会产生一个单一的结果（如缩小或折叠），在这里我们需要一个结果列表（一列）（作为映射）。我一直在检查过程和函数的工作方式，但是AFAIK它们只产生一个输出，而不是一列。我已经看到了大量的过程和功能，但是我真的无法弄清楚它如何与关系代数交互，特别是在执行类似EMA的情况下。

到目前为止，我搜索互联网还没有运气。但是EMA的定义非常简单，我希望可以将此定义转换为可在postgres中使用并且简单有效的东西，因为在我的上下文中，向NoSQL过渡将是多余的。

谢谢。

PD：在这里您可以看到一个示例：

https://docs.google.com/spreadsheet/ccc?key=0AvfclSzBscS6dDJCNWlrT3NYdJJbkh>$解决方案

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

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

 创建函数ema_func（numeric，numeric）返回数字
语言plpgsql如$$ 
声明
 alpha数字：= 0.5; 
开始
-取消注释以下行以查看参数
的含义-引发信息ema_func：％％'，$ 1，$ 2； 
返回情况
，当$ 1为空时，然后$ 2 
否则alpha * $ 2 +（1- alpha）* $ 1 
结尾； 
结束
 $$； 
创建聚合ema（basetype =数值，sfunc = ema_func，stype =数值）; 
  

这给了我：

  steve @ steve @ [local] =＃从数据窗口w中选择x，ema（x，0.1）over（w），ema（x，0.2）over（w）as（按n asc排序） ）限制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作为语句中的参数传递：

 创建或替换函数ema_func（状态数字，inval数字，字母数字）
返回数字
语言plpgsql为$$ 
开始
返回状态为null的情况下
然后inval 
 else alpha * inval +（1-alpha）*状态
 end; 
结束
 $$； 
 
创建汇总ema（数字，数字）（sfunc = ema_func，stype =数字）; 
 
从数据
  

$中选择x，ema（x，0.5 / * alpha * /）over（按n asc排序） b
$ b

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

 创建或替换函数ema_func（状态数字，inval数字，字母数字）
返回数字
语言sql为$$ 
当$ 1为空时选择大小写
然后$ 2 
否则$ 3 * $ 2 +（1- $ 3）* $ 1 
 end 
 $ $; 
  

I'm trying to implement an exponential moving average (EMA) on postgres, but as I check documentation and think about it the more I try the more confused I am.

The formula for EMA(x) is:

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

It seems to be perfect for an aggregator, keeping the result of the last calculated element is exactly what has to be done here. However an aggregator produces one single result (as reduce, or fold) and here we need a list (a column) of results (as map). I have been checking how procedures and functions work, but AFAIK they produce one single output, not a column. I have seen plenty of procedures and functions, but I can't really figure out how does this interact with relational algebra, especially when doing something like this, an EMA.

I did not have luck searching the Internets so far. But the definition for an EMA is quite simple, I hope it is possible to translate this definition into something that works in postgres and is simple and efficient, because moving to NoSQL is going to be excessive in my context.

Thank you.

PD: here you can see an example:
https://docs.google.com/spreadsheet/ccc?key=0AvfclSzBscS6dDJCNWlrT3NYdDJxbkh3cGJ2S2V0cVE

解决方案

You can define your own aggregate function and then use it with a window specification to get the aggregate output at each stage rather than a single value.

So an aggregate is a piece of state, and a transform function to modify that state for each row, and optionally a finalising function to convert the state to an output value. For a simple case like this, just a transform function should be sufficient.

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

which gives me:

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

These numbers seem to match up to the spreadsheet you added to the question.

Also, you can define the function to pass alpha as a parameter from the statement:

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

Also, this function is actually so simple that it doesn't need to be in plpgsql at all, but can be just a sql function, although you can't refer to parameters by name in one of those:

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

这篇关于如何计算postgres的指数移动平均线？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！