Windowed Statistics - 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)

Windowed Statistics

Author

Topic

jfaraya
Starting Member

5 Posts

Posted - 2006-03-06 : 13:43:36

Hello T-SQL Lovers!

Few weeks ago i've fall into a problem i'm not able to solve. I need to calculate statistics over intervals of time -windows-. So, if you have this table:

A
----
300
300
300
600
300
600
1200

i should be able to calculate a running average over three days:

A A_3mean
---------------
300 150
300 300
300 300
600 400
300 400
600 500
1200 700
300 700
600 700

How i could do that??

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-06 : 13:58:15

You will have to have some column in you table that that you can use to identify which rows of data will get averaged together, for example, a date.

CODO ERGO SUM

jfaraya
Starting Member

5 Posts

Posted - 2006-03-06 : 14:17:50

Mmm... the rows must be treated like *time series*.. i need to calculate an average on "past values"... like

A_3mean(i)=0.333*A(i)+0.333*A(i-1)+0.333*A(i-2)

I don't have an advanced use of "@ vars", so i can't get the solution yet...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-06 : 14:30:37

Rows in a database table have no inherent order that you can depend on. If you want do a time series, you must provide a column in the table that defines the order of the data.

Calculating the average is not a problem, defining the rows to be averaged together is the problem.

CODO ERGO SUM

jfaraya
Starting Member

5 Posts

Posted - 2006-03-07 : 07:10:58

So... suppose you have a time column:
t A
------
1 300
2 300
3 300
4 600
5 300
6 600
7 1200

Even so.. how i could evaluate an expression like
A_3mean(i)=0.333*A(i)+0.333*A(i-1)+0.333*A(i-2) ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-07 : 08:01:24

Is this what you want ?

create table #temp
(
	t	int,
	a	int
)

insert into #temp
select 1,  300 union all
select 2,  300	union all
select 3,  300	union all
select 4,  600	union all
select 5,  300	union all
select 6,  600	union all
select 7, 1200

select 	t.t, t.a, 
	(select sum(a)/3 from (select top 3 a from #temp x where x.t <= t.t order by t desc) as s) as mean
from	#temp t
order by t

t           a           mean        
----------- ----------- ----------- 
1           300         100
2           300         200
3           300         300
4           600         400
5           300         400
6           600         500
7           1200        700

(7 row(s) affected)

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources