1. 首页
  2. 问题
  3. 没有Oracle CONNECT BY语法的SQL中的分层控制范围报告？
admin

没有Oracle CONNECT BY语法的SQL中的分层控制范围报告？

sql

概括

控制范围是对向指定经理报告的员工人数的计数。直接和间接报告计数应分为自己的总数。还需要其他计数，包括组织中直接和间接报告的许多职位空缺。经理是指有其他职位要向其报告的职位。需要从顶部到树中任何位置的报告路径，以
使 结构 扁平 化。

我已经看到此问题经常出现在人力资源报告和数据仓库项目中。我只能在Oracle中解决它。可以使用与其他数据库（例如SQL
Server或PostgreSQL）兼容的（ANSI）SQL编写此报告吗？

细节

组织层次结构的可视化表示：

Level 1                                1:3
                                        |
                        ----------------+-----------------------------------
                        |               |               |                  |
Level 2                2:1            13:             10:12               4:2
                        |                               |
               ---------+----------           ----------+----------
               |        |         |           |         |         |
Level 3      12:10     3:        3:         5:10-1    11:11      6:
               |                              |                   |
            ---+---               ------------+------------       |
            |     |               |     |     |     |     |       |
Level 4    7:4   7:9             8:5   8:7   8:6   8:    8:      9:8

树的每个节点或叶均由以下之一表示：

  • position_id:employee_id
  • position_id:employee_id-multi_job_sequence（如果multi_job_sequence>0）
  • position_id: （空的）

预期产量

POSITION_ID    POSITION_DESCR         REPORTSTO_POSITION_ID      EMPLOYEE_ID    MULTI_JOB_SEQUENCE      EMPLOYEE_NAME      TREE_LEVEL_NUM      IS_MANAGER     MAX_INCUMBENTS       FILLED_HEAD_COUNT      VACANT_HEAD_COUNT     FILLED_DIRECT_REPORTS     VACANT_DIRECT_REPORTS       FILLED_INDIRECT_REPORTS     VACANT_INDIRECT_REPORTS       EMPLOYEES_UNDER_POSITION        VACANCIES_UNDER_POSITION       REPORTING_PATH_POSITION_ID     REPORTING_PATH_POSITION_DESCR                       REPORTING_PATH_EMPLOYEE        REPORTING_PATH_EMPLOYEE_NAME
1              CEO                    NULL                       3              0                       Jill               1                   1              1                    1                      0                     3                         1                           9                           5                             12                              6                              1                              CEO                                                 3                              Jill
2              Senior Manager         1                          1              0                       Tom                2                   1              1                    1                      0                     1                         2                           2                           0                             3                               2                              1>2                            CEO>Senior Manager                                  3>1                            Jill>Tom
3              West Winger            2                          NULL           NULL                    NULL               3                   0              2                    0                      2                     0                         0                           0                           0                             0                               0                              1>2>3                          CEO>Senior Manager>West Winger                      3>1>(vacant)                   Jill>Tom>(vacant)
4              Executive Assistant    1                          2              0                       Doug               2                   0              1                    1                      0                     0                         0                           0                           0                             0                               0                              1>4                            CEO>Executive Assistant                             3>2                            Jill>Doug
5              Supervisor South       10                         10             1                       Frank              3                   1              1                    1                      0                     3                         2                           0                           0                             3                               2                              1>10>5                         CEO>Senior Manager>Supervisor South                 3>12>10-1                      Jill>Fred>Frank
6              Supervisor East        10                         NULL           NULL                    NULL               3                   1              1                    0                      1                     1                         0                           0                           0                             1                               0                              1>10>6                         CEO>Senior Manager>Supervisor East                  3>12>(vacant)                  Jill>Fred>(vacant)
7              Expert                 12                         4              0                       Olivia             4                   0              2                    2                      0                     0                         0                           0                           0                             0                               0                              1>2>12>7                       CEO>Senior Manager>Supervisor West>Expert           3>1>10>4                       Jill>Tom>Frank>Olivia
7              Expert                 12                         9              0                       David              4                   0              2                    2                      0                     0                         0                           0                           0                             0                               0                              1>2>12>7                       CEO>Senior Manager>Supervisor West>Expert           3>1>10>9                       Jill>Tom>Frank>David
8              Minion                 5                          5              0                       Carol              4                   0              5                    3                      2                     0                         0                           0                           0                             0                               0                              1>10>5>8                       CEO>Senior Manager>Supervisor South>Minion          3>12>10-1>5                    Jill>Fred>Frank>Carol
8              Minion                 5                          6              0                       Mary               4                   0              5                    3                      2                     0                         0                           0                           0                             0                               0                              1>10>5>8                       CEO>Senior Manager>Supervisor South>Minion          3>12>10-1>6                    Jill>Fred>Frank>Mary
8              Minion                 5                          7              0                       Michael            4                   0              5                    3                      2                     0                         0                           0                           0                             0                               0                              1>10>5>8                       CEO>Senior Manager>Supervisor South>Minion          3>12>10-1>7                    Jill>Fred>Frank>Michael
9              Administrator          6                          8              0                       Nigel              4                   0              1                    1                      0                     0                         0                           0                           0                             0                               0                              1>10>6>9                       CEO>Senior Manager>Supervisor East>Administrator    3>12>(vacant)>8                Jill>Fred>(vacant)>Nigel
10             Senior Manager         1                          12             0                       Fred               2                   1              1                    1                      0                     2                         1                           4                           2                             6                               3                              1>10                           CEO>Senior Manager                                  3>12                           Jill>Fred
11             Supervisor South       10                         11             0                       Wilson             3                   0              1                    1                      0                     0                         0                           0                           0                             0                               0                              1>10>11                        CEO>Senior Manager>Supervisor South                 3>12>11                        Jill>Fred>Wilson
12             Supervisor West        2                          10             0                       Frank              3                   1              1                    1                      0                     2                         0                           0                           0                             2                               0                              1>2>12                         CEO>Senior Manager>Supervisor West                  3>1>10                         Jill>Tom>Frank
13             Executive Mid-West     1                          NULL           NULL                    NULL               2                   0              1                    0                      1                     0                         0                           0                           0                             0                               0                              1>13                           CEO>Executive Mid-West                              3>(vacant)                     Jill>(vacant)

