sql - 规范化逻辑层次结构的度量总和

Question

假设有下表Table1，描述了父子关系和指标:

Parent | Child | Metric (of a child)
------------------------------------
name0 | name1 | a  
name0 | name2 | b  
name1 | name3 | c  
name2 | name4 | d  
name2 | name5 | e  
name3 | name6 | f

特点:
1) child 总是有 1 个且只有 1 个 parent ；
2) Parent 可以有多个 child (name2 有 name4 和 name5 作为 child )；
3) 此“层次结构”中的级别数和任何给定父级的子级数是任意的，并且彼此不依赖；

我需要的 SQL 请求将返回结果集，其中包含每个名称以及其所有后代的度量总和，直至底层加上自身，因此对于此示例表，结果将是(看仔细在 name1):

Name | Metric
------------------
name1 | a + c + f  
name2 | b + d + e  
name3 | c + f  
name4 | d  
name5 | e  
name6 | f

(name0 无关，可以排除)

它应该是 ANSI 或 Teradata SQL。

我得到了一个递归查询，它可以返回给定名称的所有后代的 SUM(度量):

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;  

有没有办法把这个查询变成一个以名称为参数并返回这个总和的函数，这样就可以这样调用了？

SELECT Sum_Of_Descendants (Child) FROM Table1;

关于如何从不同角度解决这个问题的任何建议也将不胜感激，因为即使上述方法是可实现的，它的性能也会很差 - 将会有很多读取指标的迭代(值 f 会在此示例中阅读 3 次)。理想情况下，查询应该只读取每个名称的指标一次。

Answer 1

编辑:此答案适用于支持公用表表达式的 SQL Server 2005 及更高版本。我第一次回答问题时没有注意teradata标签。希望这个答案仍然相关，因为语法看起来几乎相同。

这可以在 SQL Server 中通过扩展每个级别的层次结构来实现

with recursive temp_table (RootValue, Child, Metric)
as 
(select
    root.Child, root.Child, root.Metric
from table1 root
union all
select
    direct.RootValue, indirect.Child, indirect.Metric
from temp_table direct join table1 indirect on indirect.Parent = direct.Child
)
select
    RootValue, SUM(Metric)
from temp_table
group by RootValue;

除了 Child 和 Metric 之外，(公用表表达式)CTE 定义还有一个 RootValue 列。CTE 以递归方式关联给定 RootValue 的子值和指标值。

鉴于此 CTE，所需的输出只是在 RootValue 列上聚合的问题。