专栏首页Hadoop数据仓库递归树的平面化实验

递归树的平面化实验

  1. /***
  2. 已有维度表:
  3. dim_org -- 组织机构,组织为带有历史信息的递归树,其主键为SEQ_DIM_ORG_PK序列生成的代理键
  4. dim_person -- 人员表,带历史信息,org_pk关联到dim_org的代理键
  5. 目的:
  6. 数据以平面化完整树的形式交付给OLAP工具
  7. 功能:
  8. 依照dim_org定义固定的三级组织机构,每个人员关联第三级组织机构,dim_person.org_pk不足三级的补足三级,大于三级的归于第三级
  9. ***/
  10. -- 组织机构维度表
  11. CREATE TABLE DIM_ORG
  12. (
  13. ORG_PK NUMBER,
  14. ORG_NAME VARCHAR2(100 BYTE),
  15. P_ORG_PK NUMBER,
  16. EFF_DATE DATE,
  17. EXP_DATE DATE
  18. );
  19. ALTER TABLE DIM_ORG ADD (PRIMARY KEY (ORG_PK));
  20. -- 人员维度表
  21. CREATE TABLE DIM_PERSON
  22. (
  23. PERSON_PK NUMBER,
  24. PERSON_NAME VARCHAR2(30 BYTE),
  25. EFF_DATE DATE,
  26. EXP_DATE DATE,
  27. ORG_PK NUMBER
  28. );
  29. CREATE INDEX IDX_ORG_PK ON DIM_PERSON (ORG_PK);
  30. ALTER TABLE DIM_PERSON ADD (PRIMARY KEY (PERSON_PK));
  31. ALTER TABLE DIM_PERSON ADD (CONSTRAINT DIM_PERSON_R01 FOREIGN KEY (ORG_PK) REFERENCES DIM_ORG (ORG_PK));
  32. -- 建立组织机构平面化表
  33. CREATE TABLE tmp_org_level
  34. (
  35. org_pk NUMBER NOT NULL,
  36. org_1_pk NUMBER,
  37. org_1_name VARCHAR2 (100),
  38. org_2_pk NUMBER,
  39. org_2_name VARCHAR2 (100),
  40. org_3_pk NUMBER,
  41. org_3_name VARCHAR2 (100)
  42. );
  43. CREATE UNIQUE INDEX tmp_org_lavel_pk ON tmp_org_level (org_pk);
  44. ALTER TABLE tmp_org_level ADD (CONSTRAINT tmp_org_level_pk PRIMARY KEY (org_pk));
  45. -- 建立人员与组织机构平面化表的关联视图,提供给OLAP工具
  46. CREATE VIEW v_tree_complanate
  47. AS
  48. SELECT person_pk,
  49. person_name,
  50. org_1_pk,
  51. org_1_name,
  52. org_2_pk,
  53. org_2_name,
  54. org_3_pk,
  55. org_3_name
  56. FROM dim_person
  57. JOIN
  58. tmp_org_level
  59. ON dim_person.org_pk = tmp_org_level.org_pk;
  60. -- 建立平面化存储过程
  61. CREATE OR REPLACE PROCEDURE p_tree_complanate
  62. IS
  63. BEGIN
  64. -- 每次ETL时生成平面化表数据
  65. EXECUTE IMMEDIATE 'truncate table tmp_org_level';
  66. INSERT INTO tmp_org_level (org_pk,
  67. org_1_pk,
  68. org_1_name,
  69. org_2_pk,
  70. org_2_name,
  71. org_3_pk,
  72. org_3_name)
  73. SELECT org_pk,
  74. SUBSTR (c_pk_path,
  75. INSTR (c_pk_path,
  76. '/',
  77. 1,
  78. 1)
  79. + 1,
  80. INSTR (c_pk_path,
  81. '/',
  82. 1,
  83. 2)
  84. - INSTR (c_pk_path,
  85. '/',
  86. 1,
  87. 1)
  88. - 1)
  89. org_1_pk,
  90. SUBSTR (c_name_path,
  91. INSTR (c_name_path,
  92. '/',
  93. 1,
  94. 1)
  95. + 1,
  96. INSTR (c_name_path,
  97. '/',
  98. 1,
  99. 2)
  100. - INSTR (c_name_path,
  101. '/',
  102. 1,
  103. 1)
  104. - 1)
  105. org_1_name,
  106. SUBSTR (c_pk_path,
  107. INSTR (c_pk_path,
  108. '/',
  109. 1,
  110. 2)
  111. + 1,
  112. INSTR (c_pk_path,
  113. '/',
  114. 1,
  115. 3)
  116. - INSTR (c_pk_path,
  117. '/',
  118. 1,
  119. 2)
  120. - 1)
  121. org_2_pk,
  122. SUBSTR (c_name_path,
  123. INSTR (c_name_path,
  124. '/',
  125. 1,
  126. 2)
  127. + 1,
  128. INSTR (c_name_path,
  129. '/',
  130. 1,
  131. 3)
  132. - INSTR (c_name_path,
  133. '/',
  134. 1,
  135. 3)
  136. - 1)
  137. org_2_name,
  138. SUBSTR (c_pk_path,
  139. INSTR (c_pk_path,
  140. '/',
  141. 1,
  142. 3)
  143. + 1,
  144. INSTR (c_pk_path,
  145. '/',
  146. 1,
  147. 4)
  148. - INSTR (c_pk_path,
  149. '/',
  150. 1,
  151. 3)
  152. - 1)
  153. org_3_pk,
  154. SUBSTR (c_name_path,
  155. INSTR (c_name_path,
  156. '/',
  157. 1,
  158. 3)
  159. + 1,
  160. INSTR (c_name_path,
  161. '/',
  162. 1,
  163. 4)
  164. - INSTR (c_name_path,
  165. '/',
  166. 1,
  167. 3)
  168. - 1)
  169. org_3_name
  170. FROM ( SELECT org_pk,
  171. org_name,
  172. SYS_CONNECT_BY_PATH (org_pk, '/') || '/' c_pk_path,
  173. SYS_CONNECT_BY_PATH (org_name, '/') || '/' c_name_path
  174. FROM dim_org
  175. START WITH p_Org_pk IS NULL
  176. CONNECT BY PRIOR org_pk = p_org_pk);
  177. -- 补足第二级
  178. UPDATE tmp_org_level
  179. SET org_2_pk = SEQ_DIM_ORG_PK.NEXTVAL,
  180. org_2_name = org_1_name || '本部科室'
  181. WHERE org_2_pk IS NULL;
  182. -- 补足第三级
  183. UPDATE tmp_org_level
  184. SET org_3_pk = SEQ_DIM_ORG_PK.NEXTVAL,
  185. org_3_name = org_2_name || '本部小组'
  186. WHERE org_3_pk IS NULL;
  187. COMMIT;
  188. END;
  189. /

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle 10g 流复制(Streams Replication)配置

    Oracle 流是一种数据共享的通用机制,可以被用于许多处理的基础,包括消息、复制和数据仓库的 ETL 过程。它是高级队列、LogMinor、作业调度等已存在技...

    用户1148526
  • 递归层次汇总查询

    用户1148526
  • 使用Oracle with内嵌视图优化一例

    用户1148526
  • spring service层单元测试

    service层测试较简单,目前大多数测试主要是针对public方法进行的。依据测试方法划分,可以分为两种:基于mock的隔离测试和基于dbunit的普通测试。...

    YGingko
  • FastDFS的使用

    FastDFS安装(http://blog.csdn.net/LoveCarpenter/article/details/75913329)

    用户5927264
  • 代码演示Mybatis-Generator 扩展自定义生成

    Mybatis-Generator 可自动生成Model、Dao、Mapper代码,但其自带生成的代码存在以下问题:

    宜信技术学院
  • Django学习路由(5)

    电话A 打电话给 1001 需要用到中转设备来指路,走那条线路可以到达目的地。如果表中没有,则不管他!

    萌海无涯
  • SpringBoot之Jms

    前言:JMS即Java消息服务(Java Message Service)应用程序接口,是一个Java平台中关于面向消息中间件(MOM)的API,用于在两个应用...

    王念博客
  • Python | 发包 2018 入坑

    最近业务需要抽离,抽离出来的应用需要做成 Django 第三方包的形式,可以在任何 Django(也没那么神奇,例如有些版本就没测试)版本项目中,直接安装使用,...

    py3study
  • Shiro教程7(整合SSM项目-授权)

      首先授权必须是在认证通过之后才会执行的操作,之前我们在Shiro教程4(授权操作)该教程中讲过,获取权限我们是通过如下方法实现的

    用户4919348

扫码关注云+社区

领取腾讯云代金券