문제

농구공 > 축구공 > 배구공 > 야구공 > 골프공 > 탁구공 형태의 계층 구조 데이터를 <표 1> 형태의 테이블로 구성했습니다.

계층 구조 테이블은 부모코드(PCD), 자식코드(CD), 수량(V) 항목으로 구성돼 있습니다. 입력 조건 값으로 ‘골프공’과 ‘야구공’이 주어졌을 때 주어진 입력 조건 값을 3레벨로, 최상위 농구공을 1레벨로, 중간단계의 모든 공들을 2레벨로 표현하는 문제입니다.

<표 1> 의 원본테이블로부터 <표 2>의 형태의 결과를 도출하는 문제입니다.

• [리스트 1] 계층 구조 테이블

• CREATE TABLE t
  AS
  SELECT '농구공' pcd, '축구공' cd,  1 v FROM dual
  UNION ALL SELECT '축구공', '배구공', 2 FROM dual
  UNION ALL SELECT '배구공', '야구공', 3 FROM dual
  UNION ALL SELECT '야구공', '골프공', 4 FROM dual
  UNION ALL SELECT '골프공', '탁구공', 5 FROM dual;
  
  SELECT * FROM t;
    

• [표 1] 원본 테이블

• PCD                CD                          V
  ------------------ ------------------ ----------
  농구공             축구공                      1
  축구공             배구공                      2
  배구공             야구공                      3
  야구공             골프공                      4
  골프공             탁구공                      5
    

• [표 2] 결과 테이블

• LV1          LV2                    V2 LV3                 V3
  ------------ -------------- ---------- ----------- ----------
  농구공       축구공                  1 골프공              24
  농구공       배구공                  2 골프공              24
  농구공       야구공                  6 골프공              24
  농구공       축구공                  1 야구공               6
  농구공       배구공                  2 야구공               6
    

문제설명

입력 값인 골프공을 기준으로 계층구조를 전개해 보면 ‘농구공 > 축구공 > 배구공 > 야구공 > 골프공’과 같이 전개됩니다. 이 5단계 구조를 3단계의 구조의 칼럼 형태로 바꾸어 출력하는 문제입니다.

<표 2> 결과 테이블의 LV1 은 최상위 레벨인 농구공을 나타냅니다. LV3 은 최하위인 골프공이 됩니다. LV2 는 중간 과정인 “축구공 > 배구공 > 야구공”을 아래로 나열하는 형태로 표현됩니다.

V2, V3 는 계층 구조 전개 시 소요되는 수량을 의미하며 누적 개념이 적용됩니다. 원본 테이블의 수량이 다음과 같이 표현된다면 ‘농구공 > 축구공(1) > 배구공(2) > 야구공(3) > 골프공(4)’ 결과 테이블의 누적 수량은 다음과 같이 표현됩니다.

‘농구공 > 축구공(1) > 배구공(2) > 야구공(6) > 골프공(24)’. 계산 과정을 표현해보면 다음과 같습니다. ‘농구공 > 축구공(1) > 배구공(1*2) > 야구공(1*2*3) > 골프공(1*2*3*4)’.

다단계의 계층 구조를 횡으로 3단계로 압축해 보여주면서 수량의 누적개념까지 적용시켜 보여주는 문제입니다. 다중 입력 값에 대해 각각의 결과를 보여줘야 합니다.

정답

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

• [리스트 2] 정답 리스트

SELECT FIRST_VALUE(pcd) OVER(
       PARTITION BY rcd ORDER BY lv DESC) lv1
     , cd lv2
     , ROUND(EXP(SUM(LN(v)) OVER(
       PARTITION BY rcd ORDER BY lv DESC))) v2
     , rcd lv3
     , ROUND(EXP(SUM(LN(v)) OVER(
       PARTITION BY rcd))) * rv v3
  FROM (SELECT pcd, cd, v
             , CONNECT_BY_ROOT cd rcd
             , CONNECT_BY_ROOT v  rv
             , LEVEL lv
          FROM t
         WHERE LEVEL != 1
         START WITH cd IN ('골프공', '야구공')
         CONNECT BY PRIOR pcd = cd
        )
 ORDER BY lv3, lv DESC
;

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

해설

이번 문제는 계층 구조 전개 문제입니다. 계층 전개와 더블어 수량의 누적 곱셈이 적용이 돼야 하며 또한 정해진 형식의 틀 속에 결과를 표현해야 하는 문제입니다. 계층구조 전개 쿼리 및 누적 수량 구하는 예제를 먼저 살펴보겠습니다.

• [리스트 3] 계층 전개

• SELECT lv
       , path
       , v
       , ROUND(EXP(SUM(LN(v)) OVER(ORDER BY lv))) x
    FROM (SELECT pcd, cd, v
               , LEVEL lv
               , SYS_CONNECT_BY_PATH(cd, '-') path
            FROM t
           START WITH pcd = '농구공'
           CONNECT BY PRIOR cd = pcd
          )
  ;
    

