另一种间隙和孤岛方法是使用match_recognize子句（从12 c开始可用）来执行模式匹配：

select type,
  min(fr_date) as fr_date,
  max(to_date) as to_date,
  sum(income) as income_sum
from your_table
match_recognize (
  order by fr_date
  measures match_number() as mnum
  all rows per match
  pattern (start_type same_type*)
  define same_type as (type = prev(type))
)
group by type, mnum
order by fr_date

fiddle

一种选择是将问题视为 * 间隙和岛屿 *，即将某些行分组（岛屿），然后提取所需的数据。
样本数据：

SQL> with test (type, fr_date, to_date, income) as
  2    (select 'A', date '2023-04-14', date '2024-04-14', 100 from dual union all
  3     select 'A', date '2023-04-15', date '2024-04-16', 200 from dual union all
  4     select 'B', date '2023-04-17', date '2024-04-17', 300 from dual union all
  5     select 'B', date '2023-04-18', date '2024-04-20', 100 from dual union all
  6     select 'A', date '2023-04-21', date '2024-04-21', 400 from dual union all
  7     select 'A', date '2023-04-22', date '2024-04-24', 200 from dual union all
  8     select 'B', date '2023-04-25', date '2024-04-28', 500 from dual
  9    ),

查询从这里开始：

10  temp as
 11    (select type, fr_date, to_date, income,
 12       to_number(to_char(fr_date, 'yyyymmdd')) - row_number() over (partition by type order by fr_date) grp
 13     from test
 14    )
 15  select type, min(fr_date) fr_date, max(to_date) to_date, sum(income) income_sum
 16  from temp
 17  group by type, grp
 18  order by fr_date;

TYPE FR_DATE    TO_DATE    INCOME_SUM
---- ---------- ---------- ----------
A    04/14/2023 04/16/2024        300
B    04/17/2023 04/20/2024        400
A    04/21/2023 04/24/2024        600
B    04/25/2023 04/28/2024        500

SQL>