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

[wanabe]

一个表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

[Vidor]

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

[wanabe]

能解析一下吗 大半夜的 很感谢你的热心回复

[昵称被占用了]

left join 不合理，从搂主描述应该是inner join datediff(s,a.passtime,b.passtime)<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

[昵称被占用了]

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

[wanabe]

;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