如何计算过去 n 小时的移动平均线 [英] How to compute the moving average over the last n hours

问题描述

我正在尝试有效地计算(使用 SQL Server 2008)ProductCount 的移动平均值在 24 小时内.对于 Product 表中的每一行，我想知道过去 24 小时内 ProductCount(对于给定产品)的平均值是多少.我们数据的一个问题是并非所有日期/小时都存在(请参见下面的示例).如果缺少时间戳，则表示 ProductCount 为 0.

I am trying to compute efficiently (using SQL Server 2008) the moving average of the ProductCount over a period of 24 hours. For every single row in the Product table, I'd like to know what was the average of ProductCount (for that given products) over the last 24 hours. One problem with our data is that not all the dates/hours are present (see example below). If a TimeStamp is missing, it means that the ProductCount was 0.

我有一个包含数百万或带有日期、产品和计数的行的表.下面是我必须处理的数据的简化示例.

I have a table with millions or rows with a Date, Product and Count. Below is a simplified example of the data I have to deal with.

知道如何实现吗?

编辑:我需要的另一条数据是该期间(即 24 小时)的 MIN 和 MAX ProductCount.由于缺失值，计算 MIN/MAX 有点棘手......

EDIT: One other piece of data that I need is the MIN and MAX ProductCount for the period (i.e. 24h). Computing the MIN/MAX is a bit trickier because of the missing values...

+---------------------+-------------+--------------+
|         Date        | ProductName | ProductCount |
+---------------------+-------------+--------------+
| 2012-01-01 00:00:00 |    Banana   |    15000     |
| 2012-01-01 01:00:00 |    Banana   |    16000     |
| 2012-01-01 02:00:00 |    Banana   |    17000     |
| 2012-01-01 05:00:00 |    Banana   |    12000     |
| 2012-01-01 00:00:00 |    Apple    |     5000     |
| 2012-01-01 05:00:00 |    Apple    |     6000     |
+---------------------+-------------+--------------+

SQL

CREATE TABLE ProductInventory (
    [Date]  DATETIME,
    [ProductName] NVARCHAR(50),
    [ProductCount] INT
)

INSERT INTO ProductInventory VALUES ('2012-01-01 00:00:00', 'Banana', 15000)
INSERT INTO ProductInventory VALUES ('2012-01-01 01:00:00', 'Banana', 16000)
INSERT INTO ProductInventory VALUES ('2012-01-01 02:00:00', 'Banana', 17000)
INSERT INTO ProductInventory VALUES ('2012-01-01 05:00:00', 'Banana', 12000)
INSERT INTO ProductInventory VALUES ('2012-01-01 00:00:00', 'Apple', 5000)
INSERT INTO ProductInventory VALUES ('2012-01-01 05:00:00', 'Apple', 6000)

推荐答案

嗯，事实上，您需要计算每小时的平均值，实际上使这变得更简单，因为您只需要SUM 产品计数并将其除以固定数字(24).所以我认为这会得到你想要的结果(尽管在这种特殊情况下，游标实际上更快):

Well, the fact that you need to calculate the average for every hour, actually makes this simpler, since you just need to SUM the product count and divide it by a fixed number (24). So I think that this will get the results you want (though in this particular case, a cursor by be actually faster):

SELECT A.*, B.ProductCount/24 DailyMovingAverage
FROM ProductInventory A
OUTER APPLY (   SELECT SUM(ProductCount) ProductCount
                FROM ProductInventory
                WHERE ProductName = A.ProductName 
                AND [Date] BETWEEN DATEADD(HOUR,-23,A.[Date]) AND A.[Date]) B

这篇关于如何计算过去 n 小时的移动平均线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！