使用递归CTE结合聚合函数计算子组占比的SQL实现问题

问题背景 #

我设计了一张自引用表用于描述配料混合物，表结构如下：

需要编写递归CTE查询，计算每种基础配料在各个混合物中的占比，预期输出示例如下：

• 暂未添加混合物d的结果，因当前计算难度较高

占比计算规则示例：

尝试的SQL及问题 #

我尝试通过在CTE中关联聚合总计表实现，编写的SQL代码如下：

WITH cte AS (
    SELECT id, base_input, mass_fraction FROM
        (SELECT E.id, E.base_input, E.amount/f.total_mass AS mass_fraction
        FROM mix_table E
        JOIN (SELECT id, SUM(amount) as total_mass
             FROM mix_table
             GROUP BY id
             ) AS root_totals
        ON root_totals.id = E.id
        WHERE E.base_input IS NOT NULL) AS r
        UNION ALL
        
        SELECT b.id, base_input, mass_fraction/totals.total_mass FROM 
        (SELECT F.id, cte.base_input, cte.amount/branch_totals.total_mass AS mass_fraction
         FROM mix_table F 
         JOIN cte on F.parent_input = cte.id) as b
         JOIN (SELECT id, SUM(amount) as total_mass
              FROM mix_table
              GROUP BY id 
         ) AS branch_totals
         ON branch_totals.id = totals.id
    )

select * from cte

运行时SQL Server报错。未关联总计表的查询接近目标，但混合物C的各成分未按对应比例缩放。需要支持多层嵌套父/子关系的解决方案。

解决方案 #

步骤说明 #

1. 预计算每个混合物的总数量：通过子查询一次性算出每个id对应的总数量，避免递归中重复计算，提升性能。
2. 递归CTE结构：
   • 锚点成员：处理基础配料（原始配料不为空的记录），直接计算该配料在所属混合物中的初始占比。
   • 递归成员：处理混合物嵌套，将父级混合物的基础配料占比，乘以当前混合物中父级配料的占比，得到最终的基础配料在当前混合物中的占比。

完整SQL代码 #

-- 预计算每个混合物的总数量
WITH mix_totals AS (
    SELECT id, SUM(amount) AS total_amount
    FROM mix_table
    GROUP BY id
),
-- 递归CTE计算占比
recursive_mix AS (
    -- 锚点：基础配料的初始占比
    SELECT 
        mt.id,
        mt.原始配料 AS base_ingredient,
        CAST(mt.amount AS DECIMAL(18,8)) / m.total_amount AS fraction
    FROM mix_table mt
    JOIN mix_totals m ON mt.id = m.id
    WHERE mt.原始配料 IS NOT NULL

    UNION ALL

    -- 递归：处理嵌套混合物
    SELECT 
        parent_mix.id,
        child_mix.base_ingredient,
        CAST(parent_mix.amount AS DECIMAL(18,8)) / parent_total.total_amount * child_mix.fraction AS fraction
    FROM mix_table parent_mix
    JOIN mix_totals parent_total ON parent_mix.id = parent_total.id
    JOIN recursive_mix child_mix ON parent_mix.父级配料 = child_mix.id
    WHERE parent_mix.原始配料 IS NULL
)
-- 输出结果，按id和基础配料排序
SELECT 
    id,
    base_ingredient AS 原始配料,
    fraction AS 占比
FROM recursive_mix
ORDER BY id, base_ingredient;

代码解释 #

• mix_totals：提前计算所有混合物的总数量，为后续占比计算提供基础数据。
• 锚点成员：针对包含原始配料的记录，用当前配料数量/混合物总数量得到该配料在当前混合物中的初始占比。
• 递归成员：对于引用父级配料的混合物，将父级配料在当前混合物中的占比（父级配料数量/当前混合物总数量）乘以父级混合物中基础配料的占比，得到基础配料在当前混合物中的最终占比，自动支持多层嵌套（比如混合物d的占比会被自动计算）。

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