오라클 connect by level 사용법

서비스를 개발하다보면 시간별, 월별 그래프 등을 보여줘야할 때가 있다.

 

예를들어 과거 1년간의 월별 그래프를 표현한다고 치자.

12개월간의 데이터가 모두 수집되어 있으면 고민할 필요 없이 SQL을 작성하여 그래프를 표현할 것이다.

하지만 수집에 누락이 있다던지의 문제로 인해 데이터가 비어있는 경우가 있을 수 있다.

이럴때는 CONNECT BY LEVEL을 이용하여 미리 시간 데이터를 만들어 놓고 JOIN을 통하여 차트 데이터를 구현한다.

 

다음은 현재시간을 기준으로 월별, 일별, 년도별을 출력하는 예제이다.

 

- 과거 1년간 월별 출력

SELECT
    V_MONTH
FROM (
	SELECT 
    	TO_CHAR(ADD_MONTHS(SYSDATE, -LEVEL+1), 'YYYYMM') AS V_MONTH
    FROM DUAL 
    CONNECT BY LEVEL <= MONTHS_BETWEEN( TO_DATE(TO_CHAR(SYSDATE, 'YYYYMM'), 'YYYYMM'), TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMM'), 'YYYYMM') )
)

 

- 과거 한달간 일별 출력

SELECT 
	TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') - LEVEL AS V_DAY
FROM DUAL 
CONNECT BY LEVEL <= TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') - TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') + 30

 

- 과거 10년간의 연도출력

SELECT 
	TO_CHAR(SYSDATE, 'YYYY') - LEVEL AS V_DAY
FROM DUAL 
CONNECT BY LEVEL <= TO_DATE(TO_CHAR(SYSDATE, 'YYYY'), 'YYYY') - TO_DATE(TO_CHAR(SYSDATE, 'YYYY'), 'YYYY') + 10

 

 

월별 출력은 별도의 함수를 제공하고 있기 때문에 다르게 표현하였지만 연도, 일과 같은 형태로도 구현이 가능하다.

위의 예제를 활용하여 시간뿐만 아니라 다양한 연속성이 있는 데이터 구현이 가능하다.