用户1148526
递归树的平面化实验
关注作者
前往小程序,Get
更优
阅读体验!
立即前往
腾讯云
开发者社区
文档
建议反馈
控制台
登录/注册
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
搜索
搜索
关闭
发布
首页
学习
活动
专区
工具
TVP
最新优惠活动
返回腾讯云官网
用户1148526
首页
学习
活动
专区
工具
TVP
最新优惠活动
返回腾讯云官网
社区首页
>
专栏
>
递归树的平面化实验
递归树的平面化实验
用户1148526
关注
发布于 2019-05-25 19:43:59
365
0
发布于 2019-05-25 19:43:59
举报
文章被收录于专栏:
Hadoop数据仓库
/***
已有维度表:
dim_org -- 组织机构,组织为带有历史信息的递归树,其主键为SEQ_DIM_ORG_PK序列生成的代理键
dim_person -- 人员表,带历史信息,org_pk关联到dim_org的代理键
目的:
数据以平面化完整树的形式交付给OLAP工具
功能:
依照dim_org定义固定的三级组织机构,每个人员关联第三级组织机构,dim_person.org_pk不足三级的补足三级,大于三级的归于第三级
***/
-- 组织机构维度表
CREATE
TABLE
DIM_ORG
(
ORG_PK NUMBER,
ORG_NAME VARCHAR2(100 BYTE),
P_ORG_PK NUMBER,
EFF_DATE
DATE
,
EXP_DATE
DATE
);
ALTER
TABLE
DIM_ORG
ADD
(
PRIMARY
KEY
(ORG_PK));
-- 人员维度表
CREATE
TABLE
DIM_PERSON
(
PERSON_PK NUMBER,
PERSON_NAME VARCHAR2(30 BYTE),
EFF_DATE
DATE
,
EXP_DATE
DATE
,
ORG_PK NUMBER
);
CREATE
INDEX
IDX_ORG_PK
ON
DIM_PERSON (ORG_PK);
ALTER
TABLE
DIM_PERSON
ADD
(
PRIMARY
KEY
(PERSON_PK));
ALTER
TABLE
DIM_PERSON
ADD
(
CONSTRAINT
DIM_PERSON_R01
FOREIGN
KEY
(ORG_PK)
REFERENCES
DIM_ORG (ORG_PK));
-- 建立组织机构平面化表
CREATE
TABLE
tmp_org_level
(
org_pk NUMBER NOT NULL,
org_1_pk NUMBER,
org_1_name VARCHAR2 (100),
org_2_pk NUMBER,
org_2_name VARCHAR2 (100),
org_3_pk NUMBER,
org_3_name VARCHAR2 (100)
);
CREATE
UNIQUE
INDEX
tmp_org_lavel_pk
ON
tmp_org_level (org_pk);
ALTER
TABLE
tmp_org_level
ADD
(
CONSTRAINT
tmp_org_level_pk
PRIMARY
KEY
(org_pk));
-- 建立人员与组织机构平面化表的关联视图,提供给OLAP工具
CREATE
VIEW
v_tree_complanate
AS
SELECT
person_pk,
person_name,
org_1_pk,
org_1_name,
org_2_pk,
org_2_name,
org_3_pk,
org_3_name
FROM
dim_person
JOIN
tmp_org_level
ON
dim_person.org_pk = tmp_org_level.org_pk;
-- 建立平面化存储过程
CREATE
OR REPLACE
PROCEDURE
p_tree_complanate
IS
BEGIN
-- 每次ETL时生成平面化表数据
EXECUTE
IMMEDIATE 'truncate table tmp_org_level';
INSERT
INTO
tmp_org_level (org_pk,
org_1_pk,
org_1_name,
org_2_pk,
org_2_name,
org_3_pk,
org_3_name)
SELECT
org_pk,
SUBSTR (c_pk_path,
INSTR (c_pk_path,
'/',
1,
1)
+ 1,
INSTR (c_pk_path,
'/',
1,
2)
- INSTR (c_pk_path,
'/',
1,
1)
- 1)
org_1_pk,
SUBSTR (c_name_path,
INSTR (c_name_path,
'/',
1,
1)
+ 1,
INSTR (c_name_path,
'/',
1,
2)
- INSTR (c_name_path,
'/',
1,
1)
- 1)
org_1_name,
SUBSTR (c_pk_path,
INSTR (c_pk_path,
'/',
1,
2)
+ 1,
INSTR (c_pk_path,
'/',
1,
3)
- INSTR (c_pk_path,
'/',
1,
2)
- 1)
org_2_pk,
SUBSTR (c_name_path,
INSTR (c_name_path,
'/',
1,
2)
+ 1,
INSTR (c_name_path,
'/',
1,
3)
- INSTR (c_name_path,
'/',
1,
3)
- 1)
org_2_name,
SUBSTR (c_pk_path,
INSTR (c_pk_path,
'/',
1,
3)
+ 1,
INSTR (c_pk_path,
'/',
1,
4)
- INSTR (c_pk_path,
'/',
1,
3)
- 1)
org_3_pk,
SUBSTR (c_name_path,
INSTR (c_name_path,
'/',
1,
3)
+ 1,
INSTR (c_name_path,
'/',
1,
4)
- INSTR (c_name_path,
'/',
1,
3)
- 1)
org_3_name
FROM
(
SELECT
org_pk,
org_name,
SYS_CONNECT_BY_PATH (org_pk, '/') || '/' c_pk_path,
SYS_CONNECT_BY_PATH (org_name, '/') || '/' c_name_path
FROM
dim_org
START
WITH
p_Org_pk
IS
NULL
CONNECT
BY
PRIOR
org_pk = p_org_pk);
-- 补足第二级
UPDATE
tmp_org_level
SET
org_2_pk = SEQ_DIM_ORG_PK.NEXTVAL,
org_2_name = org_1_name || '本部科室'
WHERE
org_2_pk
IS
NULL;
-- 补足第三级
UPDATE
tmp_org_level
SET
org_3_pk = SEQ_DIM_ORG_PK.NEXTVAL,
org_3_name = org_2_name || '本部小组'
WHERE
org_3_pk
IS
NULL;
COMMIT
;
END
;
/
本文参与
腾讯云自媒体同步曝光计划
,分享自作者个人站点/博客。
原始发表:2016年12月28日,如有侵权请联系
cloudcommunity@tencent.com
删除
前往查看
云推荐引擎
本文分享自
作者个人站点/博客
前往查看
如有侵权,请联系
cloudcommunity@tencent.com
删除。
本文参与
腾讯云自媒体同步曝光计划
,欢迎热爱写作的你一起参与!
云推荐引擎
评论
登录
后参与评论
0 条评论
热度
最新
推荐阅读
LV.
文章
0
获赞
0
领券
问题归档
专栏文章
快讯文章归档
关键词归档
开发者手册归档
开发者手册 Section 归档
0
0
0
推荐