숫자 함수 응용

	SELECT 
	       MONTH 
	     , EXTRACT (MONTH FROM MONTH) /3 M1
	     , CEIL(EXTRACT (MONTH FROM MONTH) /3) M2
	     , MOD(EXTRACT (MONTH FROM MONTH) ,3) M3
	 FROM(
		SELECT ADD_MONTHS(TRUNC(SYSDATE ,'YYYY'), LEVEL-1) MONTH  
		 FROM DUAL
		CONNECT BY LEVEL <= 12
	     )

리포트나 통계를 낼 때 그룹핑을 위한 값을 생성할 때 숫자함수가 유용하다.

 

SELECT M2
	  ,M3
	  ,DECODE(M3, 1, M2||'분기') M4
	  ,MONTH
 FROM(
	SELECT 
	       MONTH 
	     , EXTRACT (MONTH FROM MONTH) /3 M1
	     , CEIL(EXTRACT (MONTH FROM MONTH) /3) M2
	     , MOD(EXTRACT (MONTH FROM MONTH) ,3) M3
	 FROM(
		SELECT ADD_MONTHS(TRUNC(SYSDATE ,'YYYY'), LEVEL-1) MONTH  
		 FROM DUAL
		CONNECT BY LEVEL <= 12
	     )
     )
;