对datetime类型数据按时间间隔进行分组

[xja31415]

DB(数据库版本): MSSQL2008

同一个表中的一个字段是datetime类型的，视两个记录为同一组当且仅当两条记录时间间隔小于15分钟且id相同，该怎么样得到所有记录的组序号。

例如：表结构：
id time1
1 2010-09-20 10:20:20
1 2010-09-20 10:22:20
1 2010-09-20 10:40:20
1 2010-09-20 10:45:20
1 2010-09-20 10:50:20
2 2010-09-18 10:22:20
2 2010-09-18 10:28:20
2 2010-09-18 16:29:20
2 2010-09-18 16:59:20


期望得到的结果如下：
id time1 event_id
1 2010-09-20 10:20:20 1
1 2010-09-20 10:22:20 1
1 2010-09-20 10:40:20 2
1 2010-09-20 10:45:20 2
1 2010-09-20 10:50:20 2
2 2010-09-18 10:22:20 3
2 2010-09-18 10:28:20 3
2 2010-09-18 16:29:20 4
2 2010-09-18 16:59:20 5

我是新手，折腾了好久都没弄出来，希望各位前辈能够指点！

[gw6328]

--结果出来了，但是显示有点乱。

[gw6328]

declare @t table(
	id int
	,time1 datetime
)

insert into @t (id,time1) values
(1,'2010-09-20 10:20:20')
,(1,'2010-09-20 10:22:20')
,(1,'2010-09-20 10:40:20')
,(1,'2010-09-20 10:45:20')
,(1,'2010-09-20 10:50:20')
,(2,'2010-09-18 10:22:20')
,(2,'2010-09-18 10:28:20')
,(2,'2010-09-18 16:29:20')
,(2,'2010-09-18 16:59:20')

;with c1 as (
	select rn=ROW_NUMBER() over(order by GETDATE()),* from @T
), c2 as (
select isnull(DATEDIFF(mi,b.time1,a.time1),0) as no,a.* from c1 a left join c1 b on a.rn=b.rn+1
), c3 as (
select ISNULL(a.rn,0) as no1,ISNULL(b.rn,10000000) as no2 from 
(select *,rnx=ROW_NUMBER() over(order by id) from c2 where ABS(no)>=15) a 
full join (select *,rnx=ROW_NUMBER() over(order by id) from c2 where ABS(no)>=15) b on a.rnx=b.rnx-1
)
select a.id,a.time1,DENSE_RANK() over(order by no1) as event_id from c2 a join c3 b on a.rn>=b.no1 and a.rn<b.no2

/*
id          time1                   event_id
----------- ----------------------- --------------------
1           2010-09-20 10:20:20.000 1
1           2010-09-20 10:22:20.000 1
1           2010-09-20 10:40:20.000 2
1           2010-09-20 10:45:20.000 2
1           2010-09-20 10:50:20.000 2
2           2010-09-18 10:22:20.000 3
2           2010-09-18 10:28:20.000 3
2           2010-09-18 16:29:20.000 4
2           2010-09-18 16:59:20.000 5
*/

[hery2002]

DECLARE @t_TB TABLE ([id] NVARCHAR(5),[time1] DATETIME);
INSERT INTO @t_TB VALUES 
('1','2010-09-20 10:20:20'),
('1','2010-09-20 10:22:20'),
('1','2010-09-20 10:40:20'),
('1','2010-09-20 10:45:20'),
('1','2010-09-20 10:50:20'),
('2','2010-09-18 10:22:20'),
('2','2010-09-18 10:28:20'),
('2','2010-09-18 16:29:20'),
('2','2010-09-18 16:59:20');
 ;with mintime_cte
as
(
select id,min(time1) mintime from @t_TB group by id
)
, group_cte
as
(
SELECT   t.id, t.time1,
row_number() over(order by t.id) as rn,
row_number() over(partition by t.id,ceiling(iif(datediff(MINUTE,a.mintime,t.time1)/15.0<1,1,datediff(MINUTE,a.mintime,t.time1)/15.0)) order by t.id) as group_id
    FROM @t_TB t
left join mintime_cte a on a.id = t.id
)

-- 2008 --
select t.id,t.time1,
(select sum(g.group_id) from group_cte g  where g.rn<=t.rn and g.group_id =1) as c
from  group_cte  t
-- 2012 + --

 ;with mintime_cte
as
(
select id,min(time1) mintime from @t_TB group by id
)
, group_cte
as
(
SELECT   t.id, t.time1,
row_number() over(order by t.id) as rn,
row_number() over(partition by t.id,ceiling(iif(datediff(MINUTE,a.mintime,t.time1)/15.0<1,1,datediff(MINUTE,a.mintime,t.time1)/15.0)) order by t.id) as group_id
    FROM @t_TB t
left join mintime_cte a on a.id = t.id
)
select t.id,t.time1,
sum(iif(t.group_id=1,t.group_id,0))over(order by t.id ROWS UNBOUNDED PRECEDING) as c
from  group_cte  t
/*
id    time1                   c
----- ----------------------- --------------------
1     2010-09-20 10:20:20.000 1
1     2010-09-20 10:22:20.000 1
1     2010-09-20 10:40:20.000 2
1     2010-09-20 10:45:20.000 2
1     2010-09-20 10:50:20.000 2
2     2010-09-18 10:22:20.000 3
2     2010-09-18 10:28:20.000 3
2     2010-09-18 16:29:20.000 4
2     2010-09-18 16:59:20.000 5
*/