Moving averages smooth extreme values. There are many different ways to calculate a moving average. Examples include:

  • Simple moving average
  • Weighted moving average
  • Exponential moving average

100813_1531_EMAWhenaCu1

The formula for the last one is above. In the formula, vi = ith value, and α and β are weights. The exponential moving average formula includes the previous exponential moving average for calculating the current one. This means that an exponential moving average includes all preceding values in the calculation—more recent values with a higher weight and earlier values with a lower weight.

Here is the code to create a table and populate it with a small data sample:

USE
tempdb;
GO
— Test data table
CREATE
TABLE dbo.MAvg
(Id
INT NOT

NULL
IDENTITY(1,1),
Val

FLOAT
NULL);
GO
— Populate the table
INSERT
INTO
dbo.MAvg(Val)
VALUES
(1),
(2),
(3),
(4),
(1),
(2),
(3),
(4),
(1),
(2);
— Check the contents
SELECT

Id, Val

FROM

dbo.MAvg
ORDER
BY
ID;
GO

The simplest way to calculate the exponential moving average (EMA) is to use a cursor:

— Calculating EMA with a cursor
DECLARE
@CurrentEMA
AS
FLOAT,
@PreviousEMA AS

FLOAT,

@Id
AS
INT,
@Val AS

FLOAT,

@A
AS
FLOAT;
DECLARE

@Results
AS
TABLE(Id
INT,
Val FLOAT,
EMA FLOAT);
SET

@A =
0.7;

DECLARE
EMACursor
CURSOR
FOR

SELECT
Id, Val

FROM
dbo.MAvg
ORDER
BY
Id;

OPEN
EMACursor;

FETCH
NEXT
FROM
EMACursor


INTO
@Id, @Val;
SET @CurrentEMA

=
@Val;
SET @PreviousEMA

=
@CurrentEMA;

WHILE
@@FETCH_STATUS
= 0
BEGIN

SET
@CurrentEMA
=
@A*@Val +
(1@A)*@PreviousEMA;

INSERT
INTO
@Results
(Id, Val, EMA)

VALUES(@Id, @Val, @CurrentEMA);

SET
@PreviousEMA =

@CurrentEMA;
FETCH

NEXT
FROM
EMACursor


INTO @Id, @Val;
END;

CLOSE EMACursor;
DEALLOCATE EMACursor;

SELECT
Id, Val, EMA
FROM
@Results;
GO

You can also calculate an EMA with a recursive CTE, using the original formula:

— Calculating EMA with a recursive CTE
DECLARE
@A
AS
FLOAT;
SET

@A
= 0.7;
WITH
RnCTE
AS
(
SELECT
Id, Val,

ROW_NUMBER()
OVER(ORDER
BY
Id)
AS RN

FROM
dbo.MAvg
),

EMACTE

AS
(


SELECT
Id, RN, Val, Val
AS
EMA

FROM
RnCTE

WHERE
id
= 1

UNION
ALL

SELECT
C.Id, C.RN, C.Val,

@A
*
C.Val +
(1
@A)
* P.EMA
AS
EMA

FROM EMACTE
AS

P

INNER
JOIN RnCTE

AS
C

ON C.RN = P.RN + 1

)
SELECT
*

FROM
EMACTE;
GO

Trying to change the cursor into a set-oriented query does not bring any advantage. The code uses common table expressions and a non-equi join, which can lead to a quadratic algorithm. Note that the code uses a transformed original EMA formula to a formula that expresses the EMA using the original values only instead of referring to the current value and the EMA in the previous time point. Here is the transformed formula:

100813_1531_EMAWhenaCu1

And finally, the set-oriented query:

— Calculating EMA with a set-oriented query
DECLARE
@A
AS
FLOAT;
SET

@A
= 0.7;
WITH
RnCTE
AS
(
SELECT
Id, Val,

ROW_NUMBER()
OVER(ORDER
BY
Id)
AS RN,

FIRST_VALUE(Val)
OVER (ORDER
BY
Id)
AS V1

FROM
dbo.MAvg
),

MaCTE

AS
(

SELECT
RN1.Id
AS
Id, Rn1.RN
AS
RN1, Rn2.RN
AS
RN2,

Rn1.V1, Rn1.Val
AS
YI1, Rn2.Val
AS
YI2,

MAX(RN2.RN)
OVER (PARTITION
BY
RN1.RN)
AS TRC

FROM
RnCTE
AS
Rn1

INNER
JOIN RnCTE

AS
Rn2
ON
Rn1.RN >= Rn2.Rn
)

SELECT Id,
MAX(YI1)
AS YI,

ROUND(

SUM(@A * POWER((1
@A),
(RN1
RN2))
* YI2)

+

MAX(POWER((1
@A),
(TRC 1)))
,
7)
AS EMA

FROM
MaCTE
WHERE
RN2
> 1

GROUP
BY
ID
UNION
SELECT 1,
1, 1

ORDER
BY
Id;
GO

Turns out that the set-oriented query is the least efficient.

Therefore, I am not concluding this blog with a solution. I am concluding it with a challenge: can you find a set-oriented solution that is more efficient than a cursor for calculating the EMA?

I am adding a solution by Quintin du Bruyn.

Here is an efficient set-oriented solution. It soon hits overflow issues, which can be overcome be using a finite window length (noting that your method does the same due to rounding); or waiting for another datatype. It should require a single table scan.

Here is Quintin’s code.

DECLARE
@a
AS
FLOAT
= 0.7,
@b
AS
FLOAT;

SET
@b
= 1
@a;

WITH
cte_cnt
AS

(
SELECT
Id, Val,

ROW_NUMBER()
OVER (ORDER
BY
Id)
1 as exponent


FROM

dbo.MAvg

)

SELECT
Id, Val,


SUM
( CASE

WHEN
exponent
= 0 THEN 1 ELSE
@a
END
*

Val
*
POWER(@b,-exponent)
)
OVER (ORDER
BY
id)
*


POWER(@b, exponent)
AS ema


FROM
cte_cnt;

I checked the code and the mathematics behind it, and it worksJ Quintin, congratulations, you solved it!

And I am sorry for the delay. Of course, Quintin’s solution was posted in the comments to my blog. However, comments in the SolidQ blog site are unreadable. I informed developers and administrators about this issue and hoped for a quick resolution. However, at this time, I still don’t have a date when this would be fixed. Therefore, I decided to modify my blog post in order to make this great solution visible.

Quintin, thank you very much. And I need to thank again also to Jim Bob McGee for his solution, although I still don’t like itJ Thank you both, and sorry for keeping you waiting.

 

Dejan Sarka

Dejan Sarka, MCT and SQL Server MVP, is a trainer and consultant that focuses on development of database & business intelligence applications. Besides projects, he spends about half of the time on training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan Sarka is the main author or coauthor of eleven books about databases and SQL Server. Dejan Sarka also developed many courses and seminars for SolidQ.