PostgreSQL中按int8range重叠范围分组行的SQL方案问询

原SQL仅通过与前一个范围比较判断分组，且仅考虑了重叠（&&操作符）场景，未覆盖相邻情况，同时无法处理跨多个范围的间接连接（比如当前范围与更早的范围重叠/相邻，但与直接前一个范围无关联）的情况。

以下是正确的实现方案：

WITH sorted_ranges AS (
    SELECT
        range,
        lower(range) AS start_val,
        upper(range) AS end_val
    FROM t
    ORDER BY start_val
)
SELECT
    range,
    SUM(
        CASE
            WHEN start_val > COALESCE(MAX(end_val) OVER (ORDER BY start_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), -INFINITY)
            THEN 1
            ELSE 0
        END
    ) OVER (ORDER BY start_val) AS group_id
FROM sorted_ranges;

逻辑说明 #

1. 排序范围：先按范围的起始值排序，确保从左到右依次处理每个区间。
2. 判断新分组：对每个范围，计算此前所有已处理范围的最大结束值。如果当前范围的起始值大于该最大值，说明与之前所有范围都不重叠也不相邻，标记为新分组（值为1）；否则标记为0，归入当前分组。
3. 生成分组ID：累加上述标记值，每遇到新分组标记就递增分组ID，最终得到每个范围对应的group_id。

该方案可正确处理所有场景：

• 直接重叠的范围（如(10,20)与(15,25)）
• 相邻的范围（如(20,30)与(15,25)）
• 间接连接的范围（如(1,5)→(5,7)→(7,10)，三者连续相邻归为同一组）

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