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 | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/11/2007 :  11:14:38  Show Profile  Visit jezemine's Homepage  Reply with Quote
quote:
Originally posted by Peso

Further optimized for SQL Server 2005 (only a third of the reads for SQL Server 2000 approach).
Jezemine, if you have SQL Server 2005, can you timetest this approach for SQL Server 2005 too?



this is for 100k sample rows, on my dev box:


method     time (ms)
---------  ---------
cursor         14630
Peso 2005       3890
Peso 2000       3610






elsasoft.org
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/11/2007 :  13:11:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thanks!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/11/2007 :  14:04:41  Show Profile  Visit jezemine's Homepage  Reply with Quote
so are you done schooling me?


elsasoft.org
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/11/2007 :  14:12:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yep!
Just wanted times to be fair and square made at same box.





E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

craigp
Starting Member

2 Posts

Posted - 11/04/2008 :  11:33:34  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Further optimized for SQL Server 2005 (only a third of the reads for SQL Server 2000 approach).
Jezemine, if you have SQL Server 2005, can you timetest this approach for SQL Server 2005 too?
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

-- SQL Server 2005
SELECT		DATEADD(DAY, p.ActualDate, '19000101') AS dt,
		AVG(p.Rate) AS SimpleMovingAvg,
		SUM(
			CASE p.dt
				WHEN 'dt0' THEN p.r0
				WHEN 'dt1' THEN p.r1
				WHEN 'dt2' THEN p.r2
				ELSE 0
			END
		) AS WeightedMovingAvg
FROM		(
			SELECT	Rate,
				DATEDIFF(DAY, '19000101', dt) AS dt0,
				0.7 * Rate AS r0,
				DATEDIFF(DAY, '18991231', dt) AS dt1,
				0.2 * Rate AS r1,
				DATEDIFF(DAY, '18991230', dt) AS dt2,
				0.1 * Rate AS r2
			FROM	@Sample
		) AS y
UNPIVOT		(
				ActualDate
			FOR	dt IN (y.dt0, y.dt1, y.dt2)
		) AS p
GROUP BY	p.ActualDate
HAVING		MIN(p.dt) = 'dt0'
ORDER BY	p.ActualDate

