在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

同样地，我在产品使用表中为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查询来实现吗？

- Andy T

---

你所谓的“滚动平均”实际上是一个总和。 - Gordon Linoff

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

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

正如Gordon指出的一样，这基本上是产品使用的过去6个日期的平均值，如果表格中有某些日期没有任何产品行，则可能多于过去6天。为了解决这个问题，你可以使用一个日期表和你的产品表。

- Brian DeMilia

---

嘿，你知道如何重新编写你的SQL代码以适应MSQL 2008吗？我试着在2008版本上运行它，但是一直出错。 - collarblind

SQL Server 2008不支持ROWS BETWEEN语法。您需要采用稍微不同的方法，例如https://dev59.com/rl8d5IYBdhLWcg3wnzRz - reedstonefood

哇 - 我知道“over(partition ...)”函数的一部分功能，但显然我忽略了“preceding”元素。谢谢！ - jklemmack

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;

- Gordon Linoff

---

没错。如果出现缺失观测值，采用“排序”并取最后N个观测值的技巧就会失效。 - annoying_squid