技术要求

  1. 该reportsto_position_id含有经理position_id，对榜首的位置NULL。
  2. 在position_id必须始终存在，但可以空缺。
  3. 管理者必须具有唯一的树position_id（和max_incumbents=1），树才能正常工作。
  4. 不同子树或不同级别中的相似位置也必须具有不同的位置position_id才能维护报告结构。这是因为reportsto_position_id为树中的每个节点定义了。
  5. 一个employee_id可以存在于多个节点上，表明该员工在组织中有多个工作。如果一名雇员有一份工作，他们multi_job_sequence将是0。如果一个雇员有多个工作，他们的工作将multi_job_sequence增加。
  6. 职位max_incumbents限制了该职位所允许的员工数量。职位空缺没有职位，但可以计算。
  7. 经理职位可能空缺，即使员工仍然向该职位报告。
  8. 如果组织决定通过添加/删除级别或子树来进行重组，则SQL代码不应更改。
  9. 此示例过于简化。大型组织可以为职位和员工提供更多级别和选项（例如生效日期或状态）。为了降低复杂性，此示例中的所有员工和职位均处于活动状态。

控制范围报告业务需求

该报告必须回答以下问题，这些问题在层次结构组织中很常见：

  1. 经理有多少个直接报告（雇员人数仅比其低一级）？
  2. 经理有多少个间接报告（员工数不低于其下一层，一直到树的最低层）？
  3. 该经理有多少人“在其职位上”（即直接报告+间接报告）？
  4. 有多少经理需要填补他们的团队的空缺职位（空缺的直接报告）？
  5. 多少经理人向其团队中有空缺的经理进行报告（空缺的间接报告）？
  6. 从顶部到树中每个位置的路径是什么，按名称或ID：例如CEO>Senior Manager>Supervisor South>Minion或1>2>5>8？
  7. 从树顶到树中每个员工的路径，是按名称还是按ID（考虑到可能有多个工作的员工）是什么？例如Jill>Tom>Frank>Olivia或3>1>10-1>4？

样本数据

位置 表

