每隔一段时间统计一次

Question

select  * into #t  from
(
select 1 id, (cast ( '2006-06-11 05:52:12' as datetime)) dt, 1360065460758 as d
union select 2 , '2006-06-11 06:09:05', 1380138017292
union select 3 , '2006-06-11 06:25:13 ',97363849310
union select 4 , '2006-06-11 06:26:08 ',61763849310
union select 5 , '2006-06-11 06:34:59 ',60054190811
union select 6, '2006-06-11 06:44:04 ',1397305138553
union select 7, '2006-06-11 06:51:30 ',60063849310   
union select 8 , '2006-06-11 06:53:14 ',92713061620671
union select 9 , '2006-06-11 06:53:22 ',11713061620671
union select 10, '2006-06-11 06:58:34 ',32054196057
union select 11 , '2006-06-11 07:04:42 ',92752581156
) tselect '第'+convert(varchar(10),datediff(mi, t.dt,#t.dt)/5) +'个' as [5分钟续列] ,count(*) as 记录数 from #t,(select top 1 dt from #t order by dt) t
group by datediff(mi, t.dt,#t.dt)/55分钟续列          记录数         
-------------- ----------- 
第0个            1
第3个            1
第6个            2
第8个            1
第10个           1
第11个           1
第12个           2
第13个           1
第14个           1（所影响的行数为 9 行）

Answer 1

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[time] datetime,[phone] bigint)
insert [tb]
select 1,'2006-06-11 05:52:12',1360065460758 union all
select 2,'2006-06-11 06:09:05',1380138017292 union all
select 3,'2006-06-11 06:25:13',97363849310 union all
select 4,'2006-06-11 06:26:08',61763849310 union all
select 5,'2006-06-11 06:34:59',60054190811 union all
select 6,'2006-06-11 06:44:04',1397305138553 union all
select 7,'2006-06-11 06:51:30',60063849310 union all
select 8,'2006-06-11 06:53:14',92713061620671 union all
select 9,'2006-06-11 06:53:22',11713061620671 union all
select 10,'2006-06-11 06:58:34',32054196057 union all
select 11,'2006-06-11 07:04:42',92752581156--------------------------------查询开始------------------------------
declare @mintime datetime,@maxtime datetime
select  @mintime=convert(varchar(13),min([time]),120)+':00:00' from tb
select  @maxtime=convert(varchar(13),max([time]),120)+':59:59' from tb
select a.[time],count(b.[time])
from 
(
select convert(varchar(20),dateadd(mi,number*5,@mintime),120) as [time] 
from master..spt_values where type='p'
and convert(varchar(20),dateadd(mi,number*5,@mintime),120)<@maxtime
) a
left join 
tb b
on datediff(mi, a.[time], b.[time])/5=1
group by a.[time]
/*
time                 
-------------------- -----------
2006-06-11 05:00:00  0
2006-06-11 07:00:00  0
2006-06-11 07:45:00  0
2006-06-11 05:10:00  0
2006-06-11 07:50:00  0
2006-06-11 06:10:00  0
2006-06-11 05:30:00  0
2006-06-11 07:55:00  0
2006-06-11 06:55:00  1
2006-06-11 05:45:00  1
2006-06-11 06:40:00  0
2006-06-11 06:50:00  1
2006-06-11 07:05:00  0
2006-06-11 06:20:00  2
2006-06-11 07:20:00  0
2006-06-11 07:35:00  0
2006-06-11 05:40:00  0
2006-06-11 05:20:00  0
2006-06-11 07:30:00  0
2006-06-11 07:40:00  0
2006-06-11 05:55:00  0
2006-06-11 06:30:00  0
2006-06-11 05:25:00  0
2006-06-11 06:25:00  1
2006-06-11 05:35:00  0
2006-06-11 05:50:00  0
2006-06-11 06:00:00  1
2006-06-11 07:15:00  0
2006-06-11 06:15:00  0
2006-06-11 05:15:00  0
2006-06-11 06:45:00  3
2006-06-11 06:05:00  0
2006-06-11 07:25:00  0
2006-06-11 07:10:00  0
2006-06-11 05:05:00  0
2006-06-11 06:35:00  1
警告: 聚合或其他 SET 操作消除了空值。(36 行受影响)
*/

Answer 2

参考:一个按五分钟分段统计的例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

Answer 3

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[time] datetime,[phone] bigint)
insert [tb]
select 1,'2006-06-11 05:52:12',1360065460758 union all
select 2,'2006-06-11 06:09:05',1380138017292 union all
select 3,'2006-06-11 06:25:13',97363849310 union all
select 4,'2006-06-11 06:26:08',61763849310 union all
select 5,'2006-06-11 06:34:59',60054190811 union all
select 6,'2006-06-11 06:44:04',1397305138553 union all
select 7,'2006-06-11 06:51:30',60063849310 union all
select 8,'2006-06-11 06:53:14',92713061620671 union all
select 9,'2006-06-11 06:53:22',11713061620671 union all
select 10,'2006-06-11 06:58:34',32054196057 union all
select 11,'2006-06-11 07:04:42',92752581156--------------------------------查询开始------------------------------
declare @mintime datetime,@maxtime datetime
select  @mintime=convert(varchar(13),min([time]),120)+':00:00' from tb
select  @maxtime=convert(varchar(13),max([time]),120)+':59:59' from tb
select * from 
(
select 次数='第'+ltrim(number)+'个'  ,count(b.[time]) as 合计
from 
(
select convert(varchar(20),dateadd(mi,number*5,@mintime),120) as [time] ,number
from master..spt_values where type='p'
and convert(varchar(20),dateadd(mi,number*5,@mintime),120)<@maxtime
) a
left join 
tb b
on datediff(mi, a.[time], b.[time])/5=1
group by '第'+ltrim(number)+'个'
) a
order by cast(replace(replace(次数,'第',''),'个','') as int)
/*
time                 
次数               合计
---------------- -----------
第0个              0
第1个              0
第2个              0
第3个              0
第4个              0
第5个              0
第6个              0
第7个              0
第8个              0
第9个              1
第10个             0
第11个             0
第12个             1
第13个             0
第14个             0
第15个             0
第16个             2
第17个             1
第18个             0
第19个             1
第20个             0
第21个             3
第22个             1
第23个             1
第24个             0
第25个             0
第26个             0
第27个             0
第28个             0
第29个             0
第30个             0
第31个             0
第32个             0
第33个             0
第34个             0
第35个             0
警告: 聚合或其他 SET 操作消除了空值。(36 行受影响)
*/

Answer 4

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-17 22:01:29
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
    Jul  9 2008 14:43:34 
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[time] DATETIME,[telephone] NVARCHAR(20))
INSERT [tb]
SELECT 1,N'2006-06-11 05:52:12',1360065460758 UNION ALL
--新增记录
SELECT 12,N'2006-06-11 05:53:12',136006546058 UNION ALL
SELECT 13,N'2006-06-11 05:54:12',136006546758 UNION ALL
SELECT 14,N'2006-06-11 05:55:12',136006540758 UNION ALL
SELECT 15,N'2006-06-11 05:54:12',136006560758 UNION ALL
SELECT 16,N'2006-06-11 05:57:12',1360065460758 UNION ALL
-------
SELECT 2,N'2006-06-11 06:09:05',1380138017292 UNION ALL
SELECT 3,N'2006-06-11 06:25:13',97363849310 UNION ALL
SELECT 4,N'2006-06-11 06:26:08',61763849310 UNION ALL
SELECT 5,N'2006-06-11 06:34:59',60054190811 UNION ALL
SELECT 6,N'2006-06-11 06:44:04',1397305138553 UNION ALL
SELECT 7,N'2006-06-11 06:51:30',60063849310 UNION ALL
SELECT 8,N'2006-06-11 06:53:14',92713061620671 UNION ALL
SELECT 9,N'2006-06-11 06:53:22',11713061620671 UNION ALL
SELECT 10,N'2006-06-11 06:58:34',32054196057 UNION ALL
SELECT 11,N'2006-06-11 07:04:42',92752581156
GO
--SELECT * FROM [tb]-->SQL查询如下:
--此查询精确到秒
SELECT '第'+LTRIM(N+1)+'个5分钟' AS [5分钟系列],
    COUNT(1) AS 记录数
FROM (
    SELECT 
        N=DATEDIFF(SS,(SELECT MIN(TIME) FROM TB),TIME)/300 
    FROM TB
    ) AS T
GROUP BY N
/*
5分钟系列                 记录数
--------------------- -----------
第1个5分钟                5
第2个5分钟                1
第4个5分钟                1
第7个5分钟                2
第9个5分钟                1
第11个5分钟               1
第12个5分钟               1
第13个5分钟               2
第14个5分钟               1
第15个5分钟               1(10 行受影响)
*/