22,301
社区成员




-- data
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, zone int, road int, passtime datetime)
insert into #
select 1, 1, 1, '2012-12-18 00:00:01.000' union all
select 2, 2, 1, '2012-12-18 00:00:01.000' union all
select 3, 1, 1, '2012-12-18 00:00:00.000' union all
select 4, 2, 2, '2012-12-18 00:00:01.000' union all
select 5, 2, 1, '2012-12-18 00:00:03.000' union all
select 6, 1, 2, '2012-12-18 00:00:03.000' union all
select 7, 1, 2, '2012-12-18 00:00:06.000' union all
select 8, 2, 2, '2012-12-18 00:00:06.000' union all
select 9, 1, 2, '2012-12-18 00:00:05.000' union all
select 10, 2, 1, '2012-12-18 00:00:07.000' union all
select 11, 1, 2, '2012-12-18 00:00:08.000' union all
select 12, 2, 1, '2012-12-18 00:00:09.000' union all
select 13, 2, 2, '2012-12-18 00:00:10.000' union all
select 14, 1, 2, '2012-12-18 00:00:12.000' union all
select 15, 1, 2, '2012-12-18 00:00:11.000' union all
select 16, 2, 1, '2012-12-18 00:00:15.000' union all
select 17, 2, 3, '2012-12-18 00:00:14.000' union all
select 18, 1, 2, '2012-12-18 00:00:17.000' union all
select 19, 2, 1, '2012-12-18 00:00:16.000' union all
select 20, 2, 1, '2012-12-18 00:00:19.000' union all
select 21, 1, 2, '2012-12-18 00:00:20.000' union all
select 22, 2, 4, '2012-12-18 00:00:21.000' union all
select 23, 2, 2, '2012-12-18 00:00:22.000' union all
select 24, 1, 2, '2012-12-18 00:00:23.000' union all
select 25, 1, 1, '2012-12-18 00:00:24.000' union all
select 26, 1, 2, '2012-12-18 00:00:26.000' union all
select 27, 2, 1, '2012-12-18 00:00:25.000'
-- query
;with t as
(
select rn=row_number()over(partition by zone,road order by passtime), * from #
)
select a.zone, a.road, convert(float,count(b.id))/count(a.id) from t a left join t b
on a.zone=b.zone and a.road=b.road and a.rn=b.rn-1 and datediff(s,a.passtime,b.passtime)<3
group by a.zone, a.road order by 1, 2
/*
zone road
----------- ----------- ----------------------
1 1 0.333333333333333
1 2 0.4
2 1 0.375
2 2 0
2 3 0
2 4 0
警告: 聚合或其他 SET 操作消除了 Null 值。
*/