position_id  descr                            reportsto_position_id  max_incumbents
1            CEO                              NULL                   1
2            Senior Manager                   1                      1
3            West Winger                      2                      2
4            Executive Assistant              1                      1
5            Supervisor South                 10                     1
6            Supervisor East                  10                     1
7            Expert                           12                     2
8            Minion                           5                      5
9            Administrator                    6                      1
10           Senior Manager                   1                      1
11           Supervisor South                 10                     1
12           Supervisor West                  2                      1
13           Executive Mid-West               1                      1

工作 表

employee_id  multi_job_sequence  employee_name  position_id
1            0                   Tom            2
2            0                   Doug           4
3            0                   Jill           1
4            0                   Olivia         7
5            0                   Carol          8
6            0                   Mary           8
7            0                   Michael        8
8            0                   Nigel          9
9            0                   David          7
10           0                   Frank          12
10           1                   Frank          5
11           0                   Wilson         11
12           0                   Fred           10

的SQL

-- Position incumbents. One row for each position, employee_id, multi_job_sequence combination.
with cte_incumbents
as
(
    select
    cp.position_id,
    cp.reportsto_position_id,
    cp.max_incumbents,
    cj.employee_id,
    cj.multi_job_sequence
    from position cp
    left join job cj on cj.position_id = cp.position_id
),
-- Incumbents count (filled and vacant) per position
cte_incumbents_count
as
(
    select
    i.reportsto_position_id,
    i.position_id,
    count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence)) as filled_count,
    (i.max_incumbents - count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence))) as vacant_count,
    i.max_incumbents
    from cte_incumbents i
    where i.employee_id is not null
    group by i.reportsto_position_id,
             i.position_id,
             i.max_incumbents

    UNION ALL

    select
    i.reportsto_position_id,
    i.position_id,
    0 as filled_count,
    (count(*) * i.max_incumbents) as vacant_count,
    i.max_incumbents
    from cte_incumbents i
    where i.employee_id is null
    group by i.reportsto_position_id,
             i.position_id,
             i.max_incumbents
),
-- Count the filled and vacant reports_to positions
cte_reportsto_count
as
(
    select
    i.reportsto_position_id,
    sum(i.filled_count) as filled_count,
    sum(i.vacant_count) as vacant_count,
    sum(i.max_incumbents) as total_incumbents
    from cte_incumbents_count i
    group by i.reportsto_position_id
),
-- Create the organisation tree, based on the reportsto_position_id
cte_reportsto_tree
as
(
    select
    rtt.position_id,
    rtt.employee_id,
    rtt.multi_job_sequence,
    rtt.position_descr,
    rtt.reportsto_position_id,
    rtt.employee_name,
    level as tree_level_num,
    case when connect_by_isleaf = 0 then 1 else 0 end as is_manager,
    rtt.max_incumbents,
    nvl((
        select
        rtc.filled_count
        from cte_reportsto_count rtc
        where rtc.reportsto_position_id = rtt.position_id
    ),0) as filled_direct_reports,
    nvl((
        select
        rtc.vacant_count
        from cte_reportsto_count rtc
        where rtc.reportsto_position_id = rtt.position_id
    ),0) as vacant_direct_reports,
    substr(sys_connect_by_path(rtt.position_id,'>'),2,length(sys_connect_by_path(rtt.position_id,'>'))-1) as reporting_path_position_id,
    substr(sys_connect_by_path(rtt.position_descr,'>'),2,length(sys_connect_by_path(rtt.position_descr,'>'))-1) as reporting_path_position_descr,
    substr(sys_connect_by_path(nvl(case when rtt.employee_id is null then null else case when rtt.multi_job_sequence = 0 then to_char(rtt.employee_id) else rtt.employee_id || '-' || rtt.multi_job_sequence end end,'(vacant)'),'>'),2,length(sys_connect_by_path(nvl(case when rtt.employee_id is null then null else rtt.employee_id || '-' || rtt.multi_job_sequence end,'(vacant)'),'>'))-1) as reporting_path_employee,
    substr(sys_connect_by_path(nvl(rtt.employee_name,'(vacant)'),'>'),2,length(sys_connect_by_path(nvl(rtt.employee_name,'(vacant)'),'>'))-1) as reporting_path_name
    from
    (
        select
        cp.position_id,
        cp.descr as position_descr,
        cp.max_incumbents,
        cp.reportsto_position_id,
        cj.employee_id,
        cj.multi_job_sequence,
        cj.employee_name
        from position cp
        left join job cj on cj.position_id = cp.position_id -- Positions may not be filled
    ) rtt
    connect by prior rtt.position_id = rtt.reportsto_position_id
    start with rtt.reportsto_position_id is null -- Start at the top of the tree
),
-- Create the report detail, traversing the tree (creating subtrees to get the indirect values). This is the tough part!
cte_report_detail
as
(
    select
    soc.position_id,
    soc.position_descr,
    soc.reportsto_position_id,
    soc.employee_id,
    soc.multi_job_sequence,
    soc.employee_name,
    soc.tree_level_num,
    soc.is_manager,
    soc.max_incumbents,
    nvl(
        (
         select
         ic.filled_count
         from cte_incumbents_count ic
         where ic.position_id = soc.position_id
        ),0) as filled_head_count,
    nvl(
        (
         select
         ic.vacant_count
         from cte_incumbents_count ic
         where ic.position_id = soc.position_id
        ),0) as vacant_head_count,
    soc.filled_direct_reports as filled_direct_reports,
    soc.vacant_direct_reports as vacant_direct_reports,
    case when soc.is_manager = 1 then
    -- Get the filled count of all of the positions underneath and subtract the direct reports to arrive at the filled indirect reports count
    (
        select
        sum(
             (
                select
                rtc.filled_count
                from cte_reportsto_count rtc
                where rtc.reportsto_position_id = cp.position_id
             )
           )
        from position cp
        connect by prior cp.position_id = cp.reportsto_position_id
        start with cp.position_id = soc.position_id
    ) - soc.filled_direct_reports else 0 end as filled_indirect_reports,
    -- Get the vacant count of all of the positions underneath and subtract the direct reports to arrive at the vacant indirect reports count
    case when soc.is_manager = 1 then
    (
        select
        sum(
             (
                select
                rtc.vacant_count
                from cte_reportsto_count rtc
                where rtc.reportsto_position_id = cp.position_id
             )
           )
        from position cp
        connect by prior cp.position_id = cp.reportsto_position_id
        start with cp.position_id = soc.position_id
    ) - soc.vacant_direct_reports else 0 end as vacant_indirect_reports,
    to_clob(cast(soc.reporting_path_position_id as varchar2(4000))) as reporting_path_position_id,
    to_clob(cast(soc.reporting_path_position_descr as varchar2(4000))) as reporting_path_position_descr,
    to_clob(cast(soc.reporting_path_employee as varchar2(4000))) as reporting_path_employee,
    to_clob(cast(soc.reporting_path_name as varchar2(4000))) as reporting_path_employee_name
    from cte_reportsto_tree soc
)
-- Final calculations and sort
select
r.position_id,
r.position_descr,
r.reportsto_position_id,
r.employee_id,
r.multi_job_sequence,
r.employee_name,
r.tree_level_num,
r.is_manager,
r.max_incumbents,
r.filled_head_count,
r.vacant_head_count,
r.filled_direct_reports,
r.vacant_direct_reports,
r.filled_indirect_reports,
r.vacant_indirect_reports,
(r.filled_direct_reports + r.filled_indirect_reports) as employees_under_position,
(r.vacant_direct_reports + r.vacant_indirect_reports) as vacancies_under_position,
r.reporting_path_position_id,
r.reporting_path_position_descr,
r.reporting_path_employee,
r.reporting_path_employee_name
from cte_report_detail r
order by r.position_id,
         r.employee_id,
         r.multi_job_sequence;

