多模式时间跨度计算：经典间隔与孤岛算法适配ID1场景

问题背景 #

现有时间跨度数据包含三种典型场景：

• ID1：5个时间跨度中，4个完全包含在第一个跨度（2000-08-08至2019-03-31）内
• ID2：时间跨度存在重叠，后续跨度的起始日期落在前一跨度的结束日期范围内
• ID3：两个时间跨度之间存在间隔

原有的经典间隔与孤岛SQL算法仅能正确处理ID2（重叠）和ID3（间隔）的场景，无法合并ID1中完全包含的子跨度，原代码如下：

WITH cte1 AS (
  SELECT
    id,
    startdate,
    enddate,
    CASE
      WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= DATEADD(day, -1, startdate) THEN 0
      ELSE 1
    END AS new_grp
  FROM tab1
), cte2 AS (
  SELECT
    cte1.*,
    SUM(new_grp) OVER (PARTITION BY id ORDER BY startdate) AS grp_num
  FROM cte1
)
SELECT
  id,
  MIN(startdate) AS startdate,
  MAX(enddate) AS enddate
FROM cte2
GROUP BY id, grp_num
ORDER BY id, startdate;

预期输出：

解决方案 #

原算法的问题在于仅比较当前行与前一行的时间关系，完全被包含的子跨度会被误判为新组。我们需要先排除所有被其他跨度完全包含的记录，再对剩余记录应用经典合并逻辑：

WITH filtered_intervals AS (
  -- 筛选出不被同一ID下其他任何区间完全包含的记录
  SELECT t1.id, t1.startdate, t1.enddate
  FROM tab1 t1
  WHERE NOT EXISTS (
    SELECT 1
    FROM tab1 t2
    WHERE t2.id = t1.id
      AND t2.startdate <= t1.startdate
      AND t2.enddate >= t1.enddate
      AND (t2.startdate != t1.startdate OR t2.enddate != t1.enddate)
  )
),
cte1 AS (
  SELECT
    id,
    startdate,
    enddate,
    CASE
      WHEN LAG(enddate) OVER (PARTITION BY id ORDER BY startdate) >= DATEADD(day, -1, startdate) THEN 0
      ELSE 1
    END AS new_grp
  FROM filtered_intervals
),
cte2 AS (
  SELECT
    cte1.*,
    SUM(new_grp) OVER (PARTITION BY id ORDER BY startdate) AS grp_num
  FROM cte1
)
SELECT
  id,
  MIN(startdate) AS startdate,
  MAX(enddate) AS enddate
FROM cte2
GROUP BY id, grp_num
ORDER BY id, startdate;

逻辑说明 #

1. filtered_intervals：通过NOT EXISTS子查询排除所有被同一ID下其他区间完全包含的记录，仅保留最外层的区间（比如ID1仅保留第一个大跨度）。
2. 后续的cte1和cte2沿用经典间隔与孤岛算法，合并剩余区间中的重叠或连续部分，最终得到正确的合并结果。

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