用sql语句按指定时间段分组统计

[jakeweny]

表结构如下：
GID          VaID	    Mac       Acount          ActiveTime     SubDate
88067	1035395409	90E6BAD84598	1	2010-02-04 14:10:41.560	2010-02-04
86036	3662412139	00306731A792	3	2010-02-04 14:15:45.543	2010-02-04
86599	975963213	00E04CF8AFAF	5	2010-02-04 14:18:47.450	2010-02-04
88625	3658907870	0024210197CC	7	2010-02-04 14:21:47.433	2010-02-04
70025	1902854282	00E04CC7B849	11	2010-02-04 14:22:47.417	2010-02-04
88861	3736328770	001FC6A83210	12	2010-02-04 14:33:47.403	2010-02-04
89985	1035490878	00E04D21914D	1	2010-02-04 14:43:47.357	2010-02-04
88983	3683763045	0011D8C54063	17	2010-02-04 14:44:47.357	2010-02-04
88597	986919413	001A9276C222	22	2010-02-04 14:45:47.357	2010-02-04
76573	1019192692	00E04C04DB03	23	2010-02-04 14:47:47.340	2010-02-04
--------------------------------------------------

需要根据GID,VaID,Mac分组，得到一段时间内Acount总数
类似下面的结果：下面是用10分钟分组，这个分组是可以设置的，可能用1小时，或者30分钟等。

-------------------------------------------------
  GID        VaID       Mac                   StartTime            EndTime           SumAcount
76573	2130706433	0013D3EF3CE1	2009-10-27 09:00:00.000	2009-10-27 09:10:00.000	  47
76573	2130706433	0013D3EF3CE2	2009-10-27 09:10:00.000	2009-10-27 09:20:00.000	  58
76573	2130706433	0013D3EF3CE3	2009-10-27 09:20:00.000	2009-10-27 09:30:00.000	  99
76573	2130706433	0013D3EF3CE9	2009-10-27 09:30:00.000	2009-10-27 09:40:00.000	  156
88067	1902854282	001A9276C222	2009-10-27 09:00:00.000	2009-10-27 09:10:00.000	  55
88067	986919413	00E04C04DB03	2009-10-27 09:10:00.000	2009-10-27 09:20:00.000	  42
88067	1019192692	001A9276C222	2009-10-27 09:20:00.000	2009-10-27 09:30:00.000	  85
88067	3736328770	0013D3EF3CE9	2009-10-27 09:30:00.000	2009-10-27 09:40:00.000	  12
--------------------------------------------------------------
请各位帮帮忙！

[jwdream2008]

Up!

[--小F--]

sum(case...when ...else...end)
where
时间 between .... and .....
group by
GID,VaID,Mac

[jakeweny]

怎么没人？自己顶一下，不会是嫌分少吧？
帮帮忙，不明白可以问我，我在网上看了，不过和我这个有些不同，我改了半天，不行！
请教了.............

[csuxp2008]

你想要的结果集中StartTime和EndTime与原表中字段ActiveTime什么关系？

[jakeweny]

其实和这个有些类似：http://topic.csdn.net/u/20100102/16/bf7811f6-b79b-4221-9ee9-42ae0b8e1c6c.html
大家帮忙！

[jakeweny]

[Quote=引用 4 楼 csuxp2008 的回复:]
你想要的结果集中StartTime和EndTime与原表中字段ActiveTime什么关系？
[/Quote]
StartTime和EndTime表示一时间段。起始结束位置。而结果就是要检查ActiveTime字段落在改时间段内的Acount数

[--小F--]

这个需要先生成一个时间列 再按照2楼的来

[jakeweny]

[Quote=引用 7 楼 fredrickhu 的回复:]
这个需要先生成一个时间列 再按照2楼的来
[/Quote]
我感觉跟那边的差不多，而且你当时也回答了，能帮忙写下么？

[csuxp2008]

其实这个功能建议用存储过程去实现，设置起始时间和时间间隔2个参数，代码实现方法可以参考2楼的

[nianran520]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([GID] int,[VaID] bigint,[Mac] varchar(12),[Acount] int,[ActiveTime] datetime,[SubDate] datetime)
insert [tb]
select 88067,1035395409,'90E6BAD84598',1,'2010-02-04 14:10:41.560','2010-02-04' union all
select 86036,3662412139,'00306731A792',3,'2010-02-04 14:15:45.543','2010-02-04' union all
select 86599,975963213,'00E04CF8AFAF',5,'2010-02-04 14:18:47.450','2010-02-04' union all
select 88625,3658907870,'0024210197CC',7,'2010-02-04 14:21:47.433','2010-02-04' union all
select 70025,1902854282,'00E04CC7B849',11,'2010-02-04 14:22:47.417','2010-02-04' union all
select 88861,3736328770,'001FC6A83210',12,'2010-02-04 14:33:47.403','2010-02-04' union all
select 89985,1035490878,'00E04D21914D',1,'2010-02-04 14:43:47.357','2010-02-04' union all
select 88983,3683763045,'0011D8C54063',17,'2010-02-04 14:44:47.357','2010-02-04' union all
select 88597,986919413,'001A9276C222',22,'2010-02-04 14:45:47.357','2010-02-04' union all
select 76573,1019192692,'00E04C04DB03',23,'2010-02-04 14:47:47.340','2010-02-04'