SQL Fiddle示例


阅读 154

收藏
2021-06-07

共1个答案

admin

经过一些工作，我设法回答了自己的问题，以使用CTE再现完全相同的结果。

在这种情况下，递归CTE功能可在Oracle中使用，但有一些限制。另一个数据库将需要支持DEPTH FIRST搜索递归以及分析功能。从理论上讲，此代码可以通过对语法进行小的更改来移植。

要点/经验教训：

  1. 递归CTE的结果不能在另一个CTE或子查询中使用。如果要在子查询中使用递归CTE的结果，则必须首先在表中实现。
  2. 您不能将视图用于递归CTE，否则将收到error ORA-01702: a view is not appropriate here。
  3. 在分析功能的帮助下，该SEARCH DEPTH FIRST BY reportsto_position_id SET seq子句的设置很重要。is_manager``LEAD()
  4. 将树展平到reporting_path字段中对于正确遍历它很有用。我使用该INSTR()函数来确保在给定路径中存在一个位置。
  5. SQL Fiddle的字符数限制为8000个，这迫使我在运行报表之前实现了其他CTE。在普通的Oracle数据库上，这是没有必要的，因为对查询大小没有限制。

样本数据表和基本计数

-- Create a table for each current position.
create table position
(
    position_id           NUMBER(11) NOT NULL,
    descr                 VARCHAR2(50) NOT NULL,
    reportsto_position_id NUMBER(11),
    max_incumbents        NUMBER(4) NOT NULL
);

