如何用SQL简洁计算多层嵌套分组来源的数值？

我希望通过以下表格计算z值：

基础数值表 #

组与基础项关联表 #

组与组关联表 #

人工计算过程 #

x = 1a + 0.25b = 8
y = 1a + 0.5x = 10
z = 1x + 1y = 18

但我很难找到在SQL中实现该计算的简洁方法。

我已经通过递归CTE获取了运算顺序，代码如下：

WITH RECURSIVE
    iterations AS (
        -- 先获取不依赖其他组的分组
        SELECT
            g.group
            , 1 AS iteration
        FROM
            groups AS g
            LEFT JOIN groups_groups AS gg
                ON gg.source_group = g.group
        WHERE
            gg.source_group IS NULL
        -- 向上遍历依赖关系
        UNION ALL
            SELECT
                gg.group
                , i.iteration + 1 AS iteration
            FROM
                iterations AS i
                LEFT JOIN groups_groups AS gg
                    ON gg.source_group = i.group
    )
-- 筛选每个组的最大迭代次数
SELECT DISTINCT
    i.group
    , MAX(i.iteration) OVER (PARTITION BY i.group) AS iteration
FROM
    iterations AS i
ORDER BY
    iteration

这段代码返回的运算顺序如下：

我原本计划用另一棵递归树遍历迭代过程并计算数值，但由于只能关联上一次递归的结果，计算到z时无法获取x的值。我可以尝试在每次递归中传递所有值，通过iteration + 1关联来确保循环正常结束，但这似乎并非最优方案。

是否有更简单、更简洁的解决方法？

解决方案 #

可以用递归CTE逐步计算每个组的最终值，核心思路是在递归过程中保留已计算完成的组的数值，每次迭代计算当前层级的组值：

WITH RECURSIVE
-- 第一步：合并基础项和组与项的关联，初始化最底层的组（只依赖基础项的组）
base_calculations AS (
    SELECT
        g.group,
        SUM(i.value * g.weighting) AS group_value
    FROM
        group_items g
        JOIN items i ON g.source_item = i.item
    GROUP BY
        g.group
    -- 同时加入基础项本身，方便后续引用
    UNION ALL
    SELECT
        item AS group,
        value AS group_value
    FROM
        items
),
-- 第二步：递归计算依赖其他组的组值
group_calculations AS (
    -- 起始：已计算好的底层组和基础项
    SELECT
        group,
        group_value,
        1 AS iteration
    FROM
        base_calculations
    UNION ALL
    -- 递归步骤：计算依赖其他组的当前组值
    SELECT
        gg.group,
        SUM(gc.group_value * gg.weighting) AS group_value,
        gc.iteration + 1 AS iteration
    FROM
        group_groups gg
        JOIN group_calculations gc ON gg.source_group = gc.group
    -- 确保只计算那些所有依赖项都已完成的组
    WHERE NOT EXISTS (
        SELECT 1
        FROM group_groups gg2
        WHERE gg2.group = gg.group
        AND gg2.source_group NOT IN (SELECT group FROM group_calculations)
    )
    GROUP BY
        gg.group, gc.iteration + 1
)
-- 获取每个组的最终计算值（取最大迭代次数对应的结果）
SELECT
    group,
    MAX(group_value) AS final_value
FROM
    group_calculations
WHERE group IN ('x', 'y', 'z') -- 可根据需求调整为所有组
GROUP BY
    group
ORDER BY
    group;

代码说明：

1. base_calculations：先计算只依赖基础项的组（比如x），同时把基础项(a、b)也当作"组"加入，方便后续关联计算。
2. group_calculations：递归层每次计算所有依赖项都已完成的组，通过NOT EXISTS确保当前组的所有来源组都已经被计算过，避免计算不完整的值。
3. 最后通过MAX(group_value)获取每个组的最终结果，因为递归过程中可能会有多次迭代，但最终值是最后一次迭代的结果。

这个方法不需要额外维护运算顺序的CTE，而是在递归过程中自动处理依赖关系，逻辑更紧凑，也能正确处理多依赖的情况（比如z同时依赖x和y）。

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