PostgreSQL14递归查询目录树 递归计算分类平均成本

核心逻辑说明：
分类的平均成本是其下所有层级叶子商品的算术平均，不能通过子分类的平均值二次计算（会出现权重错误），因此通过两层递归CTE实现：

• 第一层递归先圈定查询范围：取出传入ID对应的节点，以及它的所有后代节点
• 第二层递归做自底向上的成本归集：从叶子商品节点开始，把每个商品的成本、计数逐层向上传递给所有祖先分类，最终聚合计算每个分类的平均成本，无商品的分类自动返回null。

可直接使用的SQL代码 #

WITH RECURSIVE scope AS (
    -- 锚点：匹配传入的入口节点
    SELECT id, parent, name, type, cost
    FROM items
    WHERE id = $item_id
    UNION ALL
    -- 递归：拉取所有后代节点
    SELECT it.id, it.parent, it.name, it.type, it.cost
    FROM items it
    INNER JOIN scope s ON s.id = it.parent
),
cost_agg AS (
    -- 锚点：范围内所有商品节点，初始化自身成本和商品计数
    SELECT
        id,
        cost AS total_cost,
        1 AS item_count
    FROM scope
    WHERE type = 'item'
    UNION ALL
    -- 递归：将当前节点的成本、计数向上传递给直接父分类
    SELECT
        s.parent AS id,
        ca.total_cost,
        ca.item_count
    FROM cost_agg ca
    INNER JOIN scope s ON s.id = ca.id
    WHERE s.parent IS NOT NULL
)
-- 关联基础信息，计算最终输出
SELECT
    s.id,
    s.parent,
    s.name,
    s.type,
    CASE
        WHEN s.type = 'item' THEN s.cost
        ELSE ROUND(AVG(ca.total_cost::numeric / ca.item_count))::int
    END AS cost
FROM scope s
LEFT JOIN cost_agg ca ON s.id = ca.id
GROUP BY s.id, s.parent, s.name, s.type, s.cost;

逻辑验证 #

1. 传入uuid3时：
   • 范围节点为uuid3/uuid4/uuid5
   • 叶子商品uuid4(200)、uuid5(300)向上传递成本，uuid3聚合后平均成本为(200+300)/2=250，与期望结果一致
2. 传入uuid1时：
   • 范围节点为全部5条记录
   • 叶子商品uuid2(100)直接传递给uuid1，uuid4(200)、uuid5(300)先传递给uuid3（计算得平均250），再向上传递给uuid1，uuid1聚合三个商品成本后平均为(100+200+300)/3=200，与期望结果一致
3. 传入商品ID时，范围仅包含商品自身，直接返回商品原有cost，符合需求；分类下无任何商品时，左连接无匹配数据，平均计算返回null，符合要求。

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