퀴즈로 배우는 SQL

계층 쿼리를 이용해 조직도 만들기

이번 퀴즈로 배워보는 SQL 시간에는 계층 쿼리를 이용해 조직도를 만드는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥이 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

[문제]

조직 정보를 가지고 있는 원본 테이블 <표 1>로부터 계층구조 형태로 표현한 <표 2>의 결과를 출력하는 SQL을 작성하세요.

<리스트 1> 원본 리스트WITH dept(cd, nm, pcd, flg, ord) AS
(
SELECT 10, '구루비닷넷', null, 1, 1 FROM dual
UNION ALL SELECT 11, ‘직할’ , 10, 0, 1 FROM dual
UNION ALL SELECT 12, ‘감사팀’ , 11, 1, 1 FROM dual
UNION ALL SELECT 13, ‘기획처’ , 10, 1, 2 FROM dual
UNION ALL SELECT 14, ‘기획팀’ , 13, 1, 1 FROM dual
UNION ALL SELECT 15, ‘총무팀’ , 13, 1, 2 FROM dual
UNION ALL SELECT 16, ‘영업처’ , 10, 1, 3 FROM dual
UNION ALL SELECT 17, ‘영업1팀’ , 16, 1, 1 FROM dual
UNION ALL SELECT 18, ‘영업2팀’ , 16, 1, 2 FROM dual
UNION ALL SELECT 19, ‘사업소’ , 10, 0, 4 FROM dual
UNION ALL SELECT 20, ‘서울지점’, 19, 1, 1 FROM dual
UNION ALL SELECT 24, ‘관할’ , 20, 0, 1 FROM dual
UNION ALL SELECT 21, ‘관리파트’, 24, 1, 1 FROM dual
UNION ALL SELECT 22, ‘개발파트’, 24, 1, 2 FROM dual
UNION ALL SELECT 23, ‘부산지점’, 19, 1, 2 FROM dual
)
SELECT * FROM dept;

<표 1> 원본 테이블



<표 2> 결과 테이블

[문제설명]

<리스트 1>은 구루비닷넷이라는 회사의 조직정보(DEPT) 테이블입니다. 이 조직정보 테이블에는 조직코드(CD), 조직명(NM), 상위조직코드(PCD), 실조직여부(FLG), 조직서열(ORD) 정보가 저장돼 있습니다. 상위조직코드는 조직코드를 참조하는 자기 참조 구조입니다. 이 자기 참조 구조를 이용해 <표 2>의 조직도를 출력하는 문제입니다. 그런데 조직 중에는 실제 존재하지 않는 가조직이 포함돼 있습니다. 가조직은 팀 단위 부서(팀/지점)의 조직 레벨은 통일하기 위해 관리되고 있는 정보입니다. <표 1>에서 직할, 사업소, 관할 등이 여기에 해당합니다. 실조직여부 항목에서 1은 실조직을, 0은 가조직을 의미합니다. 가조직은 시스템 관리상의 목적으로 만들어진 조직이므로, 실제 조직도에는 있어서는 안 됩니다.

조직도는 사용자가 보기 좋도록 조직 레벨과 부모조직코드를 함께 표시합니다. 조직명의 경우에는 조직레벨에 맞게 들여쓰기 형태로 ‘└’ 기호와 함께 표시돼야 합니다. 또한 조직도는 조직서열에 따라 순서대로 출력돼야 합니다. 지금부터 계층쿼리를 이용해 이 문제를 풀어볼까요.

[정답]

문제를 스스로 해결해 보셨나요 이제 정답을 알아보겠습니다.

<리스트 2> 정답 리스트SELECT cd
, LPAD('└', (lv-1)*2, ' ') || nm nm
, pcd
, lv
FROM (SELECT cd
, nm
, DECODE(PRIOR flg, 0, PRIOR pcd
, pcd) pcd
, REGEXP_COUNT(
SYS_CONNECT_BY_PATH(flg, ',')
, '1') lv
FROM dept
WHERE flg = 1
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
ORDER SIBLINGS BY ord
)
;

어떤가요 여러분이 만들어본 코드와 같은가요 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

[해설]

이번 문제는 조직코드와 부모조직코드와의 계층 관계를 이용해 조직도를 출력하는 문제입니다. 특이한 점은 가조직이라고 하는 데이터가 있고, 이 가조직 데이터를 제거한 실조직 데이터만으로 조직도를 만들어야 한다는 것입니다. 가조직을 제거한 정답 쿼리를 살펴보기 전에 우선 일반적인 계층쿼리의 사용법부터 살펴보겠습니다.

<리스트 3> 계층쿼리 사용법SELECT cd
, nm
, pcd
, flg
, ord
, LEVEL lv
, SYS_CONNECT_BY_PATH(nm, '-') nm_path
, LPAD('└', (LEVEL-1)*2, ' ') || nm nms
FROM dept
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
ORDER SIBLINGS BY ord
;

<표 3> 계층쿼리 사용 결과

<표 3>은 <리스트 3>으로 얻은 결과입니다. 오라클에서 제공하는 계층구문을 사용한 결과죠. 지금부터는 각 구문을 하나씩 차근차근 살펴보겠습니다.

START WITH pcd IS NULL

START WITH 구문은 계층구조의 최상위를 지정하는 구문입니다. 최상위는 부모코드를 가지고 있지 않으므로 이 조건을 이용합니다. 이 조건에 부합하는 자료부터 계층쿼리를 전개합니다.

CONNECT BY PRIOR cd = pcd

CONNECT BY 구문은 계층구조의 상하위 노드 관계를 기술하고, PRIOR는 상위의 항목임을 표시합니다. 따라서 PRIOR cd = pcd는 상위 코드가 하위의 부모코드와 같다는 의미입니다. 즉 내 코드가 가지고 있는 부모코드가 상위의 코드와 연결되는 것입니다. 이렇게 START WITH와 CONNECT BY, 두 가지 구문을 함께 사용하면 간단하게 계층쿼리가 완성됩니다. 이제부터는 계층쿼리에서 사용되는 몇 가지 함수와 구문들을 살펴보겠습니다.

, LEVEL lv

LEVEL은 계층쿼리에서만 사용 가능한 의사컬럼(Pseudo Column)으로, 계층구조의 깊이를 나타냅니다.

, SYS_CONNECT_BY_PATH(nm, ‘-’) nm_path

SYS_CONNECT_BY_PATH는 계층의 경로를 표시해 주는 함수입니다.

, LPAD(‘└’, (LEVEL-1)*2, ‘ ’) || nm nms

LPAD로 시작하는 이 구문은 계층구조를 사용자에게 이해하기 쉽게 표현하기 위한 가공 과정입니다. LPAD와 LEVEL을 조합해 <표 3>의 NMS 항목이 만들어집니다. 가공되지 않은 NM 항목만 보면 계층구조인지 아닌지 감이 안 오지만 NMS 항목을 보면 계층구조임을 알 수 있습니다.

ORDER SIBLINGS BY ord

ORDER SIBLING BY 구문은 계층구조에서의 정렬를 담당하는 구문입니다. 일반 정렬구문과 달리 계층의 구조를 그대로 유지하면서 동일 부모 아래 자식들끼리만 정렬을 합니다. 지금까지 살펴본 계층구조의 기본 구문에 가조직을 제거하는 조건을 추가한 결과는 <리스트 4>와 같습니다.

<리스트 4> 가조직 제거 조건 추가SELECT cd
, LPAD('└', (LEVEL-1)*2, ' ') || nm nm
, pcd
, LEVEL lv
FROM dept
WHERE flg = 1
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
ORDER SIBLINGS BY ord
;

<표 4> 가조직 제거 조건 추가

<리스트 4>의 쿼리를 통해 <표 4>의 결과를 얻었습니다.

