求SQL：计算timestamp为20分钟内的数据的平均值

Question

我有如下一个表：data
{
timestamp DATETIME
number int
}数据内容：timestamp                     number
2003-06-01 00:00:01             12
2003-06-01 00:01:01             17
2003-06-01 00:02:01             20
2003-06-01 00:03:01              7
2003-06-01 00:04:01             23
2003-06-01 00:05:01              3
2003-06-01 00:06:01             12
2003-06-01 00:07:01             13
2003-06-01 00:08:01             15
2003-06-01 00:09:01             23
2003-06-01 00:10:01              3
2003-06-01 00:11:01              7
.
.
.现在，我需要一条SQL，能够把
00:00:00 - 00:20:00
00:20:00 - 00:40:00
00:40:00 - 01:00:00
01:00:00 - 01:02:00
之间的数字的平均值给出来怎么写？select avg(number),??? where ??? group by ??

Answer 1

try:select avg(a.number), a.mins FROM (select number, floor((UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP('2007/02/20 10:06:00'))/(60*20)) mins 
from tbl)a
group by a.mins