DATOR


피벗쿼리를 이용한 SQL로 달력 만들기 SQL 활용


한달치 달력을 뽑아 오는 방법을 배워보도록
한다.

(1) 월초/월말을 구한다.

 달력을 구하기 위해서는 아무래도 해당월의 월초와 월말을 알아야 할 것이다. 월말을 구하는
방법은 LAST_DAY 함수를 사용하면 무리 없이 구할 수 있지만, 월초는 따로 함수가 없기
때문에 간단히 쿼리를 이용해 구해보도록 한다.

SELECT TO_DATE( TO_CHAR(SYSDATE,'YYYYMM') || '01', 'YYYY-MM-DD' )
FROM DUAL

TO_DATE(TO_CHAR(SYS
-------------------
2013-09-01 00:00:00



 이 경우는 아주 간단히 생각하면 될 것이다. SYSDATE의 달까지만을 CHAR형태로
 뽑고, 다시 그달의 1일을 더해주고, 이것을 다시 DATE형으로 변환을 주면 이달의
월초를 구할 수 있다.

월말은 LAST_DAY함수를 사용하면 쉽게 구할 수 있다.

SELECT LAST_DAY(SYSDATE) FROM DUAL;

LAST_DAY(SYSDATE)
-------------------
2013-09-30 18:01:44



(2) 한달의 데이터를 구한다.

 이제 우리가 뽑고자 하는 한달이라는 집합의 기간은 나온 셈이다. 그럼 우리가 이제
정말 원하는 데이터는 어떻게 뽑아야 할까? 절차형 프로그래밍 언어라면 월초에서
월말까지 FOR문이나 WHILE문을 이용해 루프를 돌리면 쉽게 뽑을 수 있겠지만, 아쉽게
ORACLE에는 FOR문과 WHILE문은 없다. 하지만, 실망할 필요는 없다. 오라클에도 루프를
돌릴 수 있는 방법이 있다. 바로 CONNECT BY 문을 이용하면 된다.

SELECT LEVEL FROM DUAL
CONNECT BY TO_DATE( TO_CHAR(SYSDATE,'YYYYMM')
|| '01', 'YYYY-MM-DD' ) + LEVEL - 1
<= LAST_DAY(SYSDATE)

     LEVEL
----------
         1
         2
        ...

        29
        30

30 rows selected.



 그럼 CONNECT BY문에 시작을 처음에 구한 월초에 LEVEL - 1로 조건을 주고, 월말보다
작거나 같다는 조건을 살펴보도록 하겠다.
 CONNECT BY의 일반적인 사용은 START WITH에서 시작한 조건 부터 시작해 하위, 또는
상위에 있는 조건을 반복적으로 보여주기 위해 사용한다.
 헌대 이번 예제에서는 START WITH문은 존재 하지 않는다. 다만 CONNECT BY의 조건으로
월초 + LEVEL - 1이라는 조건이 월말보다 작거나 같을 경우라는 조건을 부여하고 있다.
 따로 시작 조건도 없고, 끝나야 할 데이터도 없는 상황이기 대문에 CONNECT BY문은
계속 실행되게 되고, 월초 + LEVEL -1이 월말보다 커지게 되면 더이상
실행되지 않는다. 그럼 월초 + LEVEL - 1의 의미는 무엇일까?
 LEVEL은 CONNECT BY문의 시작 시 1로 시작해 반복 수행되면서 1씩 증가하는 가상의
컬럼이라 할 수 있다. 즉 시작은 1일+1-0=1일 이라는 값이 나오지만 루프를 돌면서
1+2-1=2...1+31-1=31과 같은 값이 나오게 된다.
 자, 이제 이 정도의 정보가 있으면 한달치 ROW데이터를 얻는대 문제는 없다!

SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYYMM')
|| LPAD ( LEVEL, 2 , '0' ) ) DATES
FROM DUAL
CONNECT BY TO_DATE( TO_CHAR(SYSDATE,'YYYYMM')
|| '01', 'YYYY-MM-DD' ) + LEVEL - 1
<= LAST_DAY(SYSDATE)

DATES
-------------------
2013-09-01 00:00:00
2013-09-02 00:00:00
...
2013-09-29 00:00:00
2013-09-30 00:00:00


위의 쿼리와 같이 LEVEL이 두자리가 되지 않으면 0값을 추가해 년월 과 합한 값을
DATE형으로 변환하면 한달치의 ROW데이터를 얻을 수 있다.
 한달치 데이터를 모두 구했지만, 이 데이터만 가지고 어플리케이션에서 달력을
만들기에는 약간 어려움이 있을 것이다. 이 데이터를 가지고 이번 예제에서 뽑고자
하는 데이터는 아래와 같은 데이터 이다. 이제 아래와 같은 데이터를 뽑는 방법을
알아보도록 한다.

(3) 주, 요일를 구한다.

먼저 위와 같은 결과를 얻기 위해 필요한 것이 무엇일까 하면, 주와 요일 그리고
날짜이다. 날짜야 우리가 구한 값을 그대로 사용하면 무리가 없다. 여기서 필요
한 것은 요일과 주 인대, 요일은 어렵지 않게 구할 수 있다.

 DA D
-- -
01 1
02 2
...
29 1
30 2


 필자가 이 예제를 테스트 했던 2013년 09월이 일요일부터 시작하여 이해가
조금 쉬울 것으로 보여진다.
 요일은 구하는 방법은 DATE형식을 TO_CHAR(DATES,'D')라는 형식으로 구하면
일요일=1, 월요일=2...토요일=7과 같은 값을 가지게 된다. 위와 같은 형식에
따르면 11월1일=일요일, 11월7일=토요일이라는 결과를 얻을 수 있다.
 자, 그럼 날짜와 요일을 구했으니, 이제 주를 구해야 한다. 주를 구하기 위해서
살펴보아야 할 점은 DAY는 1~31까지의 숫자이고, DAT는 1~7까지의 숫자이다.
중요한 점은, DAY가 1~31까지 반복되는 동안 DAT는 1~7까지의 숫자가 반복된다는
점이다. 증가하는 규칙을 살펴보아, DAY와 DAT의 값을 빼보도록한다.

SELECT
    TO_CHAR(DATES,'DD') DAY,
    TO_CHAR(DATES, 'D') DAT,
    TO_CHAR(DATES, 'D') - TO_CHAR(DATES,'DD') WEEK
FROM   
( SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYYMM')
|| LPAD ( LEVEL, 2 , '0' ) ) DATES
FROM DUAL
CONNECT BY TO_DATE( TO_CHAR(SYSDATE,'YYYYMM')
|| '01', 'YYYY-MM-DD' ) + LEVEL - 1
<= LAST_DAY(SYSDATE) )

