SQL Server 中 7 天滚动平均值的 SQL 查询 [英] SQL Query for 7 Day Rolling Average in SQL Server

问题描述

我有一张每小时产品使用量(产品使用多少次)数据的表格 –

ID (bigint)|ProductId (tinyint)|日期 (int - YYYYMMDD) |小时 (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 |日期 |日常使用 |滚动平均1 |20140901 |当天的使用总和|(从 20140901 到 20140826 的使用总和)/71 |20140901 |当天的使用总和|(从 20140901 到 20140826 的使用总和)/71 |20140902 |当天的使用总和|(从 20140902 到 20140827 的使用总和)/72 |20140902 |当天的使用总和|(从 20140902 到 20140827 的使用总和)/7

等等..我打算在 SQL Server 2014 中创建一个索引视图.你能想到一个高效的 SQL 查询来做到这一点吗?

解决方案

尝试:

选择 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.

这篇关于SQL Server 中 7 天滚动平均值的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！