안녕하세요 강정식입니다. 이번 퀴즈도 www.soqool.com 사이트에서 SQL Qurty Tips 게시판에 있는 문제중 제가 해설한 내용을 오라클클럽으로 가져왔고 이번 문제도 Pivot쿼리를 응용하여 만들수 있는 내용입니다.
마지막으로 좀 더 많은 사례들을 보고 싶으시면 김홍선님께서 운영하시는 www.soqool.com 사이트도 참고하시면 도움이 되실것 같습니다.

일단 퀴즈에 해당하는 데이터를 DUMMY 테이블로 만들어 보겠습니다.

SELECT TO_DATE(’20070301’, ’YYYYMMDD’) DAY FROM DUAL;

이제 이 데이터를 가지고 해당 월의 데이터를 만들어야 하는데요. 만드는 방법은 총 3개가 있습니다.

첫번째는 CREATE TABLE 구문을 이용하여 테이블을 생성하고 데이터를 넣은 뒤에 작업하는 방법.

두번째는 UNION ALL을 이용하여 DUMMY 테이블로 만드는 방법
마지막으로 CONNECT BY를 이용하여 DUMMY 테이블로 만드는 방법

이렇게 3개가 있는데 저는 두번째와 세번째만 가지고 보도록 하겠습니다.

일단 두번째에 해당하는 UNION ALL을 이용하여 만들어보면

SELECT TO_DATE(’20070301’, ’YYYYMMDD’) DAY FROM DUAL UNION ALL
SELECT TO_DATE(’20070302’, ’YYYYMMDD’) DAY FROM DUAL UNION ALL
SELECT TO_DATE(’20070303’, ’YYYYMMDD’) DAY FROM DUAL UNION ALL
..............................................................
SELECT TO_DATE(’20070331’, ’YYYYMMDD’) DAY FROM DUAL;

이렇게 되겠지요. 하지만 이 데이터를 만들기 위해서 Copy&Paste를 많이 하게 되는데요. 이렇게 똑같은 형식이 같은 크기로 늘어날경우 UNION ALL로 접근하는것 보다 CONNECT BY 절을 이용하여 접근하면 짧은 코딩으로도 같은 결과값을 가져올 수 있습니다.

SELECT DAY + LEVEL - 1 MONTH
FROM   (SELECT TO_DATE(’20070301’, ’YYYYMMDD’) DAY 
             FROM    DUAL)
CONNECT BY LEVEL <= LAST_DAY(DAY) - DAY + 1;       

이 쿼리를 잠시 설명드리면 CONNECT BY절에서 (해당월말 - 해당월초 + 1)을 통해 해당월의 일자가 얼마인지 알아내고 LEVEL값이 이 일자보다 작을동안 레코드를 늘어나도록 합니다. 그리고 레코드가 늘어날 때마다 Select_List에서 해당 날짜를 출력하는데 이를 테스트 날짜에 LEVEL을 더하여 늘어나도록 하였습니다. 아시다시피 ’날짜 + 숫자’는 날짜값이 되므로 Select_List에 있는 로직은 가능합니다.

이제 우리가 원하는 원본데이터(2007년 3월)를 노가다 없이 간단히 만들었는데 이 데이터를 가지고 2007년 3월달의 달력을 만들어 보겠습니다.

2007.03월 달력이 이렇게 되는데요. 현재 우리가 가지고 있는 데이터는 한 필드에 세로로만 들어가 있습니다. 이를 달력 형식으로 만들기 위해 ’일, 월, 화, ..., 토’처럼 가로로 늘려야 합니다. 이렇게 하기 위해선 현재 세로로 되어있는 데이터에 규칙성을 부여해야 하는데요. 만들어 보겠습니다.

세로로 되어있는 데이터를 가로로 만들기 위해서는 DECODE()를 이용하여 인위적으로 늘리는 방법은 밑에 퀴즈에서 보셨을 겁니다. 이제 이 DECODE()에 어떤 규칙으로 나열해야 하는지 찾아내야 하는데요. 우리가 알고 있는 규칙은 ’일, 월, 화, ..., 토’ 이런 규칙을 이용하여 가로로 늘리는 것입니다.

그러므로 세로로 되어있는 데이터에 이걸 구분할 수 있는 필드를 추가해서 DECODE()로 늘려야 하는데요. 어떻게 만들어야 할까요?