create unique index position_idx1 on position (position_id);

-- Create a table to store the current job data.
create table job
(
    employee_id        NUMBER(11) NOT NULL,
    multi_job_sequence NUMBER(1) NOT NULL,
    employee_name      VARCHAR2(50) NOT NULL,
    position_id        NUMBER(11) NOT NULL
);

create unique index job_idx1 on job (employee_id, multi_job_sequence);
create index job_idx2 on job (position_id, employee_id, multi_job_sequence);

-- Insert data into position table
insert into position values (1, 'CEO', NULL, 1);
insert into position values (2, 'Senior Manager', 1, 1);
insert into position values (3, 'West Winger', 2, 2);
insert into position values (4, 'Executive Assistant', 1, 1);
insert into position values (5, 'Supervisor South', 10, 1);
insert into position values (6, 'Supervisor East', 10, 1);
insert into position values (7, 'Expert', 12, 2);
insert into position values (8, 'Minion', 5, 5);
insert into position values (9, 'Administrator', 6, 1);
insert into position values (10, 'Senior Manager', 1, 1);
insert into position values (11, 'Supervisor South', 10, 1);
insert into position values (12, 'Supervisor West', 2, 1);
insert into position values (13, 'Executive Mid-West', 1, 1);

commit;

-- Insert data into job table
insert into job values (1, 0, 'Tom', 2);
insert into job values (2, 0, 'Doug', 4);
insert into job values (3, 0, 'Jill', 1);
insert into job values (4, 0, 'Olivia', 7);
insert into job values (5, 0, 'Carol', 8);
insert into job values (6, 0, 'Mary', 8);
insert into job values (7, 0, 'Michael', 8);
insert into job values (8, 0, 'Nigel', 9);
insert into job values (9, 0, 'David', 7);
insert into job values (10, 0, 'Frank', 12);
insert into job values (10, 1, 'Frank', 5);
insert into job values (11, 0, 'Wilson', 11);
insert into job values (12, 0, 'Fred', 10);

commit;

-- Build up the tables

-- Position incumbents. One row for each position, employee_id, multi_job_sequence combination.
create table cte_incumbents
as
(
    select
    cp.position_id,
    cp.reportsto_position_id,
    cp.max_incumbents,
    cj.employee_id,
    cj.multi_job_sequence
    from position cp
    left join job cj on cj.position_id = cp.position_id
);

create unique index cte_incumbents_idx1 on cte_incumbents (position_id, employee_id, multi_job_sequence);
create index cte_incumbents_idx2 on cte_incumbents (position_id, reportsto_position_id);

-- Incumbents count (filled and vacant) per position
create table cte_incumbents_count
as
(
    select
    i.reportsto_position_id,
    i.position_id,
    count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence)) as filled_count,
    (i.max_incumbents - count(to_char(i.employee_id) || '-' || to_char(i.multi_job_sequence))) as vacant_count,
    i.max_incumbents
    from cte_incumbents i
    where i.employee_id is not null
    group by i.reportsto_position_id,
             i.position_id,
             i.max_incumbents

    UNION ALL

    select
    i.reportsto_position_id,
    i.position_id,
    0 as filled_count,
    (count(*) * i.max_incumbents) as vacant_count,
    i.max_incumbents
    from cte_incumbents i
    where i.employee_id is null
    group by i.reportsto_position_id,
             i.position_id,
             i.max_incumbents
);

