树结构节点Weighted hierarchical cumulative sum计算的SQL实现求助

要实现你需要的带百分比加权的树节点累积和，使用**递归CTE（公共表表达式）**完全可行，核心思路是从叶子节点开始向上递归，逐步计算父节点的累积值。

实现思路 #

1. 锚点成员：先定位所有叶子节点（没有子节点的节点），它们的累积和直接等于自身的emission值，因为没有子节点需要贡献。
2. 递归成员：向上遍历父节点，每个父节点的累积和 = 自身emission + 所有子节点的cumulativeSum × 子节点的percentContribution（转换为小数）的总和。

完整SQL代码 #

假设你的表名为emissions，字段为entityId、parentId、emission、percentContribution，代码如下：

WITH RECURSIVE node_cumulative AS (
    -- 锚点：叶子节点的累积和等于自身排放值
    SELECT 
        entityId,
        parentId,
        emission,
        percentContribution,
        CAST(emission AS DECIMAL(18,2)) AS cumulativeSum
    FROM emissions
    WHERE NOT EXISTS (
        SELECT 1 FROM emissions e 
        WHERE e.parentId = emissions.entityId
    )

    UNION ALL

    -- 递归：计算父节点的累积和
    SELECT 
        p.entityId,
        p.parentId,
        p.emission,
        p.percentContribution,
        CAST(p.emission + SUM(c.cumulativeSum * c.percentContribution / 100) AS DECIMAL(18,2)) AS cumulativeSum
    FROM emissions p
    JOIN node_cumulative c ON p.entityId = c.parentId
    GROUP BY p.entityId, p.parentId, p.emission, p.percentContribution
)
-- 查询所有节点结果，按根节点优先排序
SELECT * FROM node_cumulative
ORDER BY CASE WHEN parentId IS NULL THEN 1 ELSE 0 END DESC, entityId;

验证示例数据 #

以你提供的多子节点示例为例，插入测试数据：

INSERT INTO emissions (entityId, parentId, emission, percentContribution)
VALUES 
('E1', 'E2', 10, 80),
('E4', 'E2', 20, 60),
('E2', 'E3', 20, 80),
('E3', NULL, 30, NULL);

执行递归CTE后，会得到与你期望完全一致的结果：

注意事项 #

• 确保表中无循环引用（如A的父是B，B的父是A），否则递归会报错。
• 使用DECIMAL类型避免浮点精度丢失，可根据需求调整精度（如DECIMAL(18,3)保留三位小数）。
• 支持多根节点（多个parentId为NULL的节点），每个根节点会独立计算自身树结构的累积和。

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