WHERE flg = 1

<리스트 4>에는 가조직 제거 조건인 flg = 1가 쓰였습니다. <표 4>의 결과가 <표 2>와 다른데요. 가조직인 직할, 사업소, 관할에 대한 행이 제거된 점은 두 결과가 같지만 다른 것은 가조직의 하위조직에 대한 정보가 바뀌지 않았다는 점입니다. 가조직의 하위에 속한 조직의 경우에는 조직명, 상위조직코드 조직레벨 항목이 모두 조정돼야 합니다. 예컨대 서울지점의 상위조직코드는 가조직인 19가 아닌 19의 상위인 10이 돼야 합니다. 마찬가지로 조직레벨도 3 레벨이 아닌 2레벨로 표현돼야 합니다. 21 관리파트의 경우엔 본래 레벨은 5이지만 상위에 가조직이 2개가 있기 때문에 3 레벨로 표현돼야 합니다. 이를 보정하기 위해서는 상위 코드가 가조직인지 아닌지를 확인해야 합니다.

<리스트 5> 상위조직의 정보 확인SELECT cd
, nm
, pcd
, flg
, LEVEL lv
, PRIOR flg p_flg
, PRIOR pcd p_pcd
, SYS_CONNECT_BY_PATH(flg, ',') path
FROM dept
WHERE flg = 1
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
ORDER SIBLINGS BY ord
;

,1 <표 5> 상위조직의 정보 확인

<리스트 5>의 쿼리를 통해 <표 5>라는 결과를 얻었습니다.

, PRIOR flg p_flg
, PRIOR pcd p_pcd

CONNECT BY 절에서 사용됐던 PRIOR 키워드가 SELECT 절에서 사용됐습니다. PRIOR 를 이용해 상위조직의 실조직여부 항목(P_FLG)과 부모조직코드(P_PCD)를 조회했습니다. 이 P_FLG 항목의 값이 0인 경우에는 P_PCD 항목을 보여주고 1인 경우엔 원래의 PCD 항목을 보여주는 식으로 가조직을 상위로 가진 조직의 부모코드를 조정할 수 있습니다. <리스트 2> 정답 쿼리의 다음 부분이 이 규칙을 적용하는 구문입니다.

, DECODE(PRIOR flg, 0, PRIOR pcd, pcd) pcd

DECODE 구문을 이용해 상위조직의 FLG 값에 따라 보여주는 항목을 다르게 지정했습니다.

, SYS_CONNECT_BY_PATH(flg, ',') path

이 부분은 SYS_CONNECT_BY_PATH를 이용해 FLG 항목을 계층 경로처럼 나열한 것입니다. 이 값은 상위에서 하위로 이어지는 FLG 값의 연속이며, 이 중 제외돼야 할 가조직값 0을 제외하고 실조직에 해당하는 1의 개수만 파악하면, 이 개수가 바로 조직레벨 값이 됩니다. <리스트 2> 정답 쿼리의 다음 부분이 이 규칙을 적용하는 구문입니다.

REGEXP_COUNT(SYS_CONNECT_BY_PATH(flg, ','), '1') lv

REGEXP_COUNT 함수를 사용해 FLG ‘1’ 의 개수를 세는 구문입니다. 오라클에서는 정규표현식과 관련 함수를 이처럼 사용할 수 있습니다. 참고로 REGEXP_COUNT 함수는 11G부터 사용이 가능합니다. 11G 미만 버전이라면 다음과 같이 사용해야 합니다.

LENGTH(REPLACE(REPLACE(
SYS_CONNECT_BY_PATH(flg, ',')
, '0'), ',')) lv

지금까지 계층쿼리와 함께 PRIOR 구문을 이용해 상위조직의 정보를 가져오고 이 정보를 이용해 DECODE 문을 통해 부모조직을 조정했습니다.

출처 : 마이크로소프트웨어 12월호

제공 : 데이터 전문가 지식포털 DBguide.net