层次查询

层次查询

create table emp_hire as
select empno,mgr,ename from emp

select * from emp_hire
EMPNO	MGR	ENAME
5555	7900	ggg
7369	7902	SMITH
7499	7698	ALLEN
7521	7698	WARD
7566	7839	JONES
7654	7698	MA & RTIN
7698	7839	BLAKE
7782	7839	CLARK
7788	7566	SCOTT
7839	　	KING
7844	7698	TURNER
7900	7698	JAMES
7902	7566	FORD
7934	7782	MILLER
7876	7788	ADAMS

这些数据存在着层次关系
select 
   lpad('*',level,'*')||e.ename ename, 
   e.empno, 
   mgr fa_id, 
   (select ename from emp_hire where empno=e.mgr) fa_name
from emp_hire e
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR

ENAME	EMPNO	FA_ID	FA_NAME
*KING	7839	　	　
**JONES	7566	7839	KING
***SCOTT	7788	7566	JONES
****ADAMS	7876	7788	SCOTT
***FORD	7902	7566	JONES
****SMITH	7369	7902	FORD
**BLAKE	7698	7839	KING
***ALLEN	7499	7698	BLAKE
***WARD	7521	7698	BLAKE
***MA & RTIN	7654	7698	BLAKE
***TURNER	7844	7698	BLAKE
***JAMES	7900	7698	BLAKE
****ggg	5555	7900	JAMES
**CLARK	7782	7839	KING
***MILLER	7934	7782	CLARK



其中
start with -- this identifies all LEVEL=1 nodes in the tree

connect by -- describes how to walk from the parent nodes above to their children and their childrens children.

层次是一棵树， 又如同一个家族图谱
 

每一个子节电只有一个父节点；
每一个分支，最末端是叶节点；

SELECT 
ename  Employee, 
CONNECT_BY_ISLEAF  IsLeaf,
LEVEL lev, 
SYS_CONNECT_BY_PATH(ename, '/')  Path
FROM emp
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR

EMPLOYEE	ISLEAF	LEV	PATH
KING	0	1	/KING
JONES	0	2	/KING/JONES
SCOTT	0	3	/KING/JONES/SCOTT
ADAMS	1	4	/KING/JONES/SCOTT/ADAMS
FORD	0	3	/KING/JONES/FORD
SMITH	1	4	/KING/JONES/FORD/SMITH
BLAKE	0	2	/KING/BLAKE
ALLEN	1	3	/KING/BLAKE/ALLEN
WARD	1	3	/KING/BLAKE/WARD
MA & RTIN	1	3	/KING/BLAKE/MA & RTIN
TURNER	1	3	/KING/BLAKE/TURNER
JAMES	0	3	/KING/BLAKE/JAMES
ggg	1	4	/KING/BLAKE/JAMES/ggg
CLARK	0	2	/KING/CLARK
MILLER	1	3	/KING/CLARK/MILLER



检查一个层次是否存在闭循环
CONNECT_BY_ISCYCLE

找出根节点
CONNECT_BY_ROOT

SELECT 
ename  Employee, 
CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT ename 
FROM emp
    START WITH MGR IS NULL
    CONNECT BY NOCYCLE PRIOR EMPNO = MGR
EMPLOYEE	CONNECT_BY_ISCYCLE	CONNECT_BY_ROOTENAME
KING	0	KING
JONES	0	KING
SCOTT	0	KING
ADAMS	0	KING
FORD	0	KING
SMITH	0	KING
BLAKE	0	KING
ALLEN	0	KING
WARD	0	KING
MA & RTIN	0	KING
TURNER	0	KING
JAMES	0	KING
ggg	0	KING
CLARK	0	KING
MILLER	0	KING


层次查询的过滤条件
SELECT 
ename  Employee, 
CONNECT_BY_ISLEAF  IsLeaf,
LEVEL lev, 
SYS_CONNECT_BY_PATH(ename, '/')  Path
FROM emp
where level<3
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR

Restriction on LEVEL in WHERE Clauses In a [NOT] IN condition in a WHERE clause, if the right-hand side of the condition is a subquery, you cannot use LEVEL on the left-hand side of the condition. However, you can specify LEVEL in a subquery of the FROM clause to achieve the same result. For example, the following statement is not valid:

SELECT employee_id, last_name FROM employees
WHERE (employee_id, LEVEL)
IN (SELECT employee_id, 2 FROM employees)
START WITH employee_id = 2
CONNECT BY PRIOR employee_id = manager_id;
But the following statement is valid because it encapsulates the query containing the
LEVEL information in the FROM clause:

SELECT v.employee_id, v.last_name, v.lev
FROM
(SELECT employee_id, last_name, LEVEL lev
FROM employees v
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id) v
WHERE (v.employee_id, v.lev) IN
(SELECT employee_id, 2 FROM employees);



SIBLINGS的排序

SELECT 
ename  Employee, 
CONNECT_BY_ISLEAF  IsLeaf,
LEVEL lev, 
SYS_CONNECT_BY_PATH(ename, '/')  Path
FROM emp
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ename desc
EMPLOYEE	ISLEAF	LEV	PATH
KING	0	1	/KING
JONES	0	2	/KING/JONES
SCOTT	0	3	/KING/JONES/SCOTT
ADAMS	1	4	/KING/JONES/SCOTT/ADAMS
FORD	0	3	/KING/JONES/FORD
SMITH	1	4	/KING/JONES/FORD/SMITH
CLARK	0	2	/KING/CLARK
MILLER	1	3	/KING/CLARK/MILLER
BLAKE	0	2	/KING/BLAKE
WARD	1	3	/KING/BLAKE/WARD
TURNER	1	3	/KING/BLAKE/TURNER
MA & RTIN	1	3	/KING/BLAKE/MA & RTIN
JAMES	0	3	/KING/BLAKE/JAMES
ggg	1	4	/KING/BLAKE/JAMES/ggg
ALLEN	1	3	/KING/BLAKE/ALLEN

注意：ORDER SIBLINGS By一定与start with 与 connect by一起使用



检查树是否有闭循环
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;

Employee	Cycle	LEVEL	Path
King	0	1	/King
Russell	0	2	/King/Russell
Tucker	0	3	/King/Russell/Tucker
Bernstein	0	3	/King/Russell/Bernstein
Hall	0	3	/King/Russell/Hall
Olsen	0	3	/King/Russell/Olsen
Cambrault	0	3	/King/Russell/Cambrault
Tuvault	0	3	/King/Russell/Tuvault
Partners	0	2	/King/Partners
King	0	3	/King/Partners/King



层次查询的累计运算

将非叶节点的度量sal置为空
update emp_hire set sal=null
where empno in
(
select empno from
(
SELECT 
empno, 
CONNECT_BY_ISLEAF  IsLeaf,
LEVEL lev, 
SYS_CONNECT_BY_PATH(ename, '/')  Path
FROM emp
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR
)
where isleaf=0
)

察看数据

SELECT 
empno, 
CONNECT_BY_ISLEAF  IsLeaf,
LEVEL lev, 
SYS_CONNECT_BY_PATH(ename, '/')  Path,
sal
FROM emp_hire
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR

EMPNO	ISLEAF	LEV	PATH	SAL
7839	0	1	/KING	　
7566	0	2	/KING/JONES	　
7788	0	3	/KING/JONES/SCOTT	　
7876	1	4	/KING/JONES/SCOTT/ADAMS	1100.00
7902	0	3	/KING/JONES/FORD	　
7369	1	4	/KING/JONES/FORD/SMITH	800.00
7698	0	2	/KING/BLAKE	　
7499	1	3	/KING/BLAKE/ALLEN	1600.00
7521	1	3	/KING/BLAKE/WARD	1250.00
7654	1	3	/KING/BLAKE/MA & RTIN	1250.00
7844	1	3	/KING/BLAKE/TURNER	1500.00
7900	0	3	/KING/BLAKE/JAMES	　
5555	1	4	/KING/BLAKE/JAMES/ggg	0.00
7782	0	2	/KING/CLARK	　
7934	1	3	/KING/CLARK/MILLER	1300.00

select 
sum(sal),
 sum(power(0.5,(level-1))*sal) 
from emp_hire
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR

SUM(SAL)	SUM(POWER(0.5,(LEVEL-1))*SAL)
8800	1962.5


我们不能用以下的方法计算累计值
select 
SYS_CONNECT_BY_PATH(ename, '/')  Path, sum(sal),
 sum(power(0.5,(level-1))*sal) 
from emp_hire
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR
group by Path

我们可以找出一个节点的所有子孙节点，以及这些节点所在的level

select 
empno,   
SYS_CONNECT_BY_PATH(ename, '/')  Path, sal,
--,sum(power(0.5,(level-1))*sal) 
(
select sum(power(0.5,(level-1))*nvl(sal,0)) from emp_hire
 start with empno=e.empno
 connect by prior empno= mgr
) s_sal,
(
select sum(nvl(sal,0)) from emp_hire
 start with empno=e.empno
 connect by prior empno= mgr
) s1_sal
from emp_hire e
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR
EMPNO	PATH	SAL	S_SAL	S1_SAL
7839	/KING	　	1962.5	8800
7566	/KING/JONES	　	475	1900
7788	/KING/JONES/SCOTT	　	550	1100
7876	/KING/JONES/SCOTT/ADAMS	1100.00	1100	1100
7902	/KING/JONES/FORD	　	400	800
7369	/KING/JONES/FORD/SMITH	800.00	800	800
7698	/KING/BLAKE	　	2800	5600
7499	/KING/BLAKE/ALLEN	1600.00	1600	1600
7521	/KING/BLAKE/WARD	1250.00	1250	1250
7654	/KING/BLAKE/MA & RTIN	1250.00	1250	1250
7844	/KING/BLAKE/TURNER	1500.00	1500	1500
7900	/KING/BLAKE/JAMES	　	0	0
5555	/KING/BLAKE/JAMES/ggg	0.00	0	0
7782	/KING/CLARK	　	650	1300
7934	/KING/CLARK/MILLER	1300.00	1300	1300


也可以通过以下过程实现：

create type myTable is table of varchar2(100);

CREATE OR REPLACE FUNCTION str_to_table (
                 string_in    IN VARCHAR2 ,
                 delimiter_in IN VARCHAR2 DEFAULT ','
                 ) RETURN myTable IS
       v_wkg_str VARCHAR2 ( 32767 ) := string_in || delimiter_in;
       v_pos     PLS_INTEGER ;
       nt_return myTable := myTable();
       i_count  pls_integer;
    BEGIN
       LOOP
          v_pos := INSTR(v_wkg_str,delimiter_in);
          EXIT WHEN NVL(v_pos, 0 ) = 0  or v_wkg_str=delimiter_in;
          if TRIM (SUBSTR(v_wkg_str, 1 ,v_pos- 1 )) is not null  then
          nt_return. EXTEND ;
          nt_return(nt_return. LAST ) := TRIM (SUBSTR(v_wkg_str, 1 ,v_pos- 1 ));
          end if;
          v_wkg_str := SUBSTR(v_wkg_str,v_pos+ 1 );
       END LOOP ;
       RETURN nt_return;     
    END ;


declare 
--create or replace type v2 is table of varchar2(2000)
  c_path v2;
  c_level v1;
  c_empno v1;
  v_pos   integer;
  v_string  varchar2(2000);
begin
select 
empno,SYS_CONNECT_BY_PATH(empno, '/')  Path, level lev 
bulk collect into c_empno,c_path,c_level
from emp_hire
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR;
for i in 1..c_empno.count loop
v_string:='';
for j in 1..c_path.count loop

v_pos := INSTR(c_path(j),c_empno(i));
if v_pos>0 and c_empno(i)<>c_empno(j) then
v_string:=v_string||'/'||c_empno(j);

end if;
end loop; 
dbms_output.put_line(c_empno(i)||'   '||v_string);
end loop;      
end;

输出结果
7839   /7566/7788/7876/7902/7369/7698/7499/7521/7654/7844/7900/5555/7782/7934
7566   /7788/7876/7902/7369
7788   /7876
7876   
7902   /7369
7369   
7698   /7499/7521/7654/7844/7900/5555
7499   
7521   
7654   
7844   
7900   /5555
5555   
7782   /7934
7934

如果要计算累计结果则
declare 
--create or replace type v2 is table of varchar2(2000)
  c_path v2;
  c_level v1;
  c_empno v1;
  c_sal  v1;
  v_pos   integer;
  v_string  varchar2(2000);
  v_sal  number;
 
begin
select 
empno,SYS_CONNECT_BY_PATH(empno, '/')  Path, level lev, nvl(sal,0)
bulk collect into c_empno,c_path,c_level,c_sal
from emp_hire
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR;
for i in 1..c_empno.count loop
v_string:='';
v_sal:=0;
for j in 1..c_path.count loop

v_pos := INSTR(c_path(j),c_empno(i));
if v_pos>0   then
if c_empno(i)<>c_empno(j) then
v_string:=v_string||'/'||c_empno(j);
end if;
v_sal:=v_sal+c_sal(j);
end if;
end loop; 
dbms_output.put_line(c_empno(i)||'   '||v_string||'   '||v_sal);
end loop;      
end;

7839   8800   /7566/7788/7876/7902/7369/7698/7499/7521/7654/7844/7900/5555/7782/7934
7566   1900   /7788/7876/7902/7369
7788   1100   /7876
7876   1100   
7902   800   /7369
7369   800   
7698   5600   /7499/7521/7654/7844/7900/5555
7499   1600   
7521   1250   
7654   1250   
7844   1500   
7900   0   /5555
5555   0   
7782   1300   /7934
7934   1300


如果存在一定的累计规则
则
declare 
--create or replace type v2 is table of varchar2(2000)
  c_path v2;
  c_level v1;
  c_empno v1;
  c_sal  v1;
  v_pos   integer;
  v_string  varchar2(2000);
  v_sal  number;
 
begin
select 
empno,SYS_CONNECT_BY_PATH(empno, '/')  Path, level lev, nvl(sal,0)
bulk collect into c_empno,c_path,c_level,c_sal
from emp_hire
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR;
for i in 1..c_empno.count loop
v_string:='';
v_sal:=0;
for j in 1..c_path.count loop

v_pos := INSTR(c_path(j),c_empno(i));
if v_pos>0   then
if c_empno(i)<>c_empno(j) then
v_string:=v_string||'/'||c_empno(j);
end if;
v_sal:=v_sal+power(0.5,(c_level(j)-c_level(i)))*c_sal(j);
end if;
end loop; 
dbms_output.put_line(c_empno(i)||'   '||'    '||c_sal(i)||'    '||v_sal||'   '||v_string);
end loop;      
end;

7839       0    1962.5   /7566/7788/7876/7902/7369/7698/7499/7521/7654/7844/7900/5555/7782/7934
7566       0    475   /7788/7876/7902/7369
7788       0    550   /7876
7876       1100    1100   
7902       0    400   /7369
7369       800    800   
7698       0    2800   /7499/7521/7654/7844/7900/5555
7499       1600    1600   
7521       1250    1250   
7654       1250    1250   
7844       1500    1500   
7900       0    0   /5555
5555       0    0   
7782       0    650   /7934
7934       1300    1300  

-----------------------------------------------------------------------------------------------------------

SELECT 
empno,
ename  Employee, 
CONNECT_BY_ROOT ename,
level lev 
FROM (select * from emp_hire where ename<>'KING')
    START WITH MGR IS NULL
    CONNECT BY NOCYCLE PRIOR EMPNO = MGR


层次查询的效率

Explain plan for
select 
   lpad('*',level,'*')||e.ename ename, 
   e.empno, 
   mgr fa_id
from emp_hire e
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR
    
    
select * from table(dbms_xplan.display());


Plan hash value: 2148023842
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |    15 |   495 |     3   (0)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|          |       |       |            |          |
|   2 |   TABLE ACCESS FULL                     | EMP_HIRE |    15 |   495 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("MGR"=PRIOR "EMPNO")
       filter("MGR" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement

层次查询从本质上来说是递归运算，因此对于大的层次查询，可以分解为多个层次查询，从而减少计算量。

层次查询的副产品
产生自然数的查询
select rownum r  from dual  connect by level <= 100



层次向普通对称维度表的转换