CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Twelfth
Twelfth

Reputation: 7180

Rolling 7 day average using calculated averages

Quick setup that can be used in a fiddle.

create table tbl
(avgdate date, avgvalue numeric(10,2));

insert into tbl values ('2021-01-01',10.4);
insert into tbl values ('2021-01-02',13.1);
insert into tbl values ('2021-01-03',11.35);
insert into tbl values ('2021-01-04',3.4);
insert into tbl values ('2021-01-05',7.12);
insert into tbl values ('2021-01-06',16.76);
insert into tbl values ('2021-01-07',17.2);
insert into tbl values ('2021-01-08',13.76);
insert into tbl values ('2021-01-09',10.56);
insert into tbl values ('2021-01-10',null);
insert into tbl values ('2021-01-11',null);
insert into tbl values ('2021-01-12',null);

I'm looking to write something along the lines of

select avgdate,
   case when avgvalue is not null then avgvalue
   else
   --avg of past 7 days
end
from tbl

Normally I'd use the lag function or rownumber to pick out the previous rows values and average them which works for the 10th, but my logic hits a bug when it needs to include calculated rows. Jan 12th should be the average values for 5th through 9th, plus the calculated values for the 10th and 11th.

Upvotes: 0

Views: 829

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is pretty much the code you want:

coalesce(avgvalue,
         avg(avgvalue) over (order by date rows between 7 preceding and 1 preceding)
        )

Wait. That chooses the previous 7 values some of which might be NULL. You can use apply for this purpose:

select t.*,
       coalesce(t.avgvalue, t2.avgvalue7)
from tbl t outer apply
     (select avg(t2.avgvalue) as avgvalue7
      from (select top (7) t2.avgvalue
            from tbl t2
            where t2.avgvalue is not null and
                  t2.date < t.date
            order by t2.date desc
           ) t2
     ) t2;

Upvotes: 1

Related Questions