递归SQL计算自关联分类表节点的子项平均价格

原递归CTE仅完成了子树节点遍历，没有建立后代商品（OFFER）和所属各级分类的映射关系，无法直接计算分类的跨层级子节点平均价格。通过递归时记录祖先路径+后代商品价格聚合两步即可实现需求：

• 递归遍历子树阶段，额外维护从根节点到当前节点的完整ID路径
• 筛选子树内所有OFFER类型节点，将其价格关联到路径上的所有分类祖先
• 按分类ID分组计算平均价格，最终关联子树节点返回结果：OFFER取自身原价，CATEGORY取聚合后的平均价

完整SQL（适配PostgreSQL，匹配给出的UUID数据场景） #

WITH RECURSIVE unit_tree AS (
    -- 锚点：查询指定起始节点
    SELECT 
        s1.id,
        s1.name,
        s1.price AS original_price,
        s1.parent_id,
        s1.type,
        0 AS level,
        ARRAY[s1.id] AS ancestor_path
    FROM shop_unit s1
    WHERE s1.id = '3fa85f64-5717-4562-b3fc-2c963f66a111' -- 替换为实际查询的节点ID

    UNION ALL

    -- 递归：逐层查询子节点，更新祖先路径
    SELECT 
        s2.id,
        s2.name,
        s2.price AS original_price,
        s2.parent_id,
        s2.type,
        ut.level + 1 AS level,
        ut.ancestor_path || s2.id AS ancestor_path
    FROM shop_unit s2
    JOIN unit_tree ut ON ut.id = s2.parent_id
),
category_avg_calc AS (
    SELECT
        unnest(ancestor_path) AS category_id,
        AVG(original_price) AS avg_price
    FROM unit_tree
    WHERE type = 'OFFER'
    GROUP BY unnest(ancestor_path)
)
SELECT
    ut.id,
    ut.name,
    ut.parent_id,
    ut.type,
    ut.level,
    CASE
        WHEN ut.type = 'CATEGORY' THEN cac.avg_price
        ELSE ut.original_price
    END AS price
FROM unit_tree ut
LEFT JOIN category_avg_calc cac ON ut.id = cac.category_id;

结果校验（匹配提供的示例数据） #

示例中子树的计算结果完全符合预期：

• 根分类3fa85f64-5717-4562-b3fc-2c963f66a111下的OFFER为价格4的Оффер 1、价格8的Оффер 2，平均价为(4+8)/2=6
• 子分类3fa85f64-5717-4562-b3fc-2c963f66a333下的OFFER只有价格8的Оффер 2，平均价为8
• 两个OFFER节点直接返回表中存储的原始价格

适配说明 #

• 如果使用MySQL 8.0+，可以用字符串拼接路径+字符串拆分函数替代PostgreSQL的数组和unnest语法，核心逻辑不变
• 如果分类下没有任何OFFER节点，返回的price为NULL，可根据业务需要用COALESCE(cac.avg_price, 0)设置默认值

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