moving average. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

moving average.

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2008-06-16 : 15:39:37

[code]
DATES VALUES 3 DAY AVERAGE
6/16/2008 7 27.33
6/15/2008 71 42.00
6/14/2008 4 20.67
6/13/2008 51 36.33
6/12/2008 7 52.67
6/11/2008 51 51.67
6/10/2008 100 43.00
6/9/2008 4 21.67
6/8/2008 25 23.00
6/7/2008 36 38.33
6/6/2008 8 30.00
6/5/2008 71 29.00
6/4/2008 11 63.33
6/3/2008 5 89.50
6/2/2008 174 174.00
[/code]
Please advise on how to get the 3 day average of the values column without using cursors.

therefore
27.33 = average of 7, 71 and 4 i.e values of 06/16, 06/15 and 06/14

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 02:32:18

[code]DECLARE @temp table
(DATES datetime,
[VALUES] int,
[3 DAY AVERAGE] float)
INSERT INTO @Temp (DATES,[VALUES])
SELECT '6/16/2008', 7 UNION ALL-- 27.33
SELECT '6/15/2008', 71 UNION ALL--42.00
SELECT '6/14/2008', 4 UNION ALL--20.67
SELECT '6/13/2008', 51 UNION ALL--36.33
SELECT '6/12/2008', 7 UNION ALL--52.67
SELECT '6/11/2008', 51 UNION ALL--51.67
SELECT '6/10/2008', 100 UNION ALL--43.00
SELECT '6/9/2008', 4 UNION ALL--21.67
SELECT '6/8/2008', 25 UNION ALL--23.00
SELECT '6/7/2008', 36 UNION ALL--38.33
SELECT '6/6/2008', 8 UNION ALL--30.00
SELECT '6/5/2008', 71 UNION ALL--29.00
SELECT '6/4/2008', 11 UNION ALL--63.33
SELECT '6/3/2008', 5 UNION ALL--89.50
SELECT '6/2/2008', 174 --174.00

SELECT t1.DATES,SUM(t2.[VALUES])*1.0/COUNT(t2.[VALUES])
FROM @temp t1
CROSS JOIN master..spt_values v
INNER JOIN @Temp t2
ON DATEADD(dd,-1 * v.number,t1.DATES)=t2.DATES
WHERE v.type='p'
AND v.number BETWEEN 0 AND 2
GROUP BY t1.DATES

output
-----------------------------------------
DATES Average
----------------------- ---------------------------------------
2008-06-16 00:00:00.000 27.333333333333
2008-06-15 00:00:00.000 42.000000000000
2008-06-14 00:00:00.000 20.666666666666
2008-06-13 00:00:00.000 36.333333333333
2008-06-12 00:00:00.000 52.666666666666
2008-06-11 00:00:00.000 51.666666666666
2008-06-10 00:00:00.000 43.000000000000
2008-06-09 00:00:00.000 21.666666666666
2008-06-08 00:00:00.000 23.000000000000
2008-06-07 00:00:00.000 38.333333333333
2008-06-06 00:00:00.000 30.000000000000
2008-06-05 00:00:00.000 29.000000000000
2008-06-04 00:00:00.000 63.333333333333
2008-06-03 00:00:00.000 89.500000000000
2008-06-02 00:00:00.000 174.000000000000[/code]

ramireddy
Starting Member

4 Posts

Posted - 2008-06-17 : 05:02:36

create table dateavgs
(

dates datetime,
myvalues int
)
insert into dateavgs values (getdate(),7)
insert into dateavgs values (getdate()-1,71)
insert into dateavgs values (getdate()-2,4)
insert into dateavgs values (getdate()-3,51)
insert into dateavgs values (getdate()-4,7)
insert into dateavgs values (getdate()-5,51)
insert into dateavgs values (getdate()-6,100)
insert into dateavgs values (getdate()-7,4)
insert into dateavgs values (getdate()-8,25)
insert into dateavgs values (getdate()-9,36)

