自底向上树结构带权重节点累计和计算问题排查

表结构与数据 #

我们有如下树结构的Emissions表：

CREATE TABLE Emissions (
    entityId    VARCHAR(512),
    parentId    VARCHAR(512),
    emission    DECIMAL,
    percentContribution VARCHAR(512)
);

INSERT INTO Emissions (entityId, parentId, emission, percentContribution) VALUES
    ('E1', 'E2', '541', '85'),
    ('E2', 'E4', '272', '85'),
    ('E3', 'E4', '270', '80'),
    ('E4', 'NULL', '362', NULL);

表数据展示：

需求说明 #

计算每个节点的累计排放量，规则为：

• 叶子节点（无下属子节点）的累计值 = 自身排放量
• 非叶子节点的累计值 = 自身排放量 + 所有子节点的累计值 × 子节点的贡献百分比（转为小数）

具体计算公式：

累计总和(E1) = 541（叶子节点）
累计总和(E2) = 272 + (541 × 85/100) = 731.85 → 取整为732
累计总和(E3) = 270（叶子节点）
累计总和(E4) = 362 + (732 × 85/100) + (270 × 80/100) = 1213.7 → 取整为1214

现有代码问题 #

原SQL递归时，父节点会与每个子节点单独关联计算，导致根节点（如E4）的自身排放量被重复累加，最终结果偏大（实际得到1576，远高于预期的1214）。

原代码：

WITH RECURSIVE HierarchyCTE(entityid, parentid, emission, total_emission, percentage, level) AS (
    SELECT
        entityid,
        parentid,
        emission,
        emission AS total_emission,
        percentageContribution,
        0
    FROM
        tree_table
    WHERE
        entityid NOT IN (SELECT DISTINCT parentid FROM tree_table WHERE parentid IS NOT NULL)

    UNION ALL

    SELECT
        h.entityid,
        h.parentid,
        h.emission,
        h.emission + cte.total_emission * (cte.percentageContribution/100) AS total_emission,
        h.percentageContribution,
        cte.level + 1
    FROM
        tree_table h
    JOIN
        HierarchyCTE cte ON h.entityid = cte.parentid
    
)
SELECT
    entityid,
    parentid,
    SUM(total_emission) AS total_emission
FROM
    (
        SELECT
            entityid,
            parentid,
            SUM(total_emission) AS total_emission,
            level
        FROM    
            HierarchyCTE
        GROUP BY
            entityid,
            parentid,
            level
    )
GROUP BY
    entityid,
    parentid
ORDER BY
    entityid

修正后的SQL代码 #

WITH RECURSIVE HierarchyCTE AS (
    -- 初始化：选取所有叶子节点（无子节点的节点）
    SELECT
        entityId,
        parentId,
        emission,
        emission AS cumulative_total,
        -- 转换贡献比为小数权重，处理NULL值
        CASE WHEN percentContribution IS NOT NULL THEN percentContribution::DECIMAL / 100 ELSE 0 END AS weight,
        0 AS level
    FROM Emissions
    WHERE entityId NOT IN (SELECT DISTINCT parentId FROM Emissions WHERE parentId IS NOT NULL)
    
    UNION ALL
    
    -- 递归向上计算父节点的累计值
    SELECT
        e.entityId,
        e.parentId,
        e.emission,
        -- 父节点自身排放量 + 所有子节点的加权贡献之和
        e.emission + SUM(cte.cumulative_total * cte.weight) OVER (PARTITION BY e.entityId) AS cumulative_total,
        CASE WHEN e.percentContribution IS NOT NULL THEN e.percentContribution::DECIMAL / 100 ELSE 0 END AS weight,
        cte.level + 1 AS level
    FROM Emissions e
    JOIN HierarchyCTE cte ON e.entityId = cte.parentId
),
-- 筛选每个节点的最终计算结果（取递归层级最高的记录）
FinalResults AS (
    SELECT
        entityId,
        parentId,
        cumulative_total,
        ROW_NUMBER() OVER (PARTITION BY entityId ORDER BY level DESC) AS rn
    FROM HierarchyCTE
)
SELECT
    entityId,
    parentId,
    ROUND(cumulative_total) AS cumulative_total
FROM FinalResults
WHERE rn = 1
ORDER BY entityId;

最终结果 #

运行修正后的代码，得到符合预期的结果：

修正说明 #

1. 递归逻辑优化：使用SUM() OVER (PARTITION BY e.entityId)一次性计算所有子节点的加权贡献总和，避免父节点重复累加自身排放量。
2. 数据类型处理：将percentContribution转换为DECIMAL并转为小数权重，同时处理NULL值，确保计算精度。
3. 结果去重：通过ROW_NUMBER()按递归层级降序排序，选取每个节点的最终计算结果（最深层级的记录），避免同一节点出现多条计算记录。

内容的提问来源于stack exchange，提问作者Ngo Chi Binh