음.. 이번 퀴즈에 대해 제가 나름대로 해석한 내용을 공유하고자 올려보았습니다.

일단 원본 테이블을 보면 다음과 같습니다.

 

1. 교시정보 테이블(이하 A 테이블)
SELECT LEVEL T_NO
           , LEVEL||'교시' T_NAME -- 교시정보
           , DECODE(LEVEL, 1, '0900',
                                      2, '1000',
                                      3, '1100',
                                      4, '1200',
                                      5, '1400',
                                      6, '1500',
                                      7, '1600',
                                      8, '1700') P_SDATE --시작시간
           , DECODE(LEVEL, 1, '0950',
                                      2, '1050',
                                      3, '1150',
                                      4, '1250',
                                      5, '1450',
                                      6, '1550',
                                      7, '1650',
                                      8, '1750') P_EDATE --종료시간
FROM DUAL
CONNECT BY LEVEL <= 8;

 

　　

 

2. 김모군의 주간 시간표(이하 B 테이블)
SELECT DECODE(LEVEL, 1,  2,
                                      2,  3,
                                      3,  2,
                                      4,  4,
                                      5,  1,
                                      6,  3,
                                      7,  5,
                                      8,  2,
                                      9,  6,
                                      10, 3) P_NO --교시정보
           , DECODE(LEVEL, 1,  2,
                                      2,  2,
                                      3,  3,
                                      4,  4,
                                      5,  5,
                                      6,  5,
                                      7,  5,
                                      8,  6,
                                      9,  6,
                                      10, 7) S_WEEK --요일정보(2:월,3:화,4,수,5:목,6:금,7:토)
           , DECODE(LEVEL, 1,  '여성학개론',
                                      2,  '사회변천사',
                                      3,  '생활물리',
                                      4,  '미분적분학',
                                      5,  'TOEIC',
                                      6,  '여성학개론',
                                      7,  '경제학',
                                      8,  'TOEIC',
                                      9,  '생물',
                                      10, '여성학개론') S_SUBJECT --과목정보
           , DECODE(LEVEL, 1,  '김만근',
                                      2,  '최민수',
                                      3,  '이정재',
                                      4,  '홍승대',
                                      5,  '우성식',
                                      6,  '김만근',
                                      7,  '김경제',
                                      8,  '우성식',
                                      9,  '나상실',
                                      10, '김만근') S_PROFESSOR --담당교수
           , DECODE(LEVEL, 1,  '101',
                                      2,  '302',
                                      3,  '202',
                                      4,  '103',
                                      5,  '204',
                                      6,  '101',
                                      7,  '401',
                                      8,  '204',
                                      9,  '102',
                                      10, '101') S_ROOM --강의실
FROM DUAL
CONNECT BY LEVEL <= 10;

 

　　

 

여기서 결과로 나와야 되는 조건이 일요일은 안나와도 되고, 각 교시마다 수업이 없어도 모두 나와야 된다고 했으니 다음과 같이 나와야 할 것입니다.

 

　　

 

이제 A테이블과 B테이블을 T_NO, P_NO를 이용하여 EquiJoin을 해보겠습니다. 그리고 아래부터는 A, B테이블의 내용을 모두 넣지 않고 Alias만 넣어서 진행하겠습니다. 만약 테스트를 하시려면 위의 내용을 인라인뷰로 넣어 테스트 하시면 됩니다.

SELECT *
FROM   A, B
WHERE  A.T_NO = B.P_NO; 

 

　　

 

그런데 문제는 EquiJoin을 했기 때문에 김모군의 주간 시간표에는 없는 7, 8교시가 안나왔습니다. 그래서 기준을 교시정보 테이블을 기준으로 하여 Outer_Join을 해보겠습니다.

 

SELECT *
FROM   A, B
WHERE  A.T_NO = B.P_NO(+);

 

　　

 

이제 주간시간표에 교시정보가 없어도 데이터가 나오는군요. 이 중간집합을 가지고 이제 세로로 되어있는 데이터를 가로로 표시해야 하는데요. 어떻게 접근해야 하는지 교시정보 테이블에서 한 레코드를 가지고 보겠습니다.

 

SELECT *
FROM   A
WHERE  T_NO = 1;             

 

　　

 

이렇게 1교시인 정보(한 레코드)를 가져와서 다음과 같이 만들어야 하는데요.

 

　　

 

어떻게 만들어야 할까요? 바로 Copy 테이블(이하 CopyT)을 만드는 것입니다.

CopyT는 복제테이블이라고 하는데  용도는 테이블 레코드를 인위적으로 늘릴때 사용합니다.

