오라클 MODEL 함수
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
MODEL 함수[편집]
--------------------------------------------------------------------------------
-- MODEL을 이용한 SUM(합계) 영역 추가
--------------------------------------------------------------------------------
SELECT COL_TERM
, COL_SAL
FROM (SELECT 1 NUM, '200701' TERM, 1000000 SALARY FROM DUAL UNION ALL
SELECT 2 NUM, '200702' TERM, 2000000 SALARY FROM DUAL UNION ALL
SELECT 3 NUM, '200703' TERM, 1500000 SALARY FROM DUAL UNION ALL
SELECT 4 NUM, '200704' TERM, 1900000 SALARY FROM DUAL UNION ALL
SELECT 5 NUM, '200705' TERM, 2300000 SALARY FROM DUAL UNION ALL
SELECT 6 NUM, '200706' TERM, 3000000 SALARY FROM DUAL )
MODEL
DIMENSION BY (TERM AS COL_TERM)
MEASURES ( SALARY AS COL_SAL )
RULES (
COL_SAL['분1'] = COL_SAL['200701'] + COL_SAL['200702'] + COL_SAL['200703']
, COL_SAL['분기'] = COL_SAL['200704'] + COL_SAL['200705'] + COL_SAL['200706']
, COL_SAL['평균' ] = AVG(COL_SAL) [ COL_TERM BETWEEN '200701' AND '200706' ]
, COL_SAL['합계' ] = SUM(COL_SAL) [ COL_TERM BETWEEN '200701' AND '200706' ]
)
ORDER BY 1
;