select p.dates,round(cast( (p.myvalues+p1.myvalues+p2.myvalues) as float)/3,2) from
( select *,(select count(*) from dateavgs where dates >= d.dates) as rank from dateavgs d ) p
inner join ( select *,(select count(*) from dateavgs where dates >= d.dates) as rank from dateavgs d ) p1 on p1.rank = p.rank+1
inner join ( select *,(select count(*) from dateavgs where dates >= d.dates) as rank from dateavgs d ) p2 on p2.rank = p.rank+2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 06:28:06

See http://weblogs.sqlteam.com/peterl/archive/2007/12/10/Cursor-is-really-faster-than-set-based-solution.aspx

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 06:35:01

[code]DECLARE @Sample TABLE (Dates DATETIME, Value INT)

INSERT @Sample
SELECT '6/16/2008', 7 UNION ALL -- 27.33
SELECT '6/15/2008', 71 UNION ALL -- 42.00
SELECT '6/14/2008', 4 UNION ALL -- 20.67
SELECT '6/13/2008', 51 UNION ALL -- 36.33
SELECT '6/12/2008', 7 UNION ALL -- 52.67
SELECT '6/11/2008', 51 UNION ALL -- 51.67
SELECT '6/10/2008', 100 UNION ALL -- 43.00
SELECT '6/9/2008', 4 UNION ALL -- 21.67
SELECT '6/8/2008', 25 UNION ALL -- 23.00
SELECT '6/7/2008', 36 UNION ALL -- 38.33
SELECT '6/6/2008', 8 UNION ALL -- 30.00
SELECT '6/5/2008', 71 UNION ALL -- 29.00
SELECT '6/4/2008', 11 UNION ALL -- 63.33
SELECT '6/3/2008', 5 UNION ALL -- 89.50
SELECT '6/2/2008', 174 --174.00

SELECT Dates,
AVG(1.0 * Value) AS Moving3dayAverage
FROM (
SELECT Dates,
Value,
1 AS realDate
FROM @Sample

UNION ALL

SELECT DATEADD(DAY, 1, Dates),
Value,
0
FROM @Sample

UNION ALL

SELECT DATEADD(DAY, 2, Dates),
Value,
0
FROM @Sample
) AS d
GROUP BY d.Dates
HAVING COUNT(*) = 3
ORDER BY d.Dates DESC[/code]

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

dpais
Yak Posting Veteran

60 Posts

Posted - 2008-06-17 : 09:52:09

thnak you for all your replies -

i found the solution on this website -

it may be of use to some of you -

thnaks

[url]
http://oreilly.com/catalog/transqlcook/chapter/ch08.html
[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:57:57

Compare the execution plans to see which is more efficient.

-- Peso 2
SELECT		DATEADD(DAY, d.delta, t.Dates),
		AVG(1.0 * t.[VALUES])
FROM		@temp AS t
CROSS JOIN	(
			SELECT	0 AS delta UNION ALL
			SELECT	1 AS delta UNION ALL
			SELECT	2 AS delta
		) AS d
GROUP BY	DATEADD(DAY, d.delta, t.Dates)
HAVING		COUNT(*) = 3
ORDER BY	DATEADD(DAY, d.delta, t.Dates) DESC

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 10:05:27

-- Visakh16

  |--Compute Scalar(DEFINE:([Expr1010]=(CONVERT_IMPLICIT(numeric(10,0),[Expr1008],0)*(1.0))/CONVERT_IMPLICIT(numeric(10,0),[Expr1009],0)))
       |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013] END, [Expr1009]=CONVERT_IMPLICIT(int,[Expr1012],0)))
            |--Stream Aggregate(GROUP BY:([t1].[DATES]) DEFINE:([Expr1012]=COUNT_BIG(@Temp.[VALUES] as [t2].[VALUES]), [Expr1013]=SUM(@Temp.[VALUES] as [t2].[VALUES])))
                 |--Nested Loops(Inner Join, WHERE:(dateadd(day,[Expr1011],@temp.[DATES] as [t1].[DATES])=@Temp.[DATES] as [t2].[DATES]))
                      |--Nested Loops(Inner Join)
                      |    |--Sort(ORDER BY:([t1].[DATES] DESC))
                      |    |    |--Table Scan(OBJECT:(@temp AS [t1]))
                      |    |--Table Scan(OBJECT:(@Temp AS [t2]))
                      |--Compute Scalar(DEFINE:([Expr1011]= -(1)*[master].[dbo].[spt_values].[number] as .[number]))
                           |--Clustered Index Seek(OBJECT:([master].[dbo].[spt_values].[spt_valuesclust] AS ), SEEK:(.[type]=N'p' AND .[number] >= (0) AND .[number] <= (2)) ORDERED FORWARD)

