对树数据进行分组、聚合和求和的最佳方法是什么？

发布于 2024-08-08 20:45:43 字数 1016 浏览 6 评论 0原文

给定一个自引用表

Item 
-------------
Id (pk)
ParentId (fk)

以及相关值的相关表

ItemValue
-------------
ItemId (fk)
Amount

和一些示例数据，

Item                       ItemValues 
Id      ParentId           ItemId      Amount
--------------------       ----------------------
1       null               1           10
2       1                  3           40
3       1                  3           20
4       2                  4           10
5       2                  5           30
6       null
7       6
8       7

我需要一个存储过程来获取 Item.Id 并返回包含所有 ItemValue.Amounts 总和的直接子级> 为了他们，他们的孩子，还有他们的孩子，一直到树下。

例如，如果传入 1，则树将为 2, 3, 4, 5 直接子级为 2, 3 输出应该

 ItemId    Amount
 ------------------
 2         40     (values from ItemIds 4 & 5)
 3         60     (values from ItemId 3)

采用什么样的方法来实现这种行为？

我正在考虑使用 CTE，但想知道是否有更好/更快的方法。

原文

Given a self referencing table

Item 
-------------
Id (pk)
ParentId (fk)

With a related table of associated values

ItemValue
-------------
ItemId (fk)
Amount

And some sample data

Item                       ItemValues 
Id      ParentId           ItemId      Amount
--------------------       ----------------------
1       null               1           10
2       1                  3           40
3       1                  3           20
4       2                  4           10
5       2                  5           30
6       null
7       6
8       7

I need a sproc to take Item.Id and return the direct children with sums of all ItemValue.Amounts for the them, their children and their children all the way down the tree.

For example, if 1 is passed in, the tree would be 2, 3, 4, 5 the direct children are 2, 3 the output would be

 ItemId    Amount
 ------------------
 2         40     (values from ItemIds 4 & 5)
 3         60     (values from ItemId 3)

What sort of approaches should be applied to make achieve this behavior?

I am considering using a CTE, but am wondering if there is a better/faster approach.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

中性美 2024-08-15 20:45:43

假设您的层次结构不是太深，这样的递归 CTE 就可以工作：

declare @ParentId int;
set @ParentId = 1;

;with 
  Recurse as (
    select 
      a.Id as DirectChildId
    , a.Id
    from Item a 
    where ParentId = @ParentId
    union all
    select
      b.DirectChildId
    , a.Id
    from Item a 
    join Recurse b on b.Id = a.ParentId
    )
select
  a.DirectChildId, sum(b.Amount) as Amount
from Recurse a
left join ItemValues b on a.Id = b.ItemId
group by
  DirectChildId;

非 CTE 方法将需要某种形式的迭代，基于游标或其他形式。由于它是一个存储过程，因此有可能，并且如果有大量数据需要递归，那么只要您适当地对数据进行切片，它可能会更好地扩展。

如果聚集索引在Id上，则在ParentId上添加非聚集索引。作为覆盖索引，它将满足无需书签查找的初始查找。聚集索引将有助于递归连接。

如果 ParentId 上已有聚集索引，请在 Id 上添加非聚集索引。它们加起来实际上等同于上述内容。对于 ItemValues，如果实际表比此宽，您可能需要 (ItemId) INCLUDE (Amount) 上的索引。

A recursive CTE like this would work, assuming your hierarchy doesn't go too deep:

declare @ParentId int;
set @ParentId = 1;

;with 
  Recurse as (
    select 
      a.Id as DirectChildId
    , a.Id
    from Item a 
    where ParentId = @ParentId
    union all
    select
      b.DirectChildId
    , a.Id
    from Item a 
    join Recurse b on b.Id = a.ParentId
    )
select
  a.DirectChildId, sum(b.Amount) as Amount
from Recurse a
left join ItemValues b on a.Id = b.ItemId
group by
  DirectChildId;

A non-CTE method would require some form of iteration, cursor-based or otherwise. Since it's a stored proc, its a possibility, and if there's a lot data to recurse through, it would probably scale better, so long as you slice the data appropriately.

If the clustered index is on Id, add a non-clustered index on ParentId. As a covering index, it will satisfy the initial seek w/out a bookmark lookup. The clustered index will then help with the recursive join.

If the clustered index is already on ParentId instead, add a non-clustered index on Id. Together, they will be virtually equivalent to the above. For ItemValues, you may want a index on (ItemId) INCLUDE (Amount), if the actual table is wider than this.

回复收藏 0 原文