[참고] 관련포스트
[오라클] 계층형 쿼리의 응용 - 달력만들기
계층형 쿼리의 특징은 선택하는 테이블에 있는 로우의 개수가 제한적이라도 상-하위관계를 어떻게 만드느냐에 따라 여러개로 늘어날수있다는 점이다. 또한 루트노드를 식별하는 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; |
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')) ; |
