[oracle] connect by level 사용

--[Quiz]최근 12개월동안의 날짜(yyyymm)출력하기

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201501','YYYYMM'), LEVEL-1),'YYYYMM') months
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE('201512','YYYYMM'),TO_DATE('201501','YYYYMM'))+1;

 

connect by level 기본

select level from dual
connect by level <= 5;

level 을 connect by level에서 범위를 정해주고, 마치 for문처럼 select 문에서 level이라는 이름으로 1부터 시작되어 차례로 나오게 된다.

 

자 그럼 퀴즈 답으로 생각한 sql 문을 하나하나 뜯어보도록 하겠다.

CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE('201512','YYYYMM'),TO_DATE('201501','YYYYMM'))+1;

이 부분에서 그냥 12로 값을 줘도 상관 없었겠지만, 동적할당을 위해 굳이굳이 이렇게 만들었다.

MONTHS_BETWEEN 함수를 통해 12월 - 1월 = 11 여기서 +1 을 통해 12을 만들었다.

그럼 지금 level 값은 1부터 차례로 1,2,3 ....12 까지 나올 것이다.

 

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201501','YYYYMM'), LEVEL-1),'YYYYMM') months

여기서 ADD_MONTH함수를 통해 0,1,2, ....11 차례로 0 개월, 1개월 이런 식으로 더해준 값을 TO_CHAR 함수를 통해 목표했던 yyyymm 형태로 만들어주고, alias로 months로 설정했다.

 

--[문제] 15년도 월별 입사자의 수를 세어서 출력하시오.

--단, 1월 12월까지 월별로 출력하시오. 입사자가 없는달은 0으로 출력하시오.

일단 이런 문제는 점진적으로 다가가 보겠다.

SELECT TO_CHAR(START_DATE,'YYYYMM') 입사월 ,COUNT(*) 입사자수
FROM S_EMP 
WHERE START_DATE < TO_DATE('20160101','YYYYMMDD')
GROUP BY TO_CHAR(START_DATE,'YYYYMM');

입사일(start_date)가 16년 1월 1일 전인 사람만 필터링한 후 yyyymm형태로 그룹핑 후 입사자 수를 집계한 결과

이렇게 된다.

 

그럼 connect by level을 통해 12달을 출력해놓은 쿼리와 합쳐보겠다.

SELECT month_all.months 입사월, NVL(semp.hirepeople,0) 입사자수
FROM 
	(SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201501','YYYYMM'), LEVEL-1),'YYYYMM') months
	FROM DUAL
	CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE('201512','YYYYMM'),TO_DATE('201501','YYYYMM'))+1) month_all, 
	(SELECT TO_CHAR(START_DATE,'YYYYMM') hiredate ,COUNT(*) hirepeople
	FROM S_EMP 
	WHERE START_DATE < TO_DATE('20160101','YYYYMMDD')
	GROUP BY TO_CHAR(START_DATE,'YYYYMM')) semp
WHERE month_all.months = semp.hiredate(+)
ORDER BY month_all.months;

이러면 요구사항대로 출력되는 것을 알 수 있다.