递归层次汇总查询

  在一个数据仓库项目中，人员、组织机构、事实表是这样设计的：

• 组织机构是一个树形结构
• 每一个人员只属于一层组织机构，该层为叶子节点和非叶子节点均可
• 事实表存储每个人员的数据

  要求按组织机构层次汇总数据，别且可下钻。

-- 建立测试表
-- 1. 组织机构

CREATE TABLE org
(
   org_id     NUMBER,
   org_name   VARCHAR2 (100),
   p_org_id   NUMBER
);

-- 2. 职员

CREATE TABLE emp
(
   emp_id     NUMBER,
   emp_name   VARCHAR2 (20),
   org_id     NUMBER
);

-- 3. 事实表

CREATE TABLE fact_data
(
   id             NUMBER,
   data_created   DATE,
   emp_id         NUMBER,
   work_hours     NUMBER,
   task_count     NUMBER
);


-- 插入测试数据
-- 组织机构

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (1, '事业部', NULL);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (2, '技术中心', 1);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (3, '营销中心', 1);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (4, '开发组', 2);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (5, '运维组', 2);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (6, '行业1', 3);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (7, '行业2', 3);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (8, '项目1', 4);

INSERT INTO ORG (ORG_ID, ORG_NAME, P_ORG_ID)
     VALUES (9, '项目2', 4);


-- 职员

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (1, '系统管理员', 1);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (2, '技术中心BOSS', 2);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (3, '营销中心BOSS', 3);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (4, '开发组LEADER', 4);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (5, '运维组LEADER', 5);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (6, '开发1', 8);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (7, '开发2', 8);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (8, '开发3', 8);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (9, '开发4', 9);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (10, '开发5', 9);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (11, '开发6', 9);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (12, '销售1', 6);

INSERT INTO EMP (EMP_ID, EMP_NAME, ORG_ID)
     VALUES (13, '销售2', 7);


-- 事实表

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (1,
             TRUNC (SYSDATE),
             1,
             10,
             3);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (2,
             TRUNC (SYSDATE),
             2,
             6,
             15);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (3,
             TRUNC (SYSDATE),
             3,
             4,
             7);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (4,
             TRUNC (SYSDATE),
             4,
             12,
             5);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (5,
             TRUNC (SYSDATE),
             5,
             30,
             13);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (6,
             TRUNC (SYSDATE),
             6,
             11,
             3);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (7,
             TRUNC (SYSDATE),
             7,
             5,
             13);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (8,
             TRUNC (SYSDATE),
             8,
             6,
             6);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (9,
             TRUNC (SYSDATE),
             9,
             1,
             2);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (10,
             TRUNC (SYSDATE),
             10,
             14,
             4);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (11,
             TRUNC (SYSDATE),
             11,
             11,
             11);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (12,
             TRUNC (SYSDATE),
             12,
             8,
             6);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (13,
             TRUNC (SYSDATE),
             13,
             9,
             5);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (14,
             TRUNC (SYSDATE - 1),
             1,
             10,
             3);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (15,
             TRUNC (SYSDATE - 1),
             2,
             6,
             15);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (16,
             TRUNC (SYSDATE - 1),
             3,
             4,
             7);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (17,
             TRUNC (SYSDATE - 1),
             4,
             12,
             5);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (18,
             TRUNC (SYSDATE - 1),
             5,
             30,
             13);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (19,
             TRUNC (SYSDATE - 1),
             6,
             11,
             3);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (20,
             TRUNC (SYSDATE - 1),
             7,
             5,
             13);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (21,
             TRUNC (SYSDATE - 1),
             8,
             6,
             6);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (22,
             TRUNC (SYSDATE - 1),
             9,
             1,
             2);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (23,
             TRUNC (SYSDATE - 1),
             10,
             14,
             4);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (24,
             TRUNC (SYSDATE - 1),
             11,
             11,
             11);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (25,
             TRUNC (SYSDATE - 1),
             12,
             8,
             6);

INSERT INTO FACT_DATA (ID,
                       DATA_CREATED,
                       EMP_ID,
                       WORK_HOURS,
                       TASK_COUNT)
     VALUES (26,
             TRUNC (SYSDATE - 1),
             13,
             9,
             5);

COMMIT;


