每隔5分钟统计一次记录的应用实例

一张电话报表（id,time,telephone） time:表示顾客打入时记录的时间

实例如下：

1 2006-06-11 05:52:12 1360065460758

2 2006-06-11 06:09:05 1380138017292

3 2006-06-11 06:25:13 97363849310

4 2006-06-11 06:26:08 61763849310

5 2006-06-11 06:34:59 60054190811

6 2006-06-11 06:44:04 1397305138553

7 2006-06-11 06:51:30 60063849310  

8 2006-06-11 06:53:14 92713061620671

9 2006-06-11 06:53:22 11713061620671

10 2006-06-11 06:58:34 32054196057

11 2006-06-11 07:04:42 92752581156

统计一下每隔5分钟打进电话多少个？

怎么写SQL语句啊

注意 我可能这样的数据有几万条

很明显,比如像鱼是按5分钟为步长生成了时间段,然后按时间段连接.保证了每个5分钟都被统计.

--> 生成测试数据表:tb

IF 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:11',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(N,(SELECT MIN(TIME) FROM TB),TIME)/5

    FROM TB

    ) AS T

GROUP BY N

--此查询精确到秒:

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

-----------

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

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))

--时间段>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))

drop table tb