sql - SQL 中的层次控制跨度报告，没有 Oracle CONNECT BY 语法？
问题描述
概括
控制范围是对给定经理报告的员工人数的计数。直接和间接报告计数应分成各自的总数。还需要其他计数，包括组织中有许多直接和间接报告的空缺。经理是有其他职位向其报告的任何职位。从顶部到树中任何位置的报告路径是展平结构所必需的。
我已经看到这个问题经常出现在 HR 报告和数据仓库项目中。我只能在 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_idposition_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)
技术要求
reportsto_position_id包含经理的，position_id最高职位为 NULL。position_id必须始终存在，但可以是空的。- 管理器必须具有唯一的
position_id(andmax_incumbents=1) 才能使树正常工作。 - 不同子树或不同级别中的类似职位也必须具有不同
position_id的维护报告结构。这是因为reportsto_position_id是为树中的每个节点定义的。 - 一个
employee_id可以存在于多个节点上，表明该员工在组织中有多个工作。如果员工有 1 份工作，他们multi_job_sequence将是0. 如果员工有多个工作，multi_job_sequence则其增加。 - 职位必须
max_incumbents限制允许填补该职位的员工数量。职位空缺没有工作行，但可以计算。 - 即使员工仍向该职位报告，经理职位也可能空缺。
- 如果组织决定通过添加/删除级别或子树进行重组，则 SQL 代码不应更改。
- 这个例子过于简单化了。大型组织可以为职位和员工提供更多级别和选项（例如生效日期或状态）。为了降低复杂性，此示例中的所有员工和职位都处于活动状态。
控制范围报告业务需求
报告必须回答以下问题，这些问题在等级组织中很常见：
- 经理有多少个直接下属（仅比他们低一级的员工人数）？
- 一个经理有多少间接报告（员工数量比他们低一级以上，一直到树的最低级别）？
- 这个经理有多少人“在他们的职位下”（即直接下属+间接下属）？
- 有多少经理需要填补团队的空缺职位（直接下属空缺）？
- 有多少经理向他们报告团队中有空缺的经理（空缺的间接报告）？
- 从顶部到树中每个位置的路径是什么，按名称或 ID：例如
CEO>Senior Manager>Supervisor South>Minion，或1>2>5>8？ - 从顶部到树中每个员工的路径是什么，按名称或按 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:1999 定义了“递归 CTE”（递归公用表表达式），它可以完成 aCONNECT BY以及更多的工作。它们旨在遍历任何类型的图形——层次结构是它们可以处理的子集。
您的查询非常广泛，所以我没有时间仔细研究它并用标准 SQL 重写它。
您询问哪些数据库可以做到这一点。好吧，它们目前由以下人员实施：
- 甲骨文。
- 数据库 2。在 Linux/Unix/Windows 中不实现循环检测。在 z/OS 中确实如此。
- PostgreSQL。
- SQL Server（自 2012 年以来？）。不执行周期检测。
- MariaDB，从 10.2 开始。不执行周期检测。
- MySQL 自 8.0. 不执行周期检测。
- H2（从 1.4 开始？）。不执行周期检测。
- 超SQL。
- 其他数据库...
如果您提供一个较小的示例，我会对使用递归 CTE 重新表述它非常感兴趣。
例如，以下递归 CTE（在 Oracle 中）将查找（直接和间接）向 position = 2 报告的员工的所有子树：
with
x (position_id, descr, reportsto_position_id, max_incumbents, cur_level) as (
select
position_id, descr, reportsto_position_id, max_incumbents,
1
from position
where position_id = 2 -- start at position = 2
union all
select
p.position_id, p.descr, p.reportsto_position_id, p.max_incumbents,
x.cur_level + 1
from position p
join x on p.reportsto_position_id = x.position_id
)
select * from x;
推荐阅读
- javascript - 在 ASP.Net MVC 中使用 Jquery 上传表单数据和文件
- xcode-server - MacOS UI 在 Xcode9 服务器上测试目标？
- python - 程序退出后消耗的内存
- java - 终端 Serenity BDD 测试跳过
- ios - 可以将 hr 数据从手表应用程序直播到 iphone 应用程序吗？如果是怎么办？
- url - 谷歌拒绝将新网站网址编入索引
- reactjs - Spotify API 令牌
- javascript - chrome 扩展中的甜蜜警报 2 不起作用
- java - Android 点击监听行为
- swift - Swift：查询以确定 Mac OS 上的默认输入设备