SUM of metric for normalized logical hierarchy
- by Alex254
Suppose there's a following table Table1, describing parent-child relationship and metric:
Parent | Child | Metric (of a child)
------------------------------------
name0 | name1 | a
name0 | name2 | b
name1 | name3 | c
name2 | name4 | d
name2 | name5 | e
name3 | name6 | f
Characteristics:
1) Child always has 1 and only 1 parent;
2) Parent can have multiple children (name2 has name4 and name5 as children);
3) Number of levels in this "hierarchy" and number of children for any given parent are arbitrary and do not depend on each other;
I need SQL request that will return result set with each name and a sum of metric of all its descendants down to the bottom level plus itself, so for this example table the result would be (look carefully at name1):
Name | Metric
------------------
name1 | a + c + f
name2 | b + d + e
name3 | c + f
name4 | d
name5 | e
name6 | f
(name0 is irrelevant and can be excluded).
It should be ANSI or Teradata SQL.
I got as far as a recursive query that can return a SUM (metric) of all descendants of a given name:
WITH RECURSIVE temp_table (Child, metric) AS (
SELECT root.Child, root.metric
FROM table1 root
WHERE root.Child = 'name1'
UNION ALL
SELECT indirect.Child, indirect.metric
FROM temp_table direct, table1 indirect
WHERE direct.Child = indirect.Parent)
SELECT SUM(metric) FROM temp_table;
Is there a way to turn this query into a function that takes name as an argument and returns this sum, so it can be called like this?
SELECT Sum_Of_Descendants (Child) FROM Table1;
Any suggestions about how to approach this from a different angle would be appreciated as well, because even if the above way is implementable, it will be of poor performance - there would be a lot of iterations of reading metrics (value f would be read 3 times in this example). Ideally, the query should read a metric of each name only once.
