계층형 쿼리의 응용 - 달력만들기

by 조쉬 posted Dec 08, 2016
?

단축키

Prev이전 문서

Next다음 문서

ESC닫기

크게 작게 위로 아래로 댓글로 가기 인쇄

[참고] 관련포스트 

 

[오라클] 계층형 쿼리 ( START WITH ... CONNECT BY )

 

[오라클] 계층형 쿼리의 응용 - 달력만들기

 

 

 

계층형 쿼리의 특징은 선택하는 테이블에 있는 로우의 개수가 제한적이라도 상-하위관계를 어떻게 만드느냐에 따라 여러개로 늘어날수있다는 점이다. 또한 루트노드를 식별하는 START WITH 절을 생략하게되면 선택하는 모든 로우가 루트노드가 된다. 이런 특징을 이용하여 달력을 작성할것이다.

 

 

 

특징 :

 

기준이 되는 데이터는 2014년 6월의 날짜이다. 즉 2014년 6월 1일부터 6월 30일까지 데이터가 쿼리의 대상이 된다.

=> 추출할 데이터들은 2014년 6월 1일부터 30일까지 30개의 DATE타입의 날짜들이다. 즉 이들이 FROM 절에 올 대상.

 

추출되는 컬럼의 개수가 일요일에서 토요일까지 7개로 정해져있다.

=> 컬럼의 개수가 정해져 있다면 DECODE나 CASE를 사용해서 일정한 조건에 해당하는 값만 추출하도록 쿼리를 작성하면된다.

 

 

일단 2014년 6월 1일의 데이터를 추출하였다. 

 

1
SELECT TO_DATE('20140601','YYYYMMDD') FROM DUAL;

 

 

 


다음 쿼리는 2014년 6월 1일 에서 6월 30일까지 30개의 로우를 추출하는 쿼리는 만드는 과정이다.

1
2
3
SELECT MAKE_DATES, LEVEL
    FROM (SELECT TO_DATE('20140601', 'YYYYMMDD') MAKE_DATES FROM DUAL)
CONNECT BY LEVEL <= 30;

 

 

 

계층형 쿼리의 특성중 START WITH절을 명시하지 않으면 모든 로우가 루트노드로 간주된다. 또한 계층형 쿼리에서 부모-자식노드사이의 관계를 정하는 것이 CONNECT BY절인데 위문장의 경우 CONNECT BY절에서 LEVEL이 30이하인것을 명시했으므로 LEVEL이 1부터 30까지 30의 로우가 강제로 만들어졌다. 이제 레벨값을 이용해서 6월 1일부터 6월 30까지의 데이터를 만들수있다.


1
2
3
SELECT (MAKE_DATES+LEVEL-1) DATES, LEVEL
    FROM (SELECT TO_DATE('20140601', 'YYYYMMDD') MAKE_DATES FROM DUAL)
CONNECT BY LEVEL <= 30;

 

 




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- TO_CHAR 
-- FORMAT_STRING을 'D'로 명시할경우 해당 날까가 일요일에 해당하면 1 월요일은 2,....토요일은 7을 반환
-- FORMAT_STRING을 'DD'로 명시할경우 연도와 일을 제외하고 날짜만 추출
 
SELECT DECODE( TO_CHAR(DATES,'D'), 1, TO_CHAR(DATES, 'DD')) 일,
       DECODE( TO_CHAR(DATES,'D'), 2, TO_CHAR(DATES, 'DD')) 월,
       DECODE( TO_CHAR(DATES,'D'), 3, TO_CHAR(DATES, 'DD')) 화,
       DECODE( TO_CHAR(DATES,'D'), 4, TO_CHAR(DATES, 'DD')) 수,
       DECODE( TO_CHAR(DATES,'D'), 5, TO_CHAR(DATES, 'DD')) 목,
             DECODE( TO_CHAR(DATES,'D'), 6, TO_CHAR(DATES, 'DD')) 금,
             DECODE( TO_CHAR(DATES,'D'), 7, TO_CHAR(DATES, 'DD')) 토
