PostgreSQL递归查询优化：筛选最近操作为added的活跃子服务项

要实现只保留**最后一次操作为added**的子项，核心是先确定每个父-子关联对的最新操作记录，再基于该记录的状态进行筛选，同时结合递归逻辑处理多层级的子项遍历。

第一步：基础查询（非递归场景） #

先处理最直接的父-子关联，用窗口函数ROW_NUMBER()来获取每个父-子对的最新操作记录：

WITH latest_link_actions AS (
    SELECT 
        parent_service_item,
        child_service_item,
        action_key,
        -- 按父-子分组，按时间倒序排序，最新的记录排第1位
        ROW_NUMBER() OVER (
            PARTITION BY parent_service_item, child_service_item 
            ORDER BY action_date DESC
        ) AS record_rank
    FROM service_item_links
    WHERE link_type = 'has_child'
      -- 替换为你需要查询的父ID集合
      AND parent_service_item IN (1)
)
-- 只保留最新操作是added的子项
SELECT child_service_item
FROM latest_link_actions
WHERE record_rank = 1 AND action_key = 'added';

这个查询会精准过滤掉那些最后一次操作是removed的子项——比如如果某个子项先被added后被removed，最新记录的action_key是removed，就会被排除。

第二步：整合递归逻辑（处理多层级子项） #

如果你的业务场景存在多层级关联（比如Epic → Card → Subcard），需要把上述过滤逻辑嵌入递归CTE中，遍历所有层级的子项：

WITH RECURSIVE filtered_hierarchy AS (
    -- 初始层：处理顶层父项的直接子项
    SELECT 
        l.child_service_item AS item_id,
        si.item_type
    FROM (
        SELECT 
            parent_service_item,
            child_service_item,
            ROW_NUMBER() OVER (
                PARTITION BY parent_service_item, child_service_item 
                ORDER BY action_date DESC
            ) AS record_rank
        FROM service_item_links
        WHERE link_type = 'has_child'
          -- 替换为目标父ID集合
          AND parent_service_item IN (1, 2)
    ) l
    JOIN service_items si ON l.child_service_item = si.id
    WHERE l.record_rank = 1 AND l.action_key = 'added'
    
    UNION ALL
    
    -- 递归层：以上一层的子项作为新父项，继续筛选其子项
    SELECT 
        l.child_service_item AS item_id,
        si.item_type
    FROM (
        SELECT 
            parent_service_item,
            child_service_item,
            ROW_NUMBER() OVER (
                PARTITION BY parent_service_item, child_service_item 
                ORDER BY action_date DESC
            ) AS record_rank
        FROM service_item_links
        WHERE link_type = 'has_child'
    ) l
    JOIN filtered_hierarchy fh ON l.parent_service_item = fh.item_id
    JOIN service_items si ON l.child_service_item = si.id
    WHERE l.record_rank = 1 AND l.action_key = 'added'
)
-- 去重避免同一子项通过不同路径被多次选中
SELECT DISTINCT item_id FROM filtered_hierarchy;

关键逻辑说明 #

1. 窗口函数分组排序：通过PARTITION BY parent_service_item, child_service_item确保我们针对每个父-子对单独计算最新操作，ORDER BY action_date DESC让最新的记录排在第一位。
2. 递归遍历：从顶层父项开始，每一层都只保留符合条件的子项，再以这些子项为父项继续向下遍历，确保整个层级结构中只有最后一次操作为added的节点被保留。
3. 去重处理：用DISTINCT避免同一个子项通过不同的父项路径被多次返回（比如某个子项可能被多个父项关联）。

性能优化建议 #

为了让这个查询在大数据量下高效运行，建议给service_item_links表创建复合索引：

CREATE INDEX idx_link_parent_child_date ON service_item_links (link_type, parent_service_item, child_service_item, action_date DESC);

这个索引能直接支持窗口函数的分组和排序操作，避免数据库进行全表扫描和额外排序，大幅提升查询速度。

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