如何在SQL中去除流转字符串里的连续重复节点？

要去掉聚合结果里的连续重复节点，核心是先过滤掉同一id_Parent_Record下连续重复的流转节点，再进行聚合。可以用窗口函数LAG()实现这个过滤逻辑：

修改后的SQL代码（PostgreSQL 13+版本） #

WITH filtered_changelog AS (
    SELECT 
        ac."id_Parent_Record",
        ac."Field_Changed",
        t."group_title",
        ac."Changed_Date"
    FROM public.api_changelog ac
    LEFT JOIN public.task_workflow_group_title t 
        ON t."task_workflow" = ac."Value_New"
    WHERE ac."Field_Changed" = 'Task_Workflow'
        AND ac."id_Parent_Record" IN ('A123', 'A124', 'A125')
    -- 过滤连续重复的节点：保留与上一条不同的记录，或分组内第一条记录
    QUALIFY t."group_title" != LAG(t."group_title") OVER (
        PARTITION BY ac."id_Parent_Record" 
        ORDER BY ac."Changed_Date" ASC
    ) OR LAG(t."group_title") OVER (
        PARTITION BY ac."id_Parent_Record" 
        ORDER BY ac."Changed_Date" ASC
    ) IS NULL
)
SELECT 
    "id_Parent_Record",
    "Field_Changed",
    STRING_AGG("group_title", ' -> ' ORDER BY "Changed_Date" ASC) AS "Transitions"
FROM filtered_changelog
GROUP BY 
    "id_Parent_Record",
    "Field_Changed";

兼容低版本PostgreSQL的写法（无QUALIFY支持） #

WITH filtered_changelog AS (
    SELECT 
        ac."id_Parent_Record",
        ac."Field_Changed",
        t."group_title",
        ac."Changed_Date",
        -- 获取上一条记录的流转节点
        LAG(t."group_title") OVER (
            PARTITION BY ac."id_Parent_Record" 
            ORDER BY ac."Changed_Date" ASC
        ) AS prev_group_title
    FROM public.api_changelog ac
    LEFT JOIN public.task_workflow_group_title t 
        ON t."task_workflow" = ac."Value_New"
    WHERE ac."Field_Changed" = 'Task_Workflow'
        AND ac."id_Parent_Record" IN ('A123', 'A124', 'A125')
)
SELECT 
    "id_Parent_Record",
    "Field_Changed",
    STRING_AGG("group_title", ' -> ' ORDER BY "Changed_Date" ASC) AS "Transitions"
FROM filtered_changelog
-- 只保留与上一条不同的记录，或分组内第一条记录
WHERE "group_title" != "prev_group_title" OR "prev_group_title" IS NULL
GROUP BY 
    "id_Parent_Record",
    "Field_Changed";

逻辑说明 #

1. 过滤阶段：通过LAG()窗口函数，按父记录ID分组、变更日期排序，对比当前节点与上一条节点是否重复，只保留不重复的节点（包括分组内第一条无前置节点的记录）
2. 聚合阶段：对过滤后的无连续重复的节点列表，再用STRING_AGG生成最终的流转字符串

这样处理后，A123的流转会自动变成Import Review -> Evidence Collection -> Legal -> Resolved，A125的连续重复节点也会被去除。

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