스프레드시트 흉내 내기
스프레드시트와 같이 계산식을 사용하여 그 결과를 가상의 셀 형태로 볼 수 있다.
구문 형식:
SELECT ...
FROM ...
MODEL [ main ]
[ reference models ]
[ PARTITION BY (<cols>) ]
DIMENSION BY (<cols>)
MEASURE (<cols>)
[ IGNORE NAV ] | [ KEEP NAV ]
[ RULES
[ UPSERT | UPDATE ]
[ AUTOMATIC ORDER | SEQUENTIAL ORDER ]
[ ITERATE (n) UNTIL <condition> ]
]
( <cell_assignment> = <expression> ... )
- PARTITION BY: 분석 함수에서 사용했던 것과 같이 쿼리에서 특정 컬럼의 값을 바탕으로 파티션(부분적인 그룹)을 만드는 역할
- DIMENSION BY: PARTITION BY로 나누어진 파티션 내부에서 로우를 식별할 컬럼을 명시한다. 이러한 컬럼을 DIMENSION 컬럼이라 하는데, 이 컬럼의 값은 하나의 로우 내에서 MEASURES에서 명시하는 컬럼의 배열 인덱스 형태로 접근할 수 있다.
- MEASURES: 새로 계산되는 컬럼을 명시한다.
- IGNORE NAV: IGNORE NAV를 명시하면 반환되는 셀의 값이 NULL이나 값이 없을 경우, 데이터 타입에 따라 다음과 같은 규칙으로 값을 반환한다. 숫자형(Numeric) 타입일 경우 0, DATETIME 타입일 경우 01-JAN-2000, 문자형 타입일 경우 빈(empty) 문자를 반환한다.
- KEEP NAV: KEEP NAV를 명시하면 문자형 타입일 경우에는 빈 문자, 나머지 유형은 무조건 NULL을 반환한다.
- RULES: RULES 다음에 어떤 계산을 수행할지 그 계산 수식을 정의한다.
- UPSERT: UPDATE와 INSERT를 혼합해 사용한 키워드로써, MEASURES에 명시한 컬럼이 각 로우의 계싼 결과가 존재할 경우에는 값을 UPDATE하며, 만약 해당 컬럼이 없을 경우에는 신규로 INSERT 해서 값을 보여준다.
- UPDATE: UPSERT와는 달리 컬럼 값을 갱신만 하며, 신규로 INSERT 하지 않는다.
- AUTOMATIC ORDER: RULES에서 명시된 계산 규칙을 Oracle이 논리적인 의존성에 따라 규칙을 처리한다.
- SEQUENTIAL ORDER: 계산 규칙을 작성된 순서에 따라 처리한다.
- ITERATE n UNTIL <condition>: 계산식의 연산을 condition에 명시한 조건을 만족할 때까지 n번 반복한다.
예제:
select col_term
, col_sal
, col_tax
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 col_term)
MEASURES ( salary col_sal
, salary col_tax )
RULES ( -- 원래의 Cell 값에 0.033을 곱한 결과
col_tax['200701'] = col_tax['200701'] * 0.033
, col_tax['200702'] = col_tax['200702'] * 0.033
, col_tax['200703'] = col_tax['200703'] * 0.033
, col_tax['200704'] = col_tax['200704'] * 0.033
, col_tax['200705'] = col_tax['200705'] * 0.033
, col_tax['200706'] = col_tax['200706'] * 0.033
)
order by 1 ;
--------------------------------
COL_TERM COL_SAL COL_TAX
--------------------------------
200701 1000000 33000
200702 2000000 66000
200703 1500000 49500
200704 1900000 62700
200705 2300000 75900
200706 3000000 99000
--------------------------------
또는,
select term
, salary
, salary * 0.033 tax
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 )
;
분기별 합계, 평균, 합계 금액을 계산하면,
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 col_term)
MEASURES ( salary col_sal )
RULES (
col_sal['분기1'] = col_sal['200701'] + col_sal['200702'] + col_sal['200703']
, col_sal['분기2'] = 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
;
--------------------
COL_TERM COL_SAL
--------------------
200701 1000000
200702 2000000
200703 1500000
200704 1900000
200705 2300000
200706 3000000
분기1 4500000
분기2 7200000
평균 1950000
합계 11700000
--------------------