尝试:

选择 x.*,avg(dailyusage) over(partition by productid order by productid, 6 前行和当前行之间的日期行) as rolling_avgfrom (select productid, date, sum(usagecount) as dailyusage来自 tbl按productid、date分组)x

小提琴:

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

如果您真正想要的是过去一周的总和，请将 "avg(dailusage) over...." 替换为 sum(而不是 avg).在你的标题中，你说你想要平均值，但后来你说你想要总和.除此以外，查询应该相同，因此请使用您真正想要的那个.

正如 Gordon 所指出的，这基本上是过去 6 天使用该产品的日期的平均值，如果表上没有该产品的任何行，则可能不仅仅是过去 6 天，因为它根本没有被使用.为了解决这个问题，您可以使用日期表和产品表.

I have a table of hourly product usage (how many times the product is used) data –

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 the usage data for 4 different products (ProductId from 1 through 4) stored for every hour in the product_usage table. As you can imagine, it is constantly growing as the nightly ETL process dumps the data for the entire previous day. If a product is not used on any hour of a day, the record for that hour won’t appear in this table. Similarly, if a product is not used for the entire day, there won’t be any record for that day in the table. I need to generate a report that gives daily usage and last 7 days’ rolling 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 am planning to create an Indexed View in SQL server 2014. Can you think of an efficient SQL query to do this?

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

Fiddle:

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

Replace "avg(dailusage) over...." with sum (rather than avg) if what you really want is the sum for the past week. In your title you say you want the average but later you say you want the sum. The query should be the same other than that, so use whichever you actually want.

As was pointed out by Gordon this is basically the average of the past 6 dates in which the product was used, which might be more than just the past 6 days if there are days without any rows for that product on the table because it wasn't used at all. To get around that you could use a date table and your products table.