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 = EMA_{prev} + k( V - EMA_{prev} ) 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 WINDOWPoints to note ...
Last updated 4 Feb 2021 |