--select * from [tb]


declare @begin datetime,@end datetime,@interval int
select @begin = '2010-02-04 14:00:00',
@end='2010-02-04 14:40:00',
@interval=10

select 
t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime,
sum(t.[Acount]) as SumAcount
from [tb] t
join
(
select dateadd(minute,number*@interval,@begin) as StartTime,
dateadd(minute,(number+1)*@interval,@begin) as EndTime
from master..spt_values
where type = 'P' and
dateadd(minute,(number+1)*@interval,@begin) <= @end
) r on t.[ActiveTime] between r.StartTime and r.EndTime
group by t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime

[jakeweny]

TO：nianran520
谢谢你的回复
不过有点问题，如果增加一些数据

select 88861,3736328770,'001FC6A83210',14,'2010-02-04 14:33:47.403','2010-02-04' union all
select 88861,3736328770,'001FC6A83210',54,'2010-02-04 14:55:47.403','2010-02-04' union all
select 88861,3736328770,'001FC6A83210',7,'2010-02-04 14:42:47.403','2010-02-04' union all
select 88861,3736328770,'001FC6A83210',12,'2010-02-04 15:33:47.403','2010-02-04' union all

那么，这个并没有把所有时间段数据都取出来.

[jakeweny]

加分了，希望高手们能帮下忙！

[nianran520]

declare @begin datetime,@end datetime,@interval int
select @begin = '2010-02-04 14:00:00',
@end='2010-02-04 14:50:00',
@interval=10

select 
t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime,
sum(t.[Acount]) as SumAcount
from [tb] t
join
(
select 
h.[GID],
dateadd(minute,number*@interval,@begin) as StartTime,
dateadd(minute,(number+1)*@interval,@begin) as EndTime
from master..spt_values,(select distinct [GID] from [tb]) h
where type = 'P' and
dateadd(minute,(number+1)*@interval,@begin) <= @end
) r 
on t.[GID]=r.[GID] and
t.[ActiveTime] between r.StartTime and r.EndTime
group by t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime

[--小F--]

[Quote=引用 13 楼 nianran520 的回复:]
SQL codedeclare@begindatetime,@enddatetime,@intervalintselect@begin='2010-02-04 14:00:00',@end='2010-02-04 14:50:00',@interval=10select
t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime,sum(t.[Acount])?-
[/Quote]

强大

[jakeweny]

TO：nianran520
兄弟，依然不对呢
-----------------
如果加上我刚给的那4条数据，那4条数据，在10分钟为分组时间段的话，
应该有4条结果，但是只有2条！

[nianran520]

--再不行就
declare @begin datetime,@end datetime,@interval int
select @begin = '2010-02-04 14:00:00',
@end='2010-02-04 14:50:00',
@interval=10

select 
t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime,
sum(t.[Acount]) as SumAcount
from [tb] t
join
(
select 
h.[GID],h.[VaID],h.[Mac],
dateadd(minute,number*@interval,@begin) as StartTime,
dateadd(minute,(number+1)*@interval,@begin) as EndTime
from master..spt_values,(select distinct [GID],[VaID],[Mac] from [tb]) h
where type = 'P' and
dateadd(minute,(number+1)*@interval,@begin) <= @end
) r 
on t.[GID]=r.[GID] and t.[VaID]=r.[VaID] and t.[Mac]=r.[Mac] and
t.[ActiveTime] between r.StartTime and r.EndTime
group by t.[GID],t.[VaID],t.[Mac],
r.StartTime,r.EndTime

[nianran520]

--显示所有分组？
declare @begin datetime,@end datetime,@interval int
select @begin = '2010-02-04 14:00:00',
@end='2010-02-04 14:50:00',
@interval=10

select 
r.[GID],r.[VaID],r.[Mac],
r.StartTime,r.EndTime,
isnull(sum(t.[Acount]),0) as SumAcount
from [tb] t
right join
(
select 
h.[GID],h.[VaID],h.[Mac],
dateadd(minute,number*@interval,@begin) as StartTime,
dateadd(minute,(number+1)*@interval,@begin) as EndTime
from master..spt_values,(select distinct [GID],[VaID],[Mac] from [tb]) h
where type = 'P' and
dateadd(minute,(number+1)*@interval,@begin) <= @end
) r 
on t.[GID]=r.[GID] and t.[VaID]=r.[VaID] and t.[Mac]=r.[Mac] and
t.[ActiveTime] between r.StartTime and r.EndTime
group by r.[GID],r.[VaID],r.[Mac],
r.StartTime,r.EndTime

[jakeweny]

谢了，可以了。结分！