如何从多表重叠时间区间生成连续无重叠区间？

表结构与测试数据 #

现有两张表table1和table2，建表及插入数据的SQL如下：

CREATE TABLE lab_dlb_suba.table1 (id string, valid_from STRING, valid_to STRING);
INSERT INTO lab_dlb_suba.table1 VALUES
    ('ABC123', '2022-12-09', '2022-12-21'),
    ('ABC123', '2022-12-22', '2023-05-07'),
    ('ABC123', '2023-05-08', '9999-12-31')
;

CREATE TABLE lab_dlb_suba.table2 (id string, valid_from STRING, valid_to STRING);
INSERT INTO lab_dlb_suba.table2 VALUES
    ('ABC123', '2022-09-30', '2022-12-30'),
    ('ABC123', '2022-12-31', '2023-03-30')
;

需求目标 #

创建dates表，将两张表的时间区间合并后，生成连续且无重叠的完整时间区间集合。

当前实现方案 #

当前采用三步法实现：

1. 生成事件集合：将两张表的起始、结束日期分别标记为类型1（开始）和-1（结束），通过UNION ALL合并
2. 计算区间结束日期：用lead()函数推导每个事件对应的区间结束日期d2
3. 修正区间起始日期：用lag()函数调整起始日期，生成最终的dates表

对应的查询SQL如下：

WITH `UNION` AS
(
       SELECT 1                   AS type,
              id                  AS id,
              to_date(valid_from) AS dt
       FROM   table1
       UNION ALL
       SELECT -1                AS type,
              id                AS id,
              to_date(valid_to) AS dt
       FROM   table1
       UNION ALL
       SELECT 1                   AS type,
              id                  AS id,
              to_date(valid_from) AS dt
       FROM   table2
       UNION ALL
       SELECT -1                AS type,
              id                AS id,
              to_date(valid_to) AS dt
       FROM   table2), 
`d2` AS
(
         SELECT   id,
                  type,
                  dt,
                  CASE
                           WHEN lead(type) OVER ( partition BY id ORDER BY dt ) = 1 THEN date_sub( lead(dt) OVER ( partition BY id ORDER BY dt ), 1 )
                           ELSE lead(dt) OVER ( partition BY id ORDER BY dt )
                  END AS d2
         FROM     `UNION` ), 
`dates` AS
(
         SELECT   id,
                  CASE
                           WHEN lag(d2) OVER ( partition BY id ORDER BY dt ) = dt THEN date_add(dt, 1)
                           ELSE to_date(dt)
                  END AS d1,
                  d2
         FROM     `d2`
         WHERE    (
                           type = 1
                  OR       d2 = '9999-12-31' ))
SELECT   *
FROM     `dates`
ORDER BY d1,
         d2

当前输出结果 #

执行上述SQL后得到以下结果：

存在的问题 #

结果中缺失了一个关键区间：

补充说明 #

• 运行环境：Impala，查询需兼容Hive语法
• 实际业务场景需要扩展到3-4张表
• 曾尝试添加第三张表适配现有查询，但出现区间重叠问题

内容的提问来源于stack exchange，提问作者legends1337