Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Calculating Moving Averages with T-SQL Expand / Collapse
Author
Message
Posted Thursday, March 4, 2010 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 11:43 AM
Points: 118, Visits: 195
Nice to have an alternative to self joins for the sliding window average. Thanks for the post.
Post #876832
Posted Thursday, March 4, 2010 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:55 AM
Points: 6, Visits: 16
Yes, but how we can make connection to some live data source so we can do live trading analyse?
F
Post #876839
Posted Thursday, March 4, 2010 9:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:01 PM
Points: 266, Visits: 2,567
So well written and explained. Thanks for taking the time to share this with us.
Post #876905
Posted Thursday, March 4, 2010 9:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #876908
Posted Thursday, March 4, 2010 12:32 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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
Post #877086
Posted Thursday, March 4, 2010 4:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 5:49 AM
Points: 9, Visits: 141
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
Post #877264
Posted Thursday, March 4, 2010 5:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 21,252, Visits: 14,960
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #877307
Posted Thursday, March 4, 2010 5:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 21,252, Visits: 14,960
Nice article and well presented.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #877308
Posted Thursday, March 4, 2010 8:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, 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]
Post #877342
Posted Thursday, March 4, 2010 8:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #877347
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse