sql - 在SQL中展平日期间隔

我有一个数据库表，其中有三列对此问题必不可少:

一个组ID，将行分组在一起

开始日期

结束日期

我想从此表进行查看，以使具有相同分组ID的重叠日期间隔变平。

不重叠的日期间隔不得展平。

例子:

Group ID       Start         End
   1        2016-01-01   2017-12-31
   1        2016-06-01   2020-01-01
   1        2022-08-31   2030-12-31
   2        2010-03-01   2017-01-01
   2        2012-01-01   2013-12-31
   3        2001-01-01   9999-13-31

...成为...

Group ID       Start         End
   1        2016-01-01   2020-01-01
   1        2022-08-31   2030-12-31
   2        2010-03-01   2017-01-01
   3        2001-01-01   9999-12-31

重叠的间隔可以以任何方式进行，完全被其他间隔包围，或者可以交错，甚至可以具有相同的开始和/或结束日期。

有个类似的ID。通常(> 95％)，只有一行具有特定的组ID。分两行显示大约一千个ID。存在于三行中的少数ID；没有四行或更多行。

但我需要做好准备，以显示可能存在于四行或更多行中的组ID。

如何编写一条创建 View 的SQL语句，该 View 显示以这种方式展平的表？

请注意，每一行都有唯一的ID。不需要以任何方式保留它，但是如果在编写SQL时有所帮助，我会告诉您。

最佳答案

首先，找到不是连续重叠序列的区间:

select *
from dateclap d1
where not exists(
    select *
    from dateclap d2
    where d2.group_id=d1.group_id and
        d2.end_date >= d1.start_date and
        (d2.start_date < d1.start_date or
        (d1.start_date=d2.start_date and d2.r_id<d1.r_id)))

最后一行区分从相同日期/时间开始的间隔，并按唯一的记录ID(r_id)对其进行排序。

然后，对于每个这样的记录，我们都可以使用connect_by_root r_id区分钳位组来获得记录的层次选择。之后，我们需要获取钳位组的最小值/最大值(connect_by_root r_id是组中父记录的ID):

select group_id, min(start_date) as start_date, max(end_date) as end_date
from dateclap d1
start with not exists(
    select *
    from dateclap d2
    where d2.group_id=d1.group_id and
        d2.end_date >= d1.start_date and
        (d2.start_date < d1.start_date or
        (d1.start_date=d2.start_date and d2.r_id<d1.r_id)))
connect by nocycle
    prior group_id=group_id and
    start_date between prior start_date and prior end_date
group by group_id, connect_by_root r_id

请在这里注意nocycle-避免异常是一个肮脏的技巧，因为连接较弱，并且实际上试图将记录与其自身连接。您可以在“connect by”之后细化条件，类似于“exists”条件，以避免使用nocycle。

P.S.该表是针对以下测试创建的:

CREATE TABLE "ANIKIN"."DATECLAP"
(
    "R_ID" NUMBER,
    "GROUP_ID" NUMBER,
    "START_DATE" DATE,
    "END_DATE" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANIKIN" ;

r_id的唯一键(或可能是主键)以及相应的序列/触发器不是特定于测试的内容，只需使用唯一值填充r_id。

关于sql - 在SQL中展平日期间隔，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39973657/