Exponential moving average

from the Artful Common Queries page

An arithmetic moving average is the arithmetic mean across a window of results that advances over a reference variable (usually time). An exponential moving average (EMA) gives more weight to more recent values in such windows.

For each value V in a series, the EMA is ... 

  EMA = EMAprev + k( V - EMAprev )

        1
  k = —————
      (m+1)
where the smoothing parameter k is computed from the number m of time units over which to take the moving average. Using our toy table for simple arithmetic moving averages ... 
DROP TABLE IF EXISTS t;
CREATE TABLE t (dt DATE, qty INT);
INSERT INTO t VALUES 
('2007-1-1',5),('2007-1-2',6),('2007-1-3',7),('2007-1-4',8),('2007-1-5',9),
('2007-1-6',10),('2007-1-7',11),('2007-1-8',12),('2007-1-9',13);
... the query for a 5-day EMA is ... 
select 
  dt, 
  qty, 
  (2/(1+5)) * (qty - lag(ema5,1,0) over w ) + lag(ema5,1,0) over w as ema5
from (
  select dt, qty, qty as ema5  -- SEED ema
  from t
) x
window w as (order by dt);     -- LAG WINDOW
Points to note ...
  • We need a starting value for ema5, thus the inner or source query creates that column from the qty column

  • The windowing function call LAG(ema,1,0)finds the previous ema5 value for each row

  • As with moving arithmetic averages, aggregation refers to window w so no GROUP BY clause is required

  • For any such dataset, to see how the parameter m shapes the result, generate a family of EMA curves for m=1,2.3,...

Last updated 4 Feb 2021