CONNECT BY 전개와 PIVOT 절

사원 테이블의 관리자번호가 같은 테이블의 사원번호를 가리키듯, 한 테이블 안에서 행이 다른 행을 부모로 가리키는 구조를 계층형 데이터라 합니다. 조직도·메뉴 트리·부품 구성표가 모두 이 모양입니다. 이 노트의 앞 절반은 그런 데이터를 트리 순서로 펼치는 계층형 질의이고, 뒤 절반은 행과 열을 맞바꾸는 PIVOT·UNPIVOT입니다. 둘 다 오라클 문법으로 출제되고, 시험은 결과가 몇 줄이고 어떤 차례로 서는가를 묻습니다.

계층형 질의는 아래 조직 테이블로 계속 갑니다.

계층형 질의

START WITH와 CONNECT BY

계층형 질의는 두 절로 트리를 펼칩니다. START WITH는 어느 행에서 출발할지, 곧 트리의 뿌리를 정하고, CONNECT BY는 이미 읽은 행과 다음에 읽을 행을 어떤 조건으로 이을지를 정합니다.

SELECT LEVEL, 사원번호, 이름, 관리자번호
  FROM 조직
 START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호;

PRIOR는 바로 앞에 읽은 행의 값을 가리킵니다. 위 조건은 「앞 행의 사원번호가 지금 행의 관리자번호와 같다」, 곧 앞 행이 지금 행의 관리자라는 뜻이라 트리를 위에서 아래로 내려갑니다. 이 방향을 순방향 전개라 합니다.

전개 방향

PRIOR를 반대쪽 칼럼에 붙이면 방향이 뒤집힙니다.

외우는 요령은 하나입니다 — PRIOR가 붙은 칼럼이 이미 읽은 쪽입니다. 등호의 좌우는 상관이 없어서 관리자번호 = PRIOR 사원번호도 순방향입니다. 정대리에서 대표까지 올라가려면 START WITH 사원번호 = 5 CONNECT BY 사원번호 = PRIOR 관리자번호로 적고, 결과는 정대리·최과장·이부장·김대표 네 줄입니다.

깊이 우선 차례

순방향 결과는 뿌리부터 한 가지를 끝까지 내려간 뒤 다음 가지로 옮기는 깊이 우선 차례로 섭니다. 김대표 아래로 이부장 가지를 끝까지(최과장·정대리) 내려간 다음 박부장 가지(한대리)로 넘어가는 식입니다. 같은 층끼리 모아 주지 않으므로 이부장과 박부장이 붙어 서지 않습니다.

계층 의사 칼럼과 함수

LEVEL과 CONNECT_BY_ISLEAF

계층형 질의는 줄마다 트리 속 자리를 알려 주는 의사 칼럼을 붙입니다. 테이블에 없지만 칼럼처럼 부를 수 있는 값입니다.

• LEVEL — 뿌리가 1이고 한 층 내려갈 때마다 1씩 커집니다. 조직 테이블의 순방향 전개에서 정대리가 4로 가장 깊습니다.
• CONNECT_BY_ISLEAF — 전개 방향으로 더 내려갈 행이 없으면 1, 있으면 0입니다. 순방향에서는 정대리와 한대리가 1입니다.

CONNECT_BY_ISLEAF는 테이블 속 관계가 아니라 전개 방향의 끝을 봅니다. 정대리에서 출발한 역방향 전개에서는 더 올라갈 곳이 없는 김대표가 1이고 정대리는 0입니다.

CONNECT_BY_ROOT와 SYS_CONNECT_BY_PATH

CONNECT_BY_ROOT 칼럼은 그 줄이 속한 트리의 뿌리 행 값을 돌려주는 연산자입니다. 순방향 전개에서 모든 줄의 CONNECT_BY_ROOT 이름이 김대표입니다. 뿌리가 여럿인 전개에서 각 줄이 어느 뿌리 밑인지 가를 때 씁니다.

SYS_CONNECT_BY_PATH(칼럼, 구분자)는 뿌리부터 현재 줄까지의 값을 구분자로 이어 경로 문자열을 만듭니다.

SELECT LPAD(' ', (LEVEL - 1) * 2) || 이름 AS 조직도,
       SYS_CONNECT_BY_PATH(이름, '/')   AS 경로
  FROM 조직
 START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호;

정대리 줄의 경로는 /김대표/이부장/최과장/정대리이고, 문자열이 구분자로 시작한다는 점을 놓치기 쉽습니다. LPAD로 LEVEL만큼 들여 쓰면 결과가 화면에서 트리처럼 보입니다.

정렬과 순환

ORDER SIBLINGS BY

계층형 질의에 일반 ORDER BY 이름을 붙이면 전체 결과가 이름순으로 다시 정렬되어 트리 차례가 깨집니다. 부모 밑에 자식이 오는 모양을 지키면서 같은 부모의 자식끼리만 정렬하려면 ORDER SIBLINGS BY를 씁니다.

