PostgreSQL如何将指定区块内的router_index统一赋值为区块首行值

可以通过纯PostgreSQL实现该需求，具体方案如下：

需求说明：将同一区块内所有行的router_index字段值，统一设置为该区块首行对应的router_index值，参考样例如下：

前置说明 #

假设你的表中有可确定行顺序的字段（如自增主键、创建时间等，下文统一用sort_id指代），区块划分规则为：router_index不为空的行为区块首行，下一个首行之前的所有行都属于当前区块，你可以根据实际业务规则调整首行判断逻辑。

步骤1：验证更新结果（必做，避免误改数据） #

先执行以下查询，确认新的router_index值符合预期：

WITH block_markers AS (
    -- 标记区块分组，每遇到一个首行，分组编号+1
    SELECT
        *,
        COUNT(CASE WHEN router_index IS NOT NULL THEN 1 END) OVER (ORDER BY sort_id) AS block_group
    FROM 你的表名
),
block_index AS (
    -- 取每个分组的首行router_index值作为该分组统一值
    SELECT
        *,
        MAX(router_index) FILTER (WHERE router_index IS NOT NULL) OVER (PARTITION BY block_group) AS new_router_index
    FROM block_markers
)
-- 对比新旧值，确认规则正确
SELECT sort_id, router_index AS old_router_index, new_router_index
FROM block_index
ORDER BY sort_id;

步骤2：执行更新 #

确认查询结果符合预期后，执行以下更新语句：

BEGIN; -- 开启事务，出错可回滚

WITH block_markers AS (
    SELECT
        *,
        COUNT(CASE WHEN router_index IS NOT NULL THEN 1 END) OVER (ORDER BY sort_id) AS block_group
    FROM 你的表名
),
block_index AS (
    SELECT
        sort_id,
        MAX(router_index) FILTER (WHERE router_index IS NOT NULL) OVER (PARTITION BY block_group) AS new_router_index
    FROM block_markers
)
UPDATE 你的表名 t
SET router_index = b.new_router_index
FROM block_index b
WHERE t.sort_id = b.sort_id;

-- 执行完更新后再次查询确认数据正确
-- SELECT * FROM 你的表名 ORDER BY sort_id;

-- 确认无误执行COMMIT提交，有误执行ROLLBACK回滚
-- COMMIT;
-- ROLLBACK;

注意事项 #

• 请将代码中的你的表名替换为实际业务表名，sort_id替换为表中实际用来排序的字段
• 首行判断规则可根据业务调整，只需修改CASE WHEN router_index IS NOT NULL THEN 1 END的判断条件即可

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