# Calculating Exponential Moving Average in SQL with Recursive CTEs

Similar to simple/weighted moving averages, exponential moving averages (EMA) smooth out the observed data values. The difference is that these methods use the previously calculated EMA value as a basis rather than the original (non-smooth) data value. Since EMA builds upon itself, all previous data values have some effect on the new EMA, though the effect diminishes quickly with time.

## 1. The math

The equation for EMA is recursive, i.e., new values use the previously calculated EMA values:

where,

Variable | Explanation |
---|---|

Current observation at . | |

Current EMA at , i.e., EMA to be calculated. | |

Smoothing factor that determines how much weight we give to the most recent observed value versus the last calculated EMA. is between 1 and 0. A value of 0.5 weighs both sides equally. | |

Last calculated EMA, with a special value of to be equal to the first data point. |

## 2. The SQL with PostgreSQL recursive query

To implement this in SQL, let's take an illustrative example: our data is sales data that shows a strong seasonal component. Moreover, it's trending higher period-over-period.

The table has two columns: dt a timestamp and sales as a number.

To solve the recursive equation, we'll use PostgreSQL's recursive queries. PostgreSQL queries can have a `WITH`

clause, that allows you use statements – know as common table expressions (CTE) – that behave like temporary tables that only exist during the execution of the query.

If the CTE contains a recursive component, that's a recursive query. Recursive queries are useful to query data that demonstrate a hierarchical nature.

To define a recursive query, we need two parts – an initial query that is non-recursive and the recursive part. The general form of a recursive query is as follows:

```
WITH cte_name (
CTE_query_definition -- non-recursive portion
UNION [ALL]
CTE_query_definion -- recursive portion
)
SELECT * from cte_name;
```

For the non-recursive portion, we will pick out the first row of the sales data. We can get the first row by numbering the results with the `row_number`

window function and adding a `WHERE`

clause:

```
SELECT * FROM (
SELECT dt,
sales,
row_number() OVER ()
FROM sales_data) w
WHERE row_number = 1;
```

For the non-recursive portion, we use a self-join with the rows offset by 1 to get the previous row (we additionally re-factor getting the columns from the table as `t`

)

```
WITH t as (
SELECT dt,
sales,
row_number() OVER ()
)
SELECT * FROM t
JOIN t t2 ON t2.row_number = t.row_number - 1;
```

Now, it's a matter of filling in the equation to calculate EMA, a `UNION`

with the non-recursive part and making sure both portions have the same number of columns.

```
WITH RECURSIVE t AS (
SELECT dt,
0.5 AS alpha,
row_number() OVER (),
sales
FROM sales_data
),
ema AS (
SELECT *, sales AS sales_ema FROM t
WHERE row_number = 1
UNION ALL
SELECT t2.dt,
t2.alpha,
t2.row_number,
t2.sales,
t2.alpha * t2.sales + (1.0 - t2.alpha) * ema.sales AS sales_ema
FROM ema
JOIN t t2 ON ema.row_number = t2.row_number - 1
)
SELECT dt, sales, sales_ema
FROM ema;
```

No spam, ever! Unsubscribe any time.