按10分钟分组的问题

[liucongjun666]

要求统计一个小时每10分钟内的平均值,也就是说按10分钟分组统计,我只能做到按分钟分组统计,但按10分钟分组我就困难了,希望得到高手的指点,赐教一个查询语句,我现在的查询语句是这样的
select convert(char(16),[riqi],120),avg(v),avg(w) from cf where riqi between '2011-02-13 00:00:00' and '2011-02-13 23:59:59' group by convert(char(16),[riqi],120)order by 1
这个语句只能那分钟分组,不能按10分钟分组
下面是数据库里面的数据

riqi v w
2011-02-13 18:07:07.000 10 9
2011-02-13 18:12:07.000 23 67
2011-02-13 18:19:07.000 55 88
2011-02-13 18:23:07.000 34 54
2011-02-13 18:26:07.000 56 33
2011-02-13 18:33:07.000 66 33
2011-02-13 18:38:07.000 23 45
2011-02-13 18:43:07.000 100 455
2011-02-13 18:49:07.000 56 78
2011-02-13 18:51:07.000 44 67
2011-02-13 18:53:07.000 55 88
2011-02-13 18:57:07.000 788 89
2011-02-14 18:02:07.000 5 8

[dawugui]

参考这个,自己修改一下.

一个按五分钟分段统计的例

create table tb(时间 datetime , 金额 int)
insert into tb values('2007-1-1 10:00:23' ,          8 )
insert into tb values('2007-1-1 10:01:24' ,          4 )
insert into tb values('2007-1-1 10:05:00' ,          2 )  
insert into tb values('2007-1-1 10:06:12' ,          3 )
insert into tb values('2007-1-1 10:08:00' ,          1 )
insert into tb values('2007-1-1 10:12:11' ,          5 )
go

--时间段>=10:00:00 and 时间段<10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
       count(*) as 行数,
       sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段                                                    行数          总金额         
------------------------------------------------------ ----------- ----------- 
2007-01-01 10:00:00.000                                3           14
2007-01-01 10:05:00.000                                2           4
2007-01-01 10:10:00.000                                1           5
（所影响的行数为 3 行）
*/

--时间段>10:00:00 and 时间段<=10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
       count(*) as 行数,
       sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段                                                    行数          总金额         
------------------------------------------------------ ----------- ----------- 
2007-01-01 10:00:00.000                                2           12
2007-01-01 10:05:00.000                                3           6
2007-01-01 10:10:00.000                                1           5

（所影响的行数为 3 行）
*/

drop table tb

[快溜]

select convert(char(14),[riqi],120),cast(datepart(minete,) as int)/11,
avg(v),avg(w) from cf 
where riqi between '2011-02-13 00:00:00' and '2011-02-13 23:59:59' 
group by convert(char(14),[riqi],120),cast(datepart(minete,) as int)/11
order by 1

[快溜]

--datepart(minete,[riqi])

[打一壶酱油]

改一下 group by 语句就行了

select convert(char(16),[riqi],120),avg(v),avg(w) from cf 
where riqi between '2011-02-13 00:00:00' and '2011-02-13 23:59:59'
group by datediff(mi,0,convert(char(16),[riqi],120))/10
order by 1

[-晴天]

在你的基础上去掉一位,就是以10分钟来分组.

select convert(char(16),[riqi],120),avg(v),avg(w) from cf where riqi between '2011-02-13 00:00:00' and '2011-02-13 23:59:59' group by convert(char(15),[riqi],120)order by 1

[-晴天]

稍作改动:

create table cf(riqi datetime , v int,w int)
insert into cf select '2011-02-13 18:07:07.000',10, 9
insert into cf select '2011-02-13 18:12:07.000',23, 67
insert into cf select '2011-02-13 18:19:07.000',55, 88
insert into cf select '2011-02-13 18:23:07.000',34, 54
insert into cf select '2011-02-13 18:26:07.000',56, 33
insert into cf select '2011-02-13 18:33:07.000',66, 33
insert into cf select '2011-02-13 18:38:07.000',23, 45
insert into cf select '2011-02-13 18:43:07.000',100, 455
insert into cf select '2011-02-13 18:49:07.000',56, 78
insert into cf select '2011-02-13 18:51:07.000',44, 67
insert into cf select '2011-02-13 18:53:07.000',55, 88
insert into cf select '2011-02-13 18:57:07.000',788, 89
insert into cf select '2011-02-14 18:02:07.000',5, 8
go
select convert(char(15),[riqi],120)+'0',avg(v),avg(w) 
from cf 
where riqi between '2011-02-13 00:00:00' and '2011-02-13 23:59:59' 
group by convert(char(15),[riqi],120)
order by 1
go
drop table cf
/*
---------------- ----------- -----------
2011-02-13 18:00 10          9
2011-02-13 18:10 39          77
2011-02-13 18:20 45          43
2011-02-13 18:30 44          39
2011-02-13 18:40 78          266
2011-02-13 18:50 295         81

(6 行受影响)
*/