Calculating a Moving Average

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Calculating a Moving Average

12 posts, Page 1 of 2

1 2 »»

Calculating a Moving Average

Rate Topic

Display Mode

Topic Options

Author

Message

mpdillon

Posted Friday, February 08, 2013 12:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 103, Visits: 277

Hi,
I have a need to calculate a moving average. Ultimately, I need to convert the Select statement into an Update command. But lets start with the SELECT statement.

I have two tables, TestDate and TestNode. The TestDate table is a date lookup table. It contains an integer field (TPIntDate) which represent the day and can be used for numeric calculations, such as subtracting 20 days.

Here is the code to create two tables and populate them with data.
TestDate Table first.


/****** Object:  Table [dbo].[TestDate]    Script Date: 02/08/2013 13:58:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestDate](
	[TimePoint] [datetime] NULL,
	[TPYear] [int] NULL,
	[TPMth] [int] NULL,
	[TPDay] [int] NULL,
	[TPHour] [int] NULL,
	[TPIntDate] [int] NULL
) ON [PRIMARY]

GO


Insert into TestDate(Timepoint,TPYear,TPMth, TPDay, TPHour, TPIntDate)
select '01/01/2012 05:00:00',2012,1,1,5,1
union
select '01/02/2012 05:00:00',2012,1,2,5,2
union
select '01/03/2012 05:00:00',2012,1,3,5,3
union
select '01/04/2012 05:00:00',2012,1,4,5,4
union
select '01/05/2012 05:00:00',2012,1,5,5,5
union
select '01/06/2012 05:00:00',2012,1,6,5,6
union
select '01/07/2012 05:00:00',2012,1,7,5,7
union
select '01/08/2012 05:00:00',2012,1,8,5,8
union
select '01/09/2012 05:00:00',2012,1,9,5,9
union
select '01/10/2012 05:00:00',2012,1,10,5,10
union
select '01/11/2012 05:00:00',2012,1,11,5,11
union
select '01/12/2012 05:00:00',2012,1,12,5,12
union
select '01/13/2012 05:00:00',2012,1,13,5,13
union
select '01/14/2012 05:00:00',2012,1,14,5,14
union
select '01/15/2012 05:00:00',2012,1,15,5,15
union
select '01/16/2012 05:00:00',2012,1,16,5,16
union
select '01/17/2012 05:00:00',2012,1,17,5,17
union
select '01/18/2012 05:00:00',2012,1,18,5,18
union
select '01/19/2012 05:00:00',2012,1,19,5,19
union
select '01/20/2012 05:00:00',2012,1,20,5,20
union
select '01/21/2012 05:00:00',2012,1,21,5,21
union
select '01/22/2012 05:00:00',2012,1,22,5,22
union
select '01/23/2012 05:00:00',2012,1,23,5,23
union
select '01/24/2012 05:00:00',2012,1,24,5,24
union
select '01/25/2012 05:00:00',2012,1,25,5,25
union
select '01/26/2012 05:00:00',2012,1,26,5,26
union
select '01/27/2012 05:00:00',2012,1,27,5,27
union
select '01/28/2012 05:00:00',2012,1,28,5,28
union
select '01/29/2012 05:00:00',2012,1,29,5,29
union
select '01/30/2012 05:00:00',2012,1,30,5,30
union
select '01/31/2012 05:00:00',2012,1,31,5,31
union
select '02/01/2012 05:00:00',2012,2,1,5,32
union
select '02/02/2012 05:00:00',2012,2,2,5,33
union
select '02/03/2012 05:00:00',2012,2,3,5,34
union
select '02/04/2012 05:00:00',2012,2,4,5,35
union
select '02/05/2012 05:00:00',2012,2,5,5,36
union
select '02/06/2012 05:00:00',2012,2,6,5,37
union
select '02/07/2012 05:00:00',2012,2,7,5,38
union
select '02/08/2012 05:00:00',2012,2,8,5,39
union
select '02/09/2012 05:00:00',2012,2,9,5,40
union
select '02/10/2012 05:00:00',2012,2,10,5,41
union
select '02/11/2012 05:00:00',2012,2,11,5,42
union
select '02/12/2012 05:00:00',2012,2,12,5,43

TestNode table next.



/****** Object:  Table [dbo].[TestNode]    Script Date: 02/08/2013 14:08:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TestNode](
	[TimePoint] [datetime] NULL,
	[Sink] [varchar](50) NULL,
	[Source] [varchar](50) NULL,
	[Delta] [decimal](8, 2) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Insert into TestNode(Timepoint,Sink, Source, Delta)
select '01/01/2012 05:00:00','PEACH BOTTOM','OVEC',1
union
select '01/02/2012 05:00:00','PEACH BOTTOM','OVEC',1.5
union
select '01/03/2012 05:00:00','PEACH BOTTOM','OVEC',1.7
union
select '01/04/2012 05:00:00','PEACH BOTTOM','OVEC',9
union
select '01/05/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '01/06/2012 05:00:00','PEACH BOTTOM','OVEC',-8
union
select '01/07/2012 05:00:00','PEACH BOTTOM','OVEC',5
union
select '01/08/2012 05:00:00','PEACH BOTTOM','OVEC',4
union
select '01/09/2012 05:00:00','PEACH BOTTOM','OVEC',5.1
union
select '01/10/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/11/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '01/12/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/13/2012 05:00:00','PEACH BOTTOM','OVEC',35
union
select '01/14/2012 05:00:00','PEACH BOTTOM','OVEC',-9
union
select '01/15/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '01/16/2012 05:00:00','PEACH BOTTOM','OVEC',47
union
select '01/17/2012 05:00:00','PEACH BOTTOM','OVEC',1.6
union
select '01/18/2012 05:00:00','PEACH BOTTOM','OVEC',7.4
union
select '01/19/2012 05:00:00','PEACH BOTTOM','OVEC',5
union
select '01/20/2012 05:00:00','PEACH BOTTOM','OVEC',6.3
union
select '01/21/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/22/2012 05:00:00','PEACH BOTTOM','OVEC',8
union
select '01/23/2012 05:00:00','PEACH BOTTOM','OVEC',6.5
union
select '01/24/2012 05:00:00','PEACH BOTTOM','OVEC',-7
union
select '01/25/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/26/2012 05:00:00','PEACH BOTTOM','OVEC',6.2
union
select '01/27/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/28/2012 05:00:00','PEACH BOTTOM','OVEC',1
union
select '01/29/2012 05:00:00','PEACH BOTTOM','OVEC',-8
union
select '01/30/2012 05:00:00','PEACH BOTTOM','OVEC',-3
union
select '01/31/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '02/01/2012 05:00:00','PEACH BOTTOM','OVEC',10
union
select '02/02/2012 05:00:00','PEACH BOTTOM','OVEC',9
union
select '02/03/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '02/04/2012 05:00:00','PEACH BOTTOM','OVEC',-9
union
select '02/05/2012 05:00:00','PEACH BOTTOM','OVEC',-74
union
select '02/06/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '02/07/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '02/08/2012 05:00:00','PEACH BOTTOM','OVEC',34
union
select '02/09/2012 05:00:00','PEACH BOTTOM','OVEC',-54
union
select '02/10/2012 05:00:00','PEACH BOTTOM','OVEC',-41
union
select '02/11/2012 05:00:00','PEACH BOTTOM','OVEC',-35
union
select '02/12/2012 05:00:00','PEACH BOTTOM','OVEC',100
Union
select '01/01/2012 05:00:00','PEACH BOTTOM','ELMARA',1
union
select '01/02/2012 05:00:00','PEACH BOTTOM','ELMARA',1.5
union
select '01/03/2012 05:00:00','PEACH BOTTOM','ELMARA',1.7
union
select '01/04/2012 05:00:00','PEACH BOTTOM','ELMARA',9
union
select '01/05/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '01/06/2012 05:00:00','PEACH BOTTOM','ELMARA',-8
union
select '01/07/2012 05:00:00','PEACH BOTTOM','ELMARA',5
union
select '01/08/2012 05:00:00','PEACH BOTTOM','ELMARA',4
union
select '01/09/2012 05:00:00','PEACH BOTTOM','ELMARA',5.1
union
select '01/10/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/11/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '01/12/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/13/2012 05:00:00','PEACH BOTTOM','ELMARA',35
union
select '01/14/2012 05:00:00','PEACH BOTTOM','ELMARA',-9
union
select '01/15/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '01/16/2012 05:00:00','PEACH BOTTOM','ELMARA',47
union
select '01/17/2012 05:00:00','PEACH BOTTOM','ELMARA',1.6
union
select '01/18/2012 05:00:00','PEACH BOTTOM','ELMARA',7.4
union
select '01/19/2012 05:00:00','PEACH BOTTOM','ELMARA',5
union
select '01/20/2012 05:00:00','PEACH BOTTOM','ELMARA',6.3
union
select '01/21/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/22/2012 05:00:00','PEACH BOTTOM','ELMARA',8
union
select '01/23/2012 05:00:00','PEACH BOTTOM','ELMARA',6.5
union
select '01/24/2012 05:00:00','PEACH BOTTOM','ELMARA',-7
union
select '01/25/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/26/2012 05:00:00','PEACH BOTTOM','ELMARA',6.2
union
select '01/27/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/28/2012 05:00:00','PEACH BOTTOM','ELMARA',1
union
select '01/29/2012 05:00:00','PEACH BOTTOM','ELMARA',-8
union
select '01/30/2012 05:00:00','PEACH BOTTOM','ELMARA',-3
union
select '01/31/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '02/01/2012 05:00:00','PEACH BOTTOM','ELMARA',10
union
select '02/02/2012 05:00:00','PEACH BOTTOM','ELMARA',9
union
select '02/03/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '02/04/2012 05:00:00','PEACH BOTTOM','ELMARA',-9
union
select '02/05/2012 05:00:00','PEACH BOTTOM','ELMARA',-74
union
select '02/06/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '02/07/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '02/08/2012 05:00:00','PEACH BOTTOM','ELMARA',34
union
select '02/09/2012 05:00:00','PEACH BOTTOM','ELMARA',-54
union
select '02/10/2012 05:00:00','PEACH BOTTOM','ELMARA',-41
union
select '02/11/2012 05:00:00','PEACH BOTTOM','ELMARA',-35
union
select '02/12/2012 05:00:00','PEACH BOTTOM','ELMARA',100

There is a record for every combination of Source, Sink, day and hour. Every source/sink combination has a specific Date and time (Time is in hours, 0 - 23). The supplied data only contains two Source/Sink pairs for only one hour of the day across 43 days.

Here is what I have tried but this code receives an error.

Select N.Source, N.Sink, N.TimePoint, D.TPHour, AVG(Delta)as MA20 From dbo.TestNode N
inner join dbo.TestDate D
On N.Timepoint = D.TimePoint 
inner Join Dbo.TestNode N2
on N.Source = N2.Source and N.Sink = N2.Source and D.TPHour = D2.TPHour and  D2.TPIntDate <= D.TPIntDate and D2.TPIntDate >= D.TPIntDate - 20
inner join dbo.TestDate D2
On N2.TimePoint = D2.TimePoint
Group by N.Source, N.Sink, N.TimePoint, D.TPHour

Could some please assist me in writing a select statement that will return a moving average (20 day) for every Souce, Sink, Date and Time record?

Thank you in advance,
pat

Post #1417876

dwain.c

Posted Sunday, February 10, 2013 6:20 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,370, Visits: 3,250

If your TestNode data contains contiguous dates (meaning no missing dates) for both source/sinks (as provided), there is no need to use the TestDate table.

SELECT a.Sink, a.[Source], a.TimePoint, [20DayAvg]=AVG(a.Delta)
FROM TestNode a
INNER JOIN TestNode b
    ON a.Sink = b.Sink AND a.[Source] = b.[Source] AND
        b.Timepoint BETWEEN a.TimePoint - 19 AND a.TimePoint
GROUP BY a.Sink, a.[Source], a.TimePoint
HAVING COUNT(*) = 20
ORDER BY a.[Sink], a.[Source], a.TimePoint

This assumes you want records with a 20 day moving average starting on 21 Jan.

If your actual records are missing data for some days, how would you calculate the moving average? Would you want to go back the 20 days where there are records?

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1418163

mpdillon

Posted Sunday, February 10, 2013 7:40 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 103, Visits: 277

Dwain,
Thanks. I was presenting the most simplistic example I would need to process. I was hoping once this basic query was working I would be able to increase its complexity on my own. This example does use contiguous data. However, all the queries that will be built from this query will not use contiguous data. So I would like to keep the date table.

The date table contains numbers representing the DOW, Year, Month and a day counter. I created this lookup table so that all the date math that is required can be performed on Integers rather than DateTime fields. This was advice I had received here on SQLServerCentral.

The only records that may be missing data would be those at the beginning of the dataset (First 19 days. We haven’t discussed how we will deal with those points). A data point will always be defined as the Day and hour of a Source/Sink combination. Values exists for every Source/Sink combination by hour for every day.

I realize the date table complicates this simplistic example but I think I need to keep it. Any suggestions?

Thanks,
pat

Post #1418187

dwain.c

Posted Sunday, February 10, 2013 7:56 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,370, Visits: 3,250

Try removing some of the TestNode data for ELMARA and then see if the modified query gives you what you want.

--union
--select '01/27/2012 05:00:00','PEACH BOTTOM','ELMARA',7
--select '02/06/2012 05:00:00','PEACH BOTTOM','ELMARA',6
--union
--select '02/10/2012 05:00:00','PEACH BOTTOM','ELMARA',-41
--union

SELECT a.Sink, a.[Source], a.TimePoint, [20DayAvg]=AVG(a.Delta)
FROM TestNode a
INNER JOIN TestDate b
    ON -- a.Sink = b.Sink AND a.[Source] = b.[Source] AND
        b.Timepoint BETWEEN a.TimePoint - 19 AND a.TimePoint
GROUP BY a.Sink, a.[Source], a.TimePoint
HAVING COUNT(*) = 20
ORDER BY a.[Sink], a.[Source], a.TimePoint

Note that I substituted TestDate for TestNode as derived table b.

If it does not give you what you want (you'll find that 27 Jan, 06&10 Feb records are missing for ELMARA) then you'll need to be a little more explicit with respect to your expected output results.

Post #1418189

ChrisM@Work

Posted Monday, February 11, 2013 1:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136

Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1418240

dwain.c

Posted Monday, February 11, 2013 1:33 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,370, Visits: 3,250

ChrisM@Work (2/11/2013)

Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.

A QU for moving averages??? w00t

Who'da thunk it!

Post #1418250

ChrisM@Work

Posted Monday, February 11, 2013 1:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136

dwain.c (2/11/2013)

ChrisM@Work (2/11/2013)

Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.

A QU for moving averages??? w00t

Who'da thunk it!

It's almost as sneaky as some of the stuff you've been messing with recently Wink

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1418254

mpdillon

Posted Tuesday, February 12, 2013 4:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 103, Visits: 277

ChrisM and DwainC,
Thank you for the reference. I had actually seen that before. I used it as best I could. But since it didn't use the lookup table, I was not able to fully implement it.

What is a QU?

I am still stuck.

pat

Post #1418867

dwain.c

Posted Tuesday, February 12, 2013 5:02 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 2,370, Visits: 3,250

mpdillon (2/12/2013)

QU=Quirky Update

Post #1418885

ChrisM@Work

Posted Tuesday, February 12, 2013 5:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705, Visits: 11,136

mpdillon (2/12/2013)

...since it didn't use the lookup table, I was not able to fully implement it....

Which lookup table, Pat? The article (and the discussion) should provide you with all the information you need to calculate SMA's. What do you need help with?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1418907

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions