Hierarchical Queries

Browser : Chrome Browser

오늘도 역시 이리저리 일하다가, 우연히 계층 구조 쿼리를 보게 되었는데.
갑자기 어느 신입이 생각이 났다. 왜(?)인지는 나도 모르겠는데...

하여튼 그 신입은 내가 기억하기로 프로그램 개발을 잘 했던 걸로 기억한다.
일거리를 던져주면 열심히 코딩도 하고, 이미 만들어진 프로그램을 분석해서 자기 것으로 하는 것도 능숙해 보였다.
그런데 아쉬움 점 하나, DB 는 학원에서 속성으로 배우다 보니 잘 모른다는 것.
회사에서 공기같은 나에게(내가 출근한 사실을 아무도 모르게 하라 - 나무명은 나의 우상) 이 신입이 그나마 물어보는 경우는 DB 관련 내용이였다.
신입 말로는 다른 사람에게 묻자니 좀 그렇고 내가 젤 편하다고... 흠.
뭐 이유가 어떻든 물어보니 이것저것 설명을 해주었던 기억이 나는데...

START WITH
- 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
- 서브쿼리를 사용할 수도 있다.

CONNECT BY
- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
- PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
- CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
- CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
- CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
- 서브쿼리를 사용할 수 없다.

ORDER SIBLINGS BY
- 일반적인 ORDER BY 가 아닌 계층내에서 정렬 할 수 있는 ORDER SIBLINGS BY

CONNECT BY의 실행순서는 다음과 같다.
- 첫째 START WITH 절
- 둘째 CONNECT BY 절
- 셋째 WHERE 절 순서로 풀리게 되어있다.

위의 설명대로 신입에게 가르쳐 주면서 느낀 점 하나는,
역시 말이나 글로 설명하는 것 보다는 직접 보여주는게 젤 좋다는 것.
그래서 난 SQL Fiddle 를 즐겨 사용한다. <Where are U?> 

왼쪽 창에는

create table test_connect_by (

  parent     number,

  child      number,

  constraint uq_tcb unique (child)

);

insert into test_connect_by values (null, 38);

insert into test_connect_by values (null, 26);

insert into test_connect_by values (null, 18);

insert into test_connect_by values (38,15);

insert into test_connect_by values (38,17);

insert into test_connect_by values (38, 6);

insert into test_connect_by values (15,10);

insert into test_connect_by values (15, 5);

insert into test_connect_by values (26,13);

insert into test_connect_by values (26, 1);

insert into test_connect_by values (26,12);

insert into test_connect_by values (17, 9);

insert into test_connect_by values (17, 8);

insert into test_connect_by values (18,11);

insert into test_connect_by values (18, 7);

insert into test_connect_by values ( 5, 2);

insert into test_connect_by values ( 5, 3);

오른쪽 창에는
select LEVEL lev, lpad('>', 2*(LEVEL-1),'.') || child s 
  from test_connect_by 
  start with parent is null
  connect by prior child = parent
ORDER SIBLINGS BY child desc;


select LEVEL lev, lpad('>', 2*(LEVEL-1),'.') || child s 
  from test_connect_by 
  start with child = 2
  connect by prior parent = child
ORDER SIBLINGS BY child desc;

각각 넣어주면 된다.
그리고 이건 그냥 생각나서 덧붙이는 글.
DB가 MS SQL 이라면? 여기로 <Where are U?>
왼쪽 창에는
CREATE TABLE [dbo].[Employee](
[EMPl Id] [int] ,
[Name] [varchar](50) ,
[reports to the Boss] [int] ,
[Position] [varchar](50) )

    INSERT INTO dbo.Employee
        ([Name], [EMPl Id], [reports to the Boss], [Position])
    VALUES
    ('John White', 3, 1, 'Assistant'),
    ('Paul Coplien',12 ,3, 'Supervisor'),
    ('Rafaela Johnson', 29, 12 ,'Assistant'),
    ('Rosa Hemingway',36, 15, 'Assistant'),
    ('Susan Armstrong', 35 ,15, 'Assistant'),
    ('Harry Anderson', 32, 12, 'Assistant'),
    ('Julie Lee' ,15, 3, 'Supervisor'),
    ('Martha McDonalds', 37, 15, 'Assistant')

오른쪽 창에는
WITH Empl_Tab( Id ,
                 ParentId ,
                 LEVEL,
        [Order]
) AS ( SELECT Employee.[EMPl Id] ,  Employee.[reports to the Boss] ,
                0 AS LEVEL ,
     CONVERT([varchar](MAX), Employee.[EMPl Id]) AS [Order]
           FROM Employee
where [reports to the Boss] = 1
   UNION ALL

   SELECT Employee.[EMPl Id] ,
                Employee.[reports to the Boss] ,
                Empl_Tab.LEVEL+1 AS LEVEL ,
Empl_Tab.[Order] + CONVERT([varchar](30), Employee.[EMPl Id]) AS [Order]
           FROM
                Employee INNER JOIN Empl_Tab
                ON Empl_Tab.Id = Employee.[reports to the Boss] 
     )

SELECT REPLICATE( '.' ,Empl_Tab.Id*1 )+Employee.Name AS Name
  FROM
       Employee INNER JOIN Empl_Tab
       ON Empl_Tab.Id = Employee.[EMPl Id]
  ORDER BY Empl_Tab.[Order]

결론 : 나는 나무명이 좋다.