SELECT LEVEL, 이름
  FROM 조직
 START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호
 ORDER SIBLINGS BY 이름;

결과는 김대표, 박부장, 한대리, 이부장, 최과장, 정대리입니다. 형제인 이부장과 박부장만 이름순(박 → 이)으로 바뀌고, 각자의 부하는 여전히 자기 밑에 붙어 따라갑니다.

조건을 두는 자리

계층형 질의의 WHERE는 트리를 다 펼친 뒤 줄을 거릅니다. 그래서 거른 줄만 빠지고 그 밑의 줄은 남습니다. 반면 CONNECT BY에 조건을 더하면 그 조건을 못 넘은 줄에서 전개 자체가 멈춰 가지째 빠집니다.

순환과 NOCYCLE

자식을 따라 내려가다 이미 지나온 조상을 다시 만나는 것이 순환입니다. 김대표의 관리자번호가 실수로 5(정대리)로 들어가 있으면 김대표 → 이부장 → 최과장 → 정대리 → 김대표로 끝없이 돕니다. 오라클은 이를 알아채고 오류(ORA-01436)를 냅니다.

CONNECT BY NOCYCLE을 적으면 오류 대신 이미 지나온 행으로는 가지 않고 전개를 이어 갑니다. 이때 CONNECT_BY_ISCYCLE 의사 칼럼이 순환을 일으키는 자리, 곧 자식이 자기 조상인 줄에서 1이 됩니다. 위 예에서는 정대리 줄이 1입니다. 이 경우 김대표도 관리자번호가 NULL이 아니므로 START WITH 관리자번호 IS NULL로는 뿌리를 못 찾아 START WITH 사원번호 = 1로 출발점을 지정해야 합니다.

셀프 조인과의 비교

셀프 조인

같은 테이블에 별칭을 둘 붙여 스스로와 조인하는 것이 셀프 조인입니다. 사원과 그 직속 관리자를 한 줄에 놓는 데는 이것이 가장 단순합니다.

SELECT a.이름 AS 사원, b.이름 AS 관리자
  FROM 조직 a
  LEFT OUTER JOIN 조직 b ON b.사원번호 = a.관리자번호;

결과는 여섯 줄이고, 관리자가 없는 김대표는 아우터 조인이라 관리자 칸이 NULL인 채 남습니다. 이너 조인이면 다섯 줄입니다.

깊이가 정해지지 않은 트리

셀프 조인은 조인 하나가 한 층입니다. 관리자의 관리자까지 보려면 조인을 한 번 더 해야 하고, 트리가 몇 층인지 모르면 조인을 몇 번 할지 미리 정할 수 없습니다. 결과 모양도 다릅니다 — 셀프 조인은 층을 옆으로 칼럼을 늘려 붙이고, 계층형 질의는 층을 아래로 줄을 늘려 쌓습니다.

SQL Server에는 CONNECT BY가 없어 WITH 절 안에서 자기를 다시 부르는 재귀 CTE로 같은 전개를 합니다. 뿌리를 뽑는 질의와 자식을 붙이는 질의를 UNION ALL로 잇는 모양입니다.

PIVOT과 UNPIVOT

PIVOT 절

PIVOT은 행으로 늘어선 값을 열로 돌립니다. 앞의 그룹 함수 노트에서 쓴 판매 테이블(지역·분기·매출)을 지역마다 한 줄, 분기마다 한 칸으로 바꾸면 다음과 같습니다.

SELECT *
  FROM (SELECT 지역, 분기, 매출 FROM 판매)
 PIVOT (SUM(매출) FOR 분기 IN ('1Q' AS Q1, '2Q' AS Q2));

FOR 뒤의 칼럼 값이 열 이름이 되고, 집계 함수가 칸을 채우며, PIVOT에 나오지 않은 나머지 칼럼이 저절로 GROUP BY 기준이 됩니다. 이것이 가장 흔한 함정입니다. 인라인 뷰 없이 FROM 판매에 바로 PIVOT을 걸었는데 판매 테이블에 판매일자 칼럼도 있으면 지역·판매일자 조합마다 줄이 생겨 결과가 흩어집니다. 그래서 필요한 칼럼만 인라인 뷰로 먼저 추립니다. IN 목록에는 상수만 올 수 있어, 분기 값이 새로 생기면 질의를 고쳐야 합니다. 같은 결과를 SUM(CASE WHEN 분기 = '1Q' THEN 매출 END)를 분기마다 적고 GROUP BY 지역으로 묶어서도 낼 수 있습니다.

UNPIVOT 절

UNPIVOT은 반대로 열을 행으로 펼칩니다. 위 결과를 분기표라는 테이블로 두고 되돌리면 다음과 같습니다.

SELECT 지역, 분기, 매출
  FROM 분기표
UNPIVOT (매출 FOR 분기 IN (Q1 AS '1Q', Q2 AS '2Q'));

