CODEWITHSUNDEEP
sqladvantage-database-servermoving-average
user1026448
user1026448

Reputation: 79

How to calculate moving average?

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+?

Upvotes: 0

Views: 474

Answers (1)

The Dag
The Dag

Reputation: 1841

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?

Upvotes: 1

Related Questions