• [표 3] 계층 전개

•       LV PATH                                              V          X
  -------- ---------------------------------------- ---------- ----------
         1 -축구공                                           1          1
         2 -축구공-배구공                                    2          2
         3 -축구공-배구공-야구공                             3          6
         4 -축구공-배구공-야구공-골프공                      4         24
         5 -축구공-배구공-야구공-골프공-탁구공               5        120
    

<리스트 3>의 쿼리를 수행해 <표 3>의 결과를 얻었습니다. <리스트 3>의 인라인뷰 안에서는 계층 쿼리를 전개합니다.

START WITH pcd = '농구공'

이 구문은 계층 구조의 시작점을 지정하는 구문입니다. 농구공이 시작점임을 나타냅니다.

CONNECT BY PRIOR cd = pcd

이 구문은 계층 구조의 전개 조건을 기술하는 부분입니다. PRIOR 는 칼럼 앞에 붙어서 상위의 칼럼임을 나태내 줍니다. 즉, 부모 레코드의 코드와 자식레코드의 부모코드가 같다는 조건입니다.

이렇게 START WITH 구문과 CONNECT BY, PRIOR 구문으로 계층구조 전개를 쉽게 표현 할 수 있습니다.

, LEVEL lv

LEVEL 은 계층 전계의 단계, 깊이(DEPTH)를 나타냅니다.

, SYS_CONNECT_BY_PATH(cd, '-') path

SYS_CONNECT_BY_PATH 는 계층 전개의 경로를 나타냅니다. <표 3>은 1레벨부터 5레벨까지의 경로를 나타내는 결과입니다.

다음은 칼럼 값들의 누적 곱셈입니다. 합계는 SUM을 이용하면 되지만 곱셈을 해주는 함수는 없습니다. 이 때 학교 다닐 때 배웠던 수학 공식을 떠올려야 할 때입니다. 각각의 로그의 합은 곱의 로그와 같습니다.

Log10(A) + Log10(B) = Log10(A*B)

여기서 로그를 없애기 위해 10 의 제곱근을 하면 우변엔 곱셈만 남게 됩니다.

10^(Log10(A) + Log10(B)) = A*B

여기서는 수식을 간단하게 하기 위해 Log와 제곱근 대신 자연로그 LN 과 EXP 함수를 사용하는 것입니다.

, ROUND(EXP(SUM(LN(v)) OVER(ORDER BY lv))) x

LN(v)를 구하고 SUM() OVER() 구문을 이용해 누적합계를 구합니다. 마지막으로 EXP 함수를 적용시키면 v 칼럼값의 곱셈값을 구할 수 있습니다.

LN 과 EXP 등은 실수(소수)를 계산하는 함수이므로 정확하게 정수 값이 떨어지지 않고 미세한 오차가 발생할 수 있습니다. ROUND 는 이 미세한 오차를 보정하기 위해 사용됩니다.

계층 구조 전개 및 누적 곱셈 문제는 해결됐습니다. 이제는 정해진 틀 속에 결과를 맞추는 작업이 필요합니다.

<리스트 3> 에서는 농구공을 시작점으로 계층 전개를 했지만 문제에서는 특정 입력 값을 기준으로 최상위로부터 입력값 까지의 내역을 조회해야 합니다. 여기서 우리는 다시 이를 역으로 생각해 볼 필요가 있습니다.

최상위로부터 입력값까지 순차적으로 전개하는 것이 아닌, 입력값으로부터 시작해서 최상위 코드까지 반대로 거슬러 올라가는 방법입니다.

• [리스트 4] 계층 역 전개

• SELECT pcd, cd, v
       , CONNECT_BY_ROOT cd rcd
       , CONNECT_BY_ROOT v  rv
       , LEVEL lv
    FROM t
   -- WHERE LEVEL != 1
   START WITH cd IN ('골프공', '야구공')
   CONNECT BY cd = PRIOR pcd
  ;
    

• [표 4] 계층 역 전개

• PCD          CD             V RCD           RV         LV
  ------------ --------- ------ ---------- ----- ----------
  야구공       골프공         4 골프공         4          1
  배구공       야구공         3 골프공         4          2
  축구공       배구공         2 골프공         4          3
  농구공       축구공         1 골프공         4          4
  배구공       야구공         3 야구공         3          1
  축구공       배구공         2 야구공         3          2
  농구공       축구공         1 야구공         3          3
  

<리스트 4>의 쿼리를 수행해 <표 4>의 결과를 얻었습니다. <리스트 4>의 쿼리가 <리스트 3> 과 달라진 점은 계층 전개의 시작점 및 전개 방향이 달라졌다는 것입니다.

