entityid | parentid | emission | percentContribution |
---|---|---|---|
E1 | NULL | 30 | NULL |
E2 | E1 | 20 | 80 |
E3 | E1 | 10 | 80 |
E4 | E3 | 10 | 80 |
I have the above tree structure, my goal is to calculate the cumulative sum of each node, with weighted contribution from the children node.
I first tried to write a bottom up hierarchical cumulative sum query without taking percentContribution into account first, where the query will traverse the tree from the leaf node up:
WITH RECURSIVE C(id, Amount, RootID,tenantid) AS (
SELECT
entityid AS id,
emissioni AS Amount,
entityid AS RootID ,
tenantid
FROM tree_table
WHERE entityId NOT IN (
SELECT DISTINCT parentId FROM tree_table WHERE parentId IS NOT NULL)
UNION ALL
SELECT
T.parentid AS id,
C.Amount + T.emission AS Amount,
C.RootID,
C.tenantid
FROM tree_table AS T
INNER JOIN C ON T.entityid = C.id AND T.tenantid = C.tenantid
)
SELECT
T.entityid AS entityid,
T.parentid,
T.emission,
S.AmountIncludingChildren AS total_carbonAmount_marketbased,
T.name,
T.category,
T.scope,
T.tenantid,
T.activityid
FROM joined_nuoa_table AS T
INNER JOIN (
SELECT id, SUM(Amount) AS AmountIncludingChildren
FROM C
GROUP BY id
) AS S ON T.entityId = S.id
ORDER BY T.entityId
Expected result:
entityid | parentid | emission | cumulative_total |
---|---|---|---|
E1 | E2 | 541 | 541 |
E2 | E4 | 272 | 541 + 272 = 813 |
E3 | E4 | 270 | 270 |
E4 | NULL | 362 | 362 + 813 + 270 = 1445 |
Actual result:
entityid | parentid | emission | cumulative_total |
---|---|---|---|
E1 | E2 | 541 | 541 |
E2 | E4 | 272 | 1082 |
E3 | E4 | 270 | 270 |
E4 | NULL | 362 | 1894 |
I’m not too familiar with bottom-up traversal, so I’m not entire sure what went wrong.
Expected result after taking into account percentContribution
entityid | parentid | emission | percentContribution | cumulative_total |
---|---|---|---|---|
E1 | E2 | 541 | 85 | 541 |
E2 | E4 | 272 | 85 | 272 + (541 * 85 / 100) = 731.85 round to 732 |
E3 | E4 | 270 | 85 | 270 |
E4 | NULL | 362 | NULL | 362 + (732 * 85 / 100) + (270 * 85 / 100) = 1213.7 round to 1214 |
Ngo Chi Binh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.