一个分组配对的问题

[minajo21]

car_no viewlog_date_time msg_type
------ ------------------------------------
200 2002-06-23 12:08:07.000 207 --a
200 2002-06-23 12:17:37.000 208 --b
200 2002-06-23 12:34:30.000 207
200 2002-06-23 12:45:30.000 208
200 2002-06-23 12:50:23.000 207
200 2002-06-23 12:50:27.000 207 --c
200 2002-06-23 12:58:30.000 208 --d
200 2002-06-23 12:59:40.000 208
200 2002-06-16 16:25:31.000 207 --e
205 2002-06-16 16:33:34.000 208 --f
205 2002-06-16 16:44:12.000 207
205 2002-06-16 16:53:34.000 208
205 2002-06-16 17:14:10.000 207
205 2002-06-16 17:20:41.000 208

按照如下的要求，分组配对：
在 car_no 相同的条件下，临近的207和208算是一组，207在前，208在后。
比如ab算是一组，cd也是一组，而ef就不算是一组。

我想得到如下的结果：

car_no viewlog_date_time Date_Diff
------ ------------------------------------
200 2002-06-23 12:08:07.000 9
200 2002-06-23 12:34:30.000 11
200 2002-06-23 12:50:27.000 8
...

其中 viewlog_date_time 对应的是207的时间，Date_Diff 是这一组的时间差(mi)

[minajo21]

我现在用游标，速度慢，效率很低:

if @CarNo=@CarNo_P and @MsgType_P=207 and @MsgType=208
insert into ...
select @CarNo_P=@CarNo,@DateTime_P=@DateTime,@MsgType_P=@MsgType
fetch next from c_Load_DateDiff into @CarNo,@DateTime,@MsgType

(_P的变量 表示前一个)

请教更好的办法，多谢了！

[zjcxc]

难道相邻的条件是固定为207与208 ?
相邻是指 msg_type 的值相邻,还是记录存储顺序相邻?

[zjcxc]

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from tb a join tb b
on a.car_no=b.car_no
and a.msg_type+1=b.msg_type
and (
select count(*) from tb
where car_no=a.car_no
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from tb
where car_no=b.car_no
and viewlog_date_time<=b.viewlog_date_time
)

[zjcxc]

--测试

--测试数据
create table tb(car_no int,viewlog_date_time datetime,msg_type int)
insert tb select 200,'2002-06-23 12:08:07.000',207 --a
union all select 200,'2002-06-23 12:17:37.000',208 --b
union all select 200,'2002-06-23 12:34:30.000',207
union all select 200,'2002-06-23 12:45:30.000',208
union all select 200,'2002-06-23 12:50:23.000',207
union all select 200,'2002-06-23 12:50:27.000',207 --c
union all select 200,'2002-06-23 12:58:30.000',208 --d
union all select 200,'2002-06-23 12:59:40.000',208
union all select 200,'2002-06-16 16:25:31.000',207 --e
union all select 205,'2002-06-16 16:33:34.000',208 --f
union all select 205,'2002-06-16 16:44:12.000',207
union all select 205,'2002-06-16 16:53:34.000',208
union all select 205,'2002-06-16 17:14:10.000',207
union all select 205,'2002-06-16 17:20:41.000',208
go

--查询
select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from tb a join tb b
on a.car_no=b.car_no
and a.msg_type+1=b.msg_type
and (
select count(*) from tb
where car_no=a.car_no
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from tb
where car_no=b.car_no
and viewlog_date_time<=b.viewlog_date_time
)
go
--删除测试
drop table tb

/*--测试结果

car_no viewlog_date_time Date_Diff
----------- -------------------------- -----------
200 2002-06-23 12:08:07.000 9
200 2002-06-23 12:34:30.000 11
200 2002-06-23 12:50:27.000 8
205 2002-06-16 16:44:12.000 9
205 2002-06-16 17:14:10.000 6

（所影响的行数为 5 行）
--*/

[minajo21]

msg_type 的值都是 207，208， 我现在是按照 car_no,viewlog_date_time 升序排列，得到的第一个结果，然后再把临近的207和208算成是一组。

就是麻烦在这里了：（
不知道自己说清楚了没有...

[minajo21]

我谢了，我试试！

[zjcxc]

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from tb a join tb b
on a.car_no=b.car_no
and a.msg_type=207 and b.msg_type=208 --只有两种情形
and (
select count(*) from tb
where car_no=a.car_no
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from tb
where car_no=b.car_no
and viewlog_date_time<=b.viewlog_date_time
)

