急咨询一个分组查询有思路 但SQL语句不熟 比较急 希望大家帮忙

Question

一个表Table  字段如下 
id int,        --记录ID
zone int,      --路段编号
road int,      --车道编号
passtime datetime  --车辆经过时间现在需分组统计 统计各个路段的各个车道的跟车比  
跟车定义：  同一路段的同一车道 相邻通过车辆的时间差小于3秒 则判断为跟车
如zone = 1 road = 1 的记录  
passtime  分别为   
2  5  20  22  50   
相邻小于5秒的有 2次 则 2/4 跟车比为 50% 网上有相邻记录的时间差 通过连接表实现  但是分组后同一分组的id不一定连续了  
id    zone    road      passtime 
1 1 1 2012-12-18 00:00:01.000
2 2 1 2012-12-18 00:00:01.000
3 1 1 2012-12-18 00:00:00.000
4 2 2 2012-12-18 00:00:01.000
5 2 1 2012-12-18 00:00:03.000
6 1 2 2012-12-18 00:00:03.000
7 1 2 2012-12-18 00:00:06.000
8 2 2 2012-12-18 00:00:06.000
9 1 2 2012-12-18 00:00:05.000
10 2 1 2012-12-18 00:00:07.000
11 1 2 2012-12-18 00:00:08.000
12 2 1 2012-12-18 00:00:09.000
13 2 2 2012-12-18 00:00:10.000
14 1 2 2012-12-18 00:00:12.000
15 1 2 2012-12-18 00:00:11.000
16 2 1 2012-12-18 00:00:15.000
17 2 3 2012-12-18 00:00:14.000
18 1 2 2012-12-18 00:00:17.000
19 2 1 2012-12-18 00:00:16.000
20 2 1 2012-12-18 00:00:19.000
21 1 2 2012-12-18 00:00:20.000
22 2 4 2012-12-18 00:00:21.000
23 2 2 2012-12-18 00:00:22.000
24 1 2 2012-12-18 00:00:23.000
25 1 1 2012-12-18 00:00:24.000
26 1 2 2012-12-18 00:00:26.000
27 2 1 2012-12-18 00:00:25.000

Answer 1

-- 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 值。
*/

Answer 2

left join 不合理，从搂主描述应该是inner joindatediff(s,a.passtime,b.passtime)<3 放在连接条件错误

Answer 3

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
,case when SUM(1)= 1 then 0 else  SUM(case when datediff(s,a.passtime,b.passtime)<3 then 1.0 else 0.0 end)/(SUM(1.0)-1) end
from t a left join t b on a.zone=b.zone and a.road=b.road and a.rn=b.rn-1 
group by a.zone, a.road 
order by 1, 2 --结果
zone road (无列名)
1 1 0.500000
1 2 0.444444
2 1 0.428571
2 2 0.000000
2 3 0.000000
2 4 0.000000

Answer 4

<3是否应该改成<=3楼主自己考虑修改

Answer 5

;with t as(     select rn=row_number()over(partition by zone,road order by passtime), * from # )  
这语句是使分组后  各个分组的记录rn 都递增吗
比如
分组1
rn  id  zone road  passtime 
1    1
2    4
3    6分组2
rn  id  zone road  passtime 
1    2
2    3
3    5