Oracle SQL窗口优化/可能的Oracle PL/SQL解决方案？
Oracle SQL窗口优化/可能的Oracle PL/SQL解决方案？
提问于 2018-06-10 02:39:57
我说不出这个社区有多大的帮助！我惯常的问题又一次困扰着我。我有功能代码，可以很好的工作，比如说一周的数据，但是没有实际的运行时。YTD数据。(包含事件的表是35亿行，10+年)我正在使用一个窗口函数来确定系统事件发生了什么。我还使用了一个case语句和两个不同的字段来创建一个自定义键来对事件进行分组(我希望源数据已经有了一个键)。
我对PL/SQL不是很了解，但我想有人会告诉我如何使用循环和游标在较小的日期窗口中循环？事件总是发生+- 24小时..然后我可以拉取比如说3个月的数据，但是窗口只能在+- 24小时内查看？我不确定是否有办法在没有PL/SQL的情况下做到这一点。
编辑:我现在在想，也许我可以查询e2.dt，然后以某种方式进行连接或子查询，以运行+-24小时时间范围的逻辑？仍然不能很好地展示如何将它们组合在一起。
任何帮助都是非常感谢的！
select
ce.DT
,ce.SYSTEM_NO, ce.EVENT_ID, ce.CALL_START, ce.CALL_CLEAR
,case when ce.event_RESTORED between ce.call_start and
ce.call_clear+0.0034722222222222 then ce.event_RESTORED end event_RESTORED
,case when ce.call_GOODCALL between ce.call_start and
ce.call_clear+0.0034722222222222 then ce.call_GOODCALL end call_GOODCALL
,case when ce.call_clear is not null and (ce.event_RESTORED not between
ce.call_start and ce.call_clear+0.0034722222222222 or ce.event_RESTORED is
null) and ce.call_GOODCALL between ce.call_start and
ce.call_clear+0.0034722222222222 then 'Y' ELSE 'N' end call_CONNECTED
from
(SELECT system_no,
dt as dt,
e2.event_id,
min(
case when e2.event_id in ('XXX001')
THEN e2.dt
END
) OVER (
partition by e2.system_no, case when e2.event_no is not null then to_char(e2.event_no) when INSTR(e2.commnt, 'callid:') <> 0 then SUBSTR(e2.commnt, INSTR(e2.commnt, 'callid:') + 7) else to_char(e2.callid) end
) AS call_start,
min(
case when e2.event_id in ('XXXCC')
THEN e2.dt
END
) OVER (
partition by e2.system_no, case when e2.event_no is not null then to_char(e2.event_no) when INSTR(e2.commnt, 'callid:') <> 0 then SUBSTR(e2.commnt, INSTR(e2.commnt, 'callid:') + 7) else to_char(e2.callid) end
) AS call_clear
, min(
case when e2.event_id in ('XXXRE')
THEN e2.dt
END
) OVER (
partition by e2.system_no
order by e2.DT
RANGE BETWEEN 0 FOLLOWING AND 1 FOLLOWING
) AS event_restored
, min(
case when e2.event_id in ('XXX003','XXX004')
THEN e2.dt
END
) OVER (
partition by e2.system_no
order by e2.DT
RANGE BETWEEN 0 FOLLOWING AND 1 FOLLOWING
) AS call_goodcall
from CALLEVENTS e2
where
e2.dt >= '1-MAR-2018') ce
--e2.dt >= '1-JUN-2018') ce
where call_clear is not null;回答 1
Stack Overflow用户
发布于 2018-06-10 09:41:04
最后，我在自己的个人模式上创建了一个表，并在dt、system_no和event_no上添加了索引……我还在e.dt-1和e.dt+1之间的条件为e3.dt2的同一个表上执行了内连接。此查询现在大约在1小时内运行。对于>= 1-2018年3月..但是如果任何人有任何可以加速这一过程的改进，那就太好了！或者仅仅是好的代码/格式建议..因为我知道。我的是非常..。草率。
select
ce.DT
,ce.SYSTEM_NO, ce.EVENT_ID, ce.CALL_START, ce.CALL_CLEAR
,case when ce.event_RESTORED between ce.call_start and
ce.call_clear+0.0034722222222222 then ce.event_RESTORED end event_RESTORED
,case when ce.call_GOODCALL between ce.call_start and
ce.call_clear+0.0034722222222222 then ce.call_GOODCALL end call_GOODCALL
,case when ce.call_clear is not null and (ce.event_RESTORED not between
ce.call_start and ce.call_clear+0.0034722222222222 or ce.event_RESTORED is
null) and ce.call_GOODCALL between ce.call_start and
ce.call_clear+0.0034722222222222 then 'Y' ELSE 'N' end call_CONNECTED
from
(SELECT system_no,
dt as dt,
e.event_id,
e3.*
from CALLEVENTS e
inner join (select
system_no,
dt as dt,
e2.event_id,
min(
case when e2.event_id in ('XXX001')
THEN e2.dt
END
) OVER (
partition by e2.system_no, case when e2.event_no is not null then to_char(e2.event_no) when INSTR(e2.commnt, 'callid:') <> 0 then SUBSTR(e2.commnt, INSTR(e2.commnt, 'callid:') + 7) else to_char(e2.callid) end
) AS call_start,
min(
case when e2.event_id in ('XXXCC')
THEN e2.dt
END
) OVER (
partition by e2.system_no, case when e2.event_no is not null then to_char(e2.event_no) when INSTR(e2.commnt, 'callid:') <> 0 then SUBSTR(e2.commnt, INSTR(e2.commnt, 'callid:') + 7) else to_char(e2.callid) end
) AS call_clear
, min(
case when e2.event_id in ('XXXRE')
THEN e2.dt
END
) OVER (
partition by e2.system_no
order by e2.DT
RANGE BETWEEN 0 FOLLOWING AND 1 FOLLOWING
) AS event_restored
, min(
case when e2.event_id in ('XXX003','XXX004')
THEN e2.dt
END
) OVER (
partition by e2.system_no
order by e2.DT
RANGE BETWEEN 0 FOLLOWING AND 1 FOLLOWING
) AS call_goodcall
from CALLEVENTS e2
) e3 on e3.sys = e.system_no and e3.seq = e.event_no and e3.dt2 between
e.dt-1 and e.dt+1
where
e.dt >= '1-MAR-2018') se
where call_clear is not null 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50777433
复制相关文章
相似问题