如何编写SQL查询实现高优先级Type B重叠下的Type A日期区间拆分

实现思路 #

核心逻辑是B类区间全部保留，A类区间扣除所有和B重叠的部分后拆分输出，支持单个A区间对应多个重叠B区间的场景，步骤如下：

• 第一步：先预处理所有B类区间，合并其中重叠/相邻的区间，得到连续不重叠的B区间集合，避免后续重复计算（如果确认你的数据中B类区间本身没有重叠，这一步可以直接简化为取所有Type=B的原始记录）
  预处理合并B区间的通用逻辑：

  WITH merged_b AS (
      SELECT Type, MIN(StartDate) AS StartDate, MAX(EndDate) AS EndDate
      FROM (
          SELECT *,
                 SUM(flag) OVER (ORDER BY StartDate) AS grp
          FROM (
              SELECT Type, StartDate, EndDate,
                     -- 判断当前区间和上一个区间是否重叠/相邻
                     CASE WHEN StartDate <= LAG(EndDate + INTERVAL '1 day') OVER (ORDER BY StartDate) THEN 0 ELSE 1 END AS flag
              FROM TableA
              WHERE Type = 'B'
          ) t
      ) t
      GROUP BY Type, grp
  )
  

  注：不同数据库日期加1天的语法有差异，MySQL用DATE_ADD(EndDate, INTERVAL 1 DAY)，Oracle用EndDate + 1。

• 第二步：将每个A类区间和所有重叠的B类区间关联，提取所有时间断点，排序后生成新的A区间
  完整查询逻辑接上面的CTE继续写：

  -- 提取所有A区间的起止、以及和A重叠的B区间的前后断点
  , all_points AS (
      SELECT a.ID, a.Type, pt AS point,
             ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY pt) AS rn
      FROM TableA a
      -- 关联所有和当前A重叠的B区间
      LEFT JOIN merged_b b 
      ON b.StartDate <= a.EndDate AND b.EndDate >= a.StartDate
      -- 拉取所有断点，不支持UNNEST的数据库可以换成UNION ALL的方式手动枚举
      CROSS JOIN UNNEST(ARRAY[a.StartDate, a.EndDate + INTERVAL '1 day', b.StartDate, b.EndDate + INTERVAL '1 day']) AS pt
      WHERE a.Type = 'A'
      -- 过滤超出A区间范围的无效断点
      AND pt BETWEEN a.StartDate AND a.EndDate + INTERVAL '1 day'
  )
  -- 断点两两配对生成新的A区间
  , splited_a AS (
      SELECT p1.ID, 'A' AS Type,
             p1.point AS StartDate,
             p2.point - INTERVAL '1 day' AS EndDate
      FROM all_points p1
      JOIN all_points p2 ON p1.ID = p2.ID AND p1.rn = p2.rn - 1
      -- 过滤无效区间和和B重叠的区间
      WHERE p1.point <= p2.point - INTERVAL '1 day'
      AND NOT EXISTS (
          SELECT 1 FROM merged_b b
          WHERE b.StartDate <= p2.point - INTERVAL '1 day' AND b.EndDate >= p1.point
      )
  )
  -- 最终合并拆分后的A区间和B区间，按需生成新ID即可
  SELECT 
    ROW_NUMBER() OVER(ORDER BY StartDate) AS new_id,
    Type,
    StartDate,
    EndDate
  FROM (
      SELECT Type, StartDate, EndDate FROM splited_a
      UNION ALL
      SELECT Type, StartDate, EndDate FROM merged_b
  ) t
  ORDER BY StartDate
  

  如果数据库不支持数组和UNNEST函数，可以把CROSS JOIN UNNEST部分替换为以下写法：

  CROSS JOIN (
      SELECT a.StartDate AS pt UNION ALL
      SELECT a.EndDate + INTERVAL '1 day' UNION ALL
      SELECT b.StartDate UNION ALL
      SELECT b.EndDate + INTERVAL '1 day'
  ) pts
  

方案说明 #

该方案不需要提前知道B区间的数量，可以自动处理单个A区间对应任意多个重叠B区间的场景，不需要手动写多个UNION分支，兼容性强。

内容的提问来源于stack exchange，提问作者Ana Munte SPB