DA D       WEEK
-- - ----------
01 1          0
02 2          0
...
29 1        -28
30 2        -28


 위와 같이 WEEK는 규칙적으로 나오게 된다. 그럼 이러한 결과가 나오게 된
이유를 살펴보도록 한다.
 먼저 1~7까지의 빼기의 값은 0이 나오는 것은 당연한 이유이다. 1-1=0... 7-7=0...
 이렇게 되면 1일에서 7일까지의 WEEK의 값은 0이라는 공통된 값을 가진다.
 그럼 8부터 14를 보도록 한다. 1-8 = -7... 7-14 = -7 이렇게 7단위로 공통된
규칙을 가진다.
 그럼 이제 아까 보았던 이미지 대로의 최종 결과를 뽑아보자!

(3) 열을 행으로 변환

 이 예제는 아래의 예제에도 나왔던 열을 행으로 변환 하는 예제이기 때문에
큰 설명은 필요 없을 것이라 생각한다 (아래의 ROW를 COLOUMN으로 변환하는
예제 참조)

SELECT
    WEEK,
    MAX(DECODE(DAT, 1, DAY)) 일,
    MAX(DECODE(DAT, 2, DAY)) 월,
    MAX(DECODE(DAT, 3, DAY)) 화,
    MAX(DECODE(DAT, 4, DAY)) 수,
    MAX(DECODE(DAT, 5, DAY)) 목,
    MAX(DECODE(DAT, 6, DAY)) 금,
    MAX(DECODE(DAT, 7, DAY)) 토
FROM   
( SELECT
    TO_CHAR(DATES,'DD') DAY,
    TO_CHAR(DATES, 'D') DAT,
    TO_CHAR(DATES, 'D') - TO_CHAR(DATES,'DD') WEEK
FROM   
( SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYYMM')
|| LPAD ( LEVEL, 2 , '0' ) ) DATES
FROM DUAL
CONNECT BY TO_DATE( TO_CHAR(SYSDATE,'YYYYMM')
|| '01', 'YYYY-MM-DD' ) + LEVEL - 1
<= LAST_DAY(SYSDATE) ) )
GROUP BY WEEK

      WEEK 일 월 화 수 목 금 토
---------- -- -- -- -- -- -- --
       -28 29 30
        -7 08 09 10 11 12 13 14
         0 01 02 03 04 05 06 07
       -14 15 16 17 18 19 20 21
       -21 22 23 24 25 26 27 28


 열을 WEEK단위의 GROUP BY로 뽑아 보면 우리가 원하는 결과에 거의 접근했으나,
정렬순서가 맞지 않다. 또한 WEEK의 값은 우리가 원하는 값이 아니기에 더이상
필요가 없다.
 이제 최종 단계로 정렬만 해줌으로서 원하는 값을 구할 수 있다.

SELECT 일,월,화,수,목,금,토 FROM
( SELECT
    WEEK,
    MAX(DECODE(DAT, 1, DAY)) 일,
    MAX(DECODE(DAT, 2, DAY)) 월,
    MAX(DECODE(DAT, 3, DAY)) 화,
    MAX(DECODE(DAT, 4, DAY)) 수,
    MAX(DECODE(DAT, 5, DAY)) 목,
    MAX(DECODE(DAT, 6, DAY)) 금,
    MAX(DECODE(DAT, 7, DAY)) 토
FROM   
( SELECT
    TO_CHAR(DATES,'DD') DAY,
    TO_CHAR(DATES, 'D') DAT,
    TO_CHAR(DATES, 'D') - TO_CHAR(DATES,'DD') WEEK
FROM   
( SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYYMM')
|| LPAD ( LEVEL, 2 , '0' ) ) DATES
FROM DUAL
CONNECT BY TO_DATE( TO_CHAR(SYSDATE,'YYYYMM')
|| '01', 'YYYY-MM-DD' ) + LEVEL - 1
<= LAST_DAY(SYSDATE) ) )
GROUP BY WEEK )
ORDER BY 일

일 월 화 수 목 금 토
-- -- -- -- -- -- --
01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30


 이제 원하던 결과가 확실히 나왔다. 위와 같은 결과라면 어플리케이션에서 간단한
루프로 한달의 데이터를 뽑아 달력을 만드는 대 큰 무리가 없을 것이다.

Tag :

Leave Comments