如何使用Postgres SQL计算数组差异并填充表中DIFF列

假设你的表名为entity_diff_calc，Entity字段为逗号分隔的字符串类型，可直接执行以下SQL完成需求：

实现SQL #

WITH prd_entity_set AS (
    -- 先提取PRD的所有Entity集合，转成数组格式
    SELECT string_to_array(Entity, ',') AS prd_arr
    FROM 你的实际表名
    WHERE SOURCE = 'PRD'
)
UPDATE 你的实际表名 t
SET DIFF = CASE
    WHEN SOURCE = 'PRD' THEN NULL
    ELSE array_to_string(
        ARRAY(
            -- 求PRD集合和当前行Entity的差集
            SELECT unnest(pe.prd_arr)
            EXCEPT
            SELECT unnest(string_to_array(t.Entity, ','))
        ), ','
    )
END
FROM prd_entity_set pe;

逻辑说明 #

• CTE部分单独提取PRD行的Entity值，转成数组方便集合运算
• 非PRD行先将自身Entity转成数组，用EXCEPT关键字求两个数组的差集，再转回逗号分隔字符串写入DIFF字段
• PRD行直接赋值为空，符合需求

结果验证 #

执行完更新后可执行以下查询校验结果：

SELECT SOURCE, Entity, DIFF FROM 你的实际表名;

返回结果和预期一致：

注意事项 #

使用时将SQL中的你的实际表名替换为你自己的表名即可。如果Entity元素间存在空格，可在unnest后加trim()函数处理避免匹配误差。

内容的提问来源于stack exchange，提问作者Bashi Wane Mwale