-- SQL Server 2000
SELECT		DATEADD(DAY, k.dt, '19000101') AS dt,
		AVG(k.Rate) AS SimpleMovingAvg,
		SUM(k.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	k.dt
HAVING		MAX(k.Actualdate) = 1
ORDER BY	k.dt



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




Hi Peter,

Could you please explain to me how this actually works? Or anyone? I would like to customise it to be able to set the number of days used in the simple rolling average, i.e. 7,14,21,28,56 etc

Because of the three unions, is it doing a 3 day rolling average? Is there a way to automate for differing number of days used in the rolling average?

Thanks.

Edited by - craigp on 11/04/2008 11:35:13
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/04/2008 :  11:55:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		DATEADD(DAY, v.Number, s.dt),
		AVG(1.0E0 * s.Rate)
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number < @Days
GROUP BY	DATEADD(DAY, v.Number, s.dt)
HAVING		COUNT(*) = @Days
ORDER BY	DATEADD(DAY, v.Number, s.dt) DESC



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

craigp
Starting Member

2 Posts

Posted - 11/04/2008 :  12:23:12  Show Profile  Reply with Quote
quote:
Originally posted by Peso

SELECT		DATEADD(DAY, v.Number, s.dt),
		AVG(1.0E0 * s.Rate)
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number < @Days
GROUP BY	DATEADD(DAY, v.Number, s.dt)
HAVING		COUNT(*) = @Days
ORDER BY	DATEADD(DAY, v.Number, s.dt) DESC



E 12°55'05.63"
N 56°04'39.26"




Awesome! Thanks.
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 01/23/2009 :  15:58:29  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
wow this is nice guys!
for moving average for sample data of past three month from today meaning using GETDATE() how do I go about doing this following awesome SELECT

SELECT DATEADD(DAY, p.ActualDate, '19000101') AS dt,
AVG(p.Rate) AS SimpleMovingAvg,
SUM(
CASE p.dt
WHEN 'dt0' THEN p.r0
WHEN 'dt1' THEN p.r1
WHEN 'dt2' THEN p.r2
ELSE 0
END
) AS WeightedMovingAvg
FROM (
SELECT Rate,
DATEDIFF(DAY, '19000101', dt) AS dt0,
0.7 * Rate AS r0,
DATEDIFF(DAY, '18991231', dt) AS dt1,
0.2 * Rate AS r1,
DATEDIFF(DAY, '18991230', dt) AS dt2,
0.1 * Rate AS r2
FROM @Sample
) AS y
UNPIVOT (
ActualDate
FOR dt IN (y.dt0, y.dt1, y.dt2)
) AS p
GROUP BY p.ActualDate
HAVING MIN(p.dt) = 'dt0'
ORDER BY p.ActualDate
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/24/2009 :  04:35:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
FROM @Sample
WHERE dt >= DATEADD(MONTH, -3, GETDATE())



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

VijayChand
Starting Member

1 Posts

Posted - 09/22/2009 :  01:01:12  Show Profile  Reply with Quote
quote:
Originally posted by Peso

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"




Hi Peso,

The above query is getting very heady for me. I tried to work on it piece by piece but am unable to understand what is happening. Could you walk me through this code and explain the subqueries....also, on what is the role of DATEADD, DATEDIFF functions and Actualdate. Thanks for your time and I apologize for my raw-ness.
Go to Top of Page

vcheung8
Starting Member

1 Posts

Posted - 11/22/2009 :  06:04:31  Show Profile  Reply with Quote
Thanks for the detailed descriptions for different ways to handle date and moving averages.

One more question, if my data is equity prices (i.e. data available for weekdays only and NO data for weekends) instead of FX, how do I adjust the DATEADD so that the resulting DATE will not fall on weekends?

Thanks again.
Go to Top of Page

mctaff
Starting Member

13 Posts

Posted - 01/13/2010 :  17:18:48  Show Profile  Reply with Quote
I'm interested in adapting this code to do moving averages over intraday data - ie time periods less than one day...

The code is hard to decipher, can anyone give me pointers please?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/13/2010 :  17:49:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Easy. Just let the data in the derived table k to be hours instead of days.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

mctaff
Starting Member

13 Posts

Posted - 01/13/2010 :  18:16:23  Show Profile  Reply with Quote
Easy for you!!

I'm just working on the simple moving average initially, yet I'm just getting the single value, not the average of the set of values... I'm using a row number, rather than the date - not having much experience with SQL I'm not that clear on manipulating dates...

I've tried to adapt the code as follows, but I'm not clear on how to get the set of data I want with the select (eg 50 rows for an average):


SELECT		y.RowNumber AS rn, y.[close] as cl,
		AVG(y.[close]) AS SimpleMovingAvg
FROM		(
			SELECT	RowNumber, FTS.dbo.test.[close] FROM	FTS.dbo.test where RowNumber between 1 and 50
 
	
		) AS y
GROUP BY	y.RowNumber, y.[close]
ORDER BY	y.RowNumber


I end up with a single value for 50 rows... I'm after the moving average of 50 rows for each row...

What am I doing wrong?


Thanks,

David.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/14/2010 :  02:28:43  Show Profile  Reply with Quote
show some sample data and explain what you want please
Go to Top of Page

mctaff
Starting Member

13 Posts

Posted - 01/14/2010 :  06:43:05  Show Profile  Reply with Quote
RowNumber Close Average
1 120.1
2 115.7
3 118.0


etc...

I want a third column - moving average - which gives the moving average of the previous 50 rows(for example ) - ie the 51st entry will have the average of the previous 50 close values...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/14/2010 :  06:46:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
show some sample data and explain what you want please


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

mctaff
Starting Member

13 Posts

Posted - 01/14/2010 :  09:49:25  Show Profile  Reply with Quote
RowNumber, Date, Close
1, 2005-01-02 22:00:00.000, 196.96
2, 2005-01-02 22:15:00.000, 196.75
3, 2005-01-02 22:30:00.000, 196.98
4, 2005-01-02 22:45:00.000, 196.90
5, 2005-01-02 23:00:00.000, 196.85
etc...

sorry I'm not sure how to format the data to display very well...

for each row, I'd then like to see the moving average of the previous 'x' rows - eg for row 6, the average of rows 1 to 5, for row 7 the average of rows 2 to 6 etc... I would like the 'x' value to be a parameter that is easy to change


Thanks for the assistance...

David.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/14/2010 :  09:57:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		RowNumber INT,
		RowDate DATETIME,
		RowValue DECIMAL(10, 2)
	)

INSERT	@Sample
	(
		RowNumber,
		RowDate,
		RowValue
	)
SELECT	1, '2005-01-02 22:00', 196.96 UNION ALL
SELECT	2, '2005-01-02 22:15', 196.75 UNION ALL
SELECT	3, '2005-01-02 22:30', 196.98 UNION ALL
SELECT	4, '2005-01-02 22:45', 196.90 UNION ALL
SELECT	5, '2005-01-02 23:00', 196.85

-- Calculate moving average
DECLARE	@Items INT

SET	@Items = 3

SELECT		DATEADD(MINUTE, 15 * v.Number, s.RowDate),
		AVG(1.0E0 * s.RowValue)
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number < @Items
GROUP BY	DATEADD(MINUTE, 15 * v.Number, s.RowDate)
HAVING		COUNT(*) = @Items
ORDER BY	DATEADD(MINUTE, 15 * v.Number, s.RowDate)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/14/2010 :  10:03:37  Show Profile  Reply with Quote
quote:
Originally posted by mctaff

RowNumber, Date, Close
1, 2005-01-02 22:00:00.000, 196.96
2, 2005-01-02 22:15:00.000, 196.75
3, 2005-01-02 22:30:00.000, 196.98
4, 2005-01-02 22:45:00.000, 196.90
5, 2005-01-02 23:00:00.000, 196.85
etc...

sorry I'm not sure how to format the data to display very well...

for each row, I'd then like to see the moving average of the previous 'x' rows - eg for row 6, the average of rows 1 to 5, for row 7 the average of rows 2 to 6 etc... I would like the 'x' value to be a parameter that is easy to change


Thanks for the assistance...

David.


so what will value for 4th? avg of 1-3?
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next 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.12 seconds. Powered By: Snitz Forums 2000