如何在Oracle层次查询中计算各层级的累计值总和？

这是此前层级查询问题的延续，需求是获取层次结构中各层级的累计Bug总数。

表结构与测试数据 #

drop table t1 purge;

create table t1(en varchar2(10),bug number, mgr varchar2(10));
insert into t1 values('z',901,'x');
insert into t1 values('z',902,'x');
insert into t1 values('z',903,'x');
insert into t1 values('a',101,'z');
insert into t1 values('a',102,'z');
insert into t1 values('a',103,'z');
insert into t1 values('a',104,'z');
insert into t1 values('b',201,'a');
insert into t1 values('b',202,'a');
insert into t1 values('b',203,'a');
insert into t1 values('c',301,'z');
insert into t1 values('c',302,'z');
insert into t1 values('c',303,'z');
insert into t1 values('d',301,'c');
insert into t1 values('d',302,'c');
commit;

期望输出 #

MGR  EN   EN_BUG_COUNT  CUMULATIVE_BUG_COUNT    LEVEL
x    null         null                    15        0
x    z               3                    12        1
z    a               4                     7        2
a    b               3                     3        3
z    c               3                     5        2
c    d               2                     2        4

已查阅资料说明 #

我已经看过以下几个Stack Overflow上的相关问题，但其中的查询语句逻辑较难理解：

• Oracle层级查询数据处理
• 如何在Oracle层级树中按层级求和
• 获取层级查询中每个层级的计数

解决方案 #

可以通过递归CTE结合窗口函数来实现需求，SQL语句如下：

WITH hierarchy_data AS (
    -- 统计每个节点自身的Bug数量，同时构建层级路径
    SELECT 
        mgr,
        en,
        COUNT(bug) AS en_bug_count,
        SYS_CONNECT_BY_PATH(en, '/') AS path,
        LEVEL AS lvl
    FROM t1
    START WITH mgr = 'x' -- 从顶级节点x开始遍历
    CONNECT BY PRIOR en = mgr
    GROUP BY mgr, en, LEVEL, SYS_CONNECT_BY_PATH(en, '/')
    UNION ALL
    -- 添加顶级节点x的汇总行
    SELECT 
        'x' AS mgr,
        NULL AS en,
        NULL AS en_bug_count,
        '/x' AS path,
        0 AS lvl
    FROM dual
),
cumulative_counts AS (
    -- 计算每个节点及其所有子节点的累计Bug数
    SELECT 
        mgr,
        en,
        en_bug_count,
        lvl AS "LEVEL",
        SUM(en_bug_count) OVER (
            ORDER BY path DESC
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS cumulative_bug_count
    FROM hierarchy_data
)
SELECT 
    mgr,
    en,
    en_bug_count,
    cumulative_bug_count,
    "LEVEL"
FROM cumulative_counts
ORDER BY "LEVEL", mgr, en;

语句解释 #

1. hierarchy_data CTE：

   • 第一部分通过CONNECT BY遍历层级结构，用COUNT(bug)统计每个节点自身的Bug数量，SYS_CONNECT_BY_PATH生成节点路径，用于后续的累计排序。
   • 第二部分手动添加顶级节点x的行，对应输出中的LEVEL 0。

2. cumulative_counts CTE：

   • 利用窗口函数SUM() OVER()，按路径倒序排序，计算当前行及后续所有行的总和，即当前节点及其所有子节点的累计Bug数。

3. 最终查询：

   • 按层级、经理、员工排序，输出符合要求的结果。

内容的提问来源于stack exchange，提问作者bprasanna