SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Moving average in TSQL
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

mctaff
Starting Member

13 Posts

Posted - 01/14/2010 :  10:33:08  Show Profile  Reply with Quote
no values needed for the first x rows...

row x+1, average column, will contain the average of the close column for rows 1 to x

row x+2, average column, will contain the average of the close column for rows 2 to x+1

etc....
Go to Top of Page

mctaff
Starting Member

13 Posts

Posted - 01/14/2010 :  14:15:53  Show Profile  Reply with Quote
Thanks Peso... great code. You can achieve a lot in a few lines in SQL...

I was sort of getting there myself, but would have taken me a few more hours to crack it I think!

Extremely fast for 100,000 rows and a small sample size (a few seconds), and still very performant for 350 sample size (5 minutes as opposed to 35 minutes in my previous version!)

For the larger sample sizes (eg 350) could a procedural approach prove faster?
Go to Top of Page

mctaff
Starting Member

13 Posts

Posted - 01/16/2010 :  13:08:57  Show Profile  Reply with Quote
thought I'd posted an update here... I adapted the procedural code to my needs and reduced the processing time down to 20 seconds....

thanks for all the assistance!
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/17/2010 :  23:57:12  Show Profile  Reply with Quote
quote:
Originally posted by mctaff

thought I'd posted an update here... I adapted the procedural code to my needs and reduced the processing time down to 20 seconds....

thanks for all the assistance!



So what does your final code look like?

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page

chris3k
Starting Member

1 Posts

Posted - 01/20/2010 :  01:32:45  Show Profile  Reply with Quote
For MS SQL Server 2008 moving average for last 10 samples:

SELECT x.RowDate as Date, avg(y.RowValue)
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY RowDate asc) AS Row, RowDate FROM YourTable) AS x,
(SELECT ROW_NUMBER() OVER (ORDER BY RowDate asc) AS Row, RowDate, RowValue FROM YourTable) AS y
WHERE x.Row between y.Row and y.Row+9
group by x.RowDate
ORDER BY x.RowDate asc

Go to Top of Page

stillCurious
Starting Member

1 Posts

Posted - 01/12/2012 :  12:16:28  Show Profile  Reply with Quote
Hi Peso,

I stumbled upon this query and really find it very interesting and useful; Especially due to the fact that it does NOT use a lag function. I was wondering if you would care to explain it a bit.
1. first of all, what Moving avg are you calculating, last 12 rows or 3 rows?
2. in the 3 way union, you have actdt as 0,0 and 2, Did you mean it to be two zeroes or 0,1,2
3. can you explain the overall logic how this goes back a certain number of rows to get a moving average.

Thank you.
quote:
Originally posted by SwePeso

I think set-based code has the fastest time
And no need for staging table either!
-- Prepare sample data
DECLARE	@Sample TABLE (dt DATETIME, Rate FLOAT)

INSERT	@Sample
SELECT	CURRENT_TIMESTAMP - 10,  1 UNION ALL
SELECT	CURRENT_TIMESTAMP -  9,  2 UNION ALL
SELECT	CURRENT_TIMESTAMP -  8,  4 UNION ALL
SELECT	CURRENT_TIMESTAMP -  7,  4 UNION ALL
SELECT	CURRENT_TIMESTAMP -  6,  5 UNION ALL
SELECT	CURRENT_TIMESTAMP -  5,  6 UNION ALL
SELECT	CURRENT_TIMESTAMP -  4,  6 UNION ALL
SELECT	CURRENT_TIMESTAMP -  3,  8 UNION ALL
SELECT	CURRENT_TIMESTAMP -  2,  9 UNION ALL
SELECT	CURRENT_TIMESTAMP -  1, 10 UNION ALL
SELECT	CURRENT_TIMESTAMP -  0, 11 UNION ALL
SELECT	CURRENT_TIMESTAMP +  1,  9

-- Peso 2
SELECT		DATEADD(DAY, dt, '19000101') AS dt,
		AVG(Rate) AS SimpleMovingAvg,
		SUM(wr) AS WeightedMovingAvg
FROM		(
			SELECT	DATEDIFF(DAY, '18991230', dt) AS dt,
				0.1 * Rate AS wr,
				Rate,
				0 AS Actualdate
			FROM	@Sample

			UNION ALL

			SELECT	DATEDIFF(DAY, '18991231', dt),
				0.2 * Rate,
				Rate,
				0
			FROM	@Sample

			UNION ALL

			SELECT	DATEDIFF(DAY, '19000101', dt),
				0.7 * Rate,
				Rate,
				1
			FROM	@Sample
		) AS k
GROUP BY	dt
HAVING		MAX(Actualdate) = 1
ORDER BY	dt

E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

upshon
Starting Member

New Zealand
3 Posts

Posted - 09/05/2012 :  17:01:36  Show Profile  Reply with Quote
If you declare @t with a primary key on date, performance will be infinitely better

quote:
Originally posted by jezemine


declare @t table (date datetime not null, rate float not null)




elsasoft.org

Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 09/07/2012 :  22:15:09  Show Profile  Visit jezemine's Homepage  Reply with Quote
i doubt it would be infinitely better. maybe 100x better. or 10000000x.

but not infinitely.


elsasoft.org
Go to Top of Page

sqltrainingonline
Starting Member

3 Posts

Posted - 01/31/2013 :  12:42:17  Show Profile  Reply with Quote
I put together a little video trying to explain the subquery that generates the moving average.

http://www.sqltrainingonline.com/sql-moving-average/

I hope it helps.

Joey
---------
http://www.SqlTrainingOnline.com/
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000