PostgreSQL仅合并重叠int8range，忽略相邻范围的实现方案

要实现只合并重叠的int8range范围、忽略相邻范围的需求，PostgreSQL内置的range_agg()函数无法直接满足（它会同时合并相邻和重叠的范围），可以通过以下步骤实现：

解决思路 #

1. 标准化范围：先处理无效的反向范围（比如[30,25)这类起始大于结束的范围），转为有效的左闭右开[)格式，并排除空范围。
2. 分组标记：通过窗口函数判断每个范围是否需要开启新的分组——当当前范围的起始值大于等于上一个范围的结束值时（包括相邻或完全不重叠的情况），标记为新组。
3. 按组合并：对每个分组内的范围取最小起始值和最大结束值，合并为一个范围。

完整SQL代码 #

-- 创建测试表并插入数据
CREATE TABLE ranges (range int8range);
INSERT INTO ranges VALUES
('[10,20)'::int8range),
('[15,25)'::int8range),
('[30,25)'::int8range),
('[40,50)'::int8range),
('[50,70)'::int8range),
('[60,80)'::int8range),
('[80,100)'::int8range);

-- 执行合并逻辑
WITH normalized_ranges AS (
    -- 标准化范围：处理反向范围，转为有效左闭右开格式，排除空范围
    SELECT 
        int8range(least(lower(range), upper(range)), greatest(lower(range), upper(range)), '[)') AS r
    FROM ranges
    WHERE range <> 'empty'::int8range
),
sorted_ranges AS (
    -- 排序并标记新组：相邻或不重叠的范围开启新组
    SELECT 
        r,
        lower(r) AS range_start,
        upper(r) AS range_end,
        CASE 
            WHEN lag(upper(r)) OVER (ORDER BY lower(r)) IS NULL THEN 1 -- 第一个范围标记为新组
            WHEN lower(r) >= lag(upper(r)) OVER (ORDER BY lower(r)) THEN 1
            ELSE 0 
        END AS is_new_group
    FROM normalized_ranges
),
grouped_ranges AS (
    -- 累加标记值，生成分组ID
    SELECT 
        r,
        range_start,
        range_end,
        SUM(is_new_group) OVER (ORDER BY range_start) AS group_id
    FROM sorted_ranges
)
-- 按组合并重叠范围
SELECT int8range(min(range_start), max(range_end), '[)') AS merged_range
FROM grouped_ranges
GROUP BY group_id
ORDER BY merged_range;

执行结果说明 #

• 原数据中的[30,25)会被标准化为[25,30)，由于它和[15,25)是相邻关系（起始值等于上一个范围的结束值），因此不会被合并。
• 若原数据中的[30,25)是[20,30)的笔误，标准化后它会和[15,25)重叠，最终合并结果会是[10,30)，与你的预期输出一致。
• range_agg()之所以不适用，是因为它内部调用range_merge()函数，该函数会将相邻范围视为可合并的（比如[40,50)和[50,70)会被合并为[40,70)），不符合仅合并重叠范围的需求。

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