22,209
社区成员
发帖
与我相关
我的任务
分享求助 按时间段统计
ID EventDT
1 2011-09-16 12:51:01
2 2011-09-16 12:53:55
3 2011-09-16 13:02:01
4 2011-09-16 13:09:35
统计间隔5分钟
查询时段 2011-09-16 12:45:00 - 2011-09-16 13:20:00
想要的结果：要体现出时间段内没查询到结果的次数为0
时间段 次数
2011-09-16 12:50:00 0
2011-09-16 12:55:00 2
2011-09-16 13:00:00 1
2011-09-16 13:10:00 1
2011-09-16 13:15:00 0
2011-09-16 13:20:00 0
1 2011-09-16 12:51:01
2 2011-09-16 12:53:55
3 2011-09-16 13:02:01
4 2011-09-16 13:09:35
统计间隔5分钟
查询时段 2011-09-16 12:45:00 - 2011-09-16 13:20:00
想要的结果：要体现出时间段内没查询到结果的次数为0
时间段 次数
2011-09-16 12:50:00 0
2011-09-16 12:55:00 2
2011-09-16 13:00:00 1
2011-09-16 13:10:00 1
2011-09-16 13:15:00 0
2011-09-16 13:20:00 0
...全文
请发表友善的回复…
发表回复
dawugui 2012-03-18
- 打赏
- 举报
[Quote=引用 5 楼 joyznm 的回复:]
如果要跨天的查询呢？
[/Quote]
如果要跨天的查询呢？
[/Quote]
create table tb(ID int,EventDT datetime)
insert into tb values(1 ,'2011-09-16 12:51:01')
insert into tb values(2 ,'2011-09-16 12:53:55')
insert into tb values(3 ,'2011-09-16 13:02:01')
insert into tb values(4 ,'2011-09-16 13:09:35')
go
declare @sdate datetime
declare @edate datetime
set @sdate = '2011-09-16 12:45:00'
set @edate = '2011-09-17 13:20:00'
--利用两个时间差生成临时表
declare @i as int
set @i = datediff(mi,@sdate,@edate) / 5 + 1
declare @sql as varchar(100)
set @sql = 'select top ' + ltrim(@i) + ' id = IDENTITY(int, 0, 1) into tmp FROM syscolumns a, syscolumns b '
exec(@sql)
select m.dt 时间段, isnull((select count(1) from tb where tb.EventDT between m.dt and m.dt1),0) 次数
from
(
select
dateadd(mi,id*5,@sdate) dt , dateadd(ss,-1,dateadd(mi,(id+1)*5,@sdate)) dt1
from
tmp a
where
dateadd(mi,id*5,@sdate)<=@edate
) m
drop table tb , tmp
/*
时间段 次数
------------------------------------------------------ -----------
2011-09-16 12:45:00.000 0
2011-09-16 12:50:00.000 2
2011-09-16 12:55:00.000 0
2011-09-16 13:00:00.000 1
2011-09-16 13:05:00.000 1
2011-09-16 13:10:00.000 0
2011-09-16 13:15:00.000 0
2011-09-16 13:20:00.000 0
2011-09-16 13:25:00.000 0
2011-09-16 13:30:00.000 0
2011-09-16 13:35:00.000 0
2011-09-16 13:40:00.000 0
2011-09-16 13:45:00.000 0
2011-09-16 13:50:00.000 0
2011-09-16 13:55:00.000 0
2011-09-16 14:00:00.000 0
2011-09-16 14:05:00.000 0
2011-09-16 14:10:00.000 0
2011-09-16 14:15:00.000 0
2011-09-16 14:20:00.000 0
2011-09-16 14:25:00.000 0
2011-09-16 14:30:00.000 0
2011-09-16 14:35:00.000 0
2011-09-16 14:40:00.000 0
2011-09-16 14:45:00.000 0
2011-09-16 14:50:00.000 0
2011-09-16 14:55:00.000 0
2011-09-16 15:00:00.000 0
2011-09-16 15:05:00.000 0
2011-09-16 15:10:00.000 0
2011-09-16 15:15:00.000 0
2011-09-16 15:20:00.000 0
2011-09-16 15:25:00.000 0
2011-09-16 15:30:00.000 0
2011-09-16 15:35:00.000 0
2011-09-16 15:40:00.000 0
2011-09-16 15:45:00.000 0
2011-09-16 15:50:00.000 0
2011-09-16 15:55:00.000 0
2011-09-16 16:00:00.000 0
2011-09-16 16:05:00.000 0
2011-09-16 16:10:00.000 0
2011-09-16 16:15:00.000 0
2011-09-16 16:20:00.000 0
2011-09-16 16:25:00.000 0
2011-09-16 16:30:00.000 0
2011-09-16 16:35:00.000 0
2011-09-16 16:40:00.000 0
2011-09-16 16:45:00.000 0
2011-09-16 16:50:00.000 0
2011-09-16 16:55:00.000 0
2011-09-16 17:00:00.000 0
2011-09-16 17:05:00.000 0
2011-09-16 17:10:00.000 0
2011-09-16 17:15:00.000 0
2011-09-16 17:20:00.000 0
2011-09-16 17:25:00.000 0
2011-09-16 17:30:00.000 0
2011-09-16 17:35:00.000 0
2011-09-16 17:40:00.000 0
2011-09-16 17:45:00.000 0
2011-09-16 17:50:00.000 0
2011-09-16 17:55:00.000 0
2011-09-16 18:00:00.000 0
2011-09-16 18:05:00.000 0
2011-09-16 18:10:00.000 0
2011-09-16 18:15:00.000 0
2011-09-16 18:20:00.000 0
2011-09-16 18:25:00.000 0
2011-09-16 18:30:00.000 0
2011-09-16 18:35:00.000 0
2011-09-16 18:40:00.000 0
2011-09-16 18:45:00.000 0
2011-09-16 18:50:00.000 0
2011-09-16 18:55:00.000 0
2011-09-16 19:00:00.000 0
2011-09-16 19:05:00.000 0
2011-09-16 19:10:00.000 0
2011-09-16 19:15:00.000 0
2011-09-16 19:20:00.000 0
2011-09-16 19:25:00.000 0
2011-09-16 19:30:00.000 0
2011-09-16 19:35:00.000 0
2011-09-16 19:40:00.000 0
2011-09-16 19:45:00.000 0
2011-09-16 19:50:00.000 0
2011-09-16 19:55:00.000 0
2011-09-16 20:00:00.000 0
2011-09-16 20:05:00.000 0
2011-09-16 20:10:00.000 0
2011-09-16 20:15:00.000 0
2011-09-16 20:20:00.000 0
2011-09-16 20:25:00.000 0
2011-09-16 20:30:00.000 0
2011-09-16 20:35:00.000 0
2011-09-16 20:40:00.000 0
2011-09-16 20:45:00.000 0
2011-09-16 20:50:00.000 0
2011-09-16 20:55:00.000 0
2011-09-16 21:00:00.000 0
2011-09-16 21:05:00.000 0
2011-09-16 21:10:00.000 0
2011-09-16 21:15:00.000 0
2011-09-16 21:20:00.000 0
2011-09-16 21:25:00.000 0
2011-09-16 21:30:00.000 0
2011-09-16 21:35:00.000 0
2011-09-16 21:40:00.000 0
2011-09-16 21:45:00.000 0
2011-09-16 21:50:00.000 0
2011-09-16 21:55:00.000 0
2011-09-16 22:00:00.000 0
2011-09-16 22:05:00.000 0
2011-09-16 22:10:00.000 0
2011-09-16 22:15:00.000 0
2011-09-16 22:20:00.000 0
2011-09-16 22:25:00.000 0
2011-09-16 22:30:00.000 0
2011-09-16 22:35:00.000 0
2011-09-16 22:40:00.000 0
2011-09-16 22:45:00.000 0
2011-09-16 22:50:00.000 0
2011-09-16 22:55:00.000 0
2011-09-16 23:00:00.000 0
2011-09-16 23:05:00.000 0
2011-09-16 23:10:00.000 0
2011-09-16 23:15:00.000 0
2011-09-16 23:20:00.000 0
2011-09-16 23:25:00.000 0
2011-09-16 23:30:00.000 0
...
2011-09-17 13:10:00.000 0
2011-09-17 13:15:00.000 0
2011-09-17 13:20:00.000 0
（所影响的行数为 296 行）
*/
开着拖拉机泡妞 2012-03-18
- 打赏
- 举报
[Quote=引用 3 楼 travylee 的回复:]
时间段 次数
2011-09-16 12:50:00 0
2011-09-16 12:55:00 2
2011-09-16 13:00:00 1
2011-09-16 13:10:00 1
2011-09-16 13:15:00 0
2011-09-16 13:20:00 0
楼主的数据不是按照5分钟递增的，有误
SQL code
--利用递归实现每隔五分钟的时间段
go……
[/Quote]
把dateadd(MI,5,a.日期)改成dateadd(dd,1,a.日期)
时间段 次数
2011-09-16 12:50:00 0
2011-09-16 12:55:00 2
2011-09-16 13:00:00 1
2011-09-16 13:10:00 1
2011-09-16 13:15:00 0
2011-09-16 13:20:00 0
楼主的数据不是按照5分钟递增的，有误
SQL code
--利用递归实现每隔五分钟的时间段
go……
[/Quote]
把dateadd(MI,5,a.日期)改成dateadd(dd,1,a.日期)
joyznm 2012-03-18
- 打赏
- 举报
如果要跨天的查询呢？[Quote=引用 4 楼 dawugui 的回复:]
SQL code
create table tb(ID int,EventDT datetime)
insert into tb values(1 ,'2011-09-16 12:51:01')
insert into tb values(2 ,'2011-09-16 12:53:55')
insert into tb values(3 ,'2011-09-16 13:02:01')
inse……
[/Quote]
SQL code
create table tb(ID int,EventDT datetime)
insert into tb values(1 ,'2011-09-16 12:51:01')
insert into tb values(2 ,'2011-09-16 12:53:55')
insert into tb values(3 ,'2011-09-16 13:02:01')
inse……
[/Quote]
dawugui 2012-03-18
- 打赏
- 举报
create table tb(ID int,EventDT datetime)
insert into tb values(1 ,'2011-09-16 12:51:01')
insert into tb values(2 ,'2011-09-16 12:53:55')
insert into tb values(3 ,'2011-09-16 13:02:01')
insert into tb values(4 ,'2011-09-16 13:09:35')
go
--sql 2000
declare @sdate datetime
declare @edate datetime
set @sdate = '2011-09-16 12:45:00'
set @edate = '2011-09-16 13:20:00'
select m.dt 时间段, isnull((select count(1) from tb where tb.EventDT between m.dt and m.dt1),0) 次数
from
(
select
dateadd(mi,num*5,@sdate) dt , dateadd(ss,-1,dateadd(mi,(num+1)*5,@sdate)) dt1
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(mi,num*5,@sdate)<=@edate
) m
drop table tb
/*
时间段 次数
------------------------------------------------------ -----------
2011-09-16 12:45:00.000 0
2011-09-16 12:50:00.000 2
2011-09-16 12:55:00.000 0
2011-09-16 13:00:00.000 1
2011-09-16 13:05:00.000 1
2011-09-16 13:10:00.000 0
2011-09-16 13:15:00.000 0
2011-09-16 13:20:00.000 0
（所影响的行数为 8 行）
*/开着拖拉机泡妞 2012-03-18
- 打赏
- 举报
时间段 次数
2011-09-16 12:50:00 0
2011-09-16 12:55:00 2
2011-09-16 13:00:00 1
2011-09-16 13:10:00 1
2011-09-16 13:15:00 0
2011-09-16 13:20:00 0
楼主的数据不是按照5分钟递增的，有误
2011-09-16 12:50:00 0
2011-09-16 12:55:00 2
2011-09-16 13:00:00 1
2011-09-16 13:10:00 1
2011-09-16 13:15:00 0
2011-09-16 13:20:00 0
楼主的数据不是按照5分钟递增的，有误
--利用递归实现每隔五分钟的时间段
go
declare @date datetime
select @date=MAX(日期) from #TBT
;with t
as(
select * from #TBT
union all
select dateadd(MI,5,a.日期) from t a
where not exists(select * from #TBT b
where b.日期=DATEADD(MI,5,a.日期)
)
and a.日期<@date
),
s as(
select a.日期 m,b.日期 n from t a
inner join t b on a.日期=dateadd(mi,-5,b.日期)--利用自连接实现两字段的时间差
--order by a.日期
),
d as(
select * from s cross join tbl--利用交叉连接实现tbl表的数据与时间段的联系
)
select convert(varchar(19),n,120) as 时间段,
sum(case when [DATE] between m and n then 1 else 0 end)as 次数
from d group by n order by n
/*
时间段 次数
2011-09-16 12:50:00 0
2011-09-16 12:55:00 2
2011-09-16 13:00:00 0
2011-09-16 13:05:00 1
2011-09-16 13:10:00 1
2011-09-16 13:15:00 0
2011-09-16 13:20:00 0
*/
开着拖拉机泡妞 2012-03-18
- 打赏
- 举报
--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl](
[id] int,
[date] datetime
)
insert [tbl]
select 1,'2011-09-16 12:51:01' union all
select 2,'2011-09-16 12:53:55' union all
select 3,'2011-09-16 13:02:01' union all
select 4,'2011-09-16 13:09:35'
-->生成测试数据:
GO
IF OBJECT_ID('#TBT')IS NOT NULL
DROP TABLE #TBT
GO
CREATE TABLE #TBT(
日期 DATETIME
)
GO
INSERT #TBT
SELECT '2011-09-16 12:45:00' UNION ALL
SELECT '2011-09-16 13:20:00'
--利用递归实现输出三月份的所有日期：
go
declare @date datetime
select @date=MAX(日期) from #TBT
;with t
as(
select * from #TBT
union all
select dateadd(MI,5,a.日期) from t a
where not exists(select * from #TBT b
where b.日期=DATEADD(MI,5,a.日期)
)
and a.日期<@date
),
s as(
select a.日期 m,b.日期 n from t a
inner join t b on a.日期=dateadd(mi,-5,b.日期)
--order by a.日期
),
d as(
select * from s cross join tbl
)
select n, sum(case when [DATE] between m and n then 1 else 0 end)as num
from d group by n order by n
/*
n num
2011-09-16 12:50:00.000 0
2011-09-16 12:55:00.000 2
2011-09-16 13:00:00.000 0
2011-09-16 13:05:00.000 1
2011-09-16 13:10:00.000 1
2011-09-16 13:15:00.000 0
2011-09-16 13:20:00.000 0
*/
xs0573 2012-03-18
- 打赏
- 举报
use tempdb
if object_id ('s1') is not null drop table s1
create table s1(id int,eventDT datetime)
insert into s1 values(1, '2011-09-16 12:51:01')
insert into s1 values(2, '2011-09-16 12:53:55')
insert into s1 values(3, '2011-09-16 13:02:01')
insert into s1 values(4, '2011-09-16 13:09:35')
select * from s1
select c,count(c) from
(select dateadd(mi,-datepart(mi,eventdt)%5,dateadd(ss,-datepart(ss,eventdt),eventdt)) as c from s1 ) as s2
group by c
if object_id ('s1') is not null drop table s1
create table s1(id int,eventDT datetime)
insert into s1 values(1, '2011-09-16 12:51:01')
insert into s1 values(2, '2011-09-16 12:53:55')
insert into s1 values(3, '2011-09-16 13:02:01')
insert into s1 values(4, '2011-09-16 13:09:35')
select * from s1
select c,count(c) from
(select dateadd(mi,-datepart(mi,eventdt)%5,dateadd(ss,-datepart(ss,eventdt),eventdt)) as c from s1 ) as s2
group by c
