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 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;
entityId | parentId | emission | percentContribution | cumulativeSum |
---|---|---|---|---|
E1 | E2 | 10 | 80 | 10.0 |
E2 | E3 | 20 | 80 | 28.0 |
E3 | NULL | 30 | null | 52.4 |