세로에서 가로로로 늘릴때는 DECODE()를 사용하여 인위적으로 만들 수 있으나 가로를 세로로 만들려면 레코드를 해당 컬럼만큼 인위적으로 늘려야 하는데 가공방법이 없기 때문에 CopyT을 이용하여 인위적으로 레코드를 늘립니다.
원리는 '1:M'조인을 이용하는건데요. '1'은 해당레코드 'M'은 CopyT이 되겠죠. 'M'쪽에서 '1'쪽을 조인하면 'M'쪽의 레코드는 늘어나지 않지만 '1'쪽은 레코드가 늘어나겠죠?
이를 이용해서 세로를 가로로 만드는것을 해결할 수 있습니다.

 

[Pivot 가로-세로, 세로-가로]를 정리해보면 다음과 같이 할 수 있습니다.

 

1. 세로를 가로로 - DECODE()를 이용하여 가공한 필드를 생성한다.
2. 가로를 세로로 - CopyT을 생성하여 DECODE()에 한 필드로 생성한다.

 

이제 이 방법을 가지고 만들어 보겠습니다. 현재 교시정보 테이블에 T_NAME, P_SDATE, P_EDATE 3개의 필드를 하나의 필드로 만들어야 하기 때문에 Copy 테이블도 3개의 로우를 만듭니다. 그리고 이를 카티션 프로덕트 조인을 하겠습니다.
      
SELECT A.*
           , B.CNT
FROM   A
         , (SELECT LEVEL CNT
            FROM    DUAL
            CONNECT BY LEVEL <= 3) B;        
        　　

 

보시는 것처럼 CNT 필드(CopyT에서 가져온 값)가 생성되었고 레코드 또한 CopyT로 인해
늘어났습니다. 이제 이를 DECODE()를 이용하여 한 줄로 만들어 보겠습니다.    

 

SELECT A.*
           , B.CNT
           , DECODE(CNT, 1, A.T_NAME,
                                   2, A.P_SDATE,
                                   3, A.P_EDATE) VAL
FROM   A
         , (SELECT LEVEL CNT
            FROM   DUAL
            CONNECT BY LEVEL <= 3) B; 
        　　

 

DECODE()를 이용해서 VAL필드에 데이터를 넣었습니다. 이제 여기서 VAL 필드만 나오게 하면 되는데요.
이 또한 CNT필드의 값을 그룹으로 묶고 가장 작은 값을 추출하면 됩니다. 만들어 보겠습니다.

 

SELECT MIN(DECODE(CNT, 1, A.T_NAME,
                                          2, A.P_SDATE,
                                          3, A.P_EDATE)) VAL
FROM   A
         , (SELECT LEVEL CNT
            FROM   DUAL
            CONNECT BY LEVEL <= 3) B
GROUP BY CNT;       

       

　　

 

보시는 것처럼 데이터가 한 필드로 나왔네요. 이처럼 'Pivot 세로-가로'는 CopyT를 이용하여 만들 수 있다는 것을 보셨는데요. 이 원리를 이용하여 위에서 만든 중간집합도 CopyT를 이용하여 만들어 보겠습니다.

A 테이블과 B 테이블에서 각각 3개의 필드를 가로에서 세로로 만들어야 하기 때문에 CopyT의 Row는 3개까지만 생성합니다. 오해하실까봐 미리 말씀드리면 만약 각각의 테이블에서 만들어야 되는 필드의 수가 틀리다면 각각 CopyT를 카티션 프로덕트 조인을 통해 중간집합을 생성하고 이들을 조인해야 하지만 이번 경우는 운이(?) 좋게도 가공하는 필드의 수가 같이 때문에 먼저 중간집합을 만들고 CopyT와 카티션 프로덕트를 하였습니다.

 

SELECT C.*, D.*
           , DECODE(CNT, 1, T_NAME, 
                                   2, P_SDATE,
                                   3, P_EDATE) GUBN_1
           , DECODE(CNT, 1, S_SUBJECT,
                                   2, S_PROFESSOR,
                                   3, S_ROOM) GUBN_2
FROM   (SELECT *
             FROM   A, B
             WHERE  A.T_NO = B.P_NO(+)) C
          , (SELECT LEVEL CNT
             FROM   DUAL
             CONNECT BY LEVEL <= 3) D
ORDER BY T_NO, S_WEEK;

 

　　

 

설명을 약간 드리자면 CopyT인 D 테이블을 생성해서 이를 DECODE()로 구분하고 GUBN_1에는 교시정보, GUBN_2에는 주간시간표를 넣었습니다. 그리고 정렬 순서를 T_NO, S_WEEK로 했는데요. 여기서 T_NO 대신에 P_NO를 넣어도 되지만 P_NO에는 7, 8교시 정보가 없기 때문에 T_NO를 기준으로 하였습니다.

