SQL query for 7-day scrolling in SQL Server

Question

I have a table of hourly use of the product (how many times the product is used) -

ID (bigint)| ProductId (tinyint)| Date (int - YYYYMMDD) | Hour (tinyint)| UsageCount (int) #|1 | 20140901 | 0 | 10 #|1 | 20140901 | 1 | 15 #|1 | 20140902 | 5 | 25 #|1 | 20140903 | 5 | 25 #|1 | 20140904 | 3 | 25 #|1 | 20140905 | 7 | 25 #|1 | 20140906 | 10 | 25 #|1 | 20140907 | 9 | 25 #|1 | 20140908 | 5 | 25 #|2 | 20140903 | 16 | 10 #|2 | 20140903 | 13 | 115 

Likewise, I have usage data for 4 different products (ProductId 1 to 4) stored for each hour in the product_usage table. As you can imagine, it is constantly growing as the nightly ETL process uploads data for the entire previous day. If the product is not used at any time of the day, a record at that hour will not be displayed in this table. Similarly, if the product is not used throughout the day, the table will not have a record for that day in the table. I need to create a report that gives daily use and the last 7 days of the moving average -

For example:

 ProductId | Date | DailyUsage | RollingAverage 1 | 20140901 | sum of usages of that day | (Sum of usages from 20140901 through 20140826) / 7 1 | 20140901 | sum of usages of that day | (Sum of usages from 20140901 through 20140826) / 7 1 | 20140902 | sum of usages of that day | (Sum of usages from 20140902 through 20140827) / 7 2 | 20140902 | sum of usages of that day | (Sum of usages from 20140902 through 20140827) / 7 

And so on .. I plan to create an indexed view on SQL Server 2014. Can you think of an efficient SQL query for this?

Answer 1

Try:

 select x.*, avg(dailyusage) over(partition by productid order by productid, date rows between 6 preceding and current row) as rolling_avg from (select productid, date, sum(usagecount) as dailyusage from tbl group by productid, date) x 

Violin:

http://sqlfiddle.com/#!6/f674a7/4/0

Replace "avg (dailusage) over ...." with the amount (not avg) if what you really want is the amount for the past week. In your name you say that you want the average, but later you say that you want to receive the amount. The request should be the same except for this, so use what you really need.

As pointed out by Gordon, this is basically the average of the last 6 dates in which the product was used, which may be more than just the last 6 days, if there are days on the table without any lines for this product, because it was not used at all . To get around this, you can use a date table and a product table.

Answer 2

You must be careful if you can lose data in a few days. If I assume that every day there is data for a product, then this approach will work:

 select p.productid, d.date, sum(usagecount), sum(sum(usagecount)) over (partition by p.productid order by d.date rows between 6 preceding and current row) as Sum7day from (select distinct productid from hourly) p cross join (select distinct date from hourly) d left join hourly h on h.productid = p.productid and h.date = p.date group by p.productid, d.date; 

Answer 3

XZZA ZA Z Az SACR2XC4WT VGY4TFC YYVGWTAAA WWWWWWWW Dssafffcgetgfdg sgfv g dfs df ef vdcsdtg tg rf sfs g ert rtdf g