给定时间内每5分钟的平均数据 [英] Average of data for every 5 minutes in the given times

问题描述

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

I have a problem with MySQL. I need the average of data from MySQL between two particular times.

为此，我正在使用:

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

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

Now, what I need is the average of data for every 5 minutes, in the given times. By using this:

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.

It's not showing any error but showing NULL.

如何在两个特定时间段内每5分钟获取一次数据?

How do I get the data for every 5mins within two particular time periods?

推荐答案

分组为间隔

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

Group to intervals

You should use group by to create one group for each interval over which you want to average.

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分钟就会产生一个唯一的数字.

This makes use of the fact that a date and time can implicitely be converted to a number, which will then be of the format YYMMDDhhmmss. Dividing such a number by 100 strips the seconds, and similarly dividing by 500 yields a unique number for every 5-minute interval.

我选择的另一列将给出间隔的中间值:从间隔中的任何时间(此处选择最小值，但这并不重要)，变成该5分钟的间隔数，再返回一个日期-时间号码，然后加上两分半钟.您可能会使用类似的语法来计算时间间隔的开始(只需省略+ 230)或(包括)结束(+ 459).

The additional column I selected will give the middle of the interval: any time from the interval (here choosing the minimum, but it doesn't really matter), turned into that 5-minute interval number, back into a date-and-time number, and then adding two and a half minutes. You might use similar syntax to compute the beginning (simply leave the + 230 out) or (inclusive) end (+ 459) of the interval.

使用 sqlfiddle 进行了测试.

请注意，您的BETWEEN范围将包括来自15:30:00的行，但不包括那时开始的间隔中的其他行.也许您想从选择中排除范围的末端:

Note that your BETWEEN range will include rows from 15:30:00 but no others from the interval started at that time. Maybe you want to exclude the end of the range from your selection:

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

为什么会为空

关于查询为何产生NULL的原因:要进行间隔算术，您无需将整个间隔内容括在引号中，并且将单数名称用作时间单位.即

Why you get NULL

As to why your query yields NULL: To do interval arithmetic, you don't enclose the whole interval thing in quotes, and you use singular names for time units. I.e.

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

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

But this would simply extend the range to 2012-09-08 15:35:00 and still not create those 5-minute intervals you want. The way you wrote it, you're trying to add two strings, which isn't possible in MySQL unless the strings can be converted to numbers, in which case those numbers will be added.

您可能要使用间隔算法来计算间隔的互斥结束，即间隔之后的第一秒 :

You might want to use interval arithmetic to compute the exclusive end of the interval, i.e. the first second after the interval:

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

仅添加500而不是查询的230无效，因为结果数字可能表示每小时最后一个间隔的日期无效.

Simply adding 500 instead of the 230 of my query won't work as the resulting number might represent an invalid date for the last interval of each hour.

这篇关于给定时间内每5分钟的平均数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！