在数据仓库的层次建模时,常用递归的方式表示一颗层次树,但有些BI工具的前端不支持递归,所以为了实现数据下钻,可以把一棵递归树进行扩展。
-- 建立原始树表,并生成数据
CREATE TABLE TREE
(
C_PARENT INTEGER,
C_CHILD INTEGER
);
INSERT INTO TREE (C_PARENT, C_CHILD)
VALUES (NULL, 1002);
INSERT INTO TREE (C_PARENT, C_CHILD)
VALUES (1002, 1003);
INSERT INTO TREE (C_PARENT, C_CHILD)
VALUES (1002, 1004);
INSERT INTO TREE (C_PARENT, C_CHILD)
VALUES (1002, 1005);
INSERT INTO TREE (C_PARENT, C_CHILD)
VALUES (1003, 1006);
INSERT INTO TREE (C_PARENT, C_CHILD)
VALUES (1003, 1007);
INSERT INTO TREE (C_PARENT, C_CHILD)
VALUES (1003, 1008);
COMMIT;
-- 建立扩展的树表
CREATE TABLE TREE_EXPLODE
(
C_PARENT INTEGER, -- 父节点
C_CHILD INTEGER, -- 子节点
C_LEVEL INTEGER, -- 父节点所在层级
C_DISTANCE INTEGER, -- 父节点到子节点的层数
C_BOTTOM CHAR (1 BYTE), -- 是否叶子节点
C_SEQ VARCHAR2 (100 BYTE), -- 从树根到子节点的路径
EFF_DT DATE, -- 生效日期,用于维护历史信息
EXP_DT DATE -- 失效日期,用于维护历史信息
);
-- 建立存储过程生成扩展树表数据
CREATE OR REPLACE PROCEDURE p_tree_explode
IS
BEGIN
FOR x IN ( SELECT c_child, LEVEL c_level
FROM tree
START WITH c_parent IS NULL
CONNECT BY PRIOR c_child = c_parent)
LOOP
INSERT INTO tree_explode (c_parent,
c_child,
c_level,
c_distance,
c_bottom,
c_seq,
eff_dt,
exp_dt)
SELECT CONNECT_BY_ROOT a.c_child,
a.c_child,
x.c_level,
LEVEL - 1,
DECODE (CONNECT_BY_ISLEAF, 1, 'Y', 'N'),
( SELECT SYS_CONNECT_BY_PATH (c_child, '/')
FROM tree
WHERE c_child = a.c_child
START WITH c_parent IS NULL
CONNECT BY PRIOR c_child = c_parent),
TRUNC (SYSDATE),
TO_DATE ('9999-12-31', 'yyyy-mm-dd')
FROM tree a
START WITH a.c_child = x.c_child
CONNECT BY PRIOR a.c_child = a.c_parent;
END LOOP;
COMMIT;
END;
/
-- 测试
TRUNCATE TABLE tree_explode;
EXEC p_tree_explode;
SELECT * FROM tree_explode;