I guess the demo provides a hint on how to efficiently solve the problem. When only points are needed in final expression, why convert points to ranges and then convert back to points? Totally waste time. After play a little bit using Oracle, it proves the above thinking (though hardly see it coming without playing). Below show the SQL script run in Oracle 12 with input data and output results. The logic flow should be simple enough for self-explaining.
--one comment here:
the id part in the sql script is only for sql operation and not needed in procedural program (a single loop is enough to reconstruct the output array for desired expression)
with t (sp, ep, ht) as (
select 2, 9, 10 from dual
union all
select 3, 7, 15 from dual
union all
select 5, 12, 12 from dual
union all
select 15, 20, 10 from dual
union all
select 19, 24, 8 from dual
), t1 as (select sp p from t union select ep p from t
), t2 as (select p, max(ht) ht,
row_number() over (order by p) - row_number() over (partition by max(ht) order by p) id
from t right join t1 on p>=sp and p<ep group by p
), t3 as (select min(p) p, ht from t2 group by id, ht
)
select p, nvl(ht,0) ht from t3 order by p
/
P HT
---------- ----------
2 10
3 15
7 12
12 0
15 10
20 8
24 0
7 rows selected.
with t (sp, ep, ht) as (
select 0, 2, 3 from dual
union all
select 2, 5, 3 from dual
), t1 as (select sp p from t union select ep p from t
), t2 as (select p, max(ht) ht,
row_number() over (order by p) - row_number() over (partition by max(ht) order by p) id
from t right join t1 on p>=sp and p<ep group by p
), t3 as (select min(p) p, ht from t2 group by id, ht
)
select p, nvl(ht,0) ht from t3 order by p
/
P HT
---------- ----------
0 3
5 0