PostgreSQL指数加权移动平均数

Question

如本文所述，

https://corporatefinanceinstitute.com/resources/knowledge/trading-investing/exponentially-weighted-moving-average-ewma/

看起来你可以计算EWMA。现在有一些方法已经发表，但大卫·G·约翰逊在Postgres Slack频道上提出了另一种方法。

create or replace function wavg(inputs float8[]) returns float8
language sql as  
$$ select sum(c*(.133)*(.133^(i-1)))
from unnest(inputs) 
with ordinality as vals (c, i);
$$;

select array_agg(v) over (order by i desc rows between 13 preceding and current row) as input,
wavg(array_agg(v) over (order by i desc rows between 13 preceding and current row)) from
(values (1,119.06),(2,119.86),(3,127.19),(4,125.04),(5,124.26),(6,125.17),
(7,127.71),(8,131.11),(9,134.10),(10,133.43),(11,131.28),(12,134.56),
(13,133.77),(14,133.61)) vals (i,v) order by i asc;  -- answer is 129.34
select array_agg(v) over (order by i desc rows between 13 preceding and current row) as input,
wavg(array_agg(v) over (order by i desc rows between 13 preceding and current row)) from
(values (1,130.88),(2,129.18),(3,127.15),(4,128.25),(5,128.54),(6,128.08),
(7,129.12),(8,129.22),(9,130.79),(10,132.04),(11,131.81),(12,132.34),
(13,131.64),(14,132.48)) vals (i,v) order by i asc;  -- answer is 132.00

由于结果与普遍接受的答案并不接近，我正在寻找上述算法中的错误。我怀疑所有输入的权重总和应该为1，但事实并非如此。如果我们能让它发挥作用，这似乎是一种非常低代码的方法。

Answer 1

权重加起来不等于1是对的。这里有一个修复方法(以及一些琐碎的清理(。

create or replace function wavg(inputs float8[]) returns float8
language sql as  
$$ select sum(c*(.133)^i)) / sum(.133^i)
from unnest(inputs) 
with ordinality as vals (c, i);
$$;

如果你想要一个加权移动平均线的时间序列，你可以使用同样的想法，但使用窗口函数