查找按管理层次分组的数据中心

Question

有两张桌子:1。申请：

 id | cost | user_id (FK)
 34756 | 1186 | 1
 37638 | 25  | 2
 37636 | 80  |  3
 37636 | 85  |  4
 37636 | 50  |  5
 37636 | 5  |   5
 37636 | 8  |   6
 37636 | 10  |  7

1. 用户表：

 id | mgr_id
 1 | null --- top
 2  | 1   --- top-1
 3  | 1   --- top-1
 4  | 2   --- top-2
 5  | 2   --- top-2
 6  | 3   --- top-2
 7  | 3   --- top-2

预期输出:两列，一列具有顶级用户，另一列具有应用程序成本之和。

   id | cost
   2   | 165
   3   | 98

我的问题是找出所有向顶级1层次结构按顶级用户分组的用户的应用成本之和。这可以通过一个查询来完成吗？

Answer 1

解决这个问题的关键是一个递归的CTE来生成管理器。

然而，我觉得这个问题很有挑战性。如果您希望每个人都严格地低于第二级经理，那么您可以使用递归CTE生成管理器，只需选择二级管理器。

但是，您也希望为这些经理添加值。我想这需要多走一步。

以下是你想做的事：

with recursive cte as (
      select u.id,'{}'::int[] as mgrs, 1 as lev
      from users u
      where mgr_id is null
      union all
      select u.id, (mgrs || array[u.mgr_id]), lev + 1
      from cte join
           users u
           on u.mgr_id = cte.id
     )
select cte.mgrs[2], sum(t.cost) + coalesce(tm.cost, 0)
from cte join
     t
     on t.user_id = cte.id left join
     (select t.user_id, sum(cost) as cost
      from t
      group by t.user_id
     ) tm
     on cte.mgrs[2] = tm.user_id
where cardinality(mgrs) >= 2
group by mgrs[2], tm.cost;

这里是db<>fiddle。