이제 이 데이터를 '월, 화, 수, 목, 금, 토'로 표현해야 하는데요. 다행이 주간시간표 테이블에 S_WEEK 필드가 있네요. 이 필드는 '월, 화, 수, 목, 금, 토' 값을 숫자로 표현한건데요. 자세한 사항은 아래 퀴즈 중 '달력생성쿼리'를 참고하시면 되겠습니다. 이를 가지고 DECODE()로 표현하면 다음과 같습니다.

 

SELECT GUBN_1 " "
           , DECODE(S_WEEK, 2, GUBN_2) "월"
           , DECODE(S_WEEK, 3, GUBN_2) "화"
           , DECODE(S_WEEK, 4, GUBN_2) "수"
           , DECODE(S_WEEK, 5, GUBN_2) "목"
           , DECODE(S_WEEK, 6, GUBN_2) "금"
           , DECODE(S_WEEK, 7, GUBN_2) "토"  
           , T_NO
           , CNT  
FROM   (SELECT C.*, D.*
                        , DECODE(CNT, 1, T_NAME,
                                                2, P_SDATE,
                                                3, P_EDATE) GUBN_1
                        , DECODE(CNT, 1, S_SUBJECT,
                                                2, S_PROFESSOR,
                                                3, S_ROOM) GUBN_2
            FROM   (SELECT *
                         FROM   A, B
                         WHERE  A.T_NO = B.P_NO(+)) C
                      , (SELECT LEVEL CNT
                         FROM   DUAL
                         CONNECT BY LEVEL <= 3) D
           ORDER BY T_NO, S_WEEK, CNT);
       

　　

 

드디어 원하는 시간표 모양이 비슷하게 나왔는데요. 문제는 2교시가 총 3번이 나왔죠. 우리가 원하는 모양은 2교시 한번에 모든 주간 시간표가 나오게 하는것인데요. 어떻게 접근해야 할까요?

위에 그림에서 빨간색으로 테두리 되어 있는 부분을 보시면 T_NO(2교시)에 대한 그룹이라는 것을 알 수 있죠. 그리고 CNT는 2교시 그룹에 대해 CopyT에서 부여한 번호이구요. 이 정보를 토대로 자세히 보시면 '여성학개론, 생활물리, TOEIC' 즉 과목정보의 CNT 필드값이 1인걸 볼 수 있습니다.

계속해서 '김만근, 이정재 우성식'인 교수정보의 CNT 필드값이 2인걸 볼 수 있고, 마지막으로 '101, 202, 204'인 강의실 정보의 CNT 필드값이 3인걸 볼 수 있습니다. 이를 다시 해석하면 2교시인 그룹에서 CNT값이 가장 작은 값을 뽑아내면 한 레코드로 표현할 수 있다는 것을 알 수 있습니다.

이런 내용을 가지고 만들어 보겠습니다.

 

SELECT MIN(GUBN_1) " "
           , MIN(DECODE(S_WEEK, 2, GUBN_2)) "월"
           , MIN(DECODE(S_WEEK, 3, GUBN_2)) "화"
           , MIN(DECODE(S_WEEK, 4, GUBN_2)) "수"
           , MIN(DECODE(S_WEEK, 5, GUBN_2)) "목"
           , MIN(DECODE(S_WEEK, 6, GUBN_2)) "금"
           , MIN(DECODE(S_WEEK, 7, GUBN_2)) "토"    
FROM   (SELECT C.*, D.*
                        , DECODE(CNT, 1, T_NAME,
                                                2, P_SDATE,
                                                3, P_EDATE) GUBN_1
                       , DECODE(CNT, 1, S_SUBJECT,
                                               2, S_PROFESSOR,
                                               3, S_ROOM) GUBN_2
            FROM   (SELECT *
                         FROM   A, B
                        WHERE  A.T_NO = B.P_NO(+)) C
                      , (SELECT LEVEL CNT
                         FROM   DUAL
                         CONNECT BY LEVEL <= 3) D
           ORDER BY T_NO, S_WEEK, CNT) E     
GROUP BY T_NO, CNT;

 

　　

 

완성되었습니다. 이 퀘즈를 정리해 보면 2가지 포인트가 있는데요.

 

1. 'Pivot 가로-세로'를 만드는 방법은 CopyT를 이용하여 레코드를 늘린다.
2. 그룹을 묶을 때 중간그룹으로 묶는 방법도 있지만 레코드별로 그룹을 묶을 수도 있다.

 

이를 이용하면 앞으로 다양한 가공 문제에 적용하여 보다 풍부한 정보를 뽑아낼 수 있을거라 생각합니다.

 

이 퀴즈를 내주신 김영현님께 감사드리고 덕분에 좋은 내용을 알게 되서 좋았습니다.
다른 분들도 퀴즈게시판에 많은 참여 부탁드립니다. ^^

 

ps. 읽으시다가 이상한 점 있으시면 리플 달아주시기 바랍니다. 감사합니다.