有没有办法在这个表中以GROUP BY为时间间隔? [英] Is there a way to GROUP BY a time interval in this table?
问题描述
我有这样一个表:
DateTime A
10:00:01 2
10:00:07 4
10:00:10 2
10:00:17 1
10:00:18 3
是否可以创建一个查询,让我每10秒返回A的平均值?在这种情况下,结果是:
3(4 + 2)/ 2
2(2 + 1 + 3)/ 3
预先感谢!
<编辑:如果你真的认为这不能做只是说不行! :)这是一个可接受的答案,我真的不知道这是否可以完成。
编辑2:我正在使用SQL Server 2008.我想有不同的分组但固定。例如,范围每10秒,1分钟,5分钟,30分钟,1小时和1天(只是一个例子,但类似的东西)
CREATE TABLE #times
(
thetime time,
A int
)
INSERT #times
VALUES('10:00 :01',2)
INSERT #times
VALUES('10:00:07',4)
INSERT #times
VALUES('10:00:10', 2)
INSERT #times
VALUES('10:00:17',1)
INSERT #times
VALUES('10:00:18',3)
SELECT avg(A) - < - 如果您需要非整数结果,您可能会遇到精度问题。例如:(avg(a * 1.0)
FROM #times
GROUP BY datepart(hour,thetime),DATEPART(minute,thetime),DATEPART(SECOND,thetime)/ 10
DROP TABLE #times
I have a table like this one:
DateTime A
10:00:01 2
10:00:07 4
10:00:10 2
10:00:17 1
10:00:18 3
Is this possible to create a query that returns me the average value of A each 10 seconds? In this case the result would be:
3 (4+2)/2
2 (2+1+3)/3
Thanks in advance!
EDIT: If you really think that this can not be done just say NO WAY! :) It's an acceptable answer, I really don't know if this can be done.
EDIT2: I'm using SQL Server 2008. I would like to have different groupings but fixed. For example, ranges each 10 sec, 1 minute, 5 minutes, 30 minutes, 1 hour and 1 day (just an example but something like that)
In SQL Server, you can use DATEPART and then group by hour, minute and second integer-division 10.
CREATE TABLE #times
(
thetime time,
A int
)
INSERT #times
VALUES ('10:00:01', 2)
INSERT #times
VALUES ('10:00:07', 4)
INSERT #times
VALUES ('10:00:10', 2)
INSERT #times
VALUES ('10:00:17', 1)
INSERT #times
VALUES ('10:00:18', 3)
SELECT avg(A) -- <-- here you might deal with precision issues if you need non-integer results. eg: (avg(a * 1.0)
FROM #times
GROUP BY datepart(hour, thetime), DATEPART(minute, thetime), DATEPART(SECOND, thetime) / 10
DROP TABLE #times
这篇关于有没有办法在这个表中以GROUP BY为时间间隔?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!