有没有办法按此表中的时间间隔进行分组？

Question

我有一个像这样的表：

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

提前致谢！

编辑：如果你真的认为这不能做到，就说“不”！ :) 这是一个可以接受的答案，我真的不知道这是否可以做到。

EDIT2：我正在使用 SQL Server 2008。我希望有不同的分组但固定。例如，范围为每 10 秒、1 分钟、5 分钟、30 分钟、1 小时和 1 天（只是一个示例，但类似的情况）

Answer 1

在 SQL Server 中，您可以使用 DATEPART，然后按小时、分钟和秒整数除以 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

Answer 2

这取决于您使用的 DBMS。
在 Oracle 中您可以执行以下操作：

SELECT AVG(A) 
FROM MYTABLE 
GROUP BY to_char(DateTime, 'HH24:MI') 

Answer 3

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `dtime` datetime NOT NULL,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


INSERT INTO `test` (`id`, `dtime`, `val`) VALUES
(1, '2011-09-27 18:36:19', 8),
(2, '2011-09-27 18:36:21', 4),
(3, '2011-09-27 18:36:27', 5),
(4, '2011-09-27 18:36:35', 3),
(5, '2011-09-27 18:36:37', 2);

---

SELECT *, AVG(val) FROM test GROUP BY FLOOR(UNIX_TIMESTAMP(dtime) / 10)

Answer 4

有人可能会用完整的代码给你一个答案，但我处理这个问题的方法是将其分解为几个较小的问题/解决方案：

（1）创建一个带有间隔的临时表。请参阅此问题的已接受答案：

获取两个日期之间的日期列表

这个答案是针对 MySQL 的，但应该可以帮助您入门。谷歌搜索“创建间隔 SQL”也应该会产生其他方法来实现此目的。您将需要使用主表中的 MAX(DateTime) 和 MIN(DateTime) 作为您使用的任何方法的输入（显然，跨度为 10 秒）。

(2) 将临时表与主表连接，连接条件为（伪代码）：

FROM mainTable m INNER JOIN #tempTable t ON m BETWEEN t.StartDate AND t.EndDate

(3) 现在应该像正确选择和分组一样简单：

SELECT 
  AVG(m.A)     
FROM  
  mainTable m 
  INNER JOIN #tempTable t ON m BETWEEN t.StartDate AND t.EndDate 
GROUP BY 
  t.StartDate 

编辑： 如果您想查看如果间隔没有记录（零平均值），则必须重新排列查询，使用 LEFT JOIN 并在 mA 上进行 COALESCE（见下文）。如果你不关心看到这样的内部结构，OCary 的解决方案更好/更干净。

SELECT 
  AVG(COALESCE(m.A, 0))
FROM  
  #tempTable t
  LEFT JOIN mainTable m ON m BETWEEN t.StartDate AND t.EndDate 
GROUP BY 
  t.StartDate 

Answer 5

我通过使用通用表表达式来获取数据的任何给定日期之间的所有周期来解决此问题。原则上，您可以将间隔更改为任何 SQL 间隔。

DECLARE @interval_minutes INT = 5, @start_date DATETIME = '20130201', @end_date DATETIME = GETDATE()

;WITH cte_period AS
 (
    SELECT  CAST(@start_date AS DATETIME) AS [date]

    UNION ALL

     SELECT DATEADD(MINUTE, @interval_minutes, cte_period.[date]) AS [date]
     FROM cte_period
     WHERE DATEADD(MINUTE, @interval_minutes, cte_period.[date]) < @end_date
 )

, cte_intervals AS
 (SELECT [first].[date] AS [Start], [second].[date] AS [End] 
 FROM cte_period [first] 
 LEFT OUTER JOIN cte_period [second] ON DATEADD(MINUTE, 5, [first].[date]) = [second].[date]
  )

SELECT i.[Start], AVG(data)
FROM cte_intervals i
LEFT OUTER JOIN your_data mu ON mu.your_date_time >= i.Start and mu.your_date_time < i.[End]
GROUP BY i.[Start]
OPTION (MAXRECURSION 0)

Answer 6

来自 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID =142634 您也可以使用以下查询：

select dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0), avg ( value )
from   yourtable
group by dateadd(minute, datediff(minute, 0, timestamp ) / 10 * 10, 0)

然后有人扩展该查询以提出建议：

Select
a.MyDate,
Start_of_10_Min =
dateadd(mi,(datepart(mi,a.MyDate)/10)*10,dateadd(hh,datediff(hh,0,a.Mydate),0))
from
( -- Test Data
select MyDate = getdate()
) a

尽管我不太清楚他们计划如何在第二个建议中获得平均值。

就我个人而言，我更喜欢 OCary 的回答，因为我知道那里发生了什么，并且我将能够在 6 个月内理解它时间没有再次查找，但为了完整性我将其包括在内。