문자로 형변환 하는 함수가 TO_CHAR()라는 것은 아실겁니다. 이 함수를 통해서 날짜포멧 데이터를 문자포멧 데이터로 자주 바꿔서 사용하곤 하죠. 대표적으로는 TO_CHAR(’2000-01-01’, ’YYYY-MM-DD’) 가 있습니다. 그런데 이 포멧에서 ’D’만 따로 빼면 해당 날짜가
무슨 요일인지 알수 있는 숫자값을 리턴합니다. 즉, ’일, 월, 화, ..., 토’까지 1 ~ 7의 숫자를 리턴하는데 우리는 이 리턴된 숫자를 가지고 요일을 판별할 수 있습니다.

이제 이 요일을 리턴한 결과를 보기 위해 SELECT 구문에 추가를 하겠습니다.

SELECT MONTH
           , TO_CHAR(MONTH, ’D’) WEEK
FROM   (SELECT DAY + LEVEL - 1 MONTH
             FROM   (SELECT TO_DATE(’20070301’, ’YYYYMMDD’) DAY
                          FROM     DUAL)
             CONNECT BY LEVEL <= LAST_DAY(DAY) - DAY + 1);

보시는바와 같이 WEEK 필드에 숫자가 나타났는데요. 이 결과를 가지고 DECODE()를 이용하여 가로로 만들어 보겠습니다.

SELECT DECODE(WEEK, 1, MONTH) SUN
           , DECODE(WEEK, 2, MONTH) MON
           , DECODE(WEEK, 3, MONTH) TUE
           , DECODE(WEEK, 4, MONTH) WED
           , DECODE(WEEK, 5, MONTH) THU
           , DECODE(WEEK, 6, MONTH) FRI
           , DECODE(WEEK, 7, MONTH) SAT
FROM   (SELECT MONTH
                        , TO_CHAR(MONTH, ’D’) WEEK
             FROM   (SELECT DAY + LEVEL - 1 MONTH
                          FROM   (SELECT TO_DATE(’20070301’, ’YYYYMMDD’) DAY
                                       FROM   DUAL)
                          CONNECT BY LEVEL <= LAST_DAY(DAY) - DAY + 1));
               

드디어 가로로 데이터가 나왔습니다. 하지만 달력 형식하고 비슷한데 문제는 한주에 요일이 모두 표시되야 하는데 지금은 한 레코드당 하나의 일자가 들어가 있습니다. 이를 한주에 모두 넣어야 하는데 어떻게 해야할까요?

위 그림에서 빨간선은 한 주를 그룹으로 묶어본 것인데요. 여기서 파란 박스 안의 데이터만 봐주시기 바랍니다. 파란 박스 안의 데이터를 유심히 보시면 각 필드당 데이터가 하나밖에 들어가 있지 않은걸 보실 수 있으실겁니다.

즉, 이 얘기는 빨간선으로 그룹을 만든 다음에 MIN()함수를 이용하여 가장 작은 값을 가져오면 파란박스안에 있는 NULL값은 없어지고 7개의 레코드가 1개의 레코드로 표현할 수 있습니다.

자 그럼 문제는 저 빨간선처럼 한 주를 확인할 수 있는 구분값을 WEEK 필드처럼 생성해야 하는데요. 어떻게 만들어야 할까요?

TRUNC()라는 함수가 있습니다. TRUNC(25.12)로 값을 넣으면 리턴되는 값은 25가 되죠. 즉 지정한 기준 이하의 값은 절삭하는 함수입니다. 이 함수를 날짜에도 적용할 수 있습니다. 예를 들어, TO_CHAR(SYSDATE, ’YYYY’)를 하면 ’2006-01-01’을 리턴하는데요.

 만약 ’YYYY’대신에 ’D’를 넣는다면 어떻게 될까요? 당연히 해당 날짜의 시작 주인 날짜를 리턴합니다. 즉 이것을 활용하면 세로로 되어있는 원본데이터에 빨간선처럼 그룹으로 묶을 수 있는 구분값을 생성할 수 있습니다. 만들어 보겠습니다.

SELECT TRUNC(MONTH, ’D’)      GUBN
           , DECODE(WEEK, 1, MONTH) SUN
           , DECODE(WEEK, 2, MONTH) MON
           , DECODE(WEEK, 3, MONTH) TUE
           , DECODE(WEEK, 4, MONTH) WED
           , DECODE(WEEK, 5, MONTH) THU
           , DECODE(WEEK, 6, MONTH) FRI
           , DECODE(WEEK, 7, MONTH) SAT
FROM   (SELECT MONTH
                       , TO_CHAR(MONTH, ’D’) WEEK
             FROM   (SELECT DAY + LEVEL - 1 MONTH
                          FROM   (SELECT TO_DATE(’20070301’, ’YYYYMMDD’) DAY
                                       FROM   DUAL)
                          CONNECT BY LEVEL <= LAST_DAY(DAY) - DAY + 1));
               

