How can I traverse a tree bottom-up to calculate a (weighted) average of node values in PostgreSQL?
|Added at||2016-12-29 10:12|
The typical example for e.g. summing a whole tree in PostgreSQL is using WITH RECURSIVE (Common Table Expressions). However, these examples typically go from top to bottom, flatten the tree and perform an aggregate function on the whole result set. I have not found a suitable example (on StackOverflow, Google, etc.) for the problem I am trying to solve:
Consider an unbalanced tree where each node can have an associated value. Most of the values are attached to leaf nodes, but the others may have values as well. If a node (leaf or not) has an explicitly attached value, this value can be directly used without further calculation (subtree can be ignored, then). If the node has no value, the value should be computed as the average of its direct children.
However, as none of the nodes are guaranteed to have a value attached, I need to go bottom up in order to obtain a total average. In a nutshell, starting from the leafs, I need to apply
I need to compute the average value for A, which should be