Here is a different solution, which should work in Oracle 10.1 and higher - using the Tabibitosan method. The problem is slightly complicated by the use of NULL to mark an indefinite valid_to date; in particular, the definition of valid_to in the outer query can't simply be max(valid_to) within each group, since that would produce the wrong answer when valid_to may be null.
Other than that, the computation that produces the grp column in the subquery is the main idea: it produces a different date for each "island" in the "gaps and islands" structure of the input data. This is a less known use of the Tabibitosan method; it makes this kind of query as efficient as possible since it requires only one level of analytic functions.
/*
with
sample_data (...) as (...)
*/
select obj_number, obj_related, min(valid_from) as valid_from,
max(valid_to) keep (dense_rank last order by valid_from) as valid_to
from (
select sd.*,
nvl(valid_to, date '9999-12-31') -
sum(nvl(valid_to, date '9999-12-31') - valid_from)
over (partition by obj_number, obj_related
order by valid_from) as grp
from sample_data sd
)
group by obj_number, obj_related, grp
order by obj_number, obj_related, valid_from
;
The best way to try to understand how the Tabibitosan method works (in this case) is to run the subquery separately and to see what it produces.
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
