Summary
控制范围是指有多少员工向特定经理报告。直接和间接报告计数应分为各自的总数。
还需要进行其他统计，包括组织中直接和间接报告的许多职位空缺。经理是指有其他职位向其报告的任何职位。
需要从顶部到树中任何位置的报告路径flatten结构。
我发现这个问题经常出现在人力资源报告和数据仓库项目中。我只能在Oracle中解决这个问题。
此报告可以用与其他数据库（例如 SQL Server 或 PostgreSQL）兼容的 (ANSI) SQL 编写吗？
Detail
组织层次结构的视觉表示：
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 (if 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)
技术要求
- The
reportsto_position_id包含经理的position_id, NULL 表示顶部位置。
- The
position_id必须始终存在，但可以为空。
- 管理者必须有独特的
position_id (and max_incumbents=1）以使树正常工作。
- 不同子树或不同层次的相似位置也必须有不同的
position_id维持报告结构。这是因为reportsto_position_id为树中的每个节点定义。
- An
employee_id可以存在于多个节点上，表明该员工在组织中拥有多个职位。如果一名员工有 1 份工作，他们的multi_job_sequence将0。如果一名员工身兼数职，他们的multi_job_sequence是递增的。
- 职位有一个
max_incumbents限制允许填补该职位的员工数量。职位空缺没有职位行，但可以计算。
- 经理职位可能会空缺，即使员工仍然向该职位汇报。
- 如果组织决定通过添加/删除级别或子树来进行重组，则 SQL 代码不应更改。
- 这个例子过于简单化了。大型组织可以为职位和员工提供更多级别和选项（例如生效日期或状态）。为了降低复杂性，本示例中的所有员工和职位均处于活动状态。
控制范围报告业务要求
报告必须回答以下在层级组织中常见的问题：
- 经理有多少名直接下属（仅比他们低一级的员工数量）？
- 经理有多少个间接报告（比他们低一级的员工计数，一直到树的最低级别）？
- 这位经理“在其职位下”有多少人（即直接下属+间接下属）？
- 有多少经理需要填补团队中的空缺职位（空缺的直接下属）？
- 有多少经理的下属经理的团队中有空缺（空缺的间接下属）？
- 从顶部到树中每个位置的路径是什么（按名称或 ID）：例如
CEO>Senior Manager>Supervisor South>Minion, or 1>2>5>8?
- 从顶部到树中每个员工的路径是什么，按姓名或 ID（考虑到可能有多个工作的员工）：例如
Jill>Tom>Frank>Olivia or 3>1>10-1>4?
样本数据
position table
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
job table
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 小提琴示例 http://sqlfiddle.com/#!4/52637/13