FROM (
        SELECT (MAKE_DATES + LEVEL -1 ) DATES
          FROM ( SELECT TO_DATE('20140601','YYYYMMDD') MAKE_DATES FROM DUAL)
CONNECT BY LEVEL <= 30
);

 

 

 

 

 

 

위 결과를 보면 30개 로우가 출력이 된다..이것을 주차로 GROUP BY을 하게되면 아래와 같은 결과를 볼수있다.  

그룹함수를 사용하기 위해서 MIN()함수를 사용함.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- TO_CHAR 
-- FORMAT_STRING을 'W'로 명시할경우 해당월의 주차를 추출
 
SELECT TO_CHAR(DATES, 'W'),
       MIN(DECODE( TO_CHAR(DATES,'D'), 1, TO_CHAR(DATES, 'DD'))) 일,
       MIN(DECODE( TO_CHAR(DATES,'D'), 2, TO_CHAR(DATES, 'DD'))) 월,
       MIN(DECODE( TO_CHAR(DATES,'D'), 3, TO_CHAR(DATES, 'DD'))) 화,
       MIN(DECODE( TO_CHAR(DATES,'D'), 4, TO_CHAR(DATES, 'DD'))) 수,
       MIN(DECODE( TO_CHAR(DATES,'D'), 5, TO_CHAR(DATES, 'DD'))) 목,
             MIN(DECODE( TO_CHAR(DATES,'D'), 6, TO_CHAR(DATES, 'DD'))) 금,
             MIN(DECODE( TO_CHAR(DATES,'D'), 7, TO_CHAR(DATES, 'DD'))) 토
FROM (
        SELECT (MAKE_DATES + LEVEL -1 ) DATES
            FROM ( SELECT TO_DATE('20140601','YYYYMMDD') MAKE_DATES FROM DUAL) 
CONNECT BY LEVEL <= 30
)
GROUP BY TO_CHAR(DATES, 'W');

 

 

 

위 결과에서 주차로 다시 정렬을 하면 원하던 결과를 얻을수있다.

그리고 30일까지의 데이터를 추출하기 위해 CONNECT BY절에 LEVEL 값이 30이하인 건을 추출하도록 명시했는데 이렇게 할경우 월의 마지막일이 31일 미만인 2월이나 그 밖의 다른 월의 달력을 추출하는데 문제가된다. 그래서 해당 날짜에 속하는 월의 마지막 날짜를 반환하는LAST_DAY함수를 사용해서 마지막 날짜를 가져오게 하였다.

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 
       MIN(DECODE( TO_CHAR(DATES,'D'), 1, TO_CHAR(DATES, 'DD'))) 일,
       MIN(DECODE( TO_CHAR(DATES,'D'), 2, TO_CHAR(DATES, 'DD'))) 월,
       MIN(DECODE( TO_CHAR(DATES,'D'), 3, TO_CHAR(DATES, 'DD'))) 화,
       MIN(DECODE( TO_CHAR(DATES,'D'), 4, TO_CHAR(DATES, 'DD'))) 수,
       MIN(DECODE( TO_CHAR(DATES,'D'), 5, TO_CHAR(DATES, 'DD'))) 목,
       MIN(DECODE( TO_CHAR(DATES,'D'), 6, TO_CHAR(DATES, 'DD'))) 금,
       MIN(DECODE( TO_CHAR(DATES,'D'), 7, TO_CHAR(DATES, 'DD'))) 토
FROM (
        SELECT (MAKE_DATES + LEVEL -1 ) DATES
            FROM ( SELECT TO_DATE('20140601','YYYYMMDD') MAKE_DATES FROM DUAL) 
CONNECT BY (MAKE_DATES + LEVEL-1) <= LAST_DAY(MAKE_DATES)
)
GROUP BY DECODE(TO_CHAR(DATES,'D'), 1, TO_CHAR(DATES, 'IW')+1, TO_CHAR(DATES, 'IW'))
ORDER BY DECODE(TO_CHAR(DATES,'D'), 1, TO_CHAR(DATES, 'IW')+1, TO_CHAR(DATES, 'IW'))
;

 

 

 


Articles

1 2 3 4 5 6 7