如何计算连续日期范围内的运行平均值

Database : SQL Server 2008 R2

数据库:SQL Server 2008 R2

Table structure:

            location,    date,                      temperature

             NY        2011-12-06 21:07:00 -05:00    20
             NY        2011-12-06 21:08:00 -05:00    21
             NY        2011-12-06 21:09:00 -05:00    22
             NY        2011-12-06 21:10:00 -05:00    23
             NY        2011-12-06 21:11:00 -05:00    24  <- Minute breaks here
             NY        2011-12-06 21:22:00 -05:00    27
             NY        2011-12-06 21:23:00 -05:00    25
             NY        2011-12-06 21:24:00 -05:00    26
             NY        2011-12-06 21:25:00 -05:00    25
             NY        2011-12-06 21:26:00 -05:00    26
             NY        2011-12-06 21:27:00 -05:00    24  <- Minute breaks here(NY ends)
             MA        2011-12-06 21:07:00 -05:00    21
             MA        2011-12-06 21:08:00 -05:00    22
             MA        2011-12-06 21:09:00 -05:00    24
             MA        2011-12-06 21:10:00 -05:00    23
             MA        2011-12-06 21:11:00 -05:00    22  <- Minute breaks here
             MA        2011-12-06 21:22:00 -05:00    22
             MA        2011-12-06 21:23:00 -05:00    25
             MA        2011-12-06 21:24:00 -05:00    26
             MA        2011-12-06 21:25:00 -05:00    24
             MA        2011-12-06 21:26:00 -05:00    25
             MA        2011-12-06 21:27:00 -05:00    29

Requirement: The table has one more column called running_average_temp. What is the best way to compute the running averages of temp for the continuous minutes of the date? (ie each set of unbroken minute readings of a location are a set of data, running average need to be computed for each row in this set) The table has more locations.

要求:该表还有一个名为running_average_temp的列。计算日期连续分钟的临时运行平均值的最佳方法是什么? (即,位置的每组完整分钟读数都是一组数据,需要为此组中的每一行计算运行平均值)该表具有更多位置。

(POST REQUIREMENT: This table gets its input every hour. The new set of rows which come in could be continued minutes of the existing rows or it could be a new set of readings. What would be the best way to compute the running averages for the new set that comes in every hour.)

(POST要求:此表每小时获取一次输入。进入的新行集可以是现有行的连续几分钟,也可以是一组新的读数。计算运行平均值的最佳方法是什么?每小时都有一套新的装置。)

1 个解决方案

#1

Here is an answer, with some edge cases added to the data set.

这是一个答案,在数据集中添加了一些边缘情况。

DECLARE @temps TABLE
    (
    location varchar(2) NOT NULL,
    date datetime NOT NULL,
    temperature tinyint NOT NULL
    )

INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:07:00',20)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:08:00',21)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:09:00',22)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:10:00',23)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:11:00',24) --<- Minute breaks here
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:22:00',27)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:23:00',25)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:24:00',26)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:25:00',25)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:26:00',26)
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 21:27:00',24) --<- Minute breaks here
INSERT INTO @temps (location, date, temperature) VALUES ('NY', '2011-12-06 22:00:00',28) --<- Minute breaks here
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:07:00',21)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:08:00',22)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:09:00',24)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:10:00',23)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:11:00',22) --<- Minute breaks here
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:22:00',22)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:23:00',25)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:24:00',26)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:25:00',24)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:26:00',25)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 21:27:00',29)
INSERT INTO @temps (location, date, temperature) VALUES ('MA', '2011-12-06 22:00:00',32)--<- Minute breaks here

;WITH TempSets (RowNumber, location, date, temperature, nextminutetemperature, previousminutetemperature)
AS
(
SELECT
    ROW_NUMBER() OVER (ORDER BY T.location ASC, T.date ASC) AS RowNumber,
    T.location,
    T.date,
    T.temperature,
    T1.temperature AS nextminutetemperature,
    T2.temperature AS previousminutetemperature
FROM
    @temps T
    LEFT JOIN @temps T1 ON
        T1.location = T.location
        AND T1.date = DATEADD(minute, +1, T.date)
    LEFT JOIN @temps T2 ON
        T2.location = T.location
        AND T2.date = DATEADD(minute, -1, T.date)
)

SELECT
    Result.location,
    MIN(Result.date) AS starttime,
    MAX(Result.date) AS endtime,
    COUNT(Result.groupidentifier) AS numberofreadings,
    AVG(Result.temperature) AS averagetemperature
FROM
(
SELECT
    (
    CASE
        WHEN
            TempSets.previousminutetemperature IS NULL
            AND TempSets.nextminutetemperature IS NULL
            THEN TempSets.rownumber
        ELSE
            (
            SELECT
                MIN(GroupNumber.rownumber)
            FROM
                TempSets AS GroupNumber
            WHERE
                GroupNumber.location = TempSets.location
                AND GroupNumber.rownumber >= TempSets.rownumber
                AND GroupNumber.nextminutetemperature IS NULL
            )
    END
    ) AS groupidentifier,
    TempSets.rownumber,
    TempSets.location,
    TempSets.date,
    TempSets.temperature,
    TempSets.nextminutetemperature,
    TempSets.previousminutetemperature
FROM
    TempSets
) AS Result
GROUP BY
    Result.groupidentifier,
    Result.location
ORDER BY
    Result.location ASC,
    MIN(Result.date) ASC

Results:

location starttime               endtime                 numberofreadings averagetemperature
-------- ----------------------- ----------------------- ---------------- ------------------
MA       2011-12-06 21:07:00.000 2011-12-06 21:11:00.000 5                22
MA       2011-12-06 21:22:00.000 2011-12-06 21:27:00.000 6                25
MA       2011-12-06 22:00:00.000 2011-12-06 22:00:00.000 1                32
NY       2011-12-06 21:07:00.000 2011-12-06 21:11:00.000 5                22
NY       2011-12-06 21:22:00.000 2011-12-06 21:27:00.000 6                25
NY       2011-12-06 22:00:00.000 2011-12-06 22:00:00.000 1                28