Formula for EMA(x) :
EMA (x 1 ) = x 1
EMA (x n ) = α * x n + (1 - α) * EMA (x n-1 )
WITH
β: = 1 - α
equivalent
EMA (x n ) = β n-1 * x 1 + α * β n-2 * x 2 + α * β n-3 * x 3 + ... + α * x n
In this form, it is easy to implement using LAG . For 4 rows of EMA, it will 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 think, after EWMA_Chart I created a SQL Fiddle showing how to get there. However, keep in mind that it uses a recursive CTE, which requires one recursion for each row. Thus, on a large dataset, you are likely to get disastrous performance. Recursion is necessary because each row depends on all the rows that were previously. Although you can get all the previous lines using LAG() , you also cannot reference previous calculations, since LAG() cannot refer to yourself.
In addition, the form in the spreadsheet below does not make sense. It seems to be trying to calculate the value of EWMA_Chart, but it fails. In the above SQLFiddle, I included the [Wrong] column, which calculates the same value as the spreadsheet.
In any case, if you need to use this in a large data set, you are probably better off writing a cursor.
This is the code that performs the calculations above SQLFiddle. it refers to the th vSMA , which calculates a moving average of 10 lines.
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;