Calculating Moving Average in SQL

AnalyticsPosted on Srdjan Stojadinovic

Definition

Moving Average is the classical method of time series decomposition to estimate the trend-cycle. You can calculate it for any period of time. 

Variations of moving average are:

  • Simple Moving Average (SMA)
  • Cumulative / Weighted Moving Average (EMA)

These types of moving averages can be used to identify the direction of the trend or define potential support and resistance levels.

The reason to use a running average is to smooth out short-term fluctuations and highlight longer-term trends or cycles.

Usage

Using Moving Average are mostly the following:

  • Technical analysis of financial data like stock prices, returns or trading volumes and will often use a 50 or 200 day moving average to help them see trends in the stock market and forecast where the stocks are headed.
  • It is used in economics to examine the gross domestic product, employment or other macroeconomic time series.
  • It is used as a Salespeople who often need to estimate the sales of upcoming months by smoothening sales data to see a trend.

Example

The first example shows how is calculated moving average for Internet Sales in Adventure Works DW.

/****** Calculating Quartaly and Average sale from FactInternetSales - Adventure Works DW2017  ******/


SELECT CAST (datepart (YEAR, OrderDate) AS varchar) + '-'+ cast(datepart ([QUARTER], OrderDate)AS varchar) Year_Quarter,


      sum([SalesAmount]) AS SalesAmount,


      avg(sum([SalesAmount])) 
	  OVER (
         ORDER BY CAST (datepart (YEAR, OrderDate) AS varchar) + '-'+ cast(datepart ([QUARTER], OrderDate)AS varchar) ROWS BETWEEN 3 preceding AND CURRENT ROW) QTLY_Moving_Average


FROM [AdventureWorksDW2017].[dbo].[FactInternetSales]


GROUP BY CAST (datepart (YEAR, OrderDate) AS varchar) + '-'+ cast(datepart ([QUARTER], OrderDate)AS varchar) 


ORDER BY 1

The second example shows calculating SMA200 (Simple Moving Average for the last 200 days) for Pandora (PNDORA).

First of all, we will have to create a table to hold stock prices for the last 200 days.

/*
Script for crating table Stock_Prices table
*/

CREATE TABLE [dbo].[StockPrices](
	[Ticker] [nchar](10) NULL,
	[Date] [date] NULL,
	[Close] [float] NULL,
	[Open] [float] NULL,
	[High] [float] NULL,
	[Low] [float] NULL,
	[Volume] [bigint] NULL
) ON [PRIMARY]
GO

Copy/Paste records from Excel Sheet “Pandora AS.xls”. Data is available for download below in Excel format.

https://onedrive.live.com/view.aspx?resid=C560A9DF7B8A8863!33142&ithint=file%2cxlsx&wdo=2&authkey=!AGwORNB7cZH71PY

We are now going to comparing Close price with SMA200, trading volumes and showing a possible correlation of those metrics.

--********* Comparing Close price with SMA200, trading volumes and showing possible corelation of those metrics. *****************
SELECT [date] as Date,

   sum([Close]) AS [Close],

   avg(sum([Close])) OVER (

           ORDER BY [date] ROWS BETWEEN 200 preceding AND CURRENT ROW) as SMA200, -- Using "preceding function to calculate AVG 200 days back

   avg(sum(Volume)) OVER (

           ORDER BY [date] ROWS BETWEEN 200 preceding AND CURRENT ROW) as SMA200_Volume -- Using "preceding function to calculate AVG 200 days back

FROM [PLAYGROUND].[dbo].[StockPrices]

GROUP BY [date] 

ORDER BY 1

This graf is included in Excel workbook together with data if you would like to reuse layout.

Conclusion

In this article, we:

• walked through basic definition of Moving Average/Running Average elements.

• Vent through two different usage of Moving Average calculation with SQL examples.

Related Articles