Calculating Moving Averages with T-SQL
-
I wonder...
Make some data:
DROP TABLE #TempSELECT TOP 1000000
RowID = IDENTITY(int, 1, 1),
Avg20day = CAST(NULL AS DECIMAL(16,6))
INTO #Temp
FROM master.dbo.syscolumns a, master.dbo.syscolumns b
CREATE UNIQUE CLUSTERED INDEX RowID ON #Temp (RowID)
-- (1000000 row(s) affected)
Write some dodgy code:
DECLARE @AVG1 DECIMAL(16,6), @AVG2 DECIMAL(16,6), @AVG3 DECIMAL(16,6), @AVG4 DECIMAL(16,6), @AVG5 DECIMAL(16,6),@AVG6 DECIMAL(16,6), @AVG7 DECIMAL(16,6), @AVG8 DECIMAL(16,6), @AVG9 DECIMAL(16,6), @AVG10 DECIMAL(16,6),
@AVG11 DECIMAL(16,6), @AVG12 DECIMAL(16,6), @AVG13 DECIMAL(16,6), @AVG14 DECIMAL(16,6), @AVG15 DECIMAL(16,6),
@AVG16 DECIMAL(16,6), @AVG17 DECIMAL(16,6), @AVG18 DECIMAL(16,6), @AVG19 DECIMAL(16,6), @AVG20 DECIMAL(16,6)
SELECT @AVG1 = 0, @AVG2 = 0, @AVG3 = 0, @AVG4 = 0, @AVG5 = 0, @AVG6 = 0, @AVG7 = 0, @AVG8 = 0, @AVG9 = 0, @AVG10 = 0,
@AVG11 = 0, @AVG12 = 0, @AVG13 = 0, @AVG14 = 0, @AVG15 = 0, @AVG16 = 0, @AVG17 = 0, @AVG18 = 0, @AVG19 = 0, @AVG20 = 0
UPDATE #Temp SET
@AVG1 = CASE WHEN 1+(RowID-1)%20 = 1 THEN RowID ELSE @AVG1 + RowID END,
@AVG2 = CASE WHEN 1+(RowID-1)%20 = 2 THEN RowID ELSE @AVG2 + RowID END,
@AVG3 = CASE WHEN 1+(RowID-1)%20 = 3 THEN RowID ELSE @AVG3 + RowID END,
@AVG4 = CASE WHEN 1+(RowID-1)%20 = 4 THEN RowID ELSE @AVG4 + RowID END,
@AVG5 = CASE WHEN 1+(RowID-1)%20 = 5 THEN RowID ELSE @AVG5 + RowID END,
@AVG6 = CASE WHEN 1+(RowID-1)%20 = 6 THEN RowID ELSE @AVG6 + RowID END,
@AVG7 = CASE WHEN 1+(RowID-1)%20 = 7 THEN RowID ELSE @AVG7 + RowID END,
@AVG8 = CASE WHEN 1+(RowID-1)%20 = 8 THEN RowID ELSE @AVG8 + RowID END,
@AVG9 = CASE WHEN 1+(RowID-1)%20 = 9 THEN RowID ELSE @AVG9 + RowID END,
@AVG10 = CASE WHEN 1+(RowID-1)%20 = 10 THEN RowID ELSE @AVG10 + RowID END,
@AVG11 = CASE WHEN 1+(RowID-1)%20 = 11 THEN RowID ELSE @AVG11 + RowID END,
@AVG12 = CASE WHEN 1+(RowID-1)%20 = 12 THEN RowID ELSE @AVG12 + RowID END,
@AVG13 = CASE WHEN 1+(RowID-1)%20 = 13 THEN RowID ELSE @AVG13 + RowID END,
@AVG14 = CASE WHEN 1+(RowID-1)%20 = 14 THEN RowID ELSE @AVG14 + RowID END,
@AVG15 = CASE WHEN 1+(RowID-1)%20 = 15 THEN RowID ELSE @AVG15 + RowID END,
@AVG16 = CASE WHEN 1+(RowID-1)%20 = 16 THEN RowID ELSE @AVG16 + RowID END,
@AVG17 = CASE WHEN 1+(RowID-1)%20 = 17 THEN RowID ELSE @AVG17 + RowID END,
@AVG18 = CASE WHEN 1+(RowID-1)%20 = 18 THEN RowID ELSE @AVG18 + RowID END,
@AVG19 = CASE WHEN 1+(RowID-1)%20 = 19 THEN RowID ELSE @AVG19 + RowID END,
@AVG20 = CASE WHEN 1+(RowID-1)%20 = 20 THEN RowID ELSE @AVG20 + RowID END,
Avg20day = CASE
WHEN 1+(RowID-1)%20 = 20 AND RowID > 19 THEN @AVG1/20
WHEN 1+(RowID-1)%20 = 1 AND RowID > 20 THEN @AVG2/20
WHEN 1+(RowID-1)%20 = 2 AND RowID > 21 THEN @AVG3/20
WHEN 1+(RowID-1)%20 = 3 AND RowID > 22 THEN @AVG4/20
WHEN 1+(RowID-1)%20 = 4 AND RowID > 23 THEN @AVG5/20
WHEN 1+(RowID-1)%20 = 5 AND RowID > 24 THEN @AVG6/20
WHEN 1+(RowID-1)%20 = 6 AND RowID > 25 THEN @AVG7/20
WHEN 1+(RowID-1)%20 = 7 AND RowID > 26 THEN @AVG8/20
WHEN 1+(RowID-1)%20 = 8 AND RowID > 27 THEN @AVG9/20
WHEN 1+(RowID-1)%20 = 9 AND RowID > 28 THEN @AVG10/20
WHEN 1+(RowID-1)%20 = 10 AND RowID > 29 THEN @AVG11/20
WHEN 1+(RowID-1)%20 = 11 AND RowID > 30 THEN @AVG12/20
WHEN 1+(RowID-1)%20 = 12 AND RowID > 31 THEN @AVG13/20
WHEN 1+(RowID-1)%20 = 13 AND RowID > 32 THEN @AVG14/20
WHEN 1+(RowID-1)%20 = 14 AND RowID > 33 THEN @AVG15/20
WHEN 1+(RowID-1)%20 = 15 AND RowID > 34 THEN @AVG16/20
WHEN 1+(RowID-1)%20 = 16 AND RowID > 35 THEN @AVG17/20
WHEN 1+(RowID-1)%20 = 17 AND RowID > 36 THEN @AVG18/20
WHEN 1+(RowID-1)%20 = 18 AND RowID > 37 THEN @AVG19/20
WHEN 1+(RowID-1)%20 = 19 AND RowID > 38 THEN @AVG20/20
ELSE Avg20day END
Now run the code.
13 seconds on this lappy at home. Yep, it surprised me too. This is what I call the "multiply and conquer" approach and it rarely works :hehe:
Cheers
ChrisM
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Chris -
That's very nice work. I ran it on the code I used in the article, and modified all the decimal variables/fields to (8,2) and it provided just as good results as I provided.
I was experimenting with something similar, but I was storing the values in each row (col1, col2, col3, col4, col5....col 19, col20) - not nearly as efficient as this. I've been trying to do something similar to this with stochastic oscillators and bollinger bands, I think this is way to do it - albeit the code will be extremely difficult to read...
-
Gabriel P (3/13/2010)
Chris -That's very nice work. I ran it on the code I used in the article, and modified all the decimal variables/fields to (8,2) and it provided just as good results as I provided.
I was experimenting with something similar, but I was storing the values in each row (col1, col2, col3, col4, col5....col 19, col20) - not nearly as efficient as this. I've been trying to do something similar to this with stochastic oscillators and bollinger bands, I think this is way to do it - albeit the code will be extremely difficult to read...
Thanks Gabriel.
It was an experiment, I wanted to get a rough idea of how much the running totals update would slow up if it was loaded up with variables and value assignments. The answer is - not as much as you might expect. That kinda suggests to me that a well-written recursive CTE would be quick too, perhaps double the run time.
You nailed the real catch, though - it's a lot of code to maintain compared with your elegant solution. I wouldn't want to do anything much more complicated with it, apart from perhaps having a go at modifying it to calculate the MACD figures. Heh - might even try doing that with a rCTE.
Good luck with your stochastic oscillators and bollinger bands. I prefer labradors and cats.
Cheers
ChrisM
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
I'd be interested to know whether anyone has an approach to calculate time-based moving averages where the time stamps are irregularly spaced. This is how financial data is often recorded intraday - not in bars of regular time intervals, but whenever a trade or quote is posted. Thus, one cannot use the exact same approach as proposed in this excellent article - one has to do calculations using the timestamp as a span indicator, rather than the number of rows.
Any thoughts?
-
greg 2201 (9/23/2010)
I'd be interested to know whether anyone has an approach to calculate time-based moving averages where the time stamps are irregularly spaced. This is how financial data is often recorded intraday - not in bars of regular time intervals, but whenever a trade or quote is posted. Thus, one cannot use the exact same approach as proposed in this excellent article - one has to do calculations using the timestamp as a span indicator, rather than the number of rows.Any thoughts?
I think you would have to figure out how you want to approach it mathematically first. The big question for me is - would you want and average of 3 prices represented the same way as an average of 20 prices?
-
One wouldn't want the average of 20 prices to be represented the same as say 3 prices, but one might very well want the average of 20 seconds worth of prices to be calculated in the same way as 3 seconds worth of prices. However, inside those 20 second or 3 second intervals, one might have a very different density of price updates (eg around economic data releases, when many prices move around wildly for a few seconds as new quotes are posted to the market).
Perhaps it's necessary to reduce a time-stamped time series into a regularly spaced time series of "bars", eg at one minute intervals, and then to use those one minute bars for moving avg calcs. The tricky part is the first one however - figuring out how to reduce irregularly time spaced rows into regularly spaced bars. Most approaches will use a query that groups data by each minute and then looks for the average or the last valid price in that group. This runs very slowly however, and is memory intensive.
-
greg 2201 (9/23/2010)
One wouldn't want the average of 20 prices to be represented the same as say 3 prices, but one might very well want the average of 20 seconds worth of prices to be calculated in the same way as 3 seconds worth of prices. However, inside those 20 second or 3 second intervals, one might have a very different density of price updates (eg around economic data releases, when many prices move around wildly for a few seconds as new quotes are posted to the market).Perhaps it's necessary to reduce a time-stamped time series into a regularly spaced time series of "bars", eg at one minute intervals, and then to use those one minute bars for moving avg calcs. The tricky part is the first one however - figuring out how to reduce irregularly time spaced rows into regularly spaced bars. Most approaches will use a query that groups data by each minute and then looks for the average or the last valid price in that group. This runs very slowly however, and is memory intensive.
Looks like a challenge to me, Greg. Post the table scripts and some data, then sit back and watch 😉
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Here is one approach for regularizing data into time "bars" that I found, posted on dbformus: http://www.dbforums.com/microsoft-sql-server/1660120-regularizing-time-intervals.html#post6478463
It runs very slowly however due to all the "group by" activity.
DECLARE @t TABLE (
midpointMONEY
, timestampDATETIME
)
INSERT INTO @t
SELECT 1.234565, '2010-05-05 16:04:23.330' UNION
SELECT 1.234533, '2010-05-05 16:04:23.997' UNION
SELECT 1.234222, '2010-05-05 16:04:24.102'
SELECT * FROM @t
SELECT (SELECT AVG(z.midpoint)
FROM @t AS z
WHERE z.timestamp = MAX(a.timestamp)) AS last_midpoint
, DateAdd(second, DATEDIFF(second, DateAdd(day
, DateDiff(day, 0, timestamp), 0), timestamp)
, DateAdd(day, DateDiff(day, 0, timestamp), 0))
FROM @t AS a
GROUP BY DateAdd(second, DATEDIFF(second
, DateAdd(day, DateDiff(day, 0, timestamp), 0)
, timestamp), DateAdd(day, DateDiff(day, 0
, timestamp), 0))
-
greg 2201 (9/24/2010)
Here is one approach for regularizing data into time "bars" that I found, posted on dbformus: http://www.dbforums.com/microsoft-sql-server/1660120-regularizing-time-intervals.html#post6478463It runs very slowly however due to all the "group by" activity.
Soooooo.... we can make it MUCH faster but how do you want it grouped? By minute, by hour, by day?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ideally by minute or by second.
-
First, my humble apologies for how long this has taken... I've been pretty busy.
Second, let's expand the test a bit. Let's say that we got a day's worth of data and there are 100,000 rows of data for the day...
--===== Conditionally drop the test table to make reruns easierIF OBJECT_ID('TempDB..#JBMTest','U') IS NOT NULL
DROP TABLE #JBMTest
;
SELECT TOP (100000) --Change this to the number of entries you want to test with
MidPoint = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY),
TimeStamp = CAST('2010' AS DATETIME) + (RAND(CHECKSUM(NEWID()))*1) --Change the * to the number of days you want to test with
INTO #JBMTest
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.All_Columns ac2
;
Here's your original "GROUP BY" code followed by some alternate code to do the same thing. Keep in mind that neither do any averaging... they both just find the latest data for any given second. The details are in the code...
--========== Original "GROUP BY" Code =================================================================================DBCC FREEPROCCACHE; --Make the test "fair" for both pieces of code
SELECT (SELECT AVG(z.midpoint)
FROM #JBMTest AS z
WHERE z.timestamp = MAX(a.timestamp)) AS last_midpoint
, DateAdd(second, DATEDIFF(second, DateAdd(day
, DateDiff(day, 0, timestamp), 0), timestamp)
, DateAdd(day, DateDiff(day, 0, timestamp), 0))
FROM #JBMTest AS a
GROUP BY DateAdd(second, DATEDIFF(second
, DateAdd(day, DateDiff(day, 0, timestamp), 0)
, timestamp), DateAdd(day, DateDiff(day, 0
, timestamp), 0))
;
GO
--========== Alternate Code ===========================================================================================
DBCC FREEPROCCACHE; --Make the test "fair" for both pieces of code
WITH
cteFindMax AS
(
SELECT SortOrder = ROW_NUMBER() OVER (PARTITION BY DATEADD(ss,DATEDIFF(ss,'20000101',timestamp),'20000101') ORDER BY TimeStamp DESC),
MidPoint, TimeStamp
FROM #JBMTest
)
SELECT LastMidPoint = MidPoint, TimeStamp = DATEADD(ss,DATEDIFF(ss,'20000101',timestamp),'20000101')
FROM cteFindMax
WHERE SortOrder = 1
;
GO
Here are what the results look like in SQL Profiler on my 8 year old machine...
Overall, the alternative code runs in about 1/3rd of the time not to mention that it's a whole lot easier to discern.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, I tried with the SMA code from article and all I get is ERRORS...
Does any one have the final script that works ??
Here is code I copied..
--Create our historical data tablecreate table #google_stock(quote_date [datetime],open_price [decimal](6,2),close_price [decimal](6,2),high_price [decimal](6,2),low_price [decimal](6,2))INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091026', 555.75, 554.21, 561.64, 550.89) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091027', 550.97, 548.29, 554.56, 544.16) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091028', 547.87, 540.30, 550.00, 538.25) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091029', 543.01, 551.05, 551.83, 541.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091030', 550.00, 536.12, 550.17, 534.24) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091102', 537.08, 533.99, 539.46, 528.24) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091103', 530.01, 537.29, 537.50, 528.30) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091104', 540.80, 540.33, 545.50, 536.42) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091105', 543.49, 548.65, 549.77, 542.66) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091106', 547.72, 551.10, 551.78, 545.50) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091109', 555.45, 562.51, 562.58, 554.23) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091110', 562.73, 566.76, 568.78, 562.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091111', 570.48, 570.56, 573.50, 565.86) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091112', 569.56, 567.85, 572.90, 565.50) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091113', 569.29, 572.05, 572.51, 566.61) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091116', 575.00, 576.28, 576.99, 572.78) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091117', 574.87, 577.49, 577.50, 573.72) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091118', 576.65, 576.65, 578.78, 572.07) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091119', 573.77, 572.99, 574.00, 570.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091120', 569.50, 569.96, 571.60, 569.40) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091123', 576.49, 582.35, 586.60, 575.86) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091124', 582.52, 583.09, 584.29, 576.54) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091125', 586.41, 585.74, 587.06, 582.69) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091127', 572.00, 579.76, 582.46, 570.97) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091130', 580.63, 583.00, 583.67, 577.11) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091201', 588.13, 589.87, 591.22, 583.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091202', 591.00, 587.51, 593.01, 586.22) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091203', 589.04, 585.74, 591.45, 585.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091204', 593.02, 585.01, 594.83, 579.18) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091207', 584.21, 586.25, 588.69, 581.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091208', 583.50, 587.05, 590.66, 582.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091209', 587.50, 589.02, 589.33, 583.58) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091210', 590.44, 591.50, 594.71, 590.41) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091211', 594.68, 590.51, 594.75, 587.73) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091214', 595.35, 595.73, 597.31, 592.61) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091215', 593.30, 593.14, 596.38, 590.99) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091216', 598.60, 597.76, 600.37, 596.64) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091217', 596.44, 593.94, 597.64, 593.76) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091218', 596.03, 596.42, 598.93, 595.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091221', 597.61, 598.68, 599.84, 595.67) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091222', 601.34, 601.12, 601.50, 598.85) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091223', 603.50, 611.68, 612.87, 602.85) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091224', 612.93, 618.48, 619.52, 612.27) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091228', 621.66, 622.87, 625.99, 618.48) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091229', 624.74, 619.40, 624.84, 618.29) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091230', 618.50, 622.73, 622.73, 618.01) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091231', 624.75, 619.98, 625.40, 619.98) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100104', 626.95, 626.75, 629.51, 624.24) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100105', 627.18, 623.99, 627.84, 621.54) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100106', 625.86, 608.26, 625.86, 606.36) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100107', 609.40, 594.10, 610.00, 592.65) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100108', 592.00, 602.02, 603.25, 589.11) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100111', 604.46, 601.11, 604.46, 594.04) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100112', 597.65, 590.48, 598.16, 588.00) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100113', 576.49, 587.09, 588.38, 573.90) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100114', 583.90, 589.85, 594.20, 582.81) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100115', 593.34, 580.00, 593.56, 578.04) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100119', 581.20, 587.62, 590.42, 576.29) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100120', 585.98, 580.41, 585.98, 575.29) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100121', 583.44, 582.98, 586.82, 572.25) INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100122', 564.50, 550.01, 570.60, 534.86)
IF OBJECT_ID('tempdb..#google_stock') IS NOT NULL DROP TABLE #google_stock;
go
create table #google_stock(quote_date [datetime],open_price [decimal](6,2),close_price [decimal](6,2),high_price [decimal](6,2),low_price [decimal](6,2))
go
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091026', 555.75, 554.21, 561.64, 550.89)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091027', 550.97, 548.29, 554.56, 544.16)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091028', 547.87, 540.30, 550.00, 538.25)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091029', 543.01, 551.05, 551.83, 541.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091030', 550.00, 536.12, 550.17, 534.24)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091102', 537.08, 533.99, 539.46, 528.24)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091103', 530.01, 537.29, 537.50, 528.30)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091104', 540.80, 540.33, 545.50, 536.42)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091105', 543.49, 548.65, 549.77, 542.66)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091106', 547.72, 551.10, 551.78, 545.50)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091109', 555.45, 562.51, 562.58, 554.23)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091110', 562.73, 566.76, 568.78, 562.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091111', 570.48, 570.56, 573.50, 565.86)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091112', 569.56, 567.85, 572.90, 565.50)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091113', 569.29, 572.05, 572.51, 566.61)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091116', 575.00, 576.28, 576.99, 572.78)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091117', 574.87, 577.49, 577.50, 573.72)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091118', 576.65, 576.65, 578.78, 572.07)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091119', 573.77, 572.99, 574.00, 570.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091120', 569.50, 569.96, 571.60, 569.40)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091123', 576.49, 582.35, 586.60, 575.86)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091124', 582.52, 583.09, 584.29, 576.54)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091125', 586.41, 585.74, 587.06, 582.69)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091127', 572.00, 579.76, 582.46, 570.97)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091130', 580.63, 583.00, 583.67, 577.11)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091201', 588.13, 589.87, 591.22, 583.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091202', 591.00, 587.51, 593.01, 586.22)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091203', 589.04, 585.74, 591.45, 585.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091204', 593.02, 585.01, 594.83, 579.18)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091207', 584.21, 586.25, 588.69, 581.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091208', 583.50, 587.05, 590.66, 582.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091209', 587.50, 589.02, 589.33, 583.58)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091210', 590.44, 591.50, 594.71, 590.41)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091211', 594.68, 590.51, 594.75, 587.73)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091214', 595.35, 595.73, 597.31, 592.61)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091215', 593.30, 593.14, 596.38, 590.99)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091216', 598.60, 597.76, 600.37, 596.64)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091217', 596.44, 593.94, 597.64, 593.76)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091218', 596.03, 596.42, 598.93, 595.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091221', 597.61, 598.68, 599.84, 595.67)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091222', 601.34, 601.12, 601.50, 598.85)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091223', 603.50, 611.68, 612.87, 602.85)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091224', 612.93, 618.48, 619.52, 612.27)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091228', 621.66, 622.87, 625.99, 618.48)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091229', 624.74, 619.40, 624.84, 618.29)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091230', 618.50, 622.73, 622.73, 618.01)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20091231', 624.75, 619.98, 625.40, 619.98)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100104', 626.95, 626.75, 629.51, 624.24)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100105', 627.18, 623.99, 627.84, 621.54)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100106', 625.86, 608.26, 625.86, 606.36)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100107', 609.40, 594.10, 610.00, 592.65)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100108', 592.00, 602.02, 603.25, 589.11)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100111', 604.46, 601.11, 604.46, 594.04)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100112', 597.65, 590.48, 598.16, 588.00)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100113', 576.49, 587.09, 588.38, 573.90)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100114', 583.90, 589.85, 594.20, 582.81)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100115', 593.34, 580.00, 593.56, 578.04)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100119', 581.20, 587.62, 590.42, 576.29)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100120', 585.98, 580.41, 585.98, 575.29)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100121', 583.44, 582.98, 586.82, 572.25)
INSERT INTO #google_stock (quote_date, open_price, close_price, high_price, low_price) VALUES ('20100122', 564.50, 550.01, 570.60, 534.86)
go
CREATE CLUSTERED INDEX ix_goog on #google_stock(quote_date)
go
IF OBJECT_ID('tempdb..#mod_goog_data') IS NOT NULL DROP TABLE #mod_goog_data;
go
select row_number() over (order by quote_date) n,
quote_date,
close_price,
CAST(null as decimal(8,2)) [sma]
into #mod_goog_data
from #google_stock
go
--create clustered index ix_n on #mod_goog_data(n)
go
IF OBJECT_ID('tempdb..#mod_goog_data') IS NOT NULL DROP TABLE #mod_goog_data;
--Create a CTE worktable t1 to allow us to refer to the n
--field in order to perform a join
with t1 as (select row_number() over (order by quote_date) n,
quote_date,
close_price
from #google_stock)
select a.n
,a.quote_date
,a.close_price ,
CAST(null as decimal(8,2)) [sma]
--add the close_price from 20 row prior to this one
,CAST(b.close_price as decimal(8,2)) [20_day_old_close]
into #mod_goog_datafrom t1 a
left join t1 b
on a.n - 20 = b.n
declare @intervals int, @initial_sum decimal(8,2)
declare @anchor int, @moving_sum decimal(8,2)
set @intervals = 20
--Retrieve the initial sum value at row 20
select @initial_sum = sum(close_price) from #mod_goog_data where n <= @intervals
update t1 --case statement to handle @moving_sum variable
--depending on the value of n
set @moving_sum = case
when n < @intervals then null
when n = @intervals then @initial_sum
when n > @intervals then
@moving_sum + [close_price] - [20_day_old_close] end,
sma = @moving_sum/Cast(@intervals as decimal(8,2)),
@anchor = n --anchor so that carryover works
from #mod_goog_data t1 with (TABLOCKX)OPTION (MAXDOP 1)
select quote_date ,close_price ,sma from #mod_goog_data
go
IF OBJECT_ID('tempdb..#google_stock') IS NOT NULL DROP TABLE #google_stock;
IF OBJECT_ID('tempdb..#mod_goog_data') IS NOT NULL DROP TABLE #mod_goog_data;
go
-
What version of SQL Server are you using?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
MSSQL 2005...
-
Try running the batches between the GO statements individually. It worked for me, using the same script which you've just posted. Running the whole script raised a single error.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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
Viewing 15 posts - 31 through 45 (of 80 total)
You must be logged in to reply to this topic. Login to reply