Given a series of dates with values I want to find the average by averaging
the data by month and then taking the average of these values. I can do this
(see below) by creating an additional temp table but I'd like to do this
with a single SELECT. Thanks.
CREATE TABLE #TestTable
(
TestDate datetime,
TestValue decimal(18,4)
)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060101',
63138498.9442)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060102', 6018866.7861)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060103',
32423405.9143)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060104',
51402184.2973)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060105',
41984683.2967)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060106',
52707356.8602)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060107',
60084423.5696)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060108',
10256576.8087)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060109',
50973538.1018)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060110',
32250398.2328)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060111',
52293744.1444)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060112',
97993941.6661)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060113',
19633802.8665)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060114',
44710651.7875)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060115',
24443905.2030)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060116',
84725698.7918)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060117',
12220534.4395)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060118',
39001430.3432)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060119',
54180100.6619)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060120',
23901300.4697)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060121',
55804613.2757)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060122',
78239680.7124)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060123',
13036976.3434)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060124',
57277368.5493)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060125',
42169222.2497)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060126',
65064414.7238)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060127',
18749702.5770)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060128',
61961708.5242)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060129',
47072115.8125)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060130',
16382094.7146)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060131', 9684892.4976)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060201',
36461918.8525)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060202',
13637750.0447)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060203',
57475888.7342)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060204',
91835958.7101)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060205',
42083192.0164)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060206',
20161492.7101)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060207',
27152605.8613)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060208',
86874863.6639)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060209',
19168743.2577)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060210',
77360233.5777)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060211',
75986666.4099)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060212',
86183511.9510)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060213',
29403571.0172)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060214',
93603660.1728)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060215', 2679256.8156)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060216',
34113885.8418)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060217',
53605552.5087)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060218',
81263888.2651)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060219',
13779351.1302)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060220',
48140149.5757)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060221', 5541682.8948)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060222',
46000410.1362)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060223',
91160648.0837)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060224', 9857530.4764)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060225',
31939509.0333)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060226',
99050916.0463)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060227', 179190.5861)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060228',
80963446.2681)
CREATE TABLE #TestResults
(
TestValue decimal(18,4)
)
INSERT INTO #TestResults
SELECT
AVG(TestValue)
FROM #TestTable
GROUP BY Month(testDate)
SELECT AVG(TestValue) FROM #TestResults
DROP TABLE #TestTable
DROP TABLE #TestResultsTerri,
You can use a derived table for this like so:
SELECT AVG(MonthAvg)
FROM (SELECT AVG(TestValue) AS MonthAvg
FROM #TestTable
GROUP BY CONVERT(CHAR(6), TestDate, 112)) AS D;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Terri" <terri@.cybernets.com> wrote in message
news:e3nr55$8d1$1@.reader2.nmix.net...
> Given a series of dates with values I want to find the average by
> averaging
> the data by month and then taking the average of these values. I can do
> this
> (see below) by creating an additional temp table but I'd like to do this
> with a single SELECT. Thanks.
> CREATE TABLE #TestTable
> (
> TestDate datetime,
> TestValue decimal(18,4)
> )
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060101',
> 63138498.9442)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060102',
> 6018866.7861)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060103',
> 32423405.9143)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060104',
> 51402184.2973)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060105',
> 41984683.2967)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060106',
> 52707356.8602)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060107',
> 60084423.5696)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060108',
> 10256576.8087)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060109',
> 50973538.1018)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060110',
> 32250398.2328)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060111',
> 52293744.1444)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060112',
> 97993941.6661)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060113',
> 19633802.8665)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060114',
> 44710651.7875)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060115',
> 24443905.2030)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060116',
> 84725698.7918)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060117',
> 12220534.4395)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060118',
> 39001430.3432)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060119',
> 54180100.6619)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060120',
> 23901300.4697)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060121',
> 55804613.2757)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060122',
> 78239680.7124)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060123',
> 13036976.3434)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060124',
> 57277368.5493)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060125',
> 42169222.2497)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060126',
> 65064414.7238)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060127',
> 18749702.5770)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060128',
> 61961708.5242)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060129',
> 47072115.8125)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060130',
> 16382094.7146)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060131',
> 9684892.4976)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060201',
> 36461918.8525)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060202',
> 13637750.0447)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060203',
> 57475888.7342)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060204',
> 91835958.7101)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060205',
> 42083192.0164)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060206',
> 20161492.7101)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060207',
> 27152605.8613)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060208',
> 86874863.6639)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060209',
> 19168743.2577)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060210',
> 77360233.5777)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060211',
> 75986666.4099)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060212',
> 86183511.9510)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060213',
> 29403571.0172)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060214',
> 93603660.1728)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060215',
> 2679256.8156)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060216',
> 34113885.8418)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060217',
> 53605552.5087)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060218',
> 81263888.2651)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060219',
> 13779351.1302)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060220',
> 48140149.5757)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060221',
> 5541682.8948)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060222',
> 46000410.1362)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060223',
> 91160648.0837)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060224',
> 9857530.4764)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060225',
> 31939509.0333)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060226',
> 99050916.0463)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060227',
> 179190.5861)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060228',
> 80963446.2681)
> CREATE TABLE #TestResults
> (
> TestValue decimal(18,4)
> )
> INSERT INTO #TestResults
> SELECT
> AVG(TestValue)
> FROM #TestTable
> GROUP BY Month(testDate)
> SELECT AVG(TestValue) FROM #TestResults
>
> DROP TABLE #TestTable
> DROP TABLE #TestResults
>
>
Showing posts with label series. Show all posts
Showing posts with label series. Show all posts
Sunday, February 19, 2012
Sunday, February 12, 2012
autoregression Trees
hi,
I am using Time series alogorrithm.I just wants to know about the autoregression tree.I am having data like
Studid Date Perf
001 01/01/2007 90
001 02/01/2007 95
001 03/01/2007 89
002 01/01/2007 79
002 02/01/2007 90
002 03/01/2007 95
Like that. when I use my Model Viewer --> Descision Tree --> It shows like
Perf = 90.0084 + 1.02 * Perf(-2) + 0.25 * Perf(-2).
What is this value and how its getting calculated?
Basically, the formula tells that the forcasted Perf value is determined by using a regression formula that uses, as regressors, the value Perf had two time slices ago.
If your time key were a date, then Today's Perf depends linearly on Perf two days ago.
More details on the autoregressive tree can be found in the paper on "Autoregressive tree models for Time Series Analysis" by C. Meek, D.M Chickering and David Heckerman, available here: http://research.microsoft.com/~dmax/publications/dmart-final.pdf
Subscribe to:
Posts (Atom)