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

bguest 2008-04-01 10:56:29
如下面的数据所示,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
...全文
207 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
bguest 2008-04-22
忘记结贴了...sorry,事实上果然没有什么合适的方法。
回复
flairsky 2008-04-02
几亿条数据?
既然你eg为0的这里没用,那就分表放阿

group by 当然费时间了,用〉0过滤行不?
回复
sp4 2008-04-02
这样庞大的数据,应该做中间的数据处理,然后再做数据获取。

如果直接从大数据集里直接完成这样的操作,是很糟糕
回复
yyyyzzzz_2002 2008-04-01
1.若是sql server 2005,建议直接建立分区表,分区标准自己定义
2.sql server2000的话,建立分区视图,将大的数据拆分开
回复
子陌红尘 2008-04-01
把表中的数据按时间段划分为多个片断,然后分区间用游标遍历。
回复
bguest 2008-04-01
感谢楼上三位的回复。其实这是gps中返回的数据,eg代表引擎,1是活动数据,0是停止数据,现在想把每段活动的数据抽取出来做统计。

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

想寻找下有无高效的写法,如果是用程序一条一条的对比下去,实在太慢了..。谢谢
回复
yyyyzzzz_2002 2008-04-01
我想你应该给一个分段标准吧,以什么样的数据段来分,不然我们也只是去拼结果,这样不具有广泛适用性.
回复
-狙击手- 2008-04-01
都这是么有规律 吗?
43434343。。。。。?
回复
子陌红尘 2008-04-01
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
*/
回复
补课 2008-04-01
如果你的时间段内的记录数都是相等的,可以试试这个. 不过这个局限性,给你提供参考吧.



------------- 添加 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 行)



还有分区表或者分表 是什么东东?
有没有实例看看.让我也知道什么是分区表或分表
回复
ojuju10 2008-04-01
你的数据库表设计有问题,需要进行表分区或者分表
回复
fuda_1985 2008-04-01
mark
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-01 10:56
社区公告
暂无公告