-- 下钻查询
-- 一级

  SELECT f,
         root_org_id,
         root_org_name,
         s_work_hours,
         s_task_count
    FROM (  SELECT 2 f,
                   b.root_org_id,
                   b.root_org_name,
                   SUM (c.work_hours) s_work_hours,
                   SUM (c.task_count) s_task_count
              FROM emp a,
                   (    SELECT org_id,
                               CONNECT_BY_ROOT org_id root_org_id,
                               CONNECT_BY_ROOT org_name root_org_name
                          FROM org
                    START WITH p_org_id IS NULL
                    CONNECT BY PRIOR org_id = p_org_id) b,
                   fact_data c
             WHERE a.org_id = b.org_id AND c.emp_id = a.emp_id
          GROUP BY b.root_org_id, b.root_org_name
          UNION ALL
            SELECT 1,
                   a.org_id,
                   a.emp_name,
                   SUM (c.work_hours),
                   SUM (c.task_count)
              FROM emp a, fact_data c
             WHERE org_id IS NULL AND c.emp_id = a.emp_id
          GROUP BY a.org_id, a.emp_name)
ORDER BY root_org_id;

-- 二级

  SELECT f,
         root_org_id,
         root_org_name,
         s_work_hours,
         s_task_count
    FROM (  SELECT 2 f,
                   b.root_org_id,
                   b.root_org_name,
                   SUM (c.work_hours) s_work_hours,
                   SUM (c.task_count) s_task_count
              FROM emp a,
                   (    SELECT org_id,
                               CONNECT_BY_ROOT org_id root_org_id,
                               CONNECT_BY_ROOT org_name root_org_name
                          FROM org
                    START WITH p_org_id = 1
                    CONNECT BY PRIOR org_id = p_org_id) b,
                   fact_data c
             WHERE a.org_id = b.org_id AND c.emp_id = a.emp_id
          GROUP BY b.root_org_id, b.root_org_name
          UNION ALL
            SELECT 1,
                   a.org_id,
                   a.emp_name,
                   SUM (c.work_hours),
                   SUM (c.task_count)
              FROM emp a, fact_data c
             WHERE org_id = 1 AND c.emp_id = a.emp_id
          GROUP BY a.org_id, a.emp_name)
ORDER BY root_org_id;

-- 三级

  SELECT f,
         root_org_id,
         root_org_name,
         s_work_hours,
         s_task_count
    FROM (  SELECT 2 f,
                   b.root_org_id,
                   b.root_org_name,
                   SUM (c.work_hours) s_work_hours,
                   SUM (c.task_count) s_task_count
              FROM emp a,
                   (    SELECT org_id,
                               CONNECT_BY_ROOT org_id root_org_id,
                               CONNECT_BY_ROOT org_name root_org_name
                          FROM org
                    START WITH p_org_id = 2
                    CONNECT BY PRIOR org_id = p_org_id) b,
                   fact_data c
             WHERE a.org_id = b.org_id AND c.emp_id = a.emp_id
          GROUP BY b.root_org_id, b.root_org_name
          UNION ALL
            SELECT 1,
                   a.org_id,
                   a.emp_name,
                   SUM (c.work_hours),
                   SUM (c.task_count)
              FROM emp a, fact_data c
             WHERE org_id = 2 AND c.emp_id = a.emp_id
          GROUP BY a.org_id, a.emp_name)
ORDER BY root_org_id;

-- 四级

  SELECT f,
         root_org_id,
         root_org_name,
         s_work_hours,
         s_task_count
    FROM (  SELECT 2 f,
                   b.root_org_id,
                   b.root_org_name,
                   SUM (c.work_hours) s_work_hours,
                   SUM (c.task_count) s_task_count
              FROM emp a,
                   (    SELECT org_id,
                               CONNECT_BY_ROOT org_id root_org_id,
                               CONNECT_BY_ROOT org_name root_org_name
                          FROM org
                    START WITH p_org_id = 4
                    CONNECT BY PRIOR org_id = p_org_id) b,
                   fact_data c
             WHERE a.org_id = b.org_id AND c.emp_id = a.emp_id
          GROUP BY b.root_org_id, b.root_org_name
          UNION ALL
            SELECT 1,
                   a.org_id,
                   a.emp_name,
                   SUM (c.work_hours),
                   SUM (c.task_count)
              FROM emp a, fact_data c
             WHERE org_id = 4 AND c.emp_id = a.emp_id
          GROUP BY a.org_id, a.emp_name)
ORDER BY root_org_id;

  在这个实现中通过传入上级组织机构ID实现下钻汇总查询。