create unique index cte_incumbents_count_idx on cte_incumbents_count (reportsto_position_id, position_id);


-- Count the filled and vacant reports_to positions
create table cte_reportsto_count
as
(
    select
    i.reportsto_position_id,
    sum(i.filled_count) as filled_count,
    sum(i.vacant_count) as vacant_count,
    sum(i.max_incumbents) as total_incumbents
    from cte_incumbents_count i
    group by i.reportsto_position_id
);

create unique index cte_reportsto_count_idx on cte_reportsto_count (reportsto_position_id);

使用CTE报告

create table cte_reportsto_tree as
-- Create the organisation tree, based on the reportsto_position_id
with cte_reportsto_tree_base (
                                 position_id,
                                 position_descr,
                                 reportsto_position_id,
                                 employee_id,
                                 multi_job_sequence,
                                 employee_name,
                                 tree_level_num,
                                 max_incumbents,
                                 filled_direct_reports,
                                 vacant_direct_reports,
                                 reporting_path_position_id,
                                 reporting_path_position_descr,
                                 reporting_path_employee,
                                 reporting_path_employee_name
                             )
as
(
    -- Anchor member
    select
    cp1.position_id,
    cp1.descr as position_descr,
    cp1.reportsto_position_id,
    cj1.employee_id,
    cj1.multi_job_sequence,
    cj1.employee_name,
    1 as tree_level_num,
    cp1.max_incumbents,
    nvl((
        select
        rtc.filled_count
        from cte_reportsto_count rtc
        where rtc.reportsto_position_id = cp1.position_id
    ),0) as filled_direct_reports,
    nvl((
        select
        rtc.vacant_count
        from cte_reportsto_count rtc
        where rtc.reportsto_position_id = cp1.position_id
    ),0) as vacant_direct_reports,
    to_char(cp1.position_id) as reporting_path_position_id,
    cp1.descr as reporting_path_position_descr,
    to_char(cj1.employee_id) as reporting_path_employee,
    cj1.employee_name as reporting_path_employee_name
    from position cp1
    left join job cj1 on cj1.position_id = cp1.position_id -- Positions may not be filled
    where cp1.position_id = 1 -- start at position = 1

    UNION ALL

    -- Recursive member
    select
    cp2.position_id,
    cp2.descr as position_descr,
    cp2.reportsto_position_id,
    cj2.employee_id,
    cj2.multi_job_sequence,
    cj2.employee_name,
    rtt.tree_level_num + 1 as tree_level_num,
    cp2.max_incumbents,
    nvl((
        select
        rtc.filled_count
        from cte_reportsto_count rtc
        where rtc.reportsto_position_id = cp2.position_id
    ),0) as filled_direct_reports,
    nvl((
        select
        rtc.vacant_count
        from cte_reportsto_count rtc
        where rtc.reportsto_position_id = cp2.position_id
    ),0) as vacant_direct_reports,
    rtt.reporting_path_position_id || '>' || to_char(cp2.position_id) as reporting_path_position_id,
    rtt.reporting_path_position_descr || '>' || cp2.descr as reporting_path_position_descr,
    rtt.reporting_path_employee || '>' || nvl(case when cj2.employee_id is null then null else case when cj2.multi_job_sequence = 0 then to_char(cj2.employee_id) else to_char(cj2.employee_id) || '-' || to_char(cj2.multi_job_sequence) end end,'(vacant)') as reporting_path_employee,
    rtt.reporting_path_employee_name || '>' || nvl(cj2.employee_name,'(vacant)') as reporting_path_employee_name
    from position cp2
    inner join cte_reportsto_tree_base rtt on rtt.position_id = cp2.reportsto_position_id
    left join job cj2 on cj2.position_id = cp2.position_id -- Positions may not be filled
)
SEARCH DEPTH FIRST BY reportsto_position_id SET seq
select
rtt.position_id,
rtt.position_descr,
rtt.reportsto_position_id,
rtt.employee_id,
rtt.multi_job_sequence,
rtt.employee_name,
rtt.tree_level_num,
rtt.max_incumbents,
rtt.filled_direct_reports,
rtt.vacant_direct_reports,
rtt.reporting_path_position_id,
rtt.reporting_path_position_descr,
rtt.reporting_path_employee,
rtt.reporting_path_employee_name,
case when (rtt.tree_level_num - lead(rtt.tree_level_num) over (order by seq)) < 0 then 1 else 0 end is_manager -- Is a manager if there is a difference between levels on the tree.
from cte_reportsto_tree_base rtt;

