How to calculate moving average?

Question

I have a field in my database named "Value" and I need to calculate the moving average of this "value".

The table has a primary key "index" which is used as the sort order for the table.

There appears several approaches to this problem in broad searches, but I find some references to special server UI's, that some servers have, to accomplish this.

Is there such a function available in the ADS version 10.1+?

Since I can't find any reference to a "sliding average" I guess you mean the "moving average". There is no built-in function for that in ADS and there is no tool in ARC32. — Jens Mühlenhoff, Nov 28 '13 at 12:19

score 1 · Answer 1 · answered Nov 27 '13 at 17:20

I think this is a fairly straightforward self-join. By joining the table to itself you can set a join condition so that each measurement (row) is joined to the N previous measurements, then select the average of that and group by row ID.

Like this:

create table T
(
    id int identity(1,1) primary key,
    value float
)
go

-- Insert some test data
declare @i int = 0

while @i < 1200 
begin
    -- something random + @i => rising trend
    insert T values (@i + rand()*100); 
    set @i = @i + 1
end

-- Take a look at some of the data (optional)
-- Chaotic, but there is an upwards trend in the values.
select top 100 * from T

-- Fetch sliding average (over 20 measurements)
-- While it can fluctuate, the upwards tendency is now plain to see.
declare @duration int = 20

select 
    t1.id, 
    avg(t2.value) [Sliding average]
from 
    T T1 
    join T T2 on T2.id between T1.id - @duration and T1.id
where 
    t1.ID < 100
group by
    t1.id

-- To see the basis for each average...
select 
    t1.id [Group number], 
    t2.id [Measurement ID],
    t2.value [Value]
from 
    T T1 
    join T T2 on T2.id between T1.id - @duration and T1.id
where 
    t1.ID < 100
order by
    t1.id, t2.id

Was this what you had in mind?

By the way: If performance is a concern, perhaps this solution is too far from the optimal one. After all, if you consider the averages for measurement N and N+1, most of the underlying values are identical. You could subtract the contribution of the first element and add the contribution of the next instead. Using such a "differential update" approach might be faster. A CTE could probably do it, but presumably there is a client getting these data and it would be easy to do this efficiently in the (imperative!) client code instead, processing the data as a stream. — The Dag, Nov 27 '13 at 17:27
+1 ADS doesn't have CTEs, but it has procedural features like `WHILE`, `DECLARE`, `SET`, etc. So this should work in ADS which some syntax adaption. — Jens Mühlenhoff, Nov 28 '13 at 12:22

How to calculate moving average?

1 Answers1