Exponential Moving Average Calculations in SQL Server

Financial systems use a large variety of different analysis functions to identify trends and provide other derivations from periodic price or volume data.  One of these is the Exponential Moving Average (EMA), which provides a price average where more recent periods are weighted more significantly than older periods.  I'm going to demonstrate a way this can be calculated in SQL Server's T-SQL in a set-based way.

The EMA has only one parameter, which is a number of periods from which the weighting value is calculated.

The calculation is:

weight = 2 / ( number of periods + 1 )  [which will yield a value between 0 and 1]

EMA = CurrentPrice * weight + PreviousPeriodEMA * (1 - weight)

    = p₀ * w + EMA₁ * (1 - w)

where p₀ is the price for the current period, EMA₁ is the EMA value for the previous period, and w is the weighting factor.

As each EMA is calculated from the previous EMA, most solutions calculate this iteratively, but it can be calculated in a set-based manner.

Row Generation in SQL Server

While writing complex queries for SQL Server, I have often needed to generate a specific number of records in order to solve a problem in a set-based manner.  At times like this I reach for a trusty Row Generator functions (also referred to as a "numbers table").

Here is a simple implementation of a row generator as a table-valued function: