Running or 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 2008 Forums

Transact-SQL (2008)

Running or moving average

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-11 : 14:52:11

Would like to see a 3rd column showing a running or moving average. How to? Can it be simple? Do I need to create an identity column so that I'm comparing 'up'?



DECLARE @t TABLE (
[Name] VARCHAR(5) NOT NULL,
[Prcnt] DECIMAL(5,2) NOT NULL,
[AvgPrcnt] DECIMAL(5,2) NULL
)

INSERT @t ( [Name], [Prcnt] )

SELECT 'a',1.1
UNION ALL
SELECT 'b',1.6
UNION ALL
SELECT 'c',.8
UNION ALL
SELECT 'd',2.5
UNION ALL
SELECT 'e',2.2

SELECT *
FROM @t

gvmk27
Starting Member

44 Posts

Posted - 2011-10-11 : 16:09:56

Hope you are looking for below result..

'a',1.1,1.1
'b',1.6,1.35
'c',.8,1.16
'd',2.5,1.5

You have to write a Insert trigger, identity column is not mandatory.

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-11 : 16:15:05

Thanks; that's the result I'm looking for. Don't know how to write an insert trigger; I wonder if cross apply would work?

gvmk27
Starting Member

44 Posts

Posted - 2011-10-11 : 17:02:10

I haven't tested it, but it should be like this...

CREATE TRIGGER mytrigger ON t
INSTEAD OF INSERT
AS BEGIN
declare @count int
declare @avg decimal(8,2)
declare @newvalue decimal(8,2)

SELECT @newvalue = Prcnt FROM inserted;

select @count = count(*), @avg = sum(Prcnt) from t

set @count = + 1

set @avg = (@avg + @newvalue) / @count

INSERT INTO t (AvgPrcnt) values (@avg) where name = (select name from inserted)

END

I think cross apply doesnt work here, I'm not sure..

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-11 : 17:44:27

My test fails but I appreciate the code anyway!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-11 : 19:06:43

The trigger approach only works if you insert a single row at a time under all conditions. If you can impose an ordering on the data (I used Name but that might not be a valid assumption on my part) you can generate the running average at select time.[CODE];with OrderedList as (
select
name,
Prcnt,
ROW_NUMBER() OVER(ORDER BY name ASC) AS rn
from
@t
)
select
a.name,
a.Prcnt,
AVG(b.Prcnt) Running
from
OrderedList a
inner join
OrderedList b
on a.rn >= b.rn
group by
a.name,
a.Prcnt
order by
a.name,
a.Prcnt[/CODE]This won't perform well for large tables but might suit your needs.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

dmilam
Posting Yak Master

185 Posts

Posted - 2011-10-11 : 19:28:45

Thanks, this is very much suited to what I need. I was wondering about the over clause but did not think to use it with a cte.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-12 : 13:25:22

Mi gusto!

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources