含DateFrom与DateTo的记录：日期范围合并优化需求

问题描述 #

我有一张包含Name、DateFrom、DateTo、Role字段的数据表，原始数据如下：

需要将同一Name和Role下的重叠或连续日期范围合并，得到不重叠的结果集：

解决方案 #

可以通过SQL窗口函数实现日期范围的合并，核心思路是先为每组重叠的日期分配同一组ID，再按组聚合得到合并后的日期范围：

WITH ranked_dates AS (
    SELECT 
        Name,
        DateFrom,
        DateTo,
        Role,
        -- 生成组ID：当前记录的开始日期不晚于上一条的结束日期时，归为同一组
        SUM(CASE WHEN DateFrom <= LAG(DateTo) OVER (PARTITION BY Name, Role ORDER BY DateFrom) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Name, Role ORDER BY DateFrom) AS group_id
    FROM your_table_name
)
SELECT 
    Name,
    MIN(DateFrom) AS DateFrom,
    MAX(DateTo) AS DateTo,
    Role
FROM ranked_dates
GROUP BY Name, Role, group_id
ORDER BY Name, Role, DateFrom;

代码说明 #

1. CTE ranked_dates：
   • 按Name和Role分区，确保只处理同一人同一角色的记录；
   • 用LAG(DateTo)获取同组内上一条记录的结束日期，对比当前记录的DateFrom；
   • 通过SUM()累加分组标识，将重叠/连续的日期分配到同一group_id。
2. 聚合查询：
   • 按Name、Role、group_id分组，取每组的最小DateFrom和最大DateTo，得到合并后的完整日期范围。

该方案适用于支持窗口函数的SQL数据库（如PostgreSQL、MySQL 8.0+、SQL Server等），替换your_table_name为实际表名即可执行。

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