PostgreSQL中如何为字段值互换的记录分配相同组编号

实现思路 #

对每行的Ref和Comp字段做值大小排序，互换关系的两行会生成完全一致的排序后组合，再基于该组合分配相同组编号即可。

实现代码 #

临时查询（直接输出结果） #

假设你的原表名为rel_pairs，执行以下查询即可得到目标输出：

SELECT
    DENSE_RANK() OVER(ORDER BY LEAST(Ref, Comp), GREATEST(Ref, Comp)) AS "GROUP",
    Ref AS REF,
    Comp AS COMP
FROM rel_pairs;

持久化到表结构 #

如果需要把组编号作为固定列存入原表，执行以下两步操作：

1. 新增组编号字段

ALTER TABLE rel_pairs ADD COLUMN "GROUP" INT;

2. 批量更新组编号值

WITH pair_group_mapping AS (
    SELECT
        Ref,
        Comp,
        DENSE_RANK() OVER(ORDER BY LEAST(Ref, Comp), GREATEST(Ref, Comp)) AS group_num
    FROM rel_pairs
)
UPDATE rel_pairs rp
SET "GROUP" = pgm.group_num
FROM pair_group_mapping pgm
WHERE rp.Ref = pgm.Ref AND rp.Comp = pgm.Comp;

验证查询 #

执行以下语句即可查看最终结果：

SELECT "GROUP", REF, COMP FROM rel_pairs;

输出效果：

GROUP | REF | COMP
-------+-----+------
     1 | A   | B
     1 | B   | A

注意事项 #

• GROUP为SQL保留关键字，引用时必须加双引号，实际使用建议替换为group_id等非关键字字段名避免歧义
• 若表中存在重复的(Ref, Comp)记录，该逻辑依然生效，相同组合会被分配到同一组
• 若不需要连续组编号，可将DENSE_RANK替换为RANK函数

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