如何使用PostgreSQL函数合并重叠的日期范围？

当然可以完全通过PostgreSQL的内置功能搞定这个日期范围合并的需求！不用额外写复杂的自定义函数，纯SQL就能实现。下面我给你一步步拆解实现方法：

核心思路 #

要合并重叠或有交集的日期范围，关键是先把所有范围按开始日期排序，然后用窗口函数识别出哪些属于同一组（需要合并的范围），最后对每组聚合得到合并后的结果。

具体实现代码 #

假设我们先把你的测试数据用CTE（公共表表达式）构造出来，然后执行合并逻辑：

WITH date_ranges AS (
    -- 模拟你的原始日期范围数据
    SELECT '2017-01-01'::DATE AS start_date, '2017-01-31'::DATE AS end_date
    UNION ALL
    SELECT '2017-01-04'::DATE, '2017-02-20'::DATE
    UNION ALL
    SELECT '2017-02-21'::DATE, '2017-03-29'::DATE
    UNION ALL
    SELECT '2017-03-17'::DATE, '2017-04-12'::DATE
),
-- 第一步：给需要合并的范围标记分组
grouped_ranges AS (
    SELECT
        start_date,
        end_date,
        -- 当当前范围的开始日期晚于之前所有范围的最大结束日期时，新建一个分组
        SUM(
            CASE 
                WHEN start_date > MAX(end_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
                THEN 1 
                ELSE 0 
            END
        ) OVER (ORDER BY start_date) AS group_id
    FROM date_ranges
)
-- 第二步：按分组聚合，得到最终合并结果
SELECT
    MIN(start_date) AS merged_start_date,
    MAX(end_date) AS merged_end_date
FROM grouped_ranges
GROUP BY group_id
ORDER BY merged_start_date;

代码逻辑解释 #

1. date_ranges CTE：把你的原始日期字符串转换成PostgreSQL的DATE类型，作为我们的数据源。如果你的数据已经存在表中，直接替换成表名即可。
2. grouped_ranges CTE：
   • 先用ORDER BY start_date把所有范围按开始日期排序。
   • 用窗口函数MAX(end_date) OVER (...)获取当前行之前所有范围的最大结束日期。
   • 通过CASE判断当前范围是否和之前的范围不重叠/不连续：如果当前开始日期大于之前的最大结束日期，说明这是一个新的独立分组，标记为1，否则标记为0。
   • 最后用SUM(...) OVER (...)累加这个标记，得到每个范围的分组ID，同一组的范围就是需要合并的。
3. 最终聚合：对每个分组取最小的开始日期和最大的结束日期，就是合并后的完整范围了。

执行结果 #

运行上面的代码后，你会得到正好符合需求的结果：

扩展说明 #

如果你的需求变成要合并连续的日期范围（比如把2017-02-20和2017-02-21合并成一个范围），只需要把CASE里的判断条件改成：

WHEN start_date > (MAX(end_date) + INTERVAL '1 day')

或者更简洁的PostgreSQL日期写法：

WHEN start_date > (MAX(end_date) + 1)

内容的提问来源于stack exchange，提问作者Bala susmitha Vinjamuri