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

The formula for the last one is above. In the formula, v_{i} = i^{th} 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:

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.