[minajo21]

我是这样写的：

select car_no,viewlog_date_time,msg_type into a# from viewlog_archive
where msg_type in (207,208)
and year(viewlog_date_time)= '2002'
order by car_no,viewlog_date_time


select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from a# a join a# b
on a.car_no=b.car_no
and a.msg_type+1=b.msg_type
and (
select count(*) from a#
where car_no=a.car_no
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from a#
where car_no=b.car_no
and viewlog_date_time<=b.viewlog_date_time
)

数据库里的记录太多，有280多万，我建了索引，做第一个 a#的查询，用了6秒钟，查出来7万多条数据在a#中。两个加在一块儿，运行了3分钟，还没出来结果，是不是哪里值得优化？

[zjcxc]

--数据太多了,分开用临时表处理:

select car_no,viewlog_date_time
into #t1 from viewlog_archive
where msg_type=207 and year(viewlog_date_time)= '2002'

select car_no,viewlog_date_time
into #t2 from viewlog_archive
where msg_type=208 and year(viewlog_date_time)= '2002'

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from #t1 a join #t2 b
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t1
where car_no=b.car_no
and viewlog_date_time<b.viewlog_date_time)

[zjcxc]

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from #t1 a join #t2 b
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t2 --上面这里改一下
where car_no=b.car_no
and viewlog_date_time<b.viewlog_date_time)

[minajo21]

测试了一下：

msg_type in (207,208) 的共有 731922 条数据，查询用时 4 秒

配对后的结果 有 363798 ，用时 4分20秒 ：（


而且，有个地方是不是有点儿问题？

select max(viewlog_date_time) from #t2 --上面这里改一下
where car_no=b.car_no
and viewlog_date_time<b.viewlog_date_time)

应该是 207 中最大的时间，和 208 中最小的时间，好像 cd 的这种情况。

200 2002-06-23 12:50:23.000 207
200 2002-06-23 12:50:27.000 207 --c
200 2002-06-23 12:58:30.000 208 --d
200 2002-06-23 12:59:40.000 208

[zjcxc]

--没考虑到,用一个临时表

select car_no,viewlog_date_time
into #t from viewlog_archive
where msg_type=207 or msg_type=208 and year(viewlog_date_time)= '2002'

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from #t a join #t b
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t
where car_no=b.car_no
and viewlog_date_time<b.viewlog_date_time)

[minajo21]

老大，不行啊，太慢了：（


我把整个存储过程都列出来了：

CREATE PROCEDURE [dbo].[sp_rpt_Quick_Meter_Summary_Report]
@Year int
AS

declare @char_year char(4)
set @char_year=convert(char(4),@Year)

select car_no,viewlog_date_time
into #t from viewlog_archive
where msg_type=207 or msg_type=208 and year(viewlog_date_time)= @char_year

select a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
into #r from #t a join #t b
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t
where car_no=b.car_no
and viewlog_date_time<b.viewlog_date_time)

select
sum(case month(Viewlog_Date_Time) when '1' then 1 else 0 end) as Jan,
sum(case month(Viewlog_Date_Time) when '2' then 1 else 0 end) as Feb,
sum(case month(Viewlog_Date_Time) when '3' then 1 else 0 end) as Mar,
sum(case month(Viewlog_Date_Time) when '4' then 1 else 0 end) as Apr,
sum(case month(Viewlog_Date_Time) when '5' then 1 else 0 end) as May,
sum(case month(Viewlog_Date_Time) when '6' then 1 else 0 end) as Jun,
sum(case month(Viewlog_Date_Time) when '7' then 1 else 0 end) as Jul,
sum(case month(Viewlog_Date_Time) when '8' then 1 else 0 end) as Aug,
sum(case month(Viewlog_Date_Time) when '9' then 1 else 0 end) as Sep,
sum(case month(Viewlog_Date_Time) when '10' then 1 else 0 end) as Oct,
sum(case month(Viewlog_Date_Time) when '11' then 1 else 0 end) as Nov,
sum(case month(Viewlog_Date_Time) when '12' then 1 else 0 end) as [Dec]

from #r

group by Viewlog_Date_Time
GO

[minajo21]

第一个临时表中有 731922条数据，是不是慢在这个地方啊 from #t a join #t b



运行超过10分钟了，还没出结果:( 欲哭啊~~~

[minajo21]

速度问题没搞定，全靠硬件顶着呢 :(