这是我的表的简化版本
+----+----------+------------+------------+
| ID | Category | Start Date | End Date |
+----+----------+------------+------------+
| 1 | 'Alpha' | 2018/04/12 | 2018/04/15 |
| 2 | null | 2018/04/17 | 2018/04/21 |
| 3 | 'Gamma' | 2018/05/02 | 2018/05/07 |
| 4 | 'Gamma' | 2018/05/09 | 2018/05/11 |
| 5 | 'Gamma' | 2018/05/11 | 2018/05/17 |
| 6 | 'Alpha' | 2018/05/17 | 2018/05/23 |
| 7 | 'Alpha' | 2018/05/23 | 2018/05/24 |
| 8 | null | 2018/05/24 | 2018/06/02 |
| 9 | 'Beta' | 2018/06/12 | 2018/06/16 |
| 10 | 'Beta' | 2018/06/16 | 2018/06/20 |
+----+----------+------------+------------+
所有开始日期都是唯一的，不可为空，并且它们与 ID 具有相同的顺序(如果 a 和 b 是 ID，a
这是一个差距和孤岛问题。 我认为您可以使用行号的差异：
select category, min(startdate), max(enddate)
from (select t.*,
row_number() over (order by id) as seqnum,
row_number() over (partition by category order by id) as seqnum_c
from t
) t
group by category, (seqnum - seqnum_c)
order by min(startdate);
>这是一个gaps and islands问题，你可以在下面使用这样的逻辑
select category, min(start_date) as start_date, max(end_date) as end_date
from
(
select tt.*, sum(grp) over (order by id, start_date) sm
from
(
with t( ID, Category, Start_Date, End_Date) as
(
select 1 , 'Alpha' , date'2018-04-12',date'2018-04-15' from dual union all
select 2 , null , date'2018-04-17',date'2018-04-21' from dual union all
select 3 , 'Gamma' , date'2018-05-02',date'2018-05-07' from dual union all
select 4 , 'Gamma' , date'2018-05-09',date'2018-05-11' from dual union all
select 5 , 'Gamma' , date'2018-05-11',date'2018-05-17' from dual union all
select 6 , 'Alpha' , date'2018-05-17',date'2018-05-23' from dual union all
select 7 , 'Alpha' , date'2018-05-23',date'2018-05-24' from dual union all
select 8 , null , date'2018-05-24',date'2018-06-02' from dual union all
select 9 , 'Beta' , date'2018-06-12',date'2018-06-16' from dual union all
select 10 , 'Beta' , date'2018-06-16',date'2018-06-20' from dual
)
select id, Category,
decode(nvl(lag(end_date) over
(order by end_date),start_date),start_date,0,1)
as grp, --> means prev. value equals or not
row_number() over (order by id, end_date) as rn, start_date, end_date
from t
) tt
order by rn
)
group by Category, sm
order by end_date;
CATEGORY START_DATE END_DATE
Alpha 12.04.2018 15.04.2018
NULL 17.04.2018 21.04.2018
Gamma 02.05.2018 07.05.2018
Gamma 09.05.2018 17.05.2018
Alpha 17.05.2018 24.05.2018
NULL 24.05.2018 02.06.2018
Beta 12.06.2018 20.06.2018