基于时间序列间隔按行生成新UUID的SQL实现方案

不用写复杂的自定义循环逻辑，用窗口函数批量回填存量数据+触发器处理增量数据的组合方案即可，逻辑简单性能高，完全满足你提出的三个规则要求。

1. 存量数据批量填充 #

你已经掌握新增字段的操作，新增完uuid类型的Period ID 2字段后，直接用下面的SQL一次性回填所有历史数据：

WITH period_groups AS (
    SELECT
        ctid, -- 如果表有自定义主键，把ctid替换成你的主键字段即可
        SUM(is_new_group) OVER (ORDER BY "Period ID", "Created At") AS group_seq
    FROM (
        SELECT
            ctid,
            "Period ID",
            "Created At",
            CASE WHEN
                -- 三种场景需要生成新的Period ID 2
                -- 1. 当前是第一条记录
                LAG("Created At") OVER (ORDER BY "Period ID", "Created At") IS NULL
                -- 2. 切换到了新的Period ID分组
                OR LAG("Period ID") OVER (ORDER BY "Period ID", "Created At") != "Period ID"
                -- 3. 同组内和上一条记录时间差超过10分钟（600秒）
                OR EXTRACT(EPOCH FROM ("Created At" - LAG("Created At") OVER (ORDER BY "Period ID", "Created At"))) > 600
            THEN 1 ELSE 0 END AS is_new_group
        FROM table1
    ) t
)
UPDATE table1
SET "Period ID 2" = gen_random_uuid()
FROM period_groups
WHERE table1.ctid = period_groups.ctid;

逻辑校验 #

• 第一条记录会命中「首行判断」条件，自动生成第一个UUID，满足首行填充要求
• 只要Period ID发生变化就会触发新分组标记，不同Period ID对应的Period ID 2绝对不重复
• 同组内相邻记录时间差超过10分钟就会触发新ID生成，间隔10分钟内的记录会被分到同一组复用UUID，和你给出的B组分段示例完全匹配

2. 增量数据自动赋值 #

存量数据刷完后，用触发器处理后续新增的数据即可，不需要每次手动计算：

CREATE OR REPLACE FUNCTION set_period_id2()
RETURNS TRIGGER AS $$
DECLARE
    last_record record;
BEGIN
    -- 查询同Period ID下最新的一条已存在记录，加行锁避免并发插入冲突
    SELECT "Created At", "Period ID 2"
    INTO last_record
    FROM table1
    WHERE "Period ID" = NEW."Period ID"
    ORDER BY "Created At" DESC
    LIMIT 1
    FOR UPDATE;

    IF NOT FOUND OR EXTRACT(EPOCH FROM (NEW."Created At" - last_record."Created At")) > 600 THEN
        -- 无同组历史记录/时间差超10分钟，生成新UUID
        NEW."Period ID 2" = gen_random_uuid();
    ELSE
        -- 时间差在10分钟内，复用已有ID
        NEW."Period ID 2" = last_record."Period ID 2";
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定前置插入触发器
CREATE TRIGGER trg_set_period_id2
BEFORE INSERT ON table1
FOR EACH ROW
EXECUTE FUNCTION set_period_id2();

注意事项 #

• 如果表数据量超过百万级，批量更新时建议按Period ID分批次执行，避免长事务锁表影响业务
• 如果存在更新Created At或者Period ID字段的场景，可以额外加一个UPDATE触发器，重新触发ID计算逻辑即可
• PostgreSQL 12及以下版本没有内置gen_random_uuid()函数，需要先执行CREATE EXTENSION IF NOT EXISTS "uuid-ossp";启用扩展，再将SQL里的UUID生成函数替换为uuid_generate_v4()

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