将 DateTime 分组为 5、15、30 和 60 分钟的时间间隔 [英] Group DateTime into 5,15,30 and 60 minute intervals

问题描述

我正在尝试将一些记录分组为 5、15、30 和 60 分钟的时间间隔:

I am trying to group some records into 5-, 15-, 30- and 60-minute intervals:

SELECT AVG(value) as "AvgValue",
sample_date/(5*60) as "TimeFive"
FROM DATA
WHERE id = 123 AND sample_date >= 3/21/2012

我想运行几个查询，每个查询都会将我的平均值分组为所需的时间增量.所以 5 分钟的查询将返回如下结果:

i want to run several queries, each would group my average values into the desired time increments. So the 5-min query would return results like this:

AvgValue  TimeFive
6.90      1995-01-01 00:05:00
7.15      1995-01-01 00:10:00
8.25      1995-01-01 00:15:00

30 分钟的查询将导致:

The 30-min query would result in this:

AvgValue  TimeThirty 
6.95      1995-01-01 00:30:00
7.40      1995-01-01 01:00:00

datetime 列采用 yyyy-mm-dd hh:mm:ss 格式

我的 datetime 列出现隐式转换错误.非常感谢任何帮助！

I am getting implicit conversion errors of my datetime column. Any help is much appreciated!

推荐答案

使用

datediff(minute, '1990-01-01T00:00:00', yourDatetime)

将为您提供自 1990-1-1 以来的分钟数(您可以使用所需的基准日期).

will give you the number of minutes since 1990-1-1 (you can use the desired base date).

然后你可以除以5、15、30或60，并根据这个除法的结果进行分组.我已经检查过它会被评估为一个整数除法，所以你会得到一个整数，你可以用它来分组.

Then you can divide by 5, 15, 30 or 60, and group by the result of this division. I've cheked it will be evaluated as an integer division, so you'll get an integer number you can use to group by.

即

group by datediff(minute, '1990-01-01T00:00:00', yourDatetime) /5

UPDATE 由于原始问题被编辑为要求数据在分组后以日期时间格式显示，我添加了这个简单的查询，它将执行 OP 想要的操作:

UPDATE As the original question was edited to require the data to be shown in date-time format after the grouping, I've added this simple query that will do what the OP wants:

-- This convert the period to date-time format
SELECT 
    -- note the 5, the "minute", and the starting point to convert the 
    -- period back to original time
    DATEADD(minute, AP.FiveMinutesPeriod * 5, '2010-01-01T00:00:00') AS Period,
    AP.AvgValue
FROM
    -- this groups by the period and gets the average
    (SELECT
        P.FiveMinutesPeriod,
        AVG(P.Value) AS AvgValue
    FROM
        -- This calculates the period (five minutes in this instance)
        (SELECT 
            -- note the division by 5 and the "minute" to build the 5 minute periods
            -- the '2010-01-01T00:00:00' is the starting point for the periods
            datediff(minute, '2010-01-01T00:00:00', T.Time)/5 AS FiveMinutesPeriod,
            T.Value
        FROM Test T) AS P
    GROUP BY P.FiveMinutesPeriod) AP

注意:为了清楚起见，我将其分为 3 个子查询.你应该从里到外阅读它.当然，它可以写成一个单一的、紧凑的查询

注意:如果您更改期间和开始日期时间，您可以获得所需的任何时间间隔，例如从给定日期开始的几周，或者您可能需要的任何时间

如果您想为此查询生成测试数据，请使用:

If you want to generate test data for this query use this:

CREATE TABLE Test
( Id INT IDENTITY PRIMARY KEY,
Time DATETIME,
Value FLOAT)

INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:00:22', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:03:22', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:04:45', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:07:21', 20)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:10:25', 30)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:11:22', 30)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:14:47', 30)

执行查询的结果是这样的:

The result of executing the query is this:

Period                     AvgValue
2012-03-22 00:00:00.000    10
2012-03-22 00:05:00.000    20
2012-03-22 00:10:00.000    30

这篇关于将 DateTime 分组为 5、15、30 和 60 分钟的时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！