How to Calc Exponential Moving Average using SQL Server 2012 Window Functions

Question

I know that it is easy to calculate simple moving average using SQL Server 2012 window functions and OVER() clause. But how can I calculate exponential moving average using this approach? Thanks!

Answer 1

The formula for EMA(x) is:

EMA(x1) = x1
EMA(xn) = α * xn + (1 - α) * EMA(xn-1)

With

β := 1 - α

that is equivalent to

EMA(xn) = βn-1 * x1 + α * βn-2 * x2 + α * βn-3 * x3 + ... + α * xn

In that form it is easy to implement with LAG. For a 4 row EMA it would look like this:

SELECT LAG(x,3)OVER(ORDER BY ?) * POWER(@beta,3) + 
       LAG(x,2)OVER(ORDER BY ?) * POWER(@beta,2) * @alpha + 
       LAG(x,1)OVER(ORDER BY ?) * POWER(@beta,1) * @alpha + 
       x * @alpha
FROM ...

---

OK, as you seem to be after the EWMA_Chart I created a SQL Fiddle showing how to get there. However, be aware that it is using a recursive CTE that requires one recursion per row returned. So on a big dataset you will most likely get disastrous performance. The recursion is necessary as each row depends on all rows that happened before. While you could get all preceding rows with LAG() you cannot also reference preceding calculations as LAG() cannot reference itself.

Also, the formular in the spreadsheet you attached below does not make sense. It seems to be trying to calculate the EWMA_Chart value but it is failing at that. In the above SQLFiddle I included a column [Wrong] that calculates the same value that the spreadsheet is calculating.

Either way, if you need to use this on a big dataset, you are probably better of writing a cursor.

This is the code that does the calculation in above SQLFiddle. it references th vSMA view that calculates the 10 row moving average.

WITH

smooth AS(
  SELECT CAST(0.1818 AS NUMERIC(20,5)) AS alpha
),

numbered AS(
  SELECT Date, Price, SMA, ROW_NUMBER()OVER(ORDER BY Date) Rn
  FROM vSMA
  WHERE SMA IS NOT NULL
),

EWMA AS(
  SELECT Date, Price, SMA, CAST(SMA AS NUMERIC(20,5)) AS EWMA, Rn
  , CAST(SMA AS NUMERIC(20,5)) AS Wrong
  FROM numbered
  WHERE Rn = 1
  UNION ALL
  SELECT numbered.Date, numbered.Price, numbered.SMA, 
    CAST(EWMA.EWMA * smooth.alpha + CAST(numbered.SMA AS NUMERIC(20,5)) * (1 - smooth.alpha) AS NUMERIC(20,5)), 
    numbered.Rn
    , CAST((numbered.Price - EWMA.EWMA) * smooth.alpha + EWMA.EWMA AS NUMERIC(20,5))
  FROM EWMA
  JOIN numbered
  ON EWMA.rn + 1 = numbered.rn
  CROSS JOIN smooth
)
SELECT Date, Price, SMA, EWMA
, Wrong
FROM EWMA

ORDER BY Date;

Answer 2

Just tried to do an SQL EMA 9 period function.

Alpha: 2 ( 9 + 1) = 0.2 Beta: 1 - Alpha = 0.8

The SQL Query I used was:

SELECT
LAG(ClosePrice,8)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,8) + 
LAG(ClosePrice,7)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,7) * 0.2 +
LAG(ClosePrice,6)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,6) * 0.2 +
LAG(ClosePrice,5)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,5) * 0.2 +
LAG(ClosePrice,4)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,4) * 0.2 +
LAG(ClosePrice,3)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,3) * 0.2 +
LAG(ClosePrice,2)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,2) * 0.2 +
LAG(ClosePrice,1)OVER(ORDER BY QuoteTime DESC) * POWER(0.8,1) * 0.2 + ClosePrice * 0.2 from StreamingStockQuotes

It returns a value of 185.3352.

My dataset (recent to oldest): 181.68, 181.77, 181.98, 182.06, 181.72, 181.74, 181.63, 181.63, 181.59.

It seems that 185, is not possible since it is higher than any of the dataset.