-- Remireddy

  |--Compute Scalar(DEFINE:([Expr1030]=round(CONVERT(float(53),(@temp.[VALUES] as [d].[VALUES]+@temp.[VALUES] as [d].[VALUES])+@temp.[VALUES] as [d].[VALUES],0)/(3.000000000000000e+000),(2))))
       |--Nested Loops(Inner Join, WHERE:([Expr1027]=([Expr1007]+(2))))
            |--Nested Loops(Inner Join, WHERE:([Expr1017]=([Expr1007]+(1))))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([d].[DATES]))
            |    |    |--Sort(ORDER BY:([d].[DATES] DESC))
            |    |    |    |--Table Scan(OBJECT:(@temp AS [d]))
            |    |    |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1039],0)))
            |    |         |--Stream Aggregate(DEFINE:([Expr1039]=Count(*)))
            |    |              |--Table Scan(OBJECT:(@temp), WHERE:([DATES]>=@temp.[DATES] as [d].[DATES]))
            |    |--Table Spool
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([d].[DATES]))
            |              |--Table Scan(OBJECT:(@temp AS [d]))
            |              |--Compute Scalar(DEFINE:([Expr1017]=CONVERT_IMPLICIT(int,[Expr1040],0)))
            |                   |--Stream Aggregate(DEFINE:([Expr1040]=Count(*)))
            |                        |--Table Scan(OBJECT:(@temp), WHERE:([DATES]>=@temp.[DATES] as [d].[DATES]))
            |--Table Spool
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([d].[DATES]))
                      |--Table Scan(OBJECT:(@temp AS [d]))
                      |--Compute Scalar(DEFINE:([Expr1027]=CONVERT_IMPLICIT(int,[Expr1041],0)))
                           |--Stream Aggregate(DEFINE:([Expr1041]=Count(*)))
                                |--Table Scan(OBJECT:(@temp), WHERE:([DATES]>=@temp.[DATES] as [d].[DATES]))

-- Peso

  |--Filter(WHERE:([Expr1020]=(3)))
       |--Compute Scalar(DEFINE:([Expr1020]=CONVERT_IMPLICIT(int,[Expr1022],0), [Expr1021]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024]/CONVERT_IMPLICIT(numeric(19,0),[Expr1023],0) END))
            |--Stream Aggregate(GROUP BY:([Union1017]) DEFINE:([Expr1022]=Count(*), [Expr1023]=COUNT_BIG((1.0)*CONVERT_IMPLICIT(numeric(10,0),[Union1018],0)), [Expr1024]=SUM((1.0)*CONVERT_IMPLICIT(numeric(10,0),[Union1018],0))))
                 |--Sort(ORDER BY:([Union1017] DESC))
                      |--Concatenation
                           |--Table Scan(OBJECT:(@temp))
                           |--Compute Scalar(DEFINE:([Expr1009]=dateadd(day,(1),[DATES])))
                           |    |--Table Scan(OBJECT:(@temp))
                           |--Compute Scalar(DEFINE:([Expr1015]=dateadd(day,(2),[DATES])))
                                |--Table Scan(OBJECT:(@temp))

