Calculating a Moving Average

Calculating a daily average measurement is easy, but computing a moving daily average is a greater challenge. Here's a way to average daily temperatures over a month.

Itzik Ben-Gan

July 31, 2002

1 Min Read
ITPro Today logo

One of my tables contains meteorological measurements per minute (e.g., temperature and humidity), and I'm trying to use a GROUP BY clause to calculate a moving daily average per month for these measurements. I want to express this average in a smalldatetime data type. Calculating a daily average temperature is easy, but I want to union these averages for a month. How can I create the data I need?

To see how you can return the moving average, first create a Measurements table and populate it with sample data for testing. Listing 1 populates the Measurements table with one year's worth of six random daily temperature measurements. The trick in calculating a daily temperature average is to use an expression in the GROUP BY clause. The expression CONVERT(char(10), DT, 112) in Listing 2's script extracts only the date part of the DT column, which contains the measurement's date and time. By using this expression in the GROUP BY clause, you return a separate row for each day. Then, you need to request the average temperature and filter the desired month, as Listing 2 shows. This statement returns a month's worth of daily temperature averages, which constitute the moving average. Table 1 displays the output from Listing 2.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like