PostgreSQL 新增持久化列统计同eqid出现次数及顺序调整咨询

假设你的业务表名为your_table，新增的顺位列名为eqid_seq，实现步骤如下：

1. 新增持久化列 #

ALTER TABLE your_table ADD COLUMN eqid_seq INT NOT NULL DEFAULT 0;

该列是普通物理存储列，完全持久化，不会随查询动态计算。

2. 初始化历史数据的顺位 #

默认按主键id升序作为初始排序规则，如需调整排序依据，修改ORDER BY后的字段即可：

WITH ranked_data AS (
  SELECT
    id,
    ROW_NUMBER() OVER (PARTITION BY eqid ORDER BY id ASC) AS seq
  FROM your_table
)
UPDATE your_table t
SET eqid_seq = rd.seq
FROM ranked_data rd
WHERE t.id = rd.id;

3. 配置自动维护新插入数据的顺位 #

通过触发器实现新增数据时自动生成对应eqid的最新顺位，无需手动赋值：

3.1 创建触发器函数 #

CREATE OR REPLACE FUNCTION set_eqid_seq()
RETURNS TRIGGER AS $$
BEGIN
  SELECT COALESCE(MAX(eqid_seq), 0) + 1 INTO NEW.eqid_seq
  FROM your_table
  WHERE eqid = NEW.eqid;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3.2 绑定触发器到表 #

CREATE TRIGGER trg_set_eqid_seq
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION set_eqid_seq();

4. 可选：添加唯一约束保证数据一致性 #

避免同eqid下出现重复的顺位值：

ALTER TABLE your_table ADD CONSTRAINT unique_eqid_seq UNIQUE (eqid, eqid_seq);

顺序调整操作示例 #

以你提到的eqid=4的两条记录互换顺序为例，操作如下：

-- 用临时值规避唯一约束冲突，没有加唯一约束可跳过临时值步骤
UPDATE your_table SET eqid_seq = 0 WHERE eqid = 4 AND etcd = 'abc';
UPDATE your_table SET eqid_seq = 1 WHERE eqid = 4 AND etcd = 'jkl';
UPDATE your_table SET eqid_seq = 2 WHERE eqid = 4 AND etcd = 'abc';

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