-- O'Relly

  |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014]/CONVERT_IMPLICIT(int,[Expr1013],0) END))
       |--Stream Aggregate(GROUP BY:([x].[DATES]) DEFINE:([Expr1013]=COUNT_BIG(@temp.[VALUES] as [y].[VALUES]), [Expr1014]=SUM(@temp.[VALUES] as [y].[VALUES])))
            |--Nested Loops(Inner Join, WHERE:(@temp.[DATES] as [x].[DATES]>=@temp.[DATES] as [y].[DATES] AND @temp.[DATES] as [x].[DATES]<=[Expr1007]))
                 |--Sort(ORDER BY:([x].[DATES] DESC))
                 |    |--Table Scan(OBJECT:(@temp AS [x]))
                 |--Compute Scalar(DEFINE:([Expr1007]=@temp.[DATES] as [y].[DATES]+'1900-01-03 00:00:00.000'))
                      |--Table Scan(OBJECT:(@temp AS [y]))

-- Peso 2

  |--Filter(WHERE:([Expr1008]=(3)))
       |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1018],0), [Expr1009]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020]/CONVERT_IMPLICIT(numeric(19,0),[Expr1019],0) END))
            |--Stream Aggregate(GROUP BY:([Expr1007]) DEFINE:([Expr1018]=Count(*), [Expr1019]=COUNT_BIG([Expr1010]), [Expr1020]=SUM([Expr1010])))
                 |--Sort(ORDER BY:([Expr1007] DESC))
                      |--Compute Scalar(DEFINE:([Expr1007]=dateadd(day,[Union1006],@temp.[DATES] as [t].[DATES])))
                           |--Nested Loops(Inner Join)
                                |--Compute Scalar(DEFINE:([Expr1010]=(1.0)*CONVERT_IMPLICIT(numeric(10,0),@temp.[VALUES] as [t].[VALUES],0)))
                                |    |--Table Scan(OBJECT:(@temp AS [t]))
                                |--Constant Scan(VALUES:(((0)),((1)),((2))))

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 10:12:23

SQL Profiler results


              CPU  Duration  Reads
              ---  --------  -----
-- Visakh16     0        23    498  16% of batch
-- Remireddy   15        15    266  38% of batch
-- Peso         0         2      9  16% of batch
-- O'Reilly     0         2     48  13% of batch
-- Peso 2       0         0      3  11% of batch

7% of batch is inserting records into @temp table.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 11:05:43

Peso, any reason of using realDate column?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 11:41:06

No, not in this case. realDate is a residue from a copy & paste operation.
Or yes. I see now that OP calculates 3day average even for first date. Then you have to use MAX(realDate) = 1 in the HAVING clause.

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-17 : 11:46:51

quote:

Originally posted by Peso

Compare the execution plans to see which is more efficient.

-- Peso 2
SELECT		DATEADD(DAY, d.delta, t.Dates),
		AVG(1.0 * t.[VALUES])
FROM		@temp AS t
CROSS JOIN	(
			SELECT	0 AS delta UNION ALL
			SELECT	1 AS delta UNION ALL
			SELECT	2 AS delta
		) AS d
GROUP BY	DATEADD(DAY, d.delta, t.Dates)
HAVING		COUNT(*) = 3
ORDER BY	DATEADD(DAY, d.delta, t.Dates) DESC

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

very nice Peso! I like it.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 11:50:14

Thank you my friend.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 12:04:34

To completely mimic OP's algorithm, use this

SELECT		DATEADD(DAY, d.delta, t.Dates),
		AVG(1.0 * t.Value)
FROM		@Sample AS t
CROSS JOIN	(
			SELECT	0 AS delta UNION ALL
			SELECT	1 UNION ALL
			SELECT	2
		) AS d
GROUP BY	DATEADD(DAY, d.delta, t.Dates)
HAVING		MIN(d.delta) = 0
ORDER BY	DATEADD(DAY, d.delta, t.Dates) DESC

SELECT		Dates,
		AVG(1.0 * Value) AS Moving3dayAverage
FROM		(
			SELECT	Dates,
				Value,
				1 AS realDate
			FROM	@Sample

			UNION ALL

			SELECT	DATEADD(DAY, 1, Dates),
				Value,
				0
			FROM	@Sample

			UNION ALL

			SELECT	DATEADD(DAY, 2, Dates),
				Value,
				0
			FROM	@Sample
		) AS d
GROUP BY	d.Dates
HAVING		MAX(realDate) = 1
ORDER BY	d.Dates DESC

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources