SQL 将 DateTime 分组为 5、15、30 和 60 分钟间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9814930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group DateTime into 5,15,30 and 60 minute intervals
提问by jrubengb
I am trying to group some records into 5-, 15-, 30- and 60-minute intervals:
我正在尝试将一些记录分组为 5、15、30 和 60 分钟的时间间隔：
SELECT AVG(value) as "AvgValue",
sample_date/(5*60) as "TimeFive"
FROM DATA
WHERE id = 123 AND sample_date >= 3/21/2012
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:
我想运行几个查询，每个查询都会将我的平均值分组为所需的时间增量。所以 5 分钟的查询将返回如下结果：
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
The 30-min query would result in this:
30 分钟的查询将导致：
AvgValue TimeThirty
6.95 1995-01-01 00:30:00
7.40 1995-01-01 01:00:00
The datetimecolumn is in yyyy-mm-dd hh:mm:ssformat
该datetime列是yyyy-mm-dd hh:mm:ss格式
I am getting implicit conversion errors of my datetimecolumn. Any help is much appreciated!
我的datetime专栏出现隐式转换错误。任何帮助深表感谢！
回答by JotaBe
Using
使用
datediff(minute, '1990-01-01T00:00:00', yourDatetime)
will give you the number of minutes since 1990-1-1 (you can use the desired base date).
将为您提供自 1990-1-1 以来的分钟数（您可以使用所需的基准日期）。
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.
然后您可以除以 5、15、30 或 60，并根据除法的结果进行分组。我已经检查过它将被评估为整数除法，因此您将获得一个可用于分组的整数。
i.e.
IE
group by datediff(minute, '1990-01-01T00:00:00', yourDatetime) /5
UPDATEAs 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:
更新由于原始问题被编辑为要求数据在分组后以日期时间格式显示，我添加了这个简单的查询，它将执行 OP 想要的操作：
-- 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
NOTE: I've divided this in 3 subqueries for clarity. You should read it from inside out. It could, of course, be written as a single, compact query
注意：为了清楚起见，我将其分为 3 个子查询。你应该从里到外阅读它。当然，它可以写成一个单一的、紧凑的查询
NOTE: if you change the period and the starting date-time you can get any interval you need, like weeks starting from a given day, or whatever you can need
注意：如果您更改期间和开始日期时间，您可以获得您需要的任何时间间隔，例如从给定日期开始的几周，或者您可能需要的任何时间
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
回答by DJ Sipe
Building on @JotaBe's answer (to which I cannot comment on--otherwise I would), you could also try something like this which would not require a subquery.
以@JotaBe 的回答为基础（我无法对此发表评论——否则我会），您也可以尝试类似这样的不需要子查询的方法。
SELECT
AVG(value) AS 'AvgValue',
-- Add the rounded seconds back onto epoch to get rounded time
DATEADD(
MINUTE,
(DATEDIFF(MINUTE, '1990-01-01T00:00:00', your_date) / 30) * 30,
'1990-01-01T00:00:00'
) AS 'TimeThirty'
FROM YourTable
-- WHERE your_date > some max lookback period
GROUP BY
(DATEDIFF(MINUTE, '1990-01-01T00:00:00', your_date) / 30)
This change removes temp tables and subqueries. It uses the same core logic for grouping by 30 minute intervals but, when presenting the data back as part of the result I'm just reversing the interval calculation to get the rounded date & time.
此更改删除临时表和子查询。它使用相同的核心逻辑按 30 分钟间隔分组，但是，当将数据作为结果的一部分显示回来时，我只是反转间隔计算以获得四舍五入的日期和时间。
回答by chiliNUT
So, in case you googled this, but you need to do it in mysql, which was my case:
所以，如果你用谷歌搜索了这个，但你需要在 mysql 中完成，这就是我的情况：
In MySQL you can do
在 MySQL 你可以做
GROUP BY
CONCAT(
DATE_FORMAT(`timestamp`,'%m-%d-%Y %H:'),
FLOOR(DATE_FORMAT(`timestamp`,'%i')/5)*5
)
