8.11 Extending Moving Averages

8.11.1 Problem

You want to calculate an extended moving average such as is used in the financial industry. In particular, you want to calculate exponential and weighted moving average. Let's say that by looking at a chart of light-bulb sales, you find that they closely mimic the company's stock-price data from the stock exchange. As a possible investor, you want to calculate some of the same moving averages that financial analysts are using.

8.11.2 Solution

To calculate a 90% exponential moving average, you begin with the same basic moving-average framework shown in the previous recipe. The difference is how you calculate the average. Recall that a 90% exponential moving average gives 90% of the importance to the previous value and only 10% of the importance to the current value. You can use a CASE statement in your query to attach the proper importance to the proper occurrence. The following example shows our solution query:

SELECT x.Id, 
   SUM(CASE WHEN y.Id=x.Id 
            THEN 0.9*y.Sales 
            ELSE 0.1*x.Sales END) exponential_average
FROM BulbSales x, BulbSales y
WHERE x.Id>=2 AND x.Id BETWEEN y.Id AND y.Id+1 
GROUP BY x.Id
ORDER BY x.Id

id          exponential_average                                   
----------- -------------------- 
2           9029.0
3           8883.0
4           10227.0
5           9556.0
6           9324.0
7           10174.0
8           9514.0
9           9102.0
10          ...

The framework for calculating an exponential moving average remains the same as for calculating a simple moving average; the only difference is in the part of the query that actually calculates the average. In our solution, the CASE statement checks which y row is currently available. If it is the latest one, the code places a 90% weight on it. Only a 10% weight is given to the preceding value. The SUM function then sums the two adjusted values, and the result is returned as the exponential weighted average for the period.

As an example of how to extend this concept, think about calculating a six-month weighted moving average where each period has an increasing weight as you move towards the current point. The query in the following example shows one solution to this problem. As you can see, the framework again remains the same. The average calculation has just been extended to include more cases.

SELECT x.Id, 
   SUM(CASE WHEN x.Id-y.Id=0 THEN 0.28*y.Sales
            WHEN x.Id-y.Id=1 THEN 0.23*y.Sales
            WHEN x.Id-y.Id=2 THEN 0.20*y.Sales
            WHEN x.Id-y.Id=3 THEN 0.14*y.Sales
            WHEN x.Id-y.Id=4 THEN 0.10*y.Sales
            WHEN x.Id-y.Id=5 THEN 0.05*y.Sales
        END)weighted_average
FROM BulbSales x, BulbSales y
WHERE x.Id>=6 AND x.Id BETWEEN y.Id AND y.Id+6 
GROUP BY x.Id
ORDER BY x.Id

Id          weighted_average                                      
----------- ----------------- 
6           9477.31
7           9675.97
8           9673.43
9           9543.89
10          9547.38
11          9286.62
12          9006.14
13          8883.86
14          8642.43
15          ...

The CASE statement in this example checks to see how far the current y row is from the x row used as a reference and adjusts the value accordingly using a predefined coefficient. You can easily extend the calculation to include ranges, or even dynamic coefficient calculations, depending on your needs. You just need to be careful that the sum of the coefficients is 1. Your weights need to add up to 100%, otherwise your results might look a bit strange.

8.11.3 Discussion

There is a small difference between the two averages shown in this recipe. When calculating an exponential moving average, you assign a proportion of the weight, expressed as a percentage, to each value used in computing the average. Exponential moving averages are calculated using two cases — the current case and its predecessor. When calculating a weighted moving average, you assign weight in terms of multiples to a value in the average. There is no limit to the number of cases that you can use in a weighted moving-average calculation. Exponential moving averages are an often-used special case of weighted moving averages.