时间戳

首页 > 解决方案 > 查找按管理层级分组的透视数据

sql - 查找按管理层级分组的透视数据

问题描述

有两个表： 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

预期输出：一列具有前 1 个层次结构的用户，另一列具有应用程序成本的总和

   id | cost
   2   | 165
   3   | 98

我的问题是找出所有向顶级层次结构用户分组的顶级层次结构报告的用户的应用程序成本总和。这可以通过单个查询来完成吗？

标签： sqlpostgresqlpivot

解决方案

解决这个问题的关键是生成管理器的递归 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。

推荐阅读