title: PostgreSQL exponentially weighted moving average
body: <p>As stated in this blog post,</p>
  <p><a href="https://corporatefinanceinstitute.com/resources/knowledge/trading-investing/exponentially-weighted-moving-average-ewma/" rel="nofollow noreferrer">https://corporatefinanceinstitute.com/resources/knowledge/trading-investing/exponentially-weighted-moving-average-ewma/</a></p>
  <p>It looks like you can calculate a EWMA. Now there are some ways that have been published but an alternative way has been suggested by David G Johnson in the Postgres Slack channel.</p>
  <pre class="lang-sql prettyprint-override"><code>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);
  $$;
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code>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
  </code></pre>
  <p>Since the results are not close to the wildly accepted answer, I am looking for the error in the above algorithm. I suspect the weights should total to one for all of the inputs but they don't. Seems like a very low code way to do this if we can get it to work.</p>