create index cte_reportsto_tree_idx on cte_reportsto_tree (position_id, reportsto_position_id, employee_id, multi_job_sequence);

create table cte_fir as
(
    select
    soc.position_id,
    soc.is_manager,
    soc.tree_level_num,
    soc.filled_direct_reports,
    soc.vacant_direct_reports,
    case when soc.is_manager = 1 then
        nvl((
            select
            sum(ind.filled_direct_reports)
            from cte_reportsto_tree ind
            where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
        ),0)
    else 0 end as filled_indirect_reports,
    case when soc.is_manager = 1 then
        nvl((
            select
            sum(ind.vacant_direct_reports)
            from cte_reportsto_tree ind
            where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
        ),0)
    else 0 end as vacant_indirect_reports
    from cte_reportsto_tree soc
    where soc.tree_level_num = 1

    UNION ALL

    select
    soc.position_id,
    soc.is_manager,
    soc.tree_level_num,
    soc.filled_direct_reports,
    soc.vacant_direct_reports,
    case when soc.is_manager = 1 then
        nvl((
            select
            sum(ind.filled_direct_reports)
            from cte_reportsto_tree ind
            where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
            and instr(ind.reporting_path_position_id, '>'|| soc.position_id || '>') > 0 -- The position must be in the flattened tree path (quick way of traversing the tree)
        ),0)
    else 0 end as filled_indirect_reports,
    case when soc.is_manager = 1 then
        nvl((
            select
            sum(ind.vacant_direct_reports)
            from cte_reportsto_tree ind
            where ind.tree_level_num > soc.tree_level_num -- Must be at a lower level
            and instr(ind.reporting_path_position_id, '>'|| soc.position_id ||'>') > 0 -- The position must be in the flattened tree path (quick way of traversing the tree)
        ),0)
    else 0 end as vacant_indirect_reports
    from cte_reportsto_tree soc
    where soc.tree_level_num > 1
);

create index cte_fir_idx on cte_fir (position_id);

select
soc.position_id,
soc.position_descr,
soc.reportsto_position_id,
soc.employee_id,
soc.multi_job_sequence,
soc.employee_name,
soc.tree_level_num,
soc.is_manager,
soc.max_incumbents,
nvl(
    (
        select
        ic.filled_count
        from cte_incumbents_count ic
        where ic.position_id = soc.position_id
    ),0) as filled_head_count,
nvl(
    (
        select
        ic.vacant_count
        from cte_incumbents_count ic
        where ic.position_id = soc.position_id
    ),0) as vacant_head_count,
soc.filled_direct_reports as filled_direct_reports,
soc.vacant_direct_reports as vacant_direct_reports,
(
    select
    sum(fir.filled_indirect_reports)
    from cte_fir fir
    where fir.position_id = soc.position_id
) as filled_indirect_reports,
(
    select
    sum(fir.vacant_indirect_reports)
    from cte_fir fir
    where fir.position_id = soc.position_id
) as vacant_indirect_reports,
(
    select
    sum(fir.filled_indirect_reports)
    from cte_fir fir
    where fir.position_id = soc.position_id
) + soc.filled_direct_reports as employees_under_position,
(
    select
    sum(fir.vacant_indirect_reports)
    from cte_fir fir
    where fir.position_id = soc.position_id
) + soc.vacant_direct_reports as vacancies_under_position,
soc.reporting_path_position_id,
soc.reporting_path_position_descr,
soc.reporting_path_employee,
soc.reporting_path_employee_name
from cte_reportsto_tree soc
order by soc.position_id,
         soc.employee_id,
         soc.multi_job_sequence;

SQL Fiddle示例

2021-06-07