我正在尝试修改以下查询:
SELECT START_END.*, START_END.LOAD_TIME_END - START_END.LOAD_TIME_START
FROM
(
SELECT START.JOB_ID, START.LOAD_TIME_START, END.LOAD_TIME_END
FROM
(
SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_START
FROM JOB_CTRL_RECON JCR
INNER JOIN
(
SELECT JOB_ID AS JOB_ID_S, MAX(RECON_ID) AS S_MAX_RECON_ID
FROM job_ctrl_recon
where job_id in ('1','2')
and count_type = 'Source'
GROUP BY JOB_ID
) SMAX
ON JCR.JOB_ID = SMAX.S_MAX_RECON_ID
AND JCR.RECON_ID = SMAX.RECON_ID
) START
INNER JOIN
(
SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_END FROM JOB_CTRL_RECON JCR INNER JOIN
(
SELECT JOB_ID AS JOB_ID_T, MAX(RECON_ID) AS T_MAX_RECON_ID
FROM job_ctrl_recon
where job_id in ('1','2')
and count_type = 'Target'
GROUP BY JOB_ID
) TMAX
ON JCR.JOB_ID = TMAX.T_MAX_RECON_ID
AND JCR.RECON_ID = TMAX.RECON_ID
) END
ON START.JOB_ID = END.JOB_ID
) START_END
使用 with 子句进行查询,如下所示:
我怎样才能消除where job_id in ('1','2')
使用 with 子句的多次使用,如下所示:
WITH A AS
(
SELECT JOB_ID FROM JOB_CTRL_RECON WHERE JOB_ID IN ('60','67')
)
SELECT START_END.*, START_END.LOAD_TIME_END - START_END.LOAD_TIME_START
FROM
(
SELECT START.JOB_ID, START.LOAD_TIME_START, END.LOAD_TIME_END
FROM
(
SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_START
FROM JOB_CTRL_RECON JCR
INNER JOIN
(
SELECT JOB_ID AS JOB_ID_S, MAX(RECON_ID) AS S_MAX_RECON_ID
FROM job_ctrl_recon
where job_id in (????)
and count_type = 'Source'
GROUP BY JOB_ID
) SMAX
ON JCR.JOB_ID = SMAX.S_MAX_RECON_ID
AND JCR.RECON_ID = SMAX.RECON_ID
) START
INNER JOIN
(
SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_END
FROM JOB_CTRL_RECON JCR
INNER JOIN
(
SELECT JOB_ID AS JOB_ID_T, MAX(RECON_ID) AS T_MAX_RECON_ID
FROM job_ctrl_recon
where job_id in (????)
and count_type = 'Target'
GROUP BY JOB_ID
) TMAX
ON JCR.JOB_ID = TMAX.T_MAX_RECON_ID
AND JCR.RECON_ID = TMAX.RECON_ID
) END
ON START.JOB_ID = END.JOB_ID
) START_END, A
WHERE START_END.JOB_ID = A.JOB_ID
代替WITH
,您可以使用KEEP
带有分析函数的来简化代码。该代码起初有点奇怪,但它只允许您指定一次 JOB_ID,并且只需从表中读取一次。
--Load time start and end for each JOB_ID, based on the latest RECON_ID.
select
job_id,
max(load_time_start) load_time_start,
max(load_time_end) load_time_end,
max(load_time_end) - max(load_time_start) diff
from
(
--First and last load time for each set of rows, with some debug data.
select job_id, count_type, recon_id, load_time
,last_value(case when count_type = 'Source' then load_time else null end)
over (
partition by job_id, count_type
order by recon_id
rows between unbounded preceding and unbounded following
) load_time_start
,last_value(case when count_type = 'Target' then load_time else null end)
over (
partition by job_id, count_type
order by recon_id
rows between unbounded preceding and unbounded following
) load_time_end
from job_ctrl_recon
where job_id in (1,2)
)
group by job_id
order by job_id;
对于此表和数据:
create table job_ctrl_recon(job_id number, recon_id number, count_type varchar2(100), load_time date);
alter session set nls_date_format = 'DD-Mon-RR';
insert into job_ctrl_recon
select 1, 400, 'Source', '24-Feb-18' from dual union all
select 1, 40, 'Source', '23-Feb-18' from dual union all
select 1, 89, 'Target', '25-Feb-18' from dual union all
select 1, 8, 'Target', '25-Feb-18' from dual union all
select 2, 700, 'Source', '24-Feb-18' from dual union all
select 2, 8, 'Source', '23-Feb-18' from dual union all
select 2, 567, 'Target', '25-Feb-18' from dual union all
select 2, 45, 'Target', '25-Feb-18' from dual union all
select 2, 678, 'Target', '25-Feb-18' from dual;
commit;
这些是结果:
JOB_ID LOAD_TIME_START LOAD_TIME_END DIFF
------ --------------- ------------- ----
1 2018-02-24 2018-02-25 1
2 2018-02-24 2018-02-25 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句