Вопрос по sql-server-2008, sql-server – Как вычислить скользящее среднее за последние n часов

1

Я пытаюсь эффективно вычислить (с помощью SQL Server 2008)скользящее среднее ProductCount в течение 24 часов. Для каждой строки таблицы Product я хотел бы знать, каково среднее значение ProductCount (для данных продуктов) за последние 24 часа. Одна проблема с нашими данными состоит в том, что присутствуют не все даты / часы (см. Пример ниже). Если TimeStamp отсутствует, это означает, что ProductCount был 0.

У меня есть таблица с миллионами или строк с датой, продуктом и количеством. Ниже приведен упрощенный пример данных, с которыми мне приходится иметь дело.

Есть идеи, как этого добиться?

EDITЕще один фрагмент данных, который мне нужен, - это MIN и MAX ProductCount за период (то есть 24 часа). Вычисление MIN / MAX немного сложнее из-за пропущенных значений ...

+---------------------+-------------+--------------+
|         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)

Ваш Ответ

2   ответа
3

SELECT *
FROM ProductInventory A
OUTER APPLY (   
    SELECT 
        SUM(ProductCount) / 24 AS DailyMovingAverage, 
        MAX(ProductCount) AS MaxProductCount,
        CASE COUNT(*) WHEN 24 THEN MIN(ProductCount) ELSE 0 END AS MinProductCount
    FROM ProductInventory
    WHERE ProductName = A.ProductName 
    AND [Date] BETWEEN DATEADD(HOUR, -23, A.[Date]) AND A.[Date]) B

Чтобы учесть пропущенные записи, убедитесь, что за последние 24 часа действительно было 24 записи.MIN(ProductCount)и вернуть 0 в противном случае.

За работойSQL Fiddle, с кучей (бушель?) апельсинов, добавленных, чтобы показать работу MinProductCount

4

every час, на самом деле делает это проще, так как вам просто нужноSUM подсчитать произведение и разделить его на фиксированное число (24). Поэтому я думаю, что это даст желаемые результаты (хотя в данном конкретном случае курсор будет на самом деле быстрее):

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
Error: User Rate Limit Exceeded Martin

Похожие вопросы