START WITH cd IN ('골프공', '야구공')
CONNECT BY cd = PRIOR pcd

PRIOR 의 위치가 CD에서 PCD 로 바뀌면서 전개방향이 거꾸러 거슬러 올라가는 형태로 바뀌게 됩니다.

-- WHERE LEVEL != 1

이 부분은 이해를 돕기 위해 일부러 주석처리 한 부분입니다. 실제로 결과에서 ‘골프공’과 ‘야구공’은 3레벨 항목으로 표현이 되고, 중간단계인 2레벨 부분만 행으로 나열하면 되므로 ‘골프공’과 ‘야구공’을 제외하기 위해 사용하는 조건입니다.

, CONNECT_BY_ROOT cd rcd
, CONNECT_BY_ROOT v rv
, LEVEL lv

CONNECT_BY_ROOT 는 계층 전개 시작점을 나타냅니다. START WITH 의 조건으로 주어진 ‘골프공’과 ‘야구공’이 바로 루트가 됩니다. 루트 노드인 LEVEL = 1 인 행을 제거하고 표현해야 하는데, 이렇게 제거되면 그 값을 알 수가 없게 됩니다.

이 구문은 루트의 값을 알아내기 위해 사용하는 구문입니다. 이렇게 구해진 값은 나중에 <표 2>의 결과표에서 마지막 3레벨 칼럼인 LV3 과 V3을 구하는데 유용하게 사용될 것입니다. 계층 구조 역 전개를 통해 대상 집합을 가져왔습니다. 이제 마무리를 할 차례입니다.

• [리스트 5] 최종 결과

• SELECT FIRST_VALUE(pcd) OVER(
         PARTITION BY rcd ORDER BY lv DESC) lv1
       , cd lv2
       , ROUND(EXP(SUM(LN(v)) OVER(
         PARTITION BY rcd ORDER BY lv DESC))) v2
       , rcd lv3
       , ROUND(EXP(SUM(LN(v)) OVER(
         PARTITION BY rcd))) * rv v3
    FROM (
          <리스트 4>의 쿼리
          )
   ORDER BY lv3, lv DESC
  ;
    

<리스트 5>의 쿼리를 수행해 <표 5>의 결과를 얻었습니다. <리스트 4>의 쿼리를 인라인뷰로 해 마지막 마무리 단계입니다.

SELECT FIRST_VALUE(pcd) OVER(
       PARTITION BY rcd ORDER BY lv DESC) lv1

레벨1은 최상위입니다, 그러나 우리는 <리스트 4>에서 계층구조를 역으로 전개했기 때문에 거꾸로 레벨이 가장 큰 값이 됩니다. <리스트 4>에서 구한 레벨(LV)를 역순으로 정렬한 첫 번째 값을 가져오는 분석함수 구문입니다. 결과는 농구공이 되겠지요.

, cd lv2
, ROUND(EXP(SUM(LN(v)) OVER(
PARTITION BY rcd ORDER BY lv DESC))) v2

레벨2는 최상위 농구공과, 최하위 입력값을 제외한 중간단계들입니다. <리스트 4>에서 구한 코드값(CD)이 그대로 LV2 가 됩니다. 다음은 누적 카운트입니다. 누적 카운트는 앞서 설명 드렸던 EXP, SUM, LN을 이용해 구합니다.

이때 전체 합계가 아닌 누적 합계이므로 분석함수인 SUM OVER 구문을 사용하며 정렬 기준은 LV 가 사용되며, 역전개를 했기 때문에 DESC 정렬을 합니다.

다중 입력값에 대해 별도로 처리해야 하기 때문에 PARTITION BY rcd를 이용해 구분을 합니다. 이렇게 해서 2레벨의 누적 카운트 V2를 구합니다. 3레벨은 <표 4>의 루트 부분이 되겠습니다.

, rcd lv3
, ROUND(EXP(SUM(LN(v)) OVER(
PARTITION BY rcd))) * rv v3

루트코드인 RCD 가 그대로 LV3 이 됩니다. V3 는 V2 와 달리 순차적인 누적값이 아니라 전체 누적값입니다. 따라서 OVER 안에 ORDER BY 구문이 없이 PARTITION BY 구문만을 사용합니다.

이렇게 분석함수를 통해 구한 값은 3레벨의 값이 제외된 상태의 합계이므로 3레벨의 값인 RV 값을 곱해주면 전체 누적 카운트 V3를 구할 수 있습니다.

정답 쿼리를 완성했습니다. 이번 퀴즈의 풀이에 사용된 TIP 을 정리해 볼까요.

• 1. 계층구조 전개 구문
• 2. 계층 구조를 역으로 전개하는 방법
• 3. EXP(SUM(LN(v)))을 이용해 칼럼의 전체 곱셈값을 구하는 방법
• 4. 분석함수를 사용해 순차적인 누적곱을 구하는 방법