## 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