[Oracle] MODEL절을 사용해서 엑셀 흉내내기 [oracle 10g]

MODEL 절은 SELECT 구문과 같이 사용되어 결과셋의 각각의 항목들을 연관배열 처럼 취급해서 이러한 항목들을

일정한 계산규칙에 따라 계산을 수행한뒤 새로운 결과셋을 리턴한다.

 

이 방법을 사용해서  저는 간단하게 월별 합계를 구해봤는데요..

장점은 테이블을 이차원배열로 접근할 수 있어서 엑셀의 함수처럼 계산을 할수 있다는 거거든요..

앞뒤 로우의 특정행에 접근할 수 있다는거 ~~이거 되게 좋네요.....

 

앞의 로우의 값을 사용해서 다음 로우의 값을 예측하는 것이 가능해지니 사용방법은 무궁무진해 질듯 합니다.

 

[구문형식]

SELECT ...   FROM ...   MODEL [main]             [ reference models ]             [ PARTITION BY (<컬럼>) ]             DIMENSION BY (<컬럼>)  .................................... 로우를 식별할 기준이 되는 컬럼을 명시           MEASURES (<컬럼>)  ........................................ 새로 계산이 되는 컬럼 명시                    [ IGNORE NAV ] | [ KEEP NAV ]             [ RULES                    [ UPSERT | UPDATE ]                    [ AUTOMATIC ORDER | SEQUENTIAL ORDER ]                    [ ITERATE (n) [UNTIL <CONDITION> ]             ]                    (<CELL_ASSIGNMENT > = <expression> ...)   ※ RULES 절에는 MEASURES 절에서 명시한 컬럼들만 기술 할 수 있다.

 

 

[ex]

다음과 같은 테이블을 생성한다.

 

[SELECT * FROM MODEL_T]

 

 

[MODEL 절을 사용한 쿼리]

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)

 

 

[결과]

 

[출처] [ oracle 10g ] 오라클 MODEL절을 사용해서 엑셀 흉내내기|작성자 GENERAL