마농의 SQL 백문백답: 단순하고 쉽게 작성하는 SQL 노하우 [2회]

계층 구조 쿼리의 이해

단순하고 쉽게 작성하는 SQL 노하우를 공유하자!

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다.
간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다.
필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다.
앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

지난 첫 회에는 계층구조 관련 질문들에 대해 기본 구문을 이용한 해답을 살펴보았다. 이번 회에는 계층구조 쿼리를 응용하는 문제를 살펴보겠다. 풀이 과정은 다소 어렵지만 독자들이 갖고 있는 계층쿼리에 대한 고정관념을 깰 수 있는 문제들로 구성했다. 풀이과정을 이해하고 자신의 것으로 만들 수 있다면 SQL 작성 능력이 배가 될 것이다.

[질문 10] 계층 구조 테이블에서 하위 노드로부터 상위 노드로의 누적합을 어떻게 구할 수 있나요

이 질문은 해결 방법이 쉽게 떠오르지 않는 어려운 질문이다.
다음 결과를 실펴보자.




계층구조쿼리를 사용하는 문제임을 알 수 있다. 첫 번째 항목은 LEVEL 절을 이용하면 되고,
두 번째 항목은 SYS_CONNECT_BY_PATH 를 이용하면 된다.세 번째 항목의 값을 이용해 네 번째 항목인 누적합을 구하는 것이 관건이다.


결과에 대해 설명해 보면 네 번째 줄의 ADAMS의 급여 1100이 상사인 SCOTT의 금액인 3000 과 합산되어 4100으로 표시된다. 즉 부하직원의 급여를 상사의 레코드에 합산하여 보여주는 형태다.


하지만 단순히 1단계에 머무르는 것이 아니라 계층구조를 계속 따라 올라가 최종단계에까지 적용이 돼야 하는 문제이므로 쉽게 생각할 수 없다.
최상위 레벨인 KING의 경우엔 모든 사원의 급여가 합산돼 보여야 한다.JONES의 경우엔 자신의 부하직원인 SCOTT/ FORD와 다시 또 그 하위직원인 ADAMS / SMITH의 급여까지 합산하면 된다.


이를 정리하면 자신의 모든 부하직원들의 급여 합계를 계산하면 된다는 의미다.
자신의 모든 부하직원은 또 한번 계층 쿼리를 이용하면 된다.

SELECT LEVEL lv
, SUBSTR(SYS_CONNECT_BY_PATH(ename,'-'), 2) enames
, sal
, (SELECT SUM(sal)
FROM scott.emp
START WITH empno = a.empno
CONNECT BY PRIOR empno = mgr
) sum_sal
FROM scott.emp a
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno
;

지난 회에 배웠던 계층 전개 쿼리를 그대로 사용하면서 SELECT 절에서 스칼라서브쿼리 형태의 계층쿼리를 다시 사용하는 구조다. 이때 서브쿼리에서의 계층 시작은 메인쿼리의 사원번호가 되는 형태다. 다시 말하여 해당 사원을 시작으로 다시 한 번 계층구조를 전개해 하위 직원들을 조회하고, 하위 직원들의 급여를 SUM해서 결과를 도출할 수 있다.

[질문 11] 하위 노드의 누적합 외에 누적 카운트도 함께 조회하고 싶어요.

위 문제에서 구하고자 하는 항목이 늘어나는 경우다.
다음 쿼리를 살펴보자.

SELECT LEVEL lv
, SUBSTR(SYS_CONNECT_BY_PATH(ename,'-'), 2) enames
, sal
, (SELECT SUM(sal)
FROM scott.emp
START WITH empno = a.empno
CONNECT BY PRIOR empno = mgr
) sum_sal
, (SELECT COUNT(*)
FROM scott.emp
START WITH empno = a.empno
CONNECT BY PRIOR empno = mgr
) sum_cnt
FROM scott.emp a
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno
;

서브쿼리가 하나 더 늘어났다.합계를 구하고자 하는 항목이 여러 개라면 어떻게 해야할까계속해서 서브쿼리를 늘려가야만 할 것이다.스칼라서브쿼리는 메인쿼리의 행마다 반복 수행되며, 이러한 서브쿼리를 여러 개 사용하게 된다면 성능 저하가 발생될 것이다.
이에 대한 해결책은 서브쿼리를 없애는 것이다. 서브쿼리에서 했던 것을 메인쿼리에서 해야 한다. 서브쿼리에서 했던 것은 현재 행을 기준으로 계층쿼리를 전개한 것이다.


메인쿼리에서 최상위를 시작으로 계층쿼리을 전개했다면 이 시작조건을 주지 않는다면 모든 행을 시작으로 계층을 전개할 것이다. 즉 시작조건을 뺌으로 해서 서브쿼리에서 했던 것을 메인쿼리에서 할 수 있다.

SELECT empno
, SUM(sal) AS sum_sal
, COUNT(*) AS sum_cnt
FROM (SELECT CONNECT_BY_ROOT empno AS empno
, sal
FROM emp
-- START WITH mgr IS NULL -- 계층시작조건 제거 : 모든 행이 시작
CONNECT BY PRIOR empno = mgr
)
GROUP BY empno
;

시작조건을 제거함으로써 모든 행이 시작돼 계층을 전개할 수 있다.
이 각 시작점을 기준으로 합계를 계산해야 하므로 CONNECT_BY_ROOT를 이용해 각 결과의 루트(시작점)를 가져오고, 이 시작점을 기준으로 합계를 구하면 된다.
이렇게 하면 합계 항목이 여러 개 오더라도 스칼라 서브쿼리를 계속 추가하는 비효율은 제거된다. 위 질문에서와 같은 계층 정렬결과를 원한다면, 한 번 더 계층 전개를 하여 조인할 필요는 있다.

