Oracle 用于消除最小和最大项目Wise重叠的SQL

这里的要求是消除重叠，但也采取最小和最大不同的组合每个CUST_EQP_CONTRACT_NUM (项目) 相应。

--create table1(EXPORT_TABLE) this is my actual data

create table EXPORT_TABLE(CUST_EQP_CONTRACT_NUM varchar2(150),EQP_ANT_BASE_HEIGHT_CALC number,EQP_ANT_TIP_HEIGHT_CALC number);

Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',166,174);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',167.52,172.48);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',168.53,171.48);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',99.69,108.31);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',102.69,111.31);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',231,239);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',231.5,238.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',232.5,240.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',233.58,239.82);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',98.98,101.02);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',118.98,121.02);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',138.5,161.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',148.5,171.5);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('666905',255,263);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('666905',256.64,261.36);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',4.2,5.2);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',3.9,4.9);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',2.1,4.8);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',6.2,6.8);
Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',5.9,6.5);
复制

我不会创建另一个表只是为了消除源行，并查看一个项目中有多少个不同的组合对我可用 (CUST_EQP_CONTRACT_NUM)

create table export_table2
as
select distinct CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC from EXPORT_TABLE;

---DESIRED OUTPUT
CUST_EQP_CONTRACT_NUM   EQP_ANT_BASE_HEIGHT_CALC   EQP_ANT_TIP_HEIGHT_CALC
168903     166          174
283874     99.69      111.31
283874     231          240.5
507286     98.98      101.02
507286     118.98      121.02
507286     138.5      171.5
666905     255          263
168999     2.1      5.2
168999     5.9      6.8

--DB VERSION

select* from v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
复制

我从https://asktom.oracle.com/pls/apex/asktom.search?tag=date-intersection

我做了这个:

select CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC
from (
 select * from EXPORT_TABLE2
 where 1=1
  model
    partition by (CUST_EQP_CONTRACT_NUM)
    dimension by (
      row_number()
        over(partition by CUST_EQP_CONTRACT_NUM order by EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC desc)
      as rn
    )
    measures(EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC, 0 contained)
    rules iterate(1E9) until(contained[iteration_number+1] is null) (
      contained[rn > iteration_number+1] =
        case when EQP_ANT_BASE_HEIGHT_CALC[cv()] <= EQP_ANT_TIP_HEIGHT_CALC[iteration_number+1]
          then 1 else contained[cv()]
        end
    )
    )
where contained = 0
order by 1,2; --If you compare this with Desired output from the below query it's not matching MIN and MAX, it eliminates overlaps but I want to tweak this solution
复制

我让下面的解决方案不起作用CUST_EQP_CONTRACT_NUM 168999,也可以在模型查询中吗？
-与源代码一样，它有800万行，因此在性能方面，我们需要一个更好的解决方案。

with export_table as (select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from  export_table2)
,tab as
(select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from export_table
where (CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC) not in (
select a.CUST_EQP_CONTRACT_NUM,b.EQP_ANT_BASE_HEIGHT_CALC, b.EQP_ANT_TIP_HEIGHT_CALC from export_table a
inner join export_table b on
(a.CUST_EQP_CONTRACT_NUM = b.CUST_EQP_CONTRACT_NUM and
((b.EQP_ANT_BASE_HEIGHT_CALC > a.EQP_ANT_BASE_HEIGHT_CALC and b.EQP_ANT_BASE_HEIGHT_CALC < a.EQP_ANT_TIP_HEIGHT_CALC)
and
(b.EQP_ANT_TIP_HEIGHT_CALC > a.EQP_ANT_BASE_HEIGHT_CALC and b.EQP_ANT_TIP_HEIGHT_CALC < a.EQP_ANT_TIP_HEIGHT_CALC))
))
)
,tab2 as
(select  a.CUST_EQP_CONTRACT_NUM,a.EQP_ANT_BASE_HEIGHT_CALC, b.EQP_ANT_TIP_HEIGHT_CALC  -- only overlap
from tab a
inner join tab b on (a.CUST_EQP_CONTRACT_NUM = b.CUST_EQP_CONTRACT_NUM
and b.EQP_ANT_BASE_HEIGHT_CALC >= a.EQP_ANT_BASE_HEIGHT_CALC and b.EQP_ANT_BASE_HEIGHT_CALC < a.EQP_ANT_TIP_HEIGHT_CALC
and b.EQP_ANT_TIP_HEIGHT_CALC > a.EQP_ANT_TIP_HEIGHT_CALC))
select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from tab a
where (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC) not in (select CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC from tab2)
and (CUST_EQP_CONTRACT_NUM,EQP_ANT_TIP_HEIGHT_CALC) not in (select CUST_EQP_CONTRACT_NUM,EQP_ANT_TIP_HEIGHT_CALC from tab2)
union all
select CUST_EQP_CONTRACT_NUM, EQP_ANT_BASE_HEIGHT_CALC, EQP_ANT_TIP_HEIGHT_CALC from tab2
order by 1,2,3
;
复制

看看这个例子，似乎重叠规则是:

eqp_ant_base_height_calc < previous eqp_ant_tip_height_calc
复制

并且您需要每个组的最小eqp_ant_base_height_calc和最大eqp_ant_tip_height_calc。

如果是这样，这很容易与模式匹配:

select * 
from   export_table2 
  match_recognize (
    partition by cust_eqp_contract_num
    order by eqp_ant_base_height_calc,
       eqp_ant_tip_height_calc
    measures
      min ( eqp_ant_base_height_calc ) mn,
      max ( eqp_ant_tip_height_calc ) mx
    pattern ( init overlap* )
    define 
      overlap as (
        eqp_ant_base_height_calc < prev ( eqp_ant_tip_height_calc )
      )
  );
  
CUST_EQP_CONTRACT_NUM     MN       MX       
168903                   166      174 
168999                   2.1      5.2 
168999                   5.9      6.8 
283874                 99.69   111.31 
283874                   231    240.5 
507286                 98.98   101.02 
507286                118.98   121.02 
507286                 138.5    171.5 
666905                   255      263 
复制