给定时间内每5分钟数据的平均值

Question

我对MySQL有个问题。我需要两个特定时间之间来自MySQL的数据的平均值。

为此，我使用：

select avg(Column) 
from Databasename.tablename 
where datetime BETWEEN '2012-09-08 00:00:00' AND '2012-09-08 15:30:00'

现在，我需要的是给定时间内每5分钟的平均数据。通过使用此命令：

select avg(Column) 
from Databasename.Tablename 
where datetime BETWEEN '2012-09-08 15:00:00' 
                   AND '2012-09-08 15:30:00' + 'INTERVAL 5minutes'

它没有显示任何错误，但显示了NULL。

如何获取两个特定时间段内每5分钟的数据？

Answer 1

分组到间隔

您应该使用group by为您想要平均的每个间隔创建一个组。

select avg(Column), convert((min(datetime) div 500)*500 + 230, datetime) as time
from Databasename.tablename 
where datetime BETWEEN '2012-09-08 00:00:00' AND '2012-09-08 15:30:00'
group by datetime div 500

这利用了这样一个事实，即日期和时间可以隐式地转换为数字，然后数字的格式将是YYMMDDhhmmss。将这样的数字除以100会剥离秒数，同样地，除以500会为每5分钟的间隔产生一个唯一的数字。

我选择的附加列将提供间隔的中间位置:从间隔开始的任何时间(这里选择最小值，但实际上并不重要)，转换为5分钟的间隔数，再转换为日期和时间数，然后添加两分半钟。您可以使用类似的语法来计算间隔的开始(只需省略+ 230 )或(包括)结束(+ 459)。

已使用sqlfiddle进行测试。

范围结束处理

请注意，您的BETWEEN范围将包括15:30:00中的行，但不包括当时开始的时间间隔中的其他行。也许您希望从选择中排除范围的末尾：

where datetime >= '2012-09-08 00:00:00' AND datetime < '2012-09-08 15:30:00'

为什么会得到NULL

至于查询产生NULL的原因:要进行区间运算，不要用引号将整个区间括起来，并且使用单数名称表示时间单位。也就是说。

'2012-09-08 15:30:00' + INTERVAL 5 minute

但这只会将范围扩展到2012-09-08 15:35:00，而仍然不会创建您想要的5分钟间隔。按照您编写的方式，您正在尝试添加两个字符串，这在MySQL中是不可能的，除非字符串可以转换为数字，在这种情况下，这些数字将被相加。

您可能希望使用区间算术来计算区间的独占结束，即该区间之后的第一秒：

 convert((min(datetime) div 500)*500, datetime) + INTERVAL 5 minute as endOfInterval

简单地添加500而不是我的查询的230是行不通的，因为结果数字可能表示每个小时的最后一个时间间隔的无效日期。