Oracle中Hierarchical层级树如何按层级实现逐级汇总求和

问题现象 #

实现树结构按层级从底向上汇总求和时，非叶子层级节点返回数值为空，仅最末级节点可匹配到对应数值，无法满足逐级汇总要求。

原有存在问题的查询语句 #

SELECT 
TREE.*, 
FVVVL.DESCRIPTION,
--SUM(VALUES_GL.TOTAL)
VALUES_GL.TOTAL
FROM (
    SELECT 
    PK1_START_VALUE, 
    PARENT_PK1_VALUE, 
    CONNECT_BY_ISCYCLE "Cycle",
    LEVEL, 
    SYS_CONNECT_BY_PATH(PK1_START_VALUE, '/') "Path" 
    FROM 
    FND_TREE_NODE 
    WHERE TREE_CODE = 'CGM_ESF'
    START WITH PK1_START_VALUE = 'ESF_A'
    CONNECT BY NOCYCLE  PRIOR  PK1_START_VALUE = PARENT_PK1_VALUE AND LEVEL <= 5
    --ORDER SIBLINGS BY PK1_START_VALUE
) TREE
INNER JOIN FND_VS_VALUES_VL FVVVL ON FVVVL.VALUE = TREE.PK1_START_VALUE
LEFT JOIN (
    SELECT 
    NVL(GLL.ACCOUNTED_DR, GLL.ACCOUNTED_CR * -1 ) AS TOTAL, 
    GLL.PERIOD_NAME, TO_CHAR(GLL.EFFECTIVE_DATE, 'DD-MM-YYYY'), 
    GLL.CODE_COMBINATION_ID,
    GLL.LEDGER_ID,
    GCC.SEGMENT2
    FROM GL_JE_LINES GLL
    INNER JOIN GL_CODE_COMBINATIONS GCC ON GLL.CODE_COMBINATION_ID = GLL.CODE_COMBINATION_ID
) VALUES_GL ON VALUES_GL.SEGMENT2 = TREE.PK1_START_VALUE --AND TREE.LEVEL = 5
ORDER BY "Path"

预期汇总规则 #

当前树结构默认设置5个层级，后续支持层级扩展，逐级汇总规则如下：

• Level 4需展示其下属所有Level 5节点的数值总和
• Level 3需展示其下属所有Level 4节点的数值总和
• Level 2需展示其下属所有Level 3节点的数值总和
• Level 1需展示其下属所有Level 2节点的数值总和

问题根因 #

1. 凭证数据关联逻辑错误：仅将当前树节点的编码与凭证SEGMENT2做等值匹配，非叶子节点本身无对应凭证明细，必然返回空值，未建立父节点与下属所有子节点的归属关联，无法实现子节点数值向上汇总
2. GL表关联条件写错：GL_JE_LINES与GL_CODE_COMBINATIONS关联时两侧均使用GLL.CODE_COMBINATION_ID，未正确关联GCC表字段，会产生笛卡尔积导致数据错误

修正后可用语句 #

SELECT 
  TREE_NODE.PK1_START_VALUE,
  TREE_NODE.PARENT_PK1_VALUE,
  TREE_NODE."Cycle",
  TREE_NODE."LEVEL",
  TREE_NODE."Path",
  FVVVL.DESCRIPTION,
  SUM(VALUES_GL.TOTAL) AS TOTAL
FROM (
  -- 生成每个节点与自身所有下属子节点的映射关系
  SELECT 
    CONNECT_BY_ROOT PK1_START_VALUE AS ROOT_PK_VALUE,
    PK1_START_VALUE
  FROM FND_TREE_NODE
  WHERE TREE_CODE = 'CGM_ESF'
  START WITH PARENT_PK1_VALUE IS NOT NULL
  CONNECT BY NOCYCLE PRIOR PK1_START_VALUE = PARENT_PK1_VALUE AND LEVEL <= 5
) TREE_MAP
-- 关联树节点基础属性
INNER JOIN (
  SELECT 
    PK1_START_VALUE, 
    PARENT_PK1_VALUE, 
    CONNECT_BY_ISCYCLE "Cycle",
    LEVEL, 
    SYS_CONNECT_BY_PATH(PK1_START_VALUE, '/') "Path" 
  FROM FND_TREE_NODE 
  WHERE TREE_CODE = 'CGM_ESF'
  START WITH PK1_START_VALUE = 'ESF_A'
  CONNECT BY NOCYCLE PRIOR PK1_START_VALUE = PARENT_PK1_VALUE AND LEVEL <=5
) TREE_NODE ON TREE_MAP.ROOT_PK_VALUE = TREE_NODE.PK1_START_VALUE
INNER JOIN FND_VS_VALUES_VL FVVVL ON FVVVL.VALUE = TREE_NODE.PK1_START_VALUE
-- 关联修正后的GL凭证明细
LEFT JOIN (
  SELECT 
    NVL(GLL.ACCOUNTED_DR, GLL.ACCOUNTED_CR * -1 ) AS TOTAL, 
    GCC.SEGMENT2
  FROM GL_JE_LINES GLL
  INNER JOIN GL_CODE_COMBINATIONS GCC 
    ON GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
  -- 按需增加期间、账套等过滤条件
) VALUES_GL ON VALUES_GL.SEGMENT2 = TREE_MAP.PK1_START_VALUE
GROUP BY 
  TREE_NODE.PK1_START_VALUE,
  TREE_NODE.PARENT_PK1_VALUE,
  TREE_NODE."Cycle",
  TREE_NODE."LEVEL",
  TREE_NODE."Path",
  FVVVL.DESCRIPTION
ORDER BY TREE_NODE."Path"

逻辑说明 #

• 核心通过CONNECT_BY_ROOT获取每个节点作为根节点时对应的所有下属子节点编码，将所有子节点对应的凭证值汇总后，即为当前节点的层级汇总值，无需逐层写固定汇总逻辑
• 修正了原GL表关联的字段错误，避免数据计算异常
• 后续需要扩展树层级时，仅需修改CONNECT BY后的LEVEL <= 5阈值即可，无需调整核心汇总逻辑
• 若需要按期间、账套、日期等维度拆分统计，只需在GL明细子查询中增加对应过滤条件，同时在外层GROUP BY中补充对应维度字段即可

内容的提问来源于stack exchange，提问作者Edisson Gabriel López