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, 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:
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?