SQL Server中7天滚动平均数的SQL查询

Question

我有一个每小时产品使用量（产品使用次数）数据表——

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

同样，我在product_usage表中存储了每小时4种不同产品（ProductId从1到4）的使用数据。正如您所能想象的，随着夜间ETL过程转储前一天的数据，它会不断增长。如果产品在一天中的任何一个小时都没有使用，则该小时的记录将不会显示在此表中。同样，如果一种产品没有使用一整天，那么表中也不会有当天的任何记录。我需要生成一份报告，给出每日使用量和最近7天的滚动平均值——

例如：

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

等等。。我计划在SQL server 2014中创建一个索引视图。你能想出一个高效的SQL查询来做到这一点吗？

Answer 1

尝试：

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

如果你真正想要的是过去一周的总和，那么用sum（而不是avg）代替"avg（dailusage）over…"。在你的标题中，你说你想要平均数，但后来你说你要总和。除此之外，查询应该是相同的，所以使用您实际想要的任何查询。

正如Gordon所指出的，这基本上是过去6天使用该产品的平均值，如果因为根本没有使用该产品，所以表上没有任何该产品的行，那么这可能会超过过去6天。为了避免这种情况，你可以使用日期表和产品表。

Answer 2

如果某些日子可能会丢失数据，则必须小心。如果我假设每天都有一些产品的数据，那么这种方法就会奏效：

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;