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

[hackfan]

我有如下一个表：

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 ??

[klan]

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