Skip to Main Content
  • Questions
  • SQL For Elimination of Overlaps + MIN and MAX Project Wise

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Darshankumar.

Asked: February 02, 2021 - 4:23 am UTC

Last updated: February 17, 2021 - 5:11 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 100+ times

You Asked

The requirement here is To Eliminate Overlaps but also Take MIN and MAX + Distinct Combinations per CUST_EQP_CONTRACT_NUM(Project) Accordingly.

--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);


Not I will create another Table just to eliminate source rows and see how many distinct combinations are available for me in One PROJECT(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


I Took a reference from https://asktom.oracle.com/pls/apex/asktom.search?tag=date-intersection

I Made this :

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


I made the below Solution not working for CUST_EQP_CONTRACT_NUM 168999, Also can it be possible in MODEL Query?
--As in Source it's 8+ Million rows, so Performace wise we need to have a better solution.

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
;

and we said...

Looking at the example, it seems the overlap rule is:

eqp_ant_base_height_calc < previous eqp_ant_tip_height_calc


And you want the smallest eqp_ant_base_height_calc and greatest eqp_ant_tip_height_calc for each group.

If so this is easy with pattern matching:

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 

Rating

  (5 ratings)

Comments

Darshankumar, February 02, 2021 - 12:09 pm UTC

Thank you so much, I will try this with Many Patterns and I will get back to this thread.

Just a sort request, I am not able to get notification of update of this thread and not able to see the same in My Asked Questions too.
Chris Saxon
February 02, 2021 - 5:13 pm UTC

You're welcome.

Ensure you use the same email address when searching for "Your Questions" you used to submit this; if you're still hitting issues please use the feedback/bug options (at the top right of the screen).

I found one Not Working Case

A reader, February 15, 2021 - 11:32 am UTC

--not working in this case
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',119.82,124.18);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',122,122);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',133.88,138.12);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',136,136);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',149.77,156.23);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',153,153);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',160.62,169.38);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',160.98,169.02);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',162.83,167.17);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',163,169);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',163.87,166.13);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',163.89,166.11);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',163.96,166.04);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',164,166);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',164.15,165.85);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',164.31,165.69);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',164.38,165.63);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',164.54,165.46);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',164.65,165.35);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',165,165);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',166,166);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',166.77,173.23);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',167,175);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',170,170);
Insert into EXPORT_TABLE2 (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('100775',171,171);

I found one Not Working Case

Darshankumar, February 15, 2021 - 11:34 am UTC

Dear Chris,
Thanks for the solution, I was testing my data and found the case where it's not working, I have pasted Insert scripts too, you can append the data with previous scripts and for the new pastes case, it's not working. I think some minor thing is missing.

Thanks,

Chris Saxon
February 15, 2021 - 3:51 pm UTC

How exactly do you define overlaps and what is the output you expect from the data above?

Working Solution

A reader, February 16, 2021 - 11:24 am UTC

with export_table3 as (
select
distinct CUST_EQP_CONTRACT_NUM,  eqp_ant_base_height_calc, eqp_ant_tip_height_calc
From STG_CUST_EQUIP_BREAKOUT 
where 1=1
and CUST_EQP_CONTRACT_NUM='100775'
order by 3,4)
select * from export_table3
match_recognize (
    partition by cust_eqp_contract_num
    order by eqp_ant_base_height_calc,
       eqp_ant_tip_height_calc
    measures
      first ( eqp_ant_base_height_calc ) mn,
      max ( eqp_ant_tip_height_calc ) mx
    pattern ( a* b)
    define 
      a as (
         max ( eqp_ant_tip_height_calc ) >= next(eqp_ant_base_height_calc)
      )
  )
  where 1=1;

Working Solution

Darshankumar, February 16, 2021 - 11:26 am UTC

Dear Chris,

Thank you so much for your inputs, those are so valuable.

I have just made a solution by defining Overlaps in different ways.

Thank you so much,
Darshankumar Prajapati
Chris Saxon
February 17, 2021 - 5:11 pm UTC

Great

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.