CODEWITHSUNDEEP
sqlsql-servercountaveragewindow-functions
Chris Fonnesbeck
Chris Fonnesbeck

Reputation: 4203

Calculating moving average over irregular data

I am trying to calculate a moving average of several fields in a SQL Server database that involved irregularly-spaced values over time. I realized that for regularly-spaced data I can use an SELECT grp, AVG(count) FROM t ... OVER (PARTITION BY grp ... ROWS 7 PRECEDING) to create a moving average of the prior week's data. However, I have data organized as follows:

DATE         GRP    COUNT
2018-07-05   1        10
2018-07-08   1        4
2018-07-11   1        6
2018-07-12   1        6
2018-07-11   2        5
2018-07-15   2        10
2018-07-17   2        8
2018-07-20   2        10
...

Where for most groups there are no observations for some dates. The output I'm looking for is:

DATE         GRP    MOVING_AVG
2018-07-05   1        10
2018-07-08   1         7
2018-07-11   1         6.67
2018-07-13   1         5.33  
2018-07-11   2         5
2018-07-15   2         7.5
2018-07-16   2         7.67
2018-07-20   2         9.33

Is there a way of specifying dates instead of rows in the PRECEDING clause, or do I have to create some sort of mask to average over?

EDITED FOR CLARIFICATION BASED ON COMMENTS

Upvotes: 1

Views: 1199

Answers (2)

劉鎮瑲
劉鎮瑲

Reputation: 597

If i'm not misunderstanding. You want 7 or whatever days but rows before a date.

DATE         GRP    COUNT
2018-07-11   2        5
2018-07-15   2        10
2018-07-17   2        8
2018-07-20   2        10    <--- the AVG of this row must include 7 days before,so 2018-07-11 not include

In that case :

select
    date,
    grp,
    (
        select avg(count)
        from t t1
        where 
            t1.grp = t.grp 
            and DATEDIFF(day, t1.date, t.date) <= 7 /*7 or whatever day you want*/
            and t1.date <= t.date
    ) as MOVING_AVG
from t

Upvotes: 0

GMB
GMB

Reputation: 222432

In SQL Server, I think this might be simpler achieved with a lateral join:

select
    date,
    grp,
    (
        select avg(count)
        from mytable t1
        where 
            t1.grp = t.grp 
            and t1.date >= dateadd(year, -1, t.date)
            and t1.date <= t.date
    ) as cnt
from mytable

Upvotes: 3

Related Questions