| entityId | parentId | emission | percentContribution | cumulativeSum |
|---|---|---|---|---|
| E1 | NULL | 30 | NULL | 30 + (28 * 80 / 100) = 52.4 |
| E2 | E1 | 20 | 80 | 20 + (10 * 80 / 100) = 28 |
| E3 | E2 | 10 | 80 | 10 |
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)
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.
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.