求含percentContribution的层级累积求和SQL实现方案

数据样本 #

字段规则 #

• 若emission非空，则emission_locationbased和emission_marketbased为空；
• 若emission为空，则emission_locationbased和emission_marketbased均有值。

计算逻辑说明 #

percentContribution表示当前节点的累积排放量（含locationbased和marketbased两类）按该百分比贡献给父节点的累积求和。计算示例如下：

当前SQL（未考虑percentContribution） #

WITH base_data AS (
    SELECT 
        entityId,
        parentId,
        emission,
        emission_locationbased ,
        emissions_marketbased ,
        array_agg(entityId) OVER (ORDER BY parentId) AS arr
    FROM entity
),

cumulative_sums AS (
    SELECT
        entityId,
        parentId,
        emission,
        emission_locationbased ,
        emissions_marketbased ,
        SUM(COALESCE(emission, emission_locationbased , 0)) 
            OVER (ORDER BY arr)  AS cumsum_locationbased, 
        SUM(COALESCE(emission, emissions_marketbased , 0)) 
            OVER (ORDER BY arr) AS cumsum_marketbased  
    FROM base_data
    GROUP BY entityId, parentId, emission, emission_locationbased, emissions_marketbased, arr
)

SELECT
    entityId,
    parentId,
    emission,
    CASE WHEN emission IS NOT NULL THEN NULL ELSE emission_locationbased END AS emission_locationbased ,
    CASE WHEN emission IS NOT NULL THEN NULL ELSE emissions_marketbased END AS emissions_marketbased,
    cumsum_locationbased,
    cumsum_marketbased
FROM cumulative_sums

修正后的SQL实现 #

要处理树形结构的百分比贡献累积，需用递归CTE遍历层级，从叶子节点往上计算每个节点的累积值，再应用百分比贡献给父节点。以下是适配PostgreSQL的实现：

WITH RECURSIVE entity_hierarchy AS (
    -- 锚点：叶子节点（无下级节点），初始化自身累积值
    SELECT
        e.entityId,
        e.parentId,
        e.percentContribution,
        e.emission,
        e.emission_locationbased,
        e.emission_marketbased,
        COALESCE(e.emission, e.emission_locationbased)::numeric AS cumsum_locationbased,
        COALESCE(e.emission, e.emission_marketbased)::numeric AS cumsum_marketbased
    FROM entity e
    WHERE NOT EXISTS (SELECT 1 FROM entity child WHERE child.parentId = e.entityId)

    UNION ALL

    -- 递归：向上计算父节点累积值 = 自身排放量 + 子节点累积值*贡献百分比/100
    SELECT
        parent.entityId,
        parent.parentId,
        parent.percentContribution,
        parent.emission,
        parent.emission_locationbased,
        parent.emission_marketbased,
        COALESCE(parent.emission, parent.emission_locationbased, 0)::numeric + 
        SUM(child.cumsum_locationbased * child.percentContribution / 100) OVER (PARTITION BY parent.entityId) AS cumsum_locationbased,
        COALESCE(parent.emission, parent.emission_marketbased, 0)::numeric + 
        SUM(child.cumsum_marketbased * child.percentContribution / 100) OVER (PARTITION BY parent.entityId) AS cumsum_marketbased
    FROM entity parent
    JOIN entity_hierarchy child ON parent.entityId = child.parentId
)
-- 去重并输出最终结果
SELECT DISTINCT ON (entityId)
    entityId,
    parentId,
    percentContribution,
    emission,
    CASE WHEN emission IS NOT NULL THEN NULL ELSE emission_locationbased END AS emission_locationbased,
    CASE WHEN emission IS NOT NULL THEN NULL ELSE emission_marketbased END AS emission_marketbased,
    cumsum_locationbased,
    cumsum_marketbased
FROM entity_hierarchy
ORDER BY entityId, cumsum_locationbased DESC;

逻辑说明 #

1. 锚点部分：先筛选所有叶子节点，将自身排放量作为初始累积值（按字段规则选择对应取值）。
2. 递归部分：关联父节点与子节点的计算结果，父节点累积值由自身排放量加上所有子节点累积值按比例贡献的总和得到。
3. 最终查询：用DISTINCT ON去除递归过程中生成的重复父节点记录，按实体ID排序输出。

样本验证结果 #

运行上述SQL后，样本数据的计算结果为：

• E3：cumsum_locationbased=10，cumsum_marketbased=20
• E2：cumsum_locationbased=28，cumsum_marketbased=36
• E1：cumsum_locationbased=46.8，cumsum_marketbased=51.6

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