PostgreSQL 如何按名称和原始顺序合并连续索引范围？

2026-11-04数据小许

结论

先按 WITH ORDINALITY 生成的原始行号排序，再用 LAG 检查当前名称与上一行是否不同。对变化标记累计求和，就能给每段连续记录分配组号。最后按组取索引的最小值和最大值。

应比较相邻行。按 name 排序会打乱输入顺序，也会合并原本分开的同名记录。IS DISTINCT FROM 可以正确比较包含 NULL 的名称。

查询

WITH dat AS (
  SELECT name, idx
  FROM unnest(ARRAY[
    NULL, NULL,
    'foo', 'foo', 'foo',
    NULL, NULL, NULL,
    'bar', 'bar',
    NULL,
    'foo', 'foo'
  ]) WITH ORDINALITY AS _(name, idx)
), marked AS (
  SELECT
    name,
    idx,
    CASE
      WHEN name IS DISTINCT FROM LAG(name) OVER (ORDER BY idx) THEN 1
      ELSE 0
    END AS starts_group
  FROM dat
), grouped AS (
  SELECT
    name,
    idx,
    SUM(starts_group) OVER (
      ORDER BY idx
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS group_id
  FROM marked
)
SELECT
  name,
  CASE
    WHEN MIN(idx) = MAX(idx) THEN MIN(idx)::text
    ELSE MIN(idx)::text || '-' || MAX(idx)::text
  END AS index_range
FROM grouped
GROUP BY group_id, name
ORDER BY MIN(idx);

结果中的范围按示例格式显示为 1-2、3-5；只有一个索引时显示为 11。每段连续记录都有自己的 group_id，因此后面再次出现的 foo 会形成新组，连续出现的 NULL 则归为一组。

如果需要 PostgreSQL range 类型

如果下游需要可计算的范围值，可以将选择表达式替换为：

int8range(MIN(idx), MAX(idx) + 1, '[)') AS index_range

这种左闭右开的表示法会将索引 1 到 2 表示为 [1,3)。PostgreSQL 的 range 上界不包含在范围内。

注意事项

idx 来自 WITH ORDINALITY，表示输入行的顺序。如果实际数据来自表，应使用能够确定顺序的列替代它。没有稳定顺序，连续分组的结果也无法稳定确定。文本形式的 index_range 适合展示；进行范围运算时，使用 int8range 结果更合适。