sql - 扁平化 SQL 中的日期间隔

Question

我有一个数据库表，其中三列对于这个问题至关重要:

• 组 ID，将行分组在一起
• 开始日期
• 结束日期

我想从此表中创建一个 View ，以便将具有相同分组 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。

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

请注意，每一行也有一个唯一的 ID。这不需要以任何方式保留，但如果它在编写 SQL 时有帮助，我会让您知道。

Answer 1

首先，找到不是重叠序列延续的间隔:

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。

附注表是为这样的测试创建的:

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 即可。