这就是所谓的“空白与孤岛”问题。这是一个非常普遍的情况,因此我建议使用累积最大值来确定是否存在重叠:
select min(start_date), max(end_date)
from (select t.*,
sum(case when prev_end_date >= start_date then 0 else 1 end) over (order by start_date) as grp
from (select t.*,
max(end_date) over (order by start_date rows between unbounded preceding and 1 preceding) as prev_end_date
from t
) t
) t
group by grp;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句