SELECT a.lv, a.enames, a.sal
, b.sum_sal, b.sum_cnt
FROM (SELECT LEVEL lv
, SUBSTR(SYS_CONNECT_BY_PATH(ename,'-'), 2) enames
, sal
, ROWNUM rn
, empno
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
) a
, (SELECT empno
, SUM(sal) AS sum_sal
, COUNT(*) AS sum_cnt
FROM (SELECT CONNECT_BY_ROOT empno AS empno
, sal
FROM emp
CONNECT BY PRIOR empno = mgr
)
GROUP BY empno
) b
WHERE a.empno = b.empno
ORDER BY a.rn
;

이 문제는 START WITH 구문이 반드시 있어야만 한다는 고정관념을 깨는 문제다.

[질문 12] 부서별 사원의 이름을 함께 묶어서 조회하고 싶어요.

이 문제는 얼핏 보면 계층 구조와 전혀 상관이 없어 보인다. 그룹바이에서 문자열을 합쳐서 보여주는 문제다. 오라클 11G 버전에서는 LISTAGG라는 새로운 기능으로 쉽게 해결된다.

SELECT deptno
, LISTAGG(ename, ',') WITHIN GROUP(ORDER BY empno) enames
FROM emp
GROUP BY deptno
ORDER BY deptno
;

하지만 11G 이전 버전을 사용하고 있다면, 해당 기능을 사용할 수 없다. 기능이 없으므로 다른 기능을 이용해 만들어 내야만 한다. 문자열을 연결해주는 SYS_CONNECT_BY_PATH를 이용해 보면 어떨까


하지만 부서별 사원들은 계층 구조가 아니므로 사용할 수 없다. 꼭 부모키와 자식키가 공존하는 테이블에서만 계층쿼리를 사용할 수 있을까
다음 쿼리를 살펴보자.

SELECT deptno, empno, ename
, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY empno) rn
FROM emp
;

부서별로 사원번호 순서에 따라 번호를 매긴 쿼리 결과다. 이 번호 순서대로 1번부터 차례로 ENAME을 연결해 주면 되는데…. 바로 이 ‘번호 순서대로’라는 말이 바로 계층 전개 조건이 되며,‘1번부터’라는 말이 계층전개의 시작조건이 된다.
이를 SQL 구문으로 옮겨 보면 다음과 같다.

SELECT deptno
, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) enames
FROM (SELECT deptno, empno, ename
, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY empno) rn
FROM emp
)
WHERE CONNECT_BY_ISLEAF = 1
START WITH rn = 1
CONNECT BY PRIOR deptno = deptno
AND PRIOR rn + 1 = rn
;

START WITH rn = 1

이 조건은 부서별로 1번부터 시작한다는 조건이다.

CONNECT BY PRIOR deptno = deptno
AND PRIOR rn + 1 = rn

이것은 계층 전개 조건으로, 같은 부서 내에서 rn이 1만큼 차이나는 것을 연결하는 조건이다. CONNECT BY 구문에 대한 잘못된 고정관념을 가진 독자라면 이 구문이 상당히 낯설 것이다. CONNECT BY 구문도 조건절이므로 여러 개의 조건문을 AND나 OR 등으로 연결할 수 있다.

WHERE CONNECT_BY_ISLEAF = 1

이 조건은 계층 전개 후 최하위 노드만을 걸러내는 구문이다.

, SUBSTR(SYS_CONNECT_BY_PATH(ename, ','), 2) enames

SYS_CONNECT_BY_PATH 구문으로 ENAME을 연결하고 맨 앞의 콤마를 없애기 위해 SUBSTR( , 2)를 사용했다.


순번을 부여한 뒤 순번을 이용해 계층쿼리를 사용하는 문제다.
‘계층 구조 테이블에서만 계층쿼리를 사용할 수 있다’는 고정관념을 깨는 문제다.


물론 이 쿼리는 8i 버전용 쿼리다.
11g가 아니더라도 9i나 10g에서는 계층쿼리를 이용하는 방법보다는 다른 방식으로 구현하는 것이 더 나을 것이다. 다만 계층쿼리가 이렇게도 사용될 수 있구나 라는 걸 보여주는 좋은 예로서 소개한 것이다.
다음은 ‘GROUP BY에서 문자열 합치기’ 쿼리를 버전별로 정리한 것이다.

-- Group By에서 문자열 합치기 --
SELECT deptno
, SUBSTR(
XMLAGG(XMLELEMENT(x, ',', ename) ORDER BY empno).EXTRACT('//text()')
, 2) enames_9i
, wm_concat(ename) enames_10g
, LISTAGG(ename, ',') WITHIN GROUP(ORDER BY empno) enames_11g
FROM emp
GROUP BY deptno
ORDER BY deptno
;

정리하며

지난 첫 회의 계층쿼리 기초과정을 거쳐 이번 회에는 응용과정을 알아 보았다.


상위노드로의 누적합산 문제를 다음과 같이 두 가지 방식으로 풀어보았다.


- 스칼라서브쿼리를 이용하여 계층쿼리 다시 전개

- START WITH 절 없이 계층 전개하여 SYS_CONNECT_BY_ROOT 로 GROUP BY 하기


순번을 부여한 뒤 순번을 이용해 계층쿼리를 다음과 같이 만들어 보았다.

- CONNECT BY PRIOR rn + 1 = rn


다음 회에도 계층 쿼리의 응용문제를R>