Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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
Tags:

2

Answers


  1. 0

    Solution with original sorting (before OP edit). Recursion starts with E3 and works backwards to E1.

    WITH RECURSIVE
cte AS (
  SELECT entityId, parentId, emission, percentContribution,  CAST(emission as DECIMAL(3,1)) as cumulativeSum
  FROM Emissions WHERE entityId='E3'  
  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 null 30 null 52.4
    E2 E1 20 80 28.0
    E3 E2 10 80 10.0
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search