sqlmysqlhierarchical-datacumulative-sum

Weighted hierarchical cumulative sum


I don't know if I'm using the correct terminology, but I'm trying to find the cumulative sum of each node in a tree structure, but the children node only contribute a percentage of its cumulative sum to its parent node's cumulative sum.

Emission(E1) = Emission(E1)
Emission(E2) = Emission(E2) + percent_Contribution(E1) * Emission(E1)
Emissions(E3) = Emission(E3) + percent_Contribution(E2) * Emission(E2)

This a sample of the result I want to achieve:

entityId parentId emission percentContribution cumulativeSum
E1 E2 10 80 10
E2 E3 20 80 20 + (10 * 80 / 100) = 28
E3 NULL 30 NULL 30 + (28 * 80 / 100) = 52.4

Note that this is a sample of the result I'm trying to achieve, as my actual table can have many more levels of hierarchy.

I'm unfamiliar with SQL, but my research tells me that I should right a recursive CTE, but I haven't been able to get it to work with the weighted value. If it's impossible to do with pure SQL, I would also appreciated being point toward the right direction.

Edited Table in case of parent having multiple child records:

entityId parentId emission percentContribution cumulativeSum
E1 E2 10 80 10
E4 E2 20 60 20
E2 E3 20 80 20 + (10 * 80 / 100) + (20 * 60 / 100)= 40
E3 NULL 30 NULL 30 + (40 * 80 / 100) = 62

Solution

  • Solution with new sorting

    WITH RECURSIVE
    cte AS (
      SELECT entityId, parentId, emission, percentContribution,  CAST(emission as DECIMAL(3,1)) as cumulativeSum
      FROM Emissions WHERE entityId='E1'  
      UNION ALL
      SELECT e.entityId, e.parentId, e.emission, e.percentContribution,  e.emission + (c.cumulativeSum * c.percentContribution /100) 
      FROM cte c
      INNER JOIN Emissions e ON e.entityId=c.parentId
      WHERE c.parentId is not null
    )
    SELECT * FROM cte ORDER BY entityId;
    

    fiddle

    entityId parentId emission percentContribution cumulativeSum
    E1 E2 10 80 10.0
    E2 E3 20 80 28.0
    E3 NULL 30 null 52.4