Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating Moving Averages with T-SQL


Calculating Moving Averages with T-SQL

Author
Message
rkkelly
rkkelly
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 196
Nice to have an alternative to self joins for the sliding window average. Thanks for the post.
kamban
kamban
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 23
Yes, but how we can make connection to some live data source so we can do live trading analyse?
F
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
So well written and explained. Thanks for taking the time to share this with us.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
Good code. Good read. Lot's of fun.

Thanks for the time and effort that you put into writing the article.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
In the immortal words of Paris Hilton, "That's hot". I love the technique and the article is very well written.

Signature is NULL
sql-troubles
sql-troubles
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 145
I was wondering why you haven't used the CROSS APPLY operator for the first query (see below), any special reason for that?

SELECT a.quote_date
, a.close_price
, dat.[20_day_sma]
FROM #google_stock a
CROSS APPLY (
SELECT AVG(b.close_price) [20_day_sma]
FROM #google_stock b
WHERE DateDiff(d, a.quote_date, b.quote_date) BETWEEN -20 AND 0) dat
ORDER BY a.quote_date
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21029 Visits: 18258
Chris Morris-439714 (3/4/2010)
Gabriel P (3/3/2010)
Comments posted to this topic are about the item <A HREF="/articles/Moving+Average/69389/">Calculating Moving Averages with T-SQL</A>


Excellent article, Gabriel. I reckon it's time to start messing with those pesky CTE's again.

<< and therefore isn't a good indicator of what the immediate future might hold. >>
Have a word with Gail about DBCC TIMEWARP, without too much effort you should get the last ten days and the next ten days.


Bwahahaha
Great one Chris.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21029 Visits: 18258
Nice article and well presented.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Alexander Kuznetsov
Alexander Kuznetsov
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 824
A very nice read. One more step: as we need running totals every time, why not precalculate and store them - that will speed it up even further. And we can use constraints to guarantee that running totals are correct. I described it here a while ago.

[url=http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx][/url]
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44804 Visits: 39847
Oddly enough, I've been thinking about this problem off and on for the last couple of days and then I saw your article today. I have to agree with what Dean Cochrane said in his post on this thread... it's one of the best articles I've seen on this or any other SQL site in a very long while.

You started out by clearly identifying the need and exactly how the problem worked. If there were any doubt, the excellent use of thoughtful and accurate graphics quickly removed all doubt.

You provided everything in code including test data. Lots of folks forget to do that. And code is what people are really after.

I started reading the article and by the time I started thinking "I know how I might do it", there you were with ...

At this point, I'm guessing most readers of this article already have the gears churning on how to solve this problem.


... so I'll add 10 points for your "mind reading" capabilities, as well.

Your formulas and explnations of the formulas where quite clear and, yeah, the more I read, the more I was sure I knew how I'd do it. I kept thinking to myself "I wonder if he's going to use a recursive CTE, triangular join, looped update, or what?" Then I saw what you used and you made my day. Thanks for the leg up and the honorable mention. :-) After all the heat I took both on the original article and the rewrite, it was a real pleasure to see someone use the method especially since it was for something totally different to anything demonstrated in the article.

As a bit of a sidebar, I do have to admit that I've not done enough testing on "heap" Temp tables to be comfortable using the method on a "heap". I'll also admit that I haven't been able to break one yet but I'd probably add the normal clustered index to the temp table just to be on the safe side... just like you did in the first example but I believe you just forgot to do in the second example.

Sorry... got carried away. I'll just finish by saying that even if the article didn't mention anything about the Quirky Update method, this would still be an outstanding article in my eyes. Very well done, Gabriel.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search