위에서 GUBN 필드를 보시면 해당 날짜의 시작 주인 날짜가 생성된 것을 볼 수 있습니다. 이제 저 GUBN 필드를 이용해서 저 그룹 안에 가장 작은 값을 가져오면 달력처럼 생성이 되겠네요. 만들어 보겠습니다.

SELECT MIN(SUN) SUN
           , MIN(MON) MON
           , MIN(TUE) TUE
           , MIN(WED) WED
           , MIN(THU) THU
           , MIN(FRI) FRI
           , MIN(SAT) SAT
FROM   (SELECT TRUNC(MONTH, ’D’)      GUBN
                        , DECODE(WEEK, 1, MONTH) SUN
                        , DECODE(WEEK, 2, MONTH) MON
                        , DECODE(WEEK, 3, MONTH) TUE
                        , DECODE(WEEK, 4, MONTH) WED
                        , DECODE(WEEK, 5, MONTH) THU
                        , DECODE(WEEK, 6, MONTH) FRI
                        , DECODE(WEEK, 7, MONTH) SAT
             FROM   (SELECT MONTH
                                     , TO_CHAR(MONTH, ’D’) WEEK
                          FROM   (SELECT DAY + LEVEL - 1 MONTH
                                       FROM   (SELECT TO_DATE(’20070301’, ’YYYYMMDD’) DAY
                                                    FROM   DUAL)
                                                    CONNECT BY LEVEL <= LAST_DAY(DAY) - DAY + 1)))
GROUP BY GUBN;                      
               

드디어 달력 형식으로 출력이 되었습니다. 이제 남은 일은 달력처럼 이쁘게 다듬기만 하면 되겠네요. 만들어 보겠습니다.

SELECT TO_NUMBER(TO_CHAR(MIN(SUN), 'DD'))   "일"
           , TO_NUMBER(TO_CHAR(MIN(MON), 'DD'))  "월"
           , TO_NUMBER(TO_CHAR(MIN(TUE), 'DD'))   "화"
           , TO_NUMBER(TO_CHAR(MIN(WED), 'DD'))   "수"
           , TO_NUMBER(TO_CHAR(MIN(THU), 'DD'))   "목"
           , TO_NUMBER(TO_CHAR(MIN(FRI), 'DD'))    "금"
           , TO_NUMBER(TO_CHAR(MIN(SAT), 'DD'))   "토"
FROM   (SELECT TRUNC(MONTH, 'D')      GUBN
                        , DECODE(WEEK, 1, MONTH) SUN
                        , DECODE(WEEK, 2, MONTH) MON
                        , DECODE(WEEK, 3, MONTH) TUE
                        , DECODE(WEEK, 4, MONTH) WED
                        , DECODE(WEEK, 5, MONTH) THU
                        , DECODE(WEEK, 6, MONTH) FRI
                        , DECODE(WEEK, 7, MONTH) SAT
            FROM   (SELECT MONTH
                                    , TO_CHAR(MONTH, 'D') WEEK
                         FROM   (SELECT DAY + LEVEL - 1 MONTH
                                      FROM   (SELECT TO_DATE('20070301', 'YYYYMMDD') DAY
                                                   FROM   DUAL)
                                                   CONNECT BY LEVEL <= LAST_DAY(DAY) - DAY + 1)))
GROUP BY GUBN
ORDER BY GUBN;

부연 설명을 드리자면 최종 Select_List에 날짜값을 리턴이 되었는데 달력에는 일자만 들어가 있기 때문에 TO_CHAR()로 변환하였고 이 값은 문자값으로 2자리를 모두 표현하기 때문에 TO_NUMBER()를 이용해서 숫자로 변환 하였습니다.
               
이 문제에서 중점적으로 보셔야 할 사항은 위에서 하나의 필드로 되어있는 값들을 어떻게 그룹으로 만들고 또 이를 어떻게 합치는지에 대해서 이해햐셔야 합니다. 아래 퀴즈에서도 말씀드렸듯이 테이블에 있는 데이터를 있는 그대로 사용하는 것이 아니라 필요에 따라 이처럼 중간집합을 만들고 또 늘였다 줄였다를 자유자재로 할 수 있는 사고를 키우셔야 합니다. 그래야 엔코아컨실팅의 이화식 선생님께서 말씀하시는 "코딩을 1/10으로 줄이면서도 10배이상의 수행속도를 향상사킬수 있다."에 다가설수 있으니까요.

지금까지 긴글 읽어주셔서 감사드립니다. 보시고 이상한 점이나 궁금하신 내용이 있으시면 리플 달아주시기 바랍니다.