Exponential moving averages are similar to weighted moving averages in that they assign less weight to changes long ago, and more weight to recent changes. Weighted moving averages are linear, but exponential moving averages are exponential. That is, the weight can be expressed as a curve:
There is a great way to calculate exponential moving averages in T-SQL using an undocumented feature about variables and running totals in SQL Server. In this blog post I will show how to use that method to calculate exponential moving average in T-SQL, but I will also present a method that is using standard features in SQL Server. Unfortunately, that means using a loop.
In the examples I will calculate a 9 days exponential moving average. The examples use the database TAdb. A script to create TAdb can be found here.
Exponential Moving Average (EMA): Running Totals Method
The theory behind the running total features in updates is described in detail by Jeff Moden in his article Solving the Running Total and Ordinal Rank Problems.
Other resources that describe using this method to calculate EMA are the blog post Calculating Moving Averages with T-SQL by Gabriel Priester and the forum post Exponential Moving Average Challenge, both on SQL Server Central.
Basically, in T-SQL you can update variables as well as columns in an update statement. The updates are done row by row internally by SQL Server. This row by row behavior is what makes calculating a running total possible.
This example shows how it works:
DECLARE @TBL_RT TABLE ( ColumnToSum INT, ColumnRunningTotal INT NULL ) INSERT @TBL_RT (ColumnToSum) VALUES (1),(2),(3) DECLARE @varRunningTotal INT = 0 UPDATE @TBL_RT SET @varRunningTotal = ColumnRunningTotal = ColumnToSum + @varRunningTotal SELECT * FROM @TBL_RT
ColumnToSum ColumnRunningTotal ----------- ------------------ 1 1 2 3 3 6
Note that “ColumnRunningTotal” is a running total of “ColumnToSum”.
Using this method we can calculate EMA9 with this T-SQL:
IF OBJECT_ID('tempdb..#TBL_EMA9_RT') IS NOT NULL BEGIN DROP TABLE #TBL_EMA9_RT END SELECT *, CAST(NULL AS FLOAT) AS EMA9 INTO #TBL_EMA9_RT FROM dbo.Quotes CREATE UNIQUE CLUSTERED INDEX EMA9_IDX_RT ON #TBL_EMA9_RT (StockId, QuoteId) IF OBJECT_ID('tempdb..#TBL_START_AVG') IS NOT NULL BEGIN DROP TABLE #TBL_START_AVG END SELECT StockId, AVG(QuoteClose) AS Start_Avg INTO #TBL_START_AVG FROM dbo.Quotes WHERE QuoteId <= 9 GROUP BY StockId DECLARE @C FLOAT = 2.0 / (1 + 9), @EMA9 FLOAT UPDATE T1 SET @EMA9 = CASE WHEN QuoteId = 9 then T2.Start_Avg WHEN QuoteId > 9 then T1.QuoteClose * @C + @EMA9 * (1 - @C) END ,EMA9 = @EMA9 FROM #TBL_EMA9_RT T1 JOIN #TBL_START_AVG T2 ON T1.StockId = T2.StockId OPTION (MAXDOP 1) SELECT StockId, QuoteId, QuoteDay, QuoteClose, CAST(EMA9 AS NUMERIC(10,2)) AS EMA9 FROM #TBL_EMA9_RT
The calculation of EMA is rather simple. We use the current row and the previous, but with more weight to the current row. The weight is calculated by the formula 2/(1+9), where “9” is the parameter for the length of the EMA. To calculate EMA9 for row 10 above, the calculation is:
SELECT (29.94*2.0/(1+9)) + 30.17 * (1 - 2.0/(1+9))
In this case the current row gets 20% of the weight (2/(1+9)=0.2) and the previous row gets 80% of the weight (1-2/(1+9)=0.8).
You find this calculation in the statement above in the CASE statement:
WHEN QuoteId > 9 then T1.QuoteClose * @C + @EMA9 * (1 - @C)
Exponential Moving Average (EMA): Looping Method
As far as I know, except for the running totals method outlined above, there is no way to calculate EMA using a set based SQL statement. Therefore, the T-SQL below is using a while loop to calculate EMA9:
IF OBJECT_ID('tempdb..#TBL_EMA9_LOOP') IS NOT NULL BEGIN DROP TABLE #TBL_EMA9_LOOP END SELECT *, CAST(NULL AS FLOAT) AS EMA9 INTO #TBL_EMA9_LOOP FROM dbo.Quotes CREATE UNIQUE CLUSTERED INDEX EMA9_IDX ON #TBL_EMA9_LOOP (StockId, QuoteId) DECLARE @StockId INT = 1, @QuoteId INT, @QuoteIdMax INT, @StartAvg FLOAT, @C FLOAT = 2.0 / (1 + 9), @EMA9 FLOAT WHILE @StockId <= 2 BEGIN SELECT @QuoteId = 1, @QuoteIdMax = MAX(QuoteId) FROM dbo.Quotes WHERE StockId = @StockId SELECT @StartAvg = AVG(QuoteClose) FROM dbo.Quotes WHERE StockId = @StockId AND QuoteId <= 9 WHILE @QuoteId <= @QuoteIdMax BEGIN UPDATE T0 SET EMA9 = CASE WHEN @QuoteId = 9 THEN @StartAvg WHEN @QuoteId > 9 THEN (T0.QuoteClose * @C) + T1.EMA9 * (1.0 - @C) END FROM #TBL_EMA9_LOOP T0 JOIN #TBL_EMA9_LOOP T1 ON T0.StockId = T1.StockId AND T0.QuoteId - 1 = T1.QuoteId WHERE T0.StockId = @StockId AND T0.QuoteId = @QuoteId SELECT @QuoteId = @QuoteId + 1 END SELECT @StockId = @StockId + 1 END SELECT StockId, QuoteId, QuoteDay, QuoteClose, CAST(EMA9 AS NUMERIC(10,2)) AS EMA9 FROM #TBL_EMA9_LOOP
The results are the same as in the running totals example above.
As expected, the set based running totals version is way faster than the loop version. On my machine the set based solution was about 300 ms, compared to about 1200 with the loop version. The loop version is more conforming to SQL standards however. So the choice between the methods depends on what’s most important for you, performance or standards.
There are also other calculations in technical analysis that uses the EMA, MACD for instance.