业务要求:两个数据库A和B, A为甲提供的业务数据库,B为自己的应用使用的数据库。现在是要使用A中的数据为自己的应用服务。A中业务表和B中的原有业务表结构不同,同时A中数据还需要处理才能满足我们的需要,而且数据量较大,为了减轻工作量,我们不同步数据到本地,我们只按照自己的表结构建立对方表的视图,此外要求本地应用程序不要修改。
表结构说明: A 业务表为四张季度表 B中只有一张业务表。
数据处理要求:过滤掉速度为0 且时间点间隔小于8秒的数据(间隔时间可根据业务数据情况作调整,先暂点为8秒)
方案一:
联合四张表建立一个视图
方案二:
分别对四张表建立视图,然后联合成一张视图。我先说明方案二:
-------1
单表视图如下:
create or replace view res_gpsfirq as
select t1.UNIID ID,
to_number(substr(t1.车机号, 3, 7)) VEHICLEID,
t1.时间 TIME,
to_number(t1.X) LONGTITUDE,
to_number(t1.Y) LATITUDE,
to_number(t1.速度) SPEED,
trunc(to_number(t1.方向)) DIRECTION
from firq@nxyj_gpslink t1
where t1.速度 <> '0'其他三张视图同上,名称分别为:res_gpssecq,res_gpsthq,res_gpsfthq---------2 联合视图如下:
create or replace view res_gpsstatus as
select t1.*, rownum rw
from res_gpsfirq t1
where t1.TIME >
((select s1.TIME from res_gpsfirq s1 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1
union all
select t2.*, rownum rw
from res_gpssecq t2
where t2.TIME >
((select s2.TIME from res_gpssecq s2 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1
union all
select t3.*, rownum rw
from res_gpsthq t3
where t3.TIME >
((select s3.TIME from res_gpsthq s3 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1
union all
select t4.*, rownum rw
from res_gpsfthq t4
where t4.TIME >
((select s4.TIME from res_gpsfthq s4 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1--------3
以下是自己业务应用中使用查询语句:[code]select TO_CHAR(T.TIME, 'HH24:mi:ss') POSTIME, LONGTITUDE, LATITUDE, speed
from RES_GPSSTATUS T
where T.VEHICLEID = 911174
AND T.TIME > to_date('2010-5-31 9','yyyy-mm-dd HH24')
AND T.TIME < to_date('2010-5-31 17', 'yyyy mm dd HH24')
ORDER BY T.TIME[/code]针对以上情况:我现在不确定两种方案中sql更为优化,效率更高,请各位大侠比较说明。此外sql处理方式是否合理或者有没更好的适合此业务应用的解决方法,还请提出讨论
表结构说明: A 业务表为四张季度表 B中只有一张业务表。
数据处理要求:过滤掉速度为0 且时间点间隔小于8秒的数据(间隔时间可根据业务数据情况作调整,先暂点为8秒)
方案一:
联合四张表建立一个视图
方案二:
分别对四张表建立视图,然后联合成一张视图。我先说明方案二:
-------1
单表视图如下:
create or replace view res_gpsfirq as
select t1.UNIID ID,
to_number(substr(t1.车机号, 3, 7)) VEHICLEID,
t1.时间 TIME,
to_number(t1.X) LONGTITUDE,
to_number(t1.Y) LATITUDE,
to_number(t1.速度) SPEED,
trunc(to_number(t1.方向)) DIRECTION
from firq@nxyj_gpslink t1
where t1.速度 <> '0'其他三张视图同上,名称分别为:res_gpssecq,res_gpsthq,res_gpsfthq---------2 联合视图如下:
create or replace view res_gpsstatus as
select t1.*, rownum rw
from res_gpsfirq t1
where t1.TIME >
((select s1.TIME from res_gpsfirq s1 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1
union all
select t2.*, rownum rw
from res_gpssecq t2
where t2.TIME >
((select s2.TIME from res_gpssecq s2 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1
union all
select t3.*, rownum rw
from res_gpsthq t3
where t3.TIME >
((select s3.TIME from res_gpsthq s3 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1
union all
select t4.*, rownum rw
from res_gpsfthq t4
where t4.TIME >
((select s4.TIME from res_gpsfthq s4 where rownum = 1) +
1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1--------3
以下是自己业务应用中使用查询语句:[code]select TO_CHAR(T.TIME, 'HH24:mi:ss') POSTIME, LONGTITUDE, LATITUDE, speed
from RES_GPSSTATUS T
where T.VEHICLEID = 911174
AND T.TIME > to_date('2010-5-31 9','yyyy-mm-dd HH24')
AND T.TIME < to_date('2010-5-31 17', 'yyyy mm dd HH24')
ORDER BY T.TIME[/code]针对以上情况:我现在不确定两种方案中sql更为优化,效率更高,请各位大侠比较说明。此外sql处理方式是否合理或者有没更好的适合此业务应用的解决方法,还请提出讨论
以下是自己业务应用中使用查询语句:
select TO_CHAR(T.TIME, 'HH24:mi:ss') POSTIME, LONGTITUDE, LATITUDE
from res_gpsstatus T
where T.VEHICLEID = 911174
AND T.TIME > to_date('2010-5-31 9', 'yyyy-mm-dd HH24')
AND T.TIME < to_date('2010-5-31 17', 'yyyy mm dd HH24')
ORDER BY T.TIME
问题2 下面的写法和联合查询2的写法 有什么不同? 返回的结果是一样的吗?
如果一样,效率上有什么区别?
create or replace view res_gpsstatus as
select t.*, rownum rw
from (select *
from res_gpsfirq
union all
select *
from res_gpssecq
union all
select *
from res_gpsthq
union all
select * from res_gpsfthq) t
where t.TIME >
((select s.TIME
from (select *
from res_gpsfirq
union all
select *
from res_gpssecq
union all
select *
from res_gpsthq
union all
select * from res_gpsfthq) s
where rownum = 1) + 1 / 24 / 60 / 60 * rownum * 8)
or rownum = 1;
下面的SQL在union all之后,取1条记录,没有意义
(select s.TIME
from (select *
from res_gpsfirq
union all
select *
from res_gpssecq
union all
select *
from res_gpsthq
union all
select * from res_gpsfthq) s
where rownum = 1)
from res_gpsstatus T
where T.VEHICLEID = 911174
AND T.TIME > to_date('2010-5-31 9', 'yyyy-mm-dd HH24')
AND T.TIME < to_date('2010-5-31 17', 'yyyy mm dd HH24')
ORDER BY T.TIME来说,确实存在问题,即它是已取全部记录的第一条作为间隔起点,不是以每个车辆编号的全部记录的第一条作为间隔起点,必然会漏掉很多数据。那样的话建立联合视图:
create or replace view res_gpsstatus as
select t1.*
from firq@nxyj_gpslink t1
where t1.速度 <> '0'
union all
select t2.*
from secq@nxyj_gpslink t2
where t2.速度 <> '0'
union all
select t3.*
from thq@nxyj_gpslink t3
where t3.速度 <> '0'
union all
select t4.*
from fthq@nxyj_gpslink t4
where t4.速度 <> '0'
业务sql就的作如下调整
select TO_CHAR(T.TIME, 'HH24:mi:ss') POSTIME,
LONGTITUDE,
LATITUDE,
rownum rw
from res_gpsstatus T
where T.VEHICLEID = 911174
AND T.TIME > to_date('2010-5-31 9', 'yyyy-mm-dd HH24')
AND T.TIME < to_date('2010-5-31 17', 'yyyy mm dd HH24')
and T.TIME >
((select TO_CHAR(S.TIME, 'HH24:mi:ss') TIME
from res_gpsstatus S
where S.VEHICLEID = 911174
AND S.TIME > to_date('2010-5-31 9', 'yyyy-mm-dd HH24')
AND S.TIME < to_date('2010-5-31 17', 'yyyy mm dd HH24')
AND ROWNUM = 1) + 1 / 24 / 60 / 60 * rownum * 8)
OR rownum = 1 order by T.TIME;
不知道什么原因我在执行的时候时间有点长,偶尔查询失败,我应该在找找原因。还有问题2 各位大侠能给分析一下