求一分段提取数据的sql写法

[bguest]

如下面的数据所示，eg代表状态，有无什么办法把每段eg为1的数据抽取出来？
特别声明的一点就是，数据量非常非常大，用group by估计会玩死数据库了，高手帮帮忙
DTime Spd Eg
2008-01-01 01:55:27.000 0 0
2008-01-01 02:00:26.000 0 0
2008-01-01 02:00:43.000 23 1
2008-01-01 02:05:42.000 25 1
2008-01-01 02:07:47.000 26 1
2008-01-01 02:12:46.000 24 1
2008-01-01 02:14:51.000 0 0
2008-01-01 02:19:50.000 0 0
2008-01-01 02:21:27.000 0 0
2008-01-01 02:56:35.000 20 1
2008-01-01 02:58:48.000 23 1
2008-01-01 03:03:47.000 23 1
2008-01-01 03:07:25.000 25 1
2008-01-01 03:12:24.000 0 0
2008-01-01 03:12:39.000 0 0
2008-01-01 03:17:38.000 0 0

显示的结果如果能如下就最好不过了，就是把eg=1的某段数据的第一条和最后一条的时间取出来，并计算spd的平均值 。做不到这个那就随便写吧，能给点分段提取的思路就行，嘿嘿
StartDTime EndDTime Spd_Avg
2008-01-01 02:00:43.000 2008-01-01 02:12:46.000 24.5
2008-01-01 02:56:35.000 2008-01-01 03:07:25.000 23

[fuda_1985]

mark

[ojuju10]

你的数据库表设计有问题，需要进行表分区或者分表

[补课]

如果你的时间段内的记录数都是相等的,可以试试这个. 不过这个局限性,给你提供参考吧.

------------- 添加 id 取模 , 但是这种方法只对 每一个时间段的记录都是相等的.

declare @t table(DTime datetime,Spd numeric(4,1),Eg int) 
insert into @t values('2008-01-01 01:55:27.000',0 ,0 )
insert into @t values('2008-01-01 02:00:26.000',0 ,0 )
insert into @t values('2008-01-01 02:00:43.000',23, 1) 
insert into @t values('2008-01-01 02:05:42.000',25, 1) 
insert into @t values('2008-01-01 02:07:47.000',26, 1) 
insert into @t values('2008-01-01 02:12:46.000',24, 1) 
insert into @t values('2008-01-01 02:14:51.000',0 ,0 )
insert into @t values('2008-01-01 02:19:50.000',0 ,0 )
insert into @t values('2008-01-01 02:21:27.000',0 ,0 )
insert into @t values('2008-01-01 02:56:35.000',20, 1) 
insert into @t values('2008-01-01 02:58:48.000',23, 1) 
insert into @t values('2008-01-01 03:03:47.000',23, 1) 
insert into @t values('2008-01-01 03:07:25.000',25, 1) 
insert into @t values('2008-01-01 03:12:24.000',0 ,0 )
insert into @t values('2008-01-01 03:12:39.000',0 ,0 )
insert into @t values('2008-01-01 03:17:38.000',0 ,0 )

select * from @t

select * into ##temp1 from @t where Eg=1  order by DTime 

