February 8, 2013 at 12:35 pm
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
February 10, 2013 at 6:20 pm
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?
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 10, 2013 at 7:40 pm
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
February 10, 2013 at 7:56 pm
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2013 at 1:04 am
Gabriel Priester wrote an article discussing the calculation of moving average. Various alternatives are covered in the discussion.
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
February 11, 2013 at 1:33 am
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!
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2013 at 1:47 am
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 😉
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
February 12, 2013 at 4:29 am
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
February 12, 2013 at 5:02 am
mpdillon (2/12/2013)
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
QU=Quirky Update
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2013 at 5:55 am
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?
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
February 13, 2013 at 9:34 am
ChrisM,
I use a lookup Table, TestDate. I agree the article is very well written and informative. Still I was not able to adapt his strategy to utilize a lookup table.
I am using a lookup table to avoid date math. It is my understanding that integer math is much faster. So my lookup table contains columns (most of which are not shown) which will enable me to do integer math.
For this moving average, I am trying to construct a query that utilizes integer math from my lookup table by subtracting the 20 from the column, TestDate.TPIntDate.
Someone pointed out the dates in my sample were not contiguous. This is wholly my fault and is just a typo. I haven’t reposted the code because I am not terribly interested in the results. I am only interested in the method or query which I can apply to our production database.
Thanks again for your assistance. If I still haven’t explained this request thoroughly, please keep asking. I am very appreciative of your assistance.
Thanks,
pat
PS Sometimes there are gaps in how long it takes me to respond. Please do not think it is a lack of interest. Rather it is my current workload. e.g. I left yesterday at 6:30 AM and returned at 1:30 AM today.
February 14, 2013 at 7:20 am
PROBLEM SOLVED!!!!
First, let me thank everyone for the interest and comments. It was a process for me to arrive at the final code.
Step 1:
I needed to visualize how this was working. So I focused on writing a query that would return the data I wanted. I wanted to see a Source, Sink and hour for each day and the preceeding 20 days that matched. The query below shows those data points (the query is for only 2 matching days rather than 20. The smaller number made it easier to experiment with).
with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.testnode N
Inner Join dbo.TestDate D
On N.TimePoint = D.TimePoint
)
Select A.Source, A.Sink, A.TimePoint as TPA, B.TimePoint as TPB, A.TPIntDate as IntDtA,
B.TPIntDate as IntDtB, A.TPHour as HourA, B.TPHour as HourB, A.Delta as DeltaA, B.Delta as DeltaB from MstrTbl A
Inner Join MstrTbl B
On A.source = B.Source and A.Sink = B.Sink and A.TPHour = B.TPHour and A.TPIntDate >= B.TPIntDate and A.TPIntDate -1 <= B.TPIntDate
Order by A.Source,A.TPIntDate,A.TimePoint, b.Timepoint
The first record returned is a single data point because there are no records prior to that date to average.
The next query is the result for which I was searching.
with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.testnode N
Inner Join dbo.TestDate D
On N.TimePoint = D.TimePoint
)
Select A.Source, A.Sink, A.TimePoint as TPA,
A.TPHour as HourA,
avg(B.Delta) as DeltaB, COUNT(A.Source) as NoOfDaysAvged
from MstrTbl A
Inner Join MstrTbl B
On A.source = B.Source and A.Sink = B.Sink and A.TPHour = B.TPHour and A.TPIntDate >= B.TPIntDate and A.TPIntDate -19 <= B.TPIntDate
Group by A.Source, A.Sink, A.TimePoint,A.TPHour
Order by A.Source, A.Sink, A.TimePoint,A.TPHour
I added the count column so I could determine how many data points were in the average. If I needed to ensure that there were always 20 data points in each Moving Average I could add a "Having Count(A.Source) = 20" to the Select statement.
Now that I have this result set, I need to determine how to use this in an Update Statement. But I will start a seperate thread for that question.
Thanks again.
pat
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply