PostgreSQL中使用窗口函数为表中重复值长链编号

完全可以用窗口函数高效实现这个需求，这属于典型的连续相同值分组场景，是窗口函数的常规应用范围，没有超出能力边界。

核心思路 #

通过「全局行号 - 同值分组内的行号」生成一个分组标识，连续相同值的行这个标识会保持一致，再对该标识做排序即可得到递增的分组编号。

具体实现 #

假设你的表名为your_table，存储0/1的列名为original_col，表有自增主键id（若无主键，可替换为能确定行顺序的列，或用ROW_NUMBER() OVER ()生成临时行号）：

1. 验证分组结果

SELECT
  original_col,
  DENSE_RANK() OVER (ORDER BY grp) AS group_id
FROM (
  SELECT
    original_col,
    -- 计算分组标识：全局行号减去同值组内的行号
    ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY original_col ORDER BY id) AS grp
  FROM your_table
) sub;

2. 新增并填充分组列

如果需要给原表新增列并批量填充值，可执行以下SQL：

-- 新增分组列
ALTER TABLE your_table ADD COLUMN group_id INT;

-- 用窗口函数计算并更新分组编号
WITH ranked_data AS (
  SELECT
    id,
    DENSE_RANK() OVER (ORDER BY grp) AS new_group_id
  FROM (
    SELECT
      id,
      ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY original_col ORDER BY id) AS grp
    FROM your_table
  ) sub
)
UPDATE your_table t
SET group_id = rd.new_group_id
FROM ranked_data rd
WHERE t.id = rd.id;

原理说明 #

• 连续相同值的行中，全局行号和同值组内行号的增长步长一致，因此两者的差值grp会保持固定。
• 当数值发生切换时，同值组内的行号会重置为1，导致grp跳变，形成新的分组标识。
• 最后用DENSE_RANK()对grp排序，就能得到连续递增的分组编号。

性能说明 #

这个方案效率很高，PostgreSQL对窗口函数的优化非常成熟，只要排序列（如id）有索引支撑，整个计算过程几乎无需额外排序开销，可高效处理百万级以上的数据量。

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