Oracle MODEL 정리 6

1. MODEL 기본개념

이번에는 MODEL을 가지고 문제풀이를 해보자. 아직 여러함수를 언급을 안했지만 나머지 함수들은 오라클 매뉴얼을 참고하기 바랍니다.

UNION ALL, ROLLUP, MODEL을 이용하여 값을 구해보자. 물론 아래에 나와있는 SQL외에도 다양한 방법이 있습니다. 단지 여러분들의 MODEL에 대한 이해를 돕기위해 만들어 봤습니다.

아래 1번과 같은 테이블에서 2번과 같은 결과값을 구하면 됩니다.

가. 업무 요구사항

테이블에서 가군과 나군의 D값인 평균을 구하고 제일 첫번째 레코드로 보여주자. 나군과 다군의 D값을 합해 제일 마지막 레코드로 보여주자.

가. 테이블

A	B	C	D
가군	A	a	100
나군	B	g	200
나군	B	h	100
다군	C	h	100

나. 결과값

A	B	C	D
평균			33.33
가군	A	a	100
나군	B	g	200
나군	B	h	100
다군	C	h	100
합계			400

2. UNION ALL

업무 요구사항에 따라 SQL을 만들때 동일 테이블에 반복 접근하는 UNION ALL은 쉽고 단순하게 SQL을 만들수있는 방법중 하나입니다. 단 반복 접근하는 문제로 인해 속도는 최악입니다. 물론 이 방법보다 더 안좋은 방법은 WAS로 데이타를 가져가서 루핑형처리를 하거나 UNION ALL을 사용하지 않고 테이블에 반복 접근하는 단순 SQL을 만들어 APP서버나 WAS로 데이타를 가져가서 묶어서 화면에 보여주는 방법이라고 볼수 있습니다.

왜 이런 방법이 좋지 않은지는 성능관리방법론에서 이야기 하도록 하겠습니다. 물론 SQL 사용횟수가 많지 않고 처리 데이타량이 작은경우는 위의 2개 방법이 크게 악영향을 미치지는 않습니다. 워낙 다양한 SQL과 하드웨어 및 소프트웨어 환경이 많아 반드시 이렇다고는 이야기하기 어렵습니다. 금융권에서 룰엔진을 사용하거나 하는 경유에는 어쩔수없이 루핑형SQL 처리를 통해 센터컷을 수행하는게 대표적인 예입니다. 그러나 대체적으로는 맞습니다.

UNION ALL을 사용하여 간단하게 결과값을 만들어 봤습니다. WITH절을 사용한 이유는 테이블을 생성하기 어려운 환경이라 가정하고 SQL로만 테이블과 비슷하게 테스트용 데이타를 만들기 위함입니다.

with t as ( 
 select '가군' a ,  'A'  b , 'a'  c , 100 d  from dual  union all  
 select '나군' a ,  'B'  b , 'g'  c , 200 d  from dual  union all
 select '나군' a ,  'B'  b , 'h'  c , 100 d  from dual  union all
 select '다군' a ,  'C'  b , 'h'  c , 100 d  from dual  )
SELECT '평균' a, '' b, '' c  , ROUND(SUM(DECODE(a, '가군', d)) /
        SUM(DECODE(a, '나군', d)) * 100, 2) d
FROM t
WHERE a IN ('가군', '나군')
UNION ALL
SELECT *
FROM (SELECT a, b, c, d FROM t ORDER BY a, b, c)
UNION ALL
SELECT '합계' a, '' b, '' c, SUM(d) d
FROM t
WHERE a IN ('나군', '다군')  ; 

3. ROLLUP

가번 SQL은 ROLLUP의 복제 기업을 통해 1이라는 숫자를 넣어 처리한 예입니다. ROLLUP의 복제기법편을 보기 바랍니다. 2번 SQL 형태도 ROLLUP을 이용하여 사용가능하며 참고하기 바랍니다.

가. SQL
with t as (
 select '가군' a ,  'A'  b , 'a'  c , 100 d from dual  union all
 select '나군' a ,  'B'  b , 'g'  c , 200 d from dual  union all
 select '나군' a ,  'B'  b , 'h'  c , 100 d from dual  union all
 select '다군' a ,  'C'  b , 'h'  c , 100 d from dual 
 )
