Calculating moving average over irregular data

Question

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

Can't you just put a `WHERE YEAR(Date) >= '2018' AND YEAR(Date) < '2019'`? — Eric, Aug 17 '20 at 22:32
I need it to be a moving average, so specifically, its all events in the preceding 365 days, so I can't hard-code a year like that. — Chris Fonnesbeck, Aug 17 '20 at 22:56
Then you just turn it into a column name like `WHERE YEAR(Date) >= DATEADD(YEAR, -1, Date) AND YEAR(Date) < YEAR(Date)`. Given the example, it's hard to understand what you exactly want. At least give sample input and expected output. — Eric, Aug 17 '20 at 23:08
I think the suggestion below may be what I need, but I will edit my question to better illustrate what I am after. — Chris Fonnesbeck, Aug 18 '20 at 00:56

score 3 · Answer 1 · answered Aug 17 '20 at 22:39

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

劉鎮瑲 · Answer 2 · 2020-08-20T08:45:27.717

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

Calculating moving average over irregular data

2 Answers2