【【谁能告诉我SQL中怎么按时间段求平均值】】

[may_kid]

表：ash
value time
23 2006-12-26 10:58:00
33 2006-12-26 10:59:00
45 2006-12-26 11:00:00
34 2006-12-26 10:01:00
24 2006-12-26 10:02:00
....
我现在想把每5分钟段内求一个平均值
结果：
时间段 平均值
2006-12-26 10:55:00 37
2006-12-26 11:00:00 33
2006-12-26 11:05:00 36
2006-12-26 11:10:00 39

[Oliver5914]

create table t_1 ( value int,times datetime ) insert into t_1 select 23,'2006-12-26 10:58:00' union select 33, '2006-12-26 10:59:00' union select 45 ,'2006-12-26 11:00:00' union select 34, '2006-12-26 10:01:00' union select 24, '2006-12-26 10:02:00' select avg(value),dateadd(mi,times*5,'1990-01-01 00:00:00') from ( select value,DATEDIFF(mi,'1990-01-01 00:00:00',times)/5 as times from t_1)a group by times

[KevinLiu]

应该要先将时间更新到每个固定的区间才可以计算，可以用DATEPART查询分钟然后计算获得正确的时间区间

[may_kid]

自己解决了。。 select dateadd(minute,-datepart(minute,time)%5,convert(varchar(16),time,120)) as time,avg(p) as p from his_yc where datepart(minute,time) between 0 and 59 group by dateadd(minute,-datepart(minute,time)%5,convert(varchar(16),time,120))