select decode(z, 1, '평균', 3, '합계',a) a, b, c, decode(z, 1, y, 3, x, d)
from (select a, b, c, sum(d) d, sum(decode(a, '나군', d, '다군', d)) x, 
             round(sum(decode(a, '가군', d)) / sum(decode(a, '나군', d))*100, 2) y, grouping_id(1, a) z
      from t
      group by rollup(1, (a, b, c))
 )
 order by decode(a, '평균',1,'합계', 99, 3) asc, a, b ;

나. SQL
with t as (
 select '가군' a ,  'A'  b , 'a'  c , 100 d from dual  union all
 select '나군' a ,  'B'  b , 'g'  c , 200 d from dual  union all
 select '나군' a ,  'B'  b , 'h'  c , 100 d from dual  union all
 select '다군' a ,  'C'  b , 'h'  c , 100 d from dual 
 )
SELECT a, b, c , COALESCE(d, e, f) d
FROM (SELECT a, b, c, d, ROUND( SUM(DECODE(a, '가군', d)) OVER()
             / SUM(DECODE(a, '나군', d)) OVER() * 100, 2) e,
             SUM(CASE WHEN a IN ('나군', '다군') THEN d END) OVER() f
      FROM t)
GROUP BY f, ROLLUP(e, (a, b, c, d))
ORDER BY GROUPING(e), a NULLS FIRST, b, c ;

4. MODEL

MODEL절을 사용하여 동일한 결과값을 추출하였습니다. 참고하기 바란다. 3가지 방법중 어느것이 더 사용하기 편한가? 가능하면 3가지 다 알면 좋겠습니다.

가. SQL
with t as (
 select '가군' a ,  'A'  b , 'a'  c , 100 d from dual  union all
 select '나군' a ,  'B'  b , 'g'  c , 200 d from dual  union all
 select '나군' a ,  'B'  b , 'h'  c , 100 d from dual  union all
 select '다군' a ,  'C'  b , 'h'  c , 100 d from dual 
 )
SELECT a, b, c, d
FROM t
MODEL
DIMENSION BY (a, b, c)
MEASURES (d)
RULES
 ( d['평균', '', ''] = ROUND(SUM(d)['가군', any, any] / 
                       SUM(d)['나군', any, any] * 100, 2),
   d['합계', '', ''] = SUM(d)[A IN ('나군','다군'), any, any] 
  )
 ORDER BY  decode(a, '평균', 1, '합계', 99, '가군', 2, '나군', 3, '다군', 4) asc;

나. SQL
with t as (
 select '가군' a ,  'A'  b , 'a'  c , 100 d from dual  union all
 select '나군' a ,  'B'  b , 'g'  c , 200 d from dual  union all
 select '나군' a ,  'B'  b , 'h'  c , 100 d from dual  union all
 select '다군' a ,  'C'  b , 'h'  c , 100 d from dual 
 )
 SELECT a, b, c, d
   FROM t
  MODEL
  DIMENSION BY (ROW_NUMBER() OVER(ORDER BY a, b, c) rn, a)
  MEASURES (b, c, d)
  RULES
  ( d[ 0, '평균'] = ROUND(SUM(d)[ANY, '가군'] / SUM(d)[ANY, '나군'] * 100, 2) ,
    d[99, '합계'] = SUM(d)[ANY, a IN ('나군', '다군')]
  )
  ORDER BY rn
 ; 

5. 결언

이번 칼럼은 UNION ALL, ROLLUP, MODEL을 사용하여 동일한 결과값을 추출하는 문제를 해결해 보았습니다. 속도는 데이타가 많이 없어 테스트를 못했지만 향후에 테스트해서 포스팅하겠습니다. MODEL은 10년전 나온 기술로 아직 개발자들이 모르는 사람들이 많이 있습니다. 하지만 익숙해지면 대단히 유용하니 꼭 익혀서 사용해보기 바란니다. 업무가 복잡한 루핑형 SQL이나 처리요구사항이 어려운 로직을 원쿼리로 해결할수 있는 매력을 MODEL이 가지고 있습니다.