在合并重叠的合同覆盖期成为有效的连续块的背景下，您如何使用严格的 ANSI SQL 窗口函数而不使用过程循环将这些区间合并为不重叠的区间？

问题的答案

问题的背景。 合并重叠时间间隔的需求源自艾伦的区间代数（1983）和早期关系数据库对时间数据库的研究。保险系统、酒店预订平台和资源调度应用程序经常遇到此挑战，当多个覆盖期、预订或维护窗口重叠时，需要对其进行归一化处理，以形成不重叠的连续块，以便进行准确的可用性报告或计费。与简单的聚合不同，此操作要求理解顺序和连贯性，这使其成为高级 ANSI SQL 窗口函数掌握的标准测试。

问题描述。 给定一个由 start_date 和 end_date 列定义的日期范围表，目标是将所有重叠或相邻的区间合并为一组最少的不重叠区间。过程方法将维护一个运行缓冲区，将每一行与当前合并区间进行比较，但这违反了 SQL 的集合基础范例。核心难点在于在没有自连接或递归 CTE 的情况下识别连续的“岛”，特别是当存在传递重叠时（区间 A 重叠 B，B 重叠 C，但 A 和 C 不直接相接）。

解决方案。 利用 ANSI SQL 窗口函数，通过将当前行的 start_date 与同一分区中所有前面行的最大 end_date 进行比较，以检测每个新岛屿的开始。当 start_date 超过先前的最大结束日期时，一个新的岛屿开始；否则，当前行将扩展现有岛屿。为这些“断裂”标志分配一个运行总计作为 island_id，然后按该标识符分组以计算合并的 min(start_date) 和 max(end_date)。这种方法通过单通道排序和聚合实现 O(n log n) 复杂度。

生活中的情况

问题描述。 一家跨国医疗服务提供商维护了一项索赔处理数据库，患者拥有多个重叠的保险政策——从 1 月 1 日到 3 月 31 日的主要覆盖，从 2 月 15 日到 4 月 15 日的次要覆盖，以及从 5 月 1 日开始的第三方覆盖。现有系统生成重复的索赔拒绝，因为它将这些视为单独的活跃期，而不是从 1 月 1 日到 4 月 15 日的一个连续覆盖区块，然后是 5 月的延续。业务需要一个合并视图，以强制执行“无重复支付”规则，同时保留原始政策记录的审计轨迹。

解决方案 1: 基于游标的过程迭代。 初步提案使用一个按 start_date 排序的游标的存储过程，维护变量 @current_start 和 @current_end。对于每一行，如果 start_date ≤ @current_end，代码就将 @current_end 更新为 max(@current_end, end_date)；否则，它会发出当前范围，并重置变量。优点：对具有命令式背景的开发人员来说概念上简单；易于逐步调试。缺点：需要 PL/pgSQL 或 T-SQL 过程扩展；逐行执行具有 O(n) 内存但较差的 I/O 性能；违反了可以在任何兼容引擎上运行的纯声明性 ANSI SQL 要求。

解决方案 2: 自连接和传递闭包检测。 另一种方法执行自连接 t1 JOIN t2 ON t1.start_date <= t2.end_date AND t1.end_date >= t2.start_date 以寻找直接重叠，然后使用递归 CTE 来遍历重叠图并识别连接成分。优点：理论上正确处理复杂的传递关系，不需要窗口函数。缺点：在递归之前生成 O(n²) 中间行；对于大型数据集计算膨胀；依赖递归 CTE，尽管它是 ANSI SQL 标准，但在这个特定的线性排序问题上性能较差。

解决方案 3: 窗口函数间隙检测（选择）。 团队实施了一种纯窗口函数解决方案：标记 is_new_island = CASE WHEN start_date > MAX(end_date) OVER (PARTITION BY patient_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 END，然后计算 island_id = SUM(is_new_island) OVER (PARTITION BY patient_id ORDER BY start_date)。最终聚合按 patient_id, island_id 分组。优点：使用 ANSI SQL 标准语法进行单通道执行；O(n log n) 复杂度主要受排序限制；通过运行最大值隐式处理传递重叠。缺点：需要小心处理 NULL 结束日期（无限覆盖）和相邻区间语义（是否合并接触的区间）。

结果。 部署将 230 万政策记录合并为 89 万个连续覆盖块，在标准硬件上不到 12 秒，取代了 45 分钟的基于游标的批处理作业。查询被封装为视图，实现了实时资格检查，并在随后的季度中消除了 99% 的重复索赔拒绝。

WITH coverage_flags AS (
    SELECT patient_id, start_date, end_date,
           CASE 
             WHEN start_date > MAX(end_date) OVER (
                 PARTITION BY patient_id 
                 ORDER BY start_date 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ) THEN 1 
             ELSE 0 
           END AS is_new_island
    FROM insurance_periods
),
islands AS (
    SELECT patient_id, start_date, end_date,
           SUM(is_new_island) OVER (
               PARTITION BY patient_id 
               ORDER BY start_date
           ) AS island_id
    FROM coverage_flags
)
SELECT patient_id, 
       MIN(start_date) AS consolidated_start,
       MAX(end_date) AS consolidated_end
FROM islands
GROUP BY patient_id, island_id;

候选人常常遗漏的内容

您如何处理在端点接触的相邻区间，例如 [1 月 1-10 日] 和 [1 月 11-20 日]，需要什么谓词修改？

候选人通常使用严格不等式 start_date > previous_end_date，这将相邻区间视为独立岛屿。对于医疗覆盖或连续调度，相邻期通常表示不间断服务，应该合并。谓词必须适应区间类型：对于闭区间（起始和结束均包含），使用 start_date > previous_end_date + INTERVAL '1' DAY。对于半开区间 [start, end)（结束是排他的），条件 start_date > previous_end_date 自然合并相邻部分，因为 1 月 11 日等于 1 月 11 日。ANSI SQL 直接支持区间算术，因此解决方案需要 CASE WHEN start_date > MAX(end_date) OVER (...) + INTERVAL '1' DAY THEN 1 ELSE 0 END。

为什么 MAX(end_date) 窗口函数在输入中包含表示无限覆盖的 NULL 值时会产生不正确的岛屿边界？

ANSI SQL 聚合窗口函数如 MAX() 在帧中忽略 NULL 值。如果一个政策没有结束日期（NULL 表示“当前”），在前面的行中 MAX(end_date) 返回最新的非 NULL 日期，这可能会合并随后应该在无限间隙后开始的新岛屿的间隔。候选人必须认识到 NULL 需要特殊处理：要么在初步 CTE 中将其过滤掉，或者使用 COALESCE(end_date, DATE '9999-12-31') 将无限覆盖视为延续到无限。或者，通过使用 CASE WHEN end_date IS NULL THEN 0 ELSE 1 END 逻辑将 NULL 视为被强制中断，确保下一行开始一个新岛屿。

您如何将此逻辑扩展到多维打包，例如在不失去原子性的情况下分别合并每个患者 ID 和保险类型的区间？

许多候选人尝试手动分区的子查询或自连接。正确的方法是在 ANSI SQL 窗口函数中利用 PARTITION BY 子句。在 MAX(end_date) 和 SUM(is_new_island) 计算中将帧说明修改为 PARTITION BY patient_id, insurance_type。这确保运行最大值和岛屿 ID 计数器在每个不同组中重置，保持跨分区的 O(n log n) 性能。如果未正确分区，可能导致一个患者的时间线中的间隙错误地触发另一个患者的新岛屿，从而破坏了合并逻辑。