매출은 값이 들어갈 새 칼럼, 분기는 원래 열 이름이 들어갈 새 칼럼입니다. 서울·부산 두 줄이 네 줄로 펼쳐집니다.

되돌릴 때 사라지는 것

UNPIVOT은 PIVOT의 정확한 역연산이 아닙니다. 두 가지가 돌아오지 않습니다.

• 집계로 합쳐진 원래 행 — 서울 1Q 매출이 60과 40 두 건이었다면 PIVOT이 100 한 칸으로 합쳤고, UNPIVOT은 100 한 줄을 돌려줄 뿐 두 건으로 가르지 못합니다.
• NULL 칸 — UNPIVOT은 기본값이 EXCLUDE NULLS라 값이 NULL인 칸의 줄을 만들지 않습니다. 부산에 2Q 매출이 없어 칸이 NULL이었다면 부산은 한 줄만 나옵니다. 그 줄까지 남기려면 UNPIVOT INCLUDE NULLS로 적습니다.

출제는 PIVOT 뒤의 결과 칸 수와, NULL 칸이 섞인 표를 UNPIVOT했을 때의 줄 수를 묻는 모양이 많습니다.

연습 문제

1. 조직 테이블에서 정대리부터 대표까지 올라가며 전개하는 조건으로 옳은 것은?
   ① START WITH 사원번호 = 5 CONNECT BY PRIOR 사원번호 = 관리자번호
   ② START WITH 사원번호 = 5 CONNECT BY 사원번호 = PRIOR 관리자번호
   ③ START WITH 관리자번호 IS NULL CONNECT BY 사원번호 = PRIOR 관리자번호
   ④ START WITH 사원번호 = 1 CONNECT BY PRIOR 관리자번호 = 사원번호

   ②. 앞 행(정대리)의 관리자번호가 지금 행의 사원번호여야 위로 올라갑니다. ①은 정대리의 부하를 찾으므로 정대리 한 줄만 나오고, ③④는 김대표에서 출발해 김대표 위를 찾으므로 한 줄에서 멈춥니다.
2. 조직 테이블을 순방향으로 전개했을 때 CONNECT_BY_ISLEAF = 1인 줄의 수와 가장 큰 LEVEL은?
   ① 2, 4
   ② 3, 4
   ③ 2, 3
   ④ 4, 4

   ①. 부하가 없는 정대리·한대리 둘이 잎이고, 김대표(1) → 이부장(2) → 최과장(3) → 정대리(4)가 가장 깊은 가지입니다.
3. 조직 테이블의 순방향 전개에서 CONNECT BY PRIOR 사원번호 = 관리자번호 AND 이름 <> '이부장'으로 적었을 때의 줄 수와, 같은 조건을 WHERE 이름 <> '이부장'으로 옮겼을 때의 줄 수는?
   ① 3, 5
   ② 5, 3
   ③ 5, 5
   ④ 3, 3

   ①. CONNECT BY 조건은 이부장에서 전개를 멈춰 최과장·정대리까지 빠지므로 김대표·박부장·한대리 세 줄입니다. WHERE는 다 펼친 뒤 이부장 한 줄만 거르므로 다섯 줄입니다.
4. 순방향 전개에서 SYS_CONNECT_BY_PATH(이름, '>')가 한대리 줄에서 돌려주는 값은?
   ① 김대표>박부장>한대리
   ② >김대표>박부장>한대리
   ③ >한대리>박부장>김대표
   ④ >박부장>한대리

   ②. 경로는 뿌리부터 현재 줄까지 이어지고, 값마다 앞에 구분자가 붙으므로 구분자로 시작합니다.
5. 계층형 질의 결과를 트리 모양을 지키며 형제끼리만 이름순으로 세우려 할 때 쓰는 것은?
   ① ORDER BY LEVEL, 이름
   ② ORDER BY 이름
   ③ ORDER SIBLINGS BY 이름
   ④ GROUP BY LEVEL ORDER BY 이름

   ③. ①②는 전체 결과를 다시 정렬해 부모 밑에 자식이 오는 차례를 깨뜨립니다. ORDER SIBLINGS BY는 같은 부모 아래의 형제끼리만 정렬합니다.
6. 분기표가 서울(Q1 100, Q2 150), 부산(Q1 80, Q2 NULL), 대구(Q1 NULL, Q2 NULL) 세 줄이다. UNPIVOT (매출 FOR 분기 IN (Q1, Q2))의 결과 줄 수와, UNPIVOT INCLUDE NULLS로 바꿨을 때의 줄 수는?
   ① 3, 6
   ② 4, 6
   ③ 3, 4
   ④ 6, 6

   ①. 기본값 EXCLUDE NULLS는 NULL 칸의 줄을 만들지 않으므로 서울 둘, 부산 하나, 대구 없음으로 $2 + 1 + 0 = 3$ 줄입니다. 대구는 결과에서 통째로 사라집니다. INCLUDE NULLS면 세 지역 × 두 분기로 $3 \times 2 = 6$ 줄입니다.