select (select count(1) from ##temp as b where a.Dtime>=b.Dtime) as id ,* into ##temp2 from ##temp as a

select * from ##temp2


select t1.DTime as SDTime,t2.DTime as EDTime , (t1.Spd+t2.Spd)/2 as Spd_Avg 
from 
(select id,DTime,Spd from ##temp2 where id%4=1) as t1 left join
(select id, DTime ,spd from ##temp2 where id%4=0) as t2 on t1.id=t2.id-3

drop table ##temp1
drop table ##temp2

结果


DTime Spd Eg
------------------------------------------------------ ------ -----------
2008-01-01 01:55:27.000 .0 0
2008-01-01 02:00:26.000 .0 0
2008-01-01 02:00:43.000 23.0 1
2008-01-01 02:05:42.000 25.0 1
2008-01-01 02:07:47.000 26.0 1
2008-01-01 02:12:46.000 24.0 1
2008-01-01 02:14:51.000 .0 0
2008-01-01 02:19:50.000 .0 0
2008-01-01 02:21:27.000 .0 0
2008-01-01 02:56:35.000 20.0 1
2008-01-01 02:58:48.000 23.0 1
2008-01-01 03:03:47.000 23.0 1
2008-01-01 03:07:25.000 25.0 1
2008-01-01 03:12:24.000 .0 0
2008-01-01 03:12:39.000 .0 0
2008-01-01 03:17:38.000 .0 0

（所影响的行数为 16 行）


（所影响的行数为 8 行）


（所影响的行数为 8 行）

id DTime Spd Eg
----------- ------------------------------------------------------ ------ -----------
1 2008-01-01 02:00:43.000 23.0 1
2 2008-01-01 02:05:42.000 25.0 1
3 2008-01-01 02:07:47.000 26.0 1
4 2008-01-01 02:12:46.000 24.0 1
5 2008-01-01 02:56:35.000 20.0 1
6 2008-01-01 02:58:48.000 23.0 1
7 2008-01-01 03:03:47.000 23.0 1
8 2008-01-01 03:07:25.000 25.0 1

（所影响的行数为 8 行）

SDTime EDTime Spd_Avg
------------------------------------------------------ ------------------------------------------------------ ------------
2008-01-01 02:00:43.000 2008-01-01 02:12:46.000 23.500000
2008-01-01 02:56:35.000 2008-01-01 03:07:25.000 22.500000

（所影响的行数为 2 行）



还有分区表或者分表 是什么东东?
有没有实例看看.让我也知道什么是分区表或分表

[子陌红尘]

declare @t table(DTime datetime,Spd numeric(4,1),Eg int) 
insert into @t values('2008-01-01 01:55:27.000',0 ,0 )
insert into @t values('2008-01-01 02:00:26.000',0 ,0 )
insert into @t values('2008-01-01 02:00:43.000',23, 1) 
insert into @t values('2008-01-01 02:05:42.000',25, 1) 
insert into @t values('2008-01-01 02:07:47.000',26, 1) 
insert into @t values('2008-01-01 02:12:46.000',24, 1) 
insert into @t values('2008-01-01 02:14:51.000',0 ,0 )
insert into @t values('2008-01-01 02:19:50.000',0 ,0 )
insert into @t values('2008-01-01 02:21:27.000',0 ,0 )
insert into @t values('2008-01-01 02:56:35.000',20, 1) 
insert into @t values('2008-01-01 02:58:48.000',23, 1) 
insert into @t values('2008-01-01 03:03:47.000',23, 1) 
insert into @t values('2008-01-01 03:07:25.000',25, 1) 
insert into @t values('2008-01-01 03:12:24.000',0 ,0 )
insert into @t values('2008-01-01 03:12:39.000',0 ,0 )
insert into @t values('2008-01-01 03:17:38.000',0 ,0 ) 

select
    d.Time1 StartDTime,
    d.Time2 EndDTime  ,
    avg(c.Spd) as Spd_Avg
from
    @t c,
    (select
         a.DTime Time1,min(b.DTime) Time2
     from
         (select m.* from @t m where m.Eg=1 and (select top 1 Eg from @t where DTime<m.DTime order by DTime desc)=0) a,
         (select m.* from @t m where m.Eg=1 and (select top 1 Eg from @t where DTime>m.DTime order by DTime asc)=0)  b
     where
         a.DTIme<b.DTime
     group by 
         a.DTime) d
where
    c.DTime between d.Time1 and d.Time2
group by
    d.Time1,d.Time2

/*
StartDTime                     EndDTime                       Spd_Avg           
------------------------------ ------------------------------ ----------------- 
2008-01-01 02:00:43.000        2008-01-01 02:12:46.000        24.500000
2008-01-01 02:56:35.000        2008-01-01 03:07:25.000        22.750000
*/

[-狙击手-]

都这是么有规律 吗？
43434343。。。。。？

[yyyyzzzz_2002]

我想你应该给一个分段标准吧，以什么样的数据段来分，不然我们也只是去拼结果，这样不具有广泛适用性．

[bguest]

感谢楼上三位的回复。其实这是gps中返回的数据，eg代表引擎，1是活动数据，0是停止数据，现在想把每段活动的数据抽取出来做统计。

感谢2楼的写法，表中大概有几亿条数据，用到group by就死在那里了，呵呵。
3，4楼，我是以Eg为分段字段的。这个字只有1或者0。

想寻找下有无高效的写法，如果是用程序一条一条的对比下去，实在太慢了..。谢谢

[子陌红尘]

把表中的数据按时间段划分为多个片断，然后分区间用游标遍历。

[yyyyzzzz_2002]

1.若是sql server 2005，建议直接建立分区表，分区标准自己定义
２．sql server2000的话，建立分区视图，将大的数据拆分开

[sp4]

这样庞大的数据，应该做中间的数据处理，然后再做数据获取。

如果直接从大数据集里直接完成这样的操作，是很糟糕

[flairsky]

几亿条数据？
既然你eg为0的这里没用，那就分表放阿

group by 当然费时间了，用〉0过滤行不？

[bguest]

忘记结贴了...sorry，事实上果然没有什么合适的方法。