Oracle - MODEL 1단계

[ Oracle - MODEL 1단계 ]

 

정의 : MODEL 절은 SELECT 구문과 같이 사용되어 결과셋의 각각의 항목들을 연관배열 처럼취급해서 
       이러한 항목들을 일정한 계산규칙에 따라 계산을 수행한뒤 새로운 결과셋을 리턴한다.
       장점은 테이블을 이차원배열로 접근할 수 있어서 엑셀의 함수처럼 계산을 할수 있다
       즉, 앞뒤 로우의 특정행에 접근할 수 있다

☞  [구문형식]     SELECT ...   FROM   ...   MODEL [main]         [ reference models ]         [ PARTITION BY (<컬럼>) ]         DIMENSION BY (<컬럼>)                              <------------ 로우를 식별할 기준이 되는 컬럼을 명시         MEASURES (<컬럼>)                                              <------------ 새로 계산이 되는 컬럼 명시                [ IGNORE NAV ] | [ KEEP NAV ]         [ RULES           <------------ RULES 절에는 MEASURES 절에서 명시한 컬럼들만 기술 할 수 있다.                    [ UPSERT | UPDATE ]                    [ AUTOMATIC ORDER | SEQUENTIAL ORDER ]                    [ ITERATE (n) [UNTIL <CONDITION> ]             ]                    (<CELL_ASSIGNMENT > = <expression> ...) ; ☞ 예제   WITH MODEL_T AS ( SELECT '201101' TERM, 100000 SALARY FROM DUAL UNION ALL   SELECT '201102' TERM, 112000 SALARY FROM DUAL UNION ALL   SELECT '201103' TERM, 123000 SALARY FROM DUAL UNION ALL   SELECT '201104' TERM, 131000 SALARY FROM DUAL UNION ALL   SELECT '201105' TERM, 122000 SALARY FROM DUAL UNION ALL   SELECT '201106' TERM, 140000 SALARY FROM DUAL UNION ALL   SELECT '201107' TERM, 160000 SALARY FROM DUAL )  SELECT TERM, ORI_SAL, TAXES    FROM MODEL_T MODEL     DIMENSION BY (TERM)      MEASURES (SALARY ORI_SAL, SALARY TAXES ) RULES(     ORI_SAL['200701'] = ORI_SAL['200701'] * 0.33,      -- 순서를 함 바꿔봤다.^^     TAXES  ['200702'] = TAXES  ['200702'] * 0.33,     TAXES  ['200703'] = TAXES  ['200703'] * 0.33,     TAXES  ['200704'] = TAXES  ['200704'] * 0.33,     TAXES  ['200705'] = TAXES  ['200705'] * 0.33,     TAXES  ['200706'] = TAXES  ['200706'] * 0.33  ) ORDER BY 1 ☞     ※ DIMENSION 컬럼이 TERM 이다.     즉, DIMENSION 컬럼의 값을 연관배열의 KEY 처럼 사용해서 로우를 식별한다.    여기서는 '20070x' 라는 컬럼값으로 로우를 식별하고 있다.  SELECT TERM      , SAL    FROM model_t MODEL     DIMENSION BY (term )     MEASURES (SALARY sal) RULES(    SAL['QTR1']   = SAL['200701']+SAL['200702']+SAL['200703'],    SAL['TOTAL1'] = sum(sal)[term IN ('200701','200702') ]    SAL['TOTAL2'] = sum(sal)[term IN ('200701','200702') ] ) ORDER BY 1; ※ sum() 함수도 사용할 수 있는데 배열키값에 저런식으로도 사용이 가능. ※ MODEL 절을 사용할 경우 SELECT 문장의 컬럼명은 MEASURES 절에서 반드시 명시한     것들만 올수 있다.      따라서 그룹쿼리를 사용하기전에 MODEL절을 사용하여 계산을 마친 결과셋을 인라인뷰로     사용해야한다.      왜냐면 SELECT 구문의 컬럼명이 MODEL 절에 종속적이기 때문이다.  WITH TOTAL_T AS (     SELECT NO, CAMP_CODE, GUBUN          , MONTH_1, MONTH_2,MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7,MONTH_8          , MONTH_9, MONTH_10,MONTH_11,MONTH_12           , TOTAL        FROM DATA_1_T     MODEL          DIMENSION BY (ID )         MEASURES (TOTAL,MONTH_1, MONTH_2,MONTH_3, MONTH_4, MONTH_5,MONTH_6, MONTH_7                  ,MONTH_8, MONTH_9,MONTH_10, MONTH_11,MONTH_12                  ,ID NO,GUBUN,CAMP_CODE)     RULES(       TOTAL[ ANY ] = MONTH_1[cv(ID)] + MONTH_2[CV(ID)] + MONTH_3[CV(ID)] +MONTH_4[CV(ID)] + MONTH_5[CV(ID)] + MONTH_6[CV(ID)] + MONTH_7[CV(ID)] +MONTH_8[CV(ID)] + MONTH_9[CV(ID)] + MONTH_10[CV(ID)] + MONTH_11[CV(ID)] +MONTH_12[CV(ID)]          ) )  SELECT ROW_NUMBER() OVER (ORDER BY 1) RN1       , ROW_NUMBER() OVER( PARTITION BY DTL_RGN_CODE ORDER BY GUBUN ) RN2       , DECODE( ROW_NUMBER() OVER(PARTITION BY DTL_RGN_CODE ORDER BY GUBUN )                , 1, DTL_RGN_CODE,' ') "그 룹"       , NVL2(GUBUN,GUBUN,'합계') GUBUN       , sum(MONTH_1) jan       , sum(MONTH_2) feb       , sum(MONTH_3) mar       , sum(MONTH_4) apr       , sum(MONTH_5) may       , sum(MONTH_6) jun       , sum(MONTH_7) jul       , sum(total)   total    from total_t a, CAMP_INFO_T b   where a.CAMP_CODE = b.CAMP_CODE   group by rollup(DTL_RGN_CODE,gubun) ;