前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >递归层次汇总查询

递归层次汇总查询

作者头像
用户1148526
发布2019-05-25 19:52:29
8670
发布2019-05-25 19:52:29
举报
文章被收录于专栏:Hadoop数据仓库

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

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

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

代码语言:javascript
复制
-- 建立测试表
-- 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实现下钻汇总查询。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2016年12月28日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
对象存储
对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档