[Oracle/Sql] 통계테이블에 유용하게 쓰이는 pivot,rollup

front 작업만 해서 쿼리 만질일이 없다가 오랜만에 다시 쿼리를 해본다. 

통계데이터를 만드는 일인데, 확실히 쿼리로 처리하면 java로 처리하는것 보다 편하다.

with data as (
select '2016-06-11' dt,  'typeA' type, 'itemA' item, '10000' amount from dual union all
select '2016-06-11' dt,  'typeA' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemD' item, '20000' amount from dual union all
select '2016-06-12' dt,  'typeC' type, 'itemC' item, '15000' amount from dual union all
select '2016-06-13' dt,  'typeC' type, 'itemA' item, '30000' amount from dual
)
select * from data;

위와 같이 데이터가 있을 대 날짜를 열로 하고 Type,Item별로 합계를 내는 통계라고 할때, oracle pivot함수를 이용하면 조회를 

하면 아래와 같은 결과물이 나온다. 

with data as (
select '2016-06-11' dt,  'typeA' type, 'itemA' item, '10000' amount from dual union all
select '2016-06-11' dt,  'typeA' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemD' item, '20000' amount from dual union all
select '2016-06-12' dt,  'typeC' type, 'itemC' item, '15000' amount from dual union all
select '2016-06-13' dt,  'typeC' type, 'itemA' item, '30000' amount from dual
)
select * from data
PIVOT (
  SUM(AMOUNT)
  FOR DT IN ('2016-06-11','2016-06-12','2016-06-13')
);

여기서 Type과 Item별로 합산 통계를 중간에 넣으려면 rollup 함수를 이용하면 간단히 해결가능하다

with data as (
select '2016-06-11' dt,  'typeA' type, 'itemA' item, '10000' amount from dual union all
select '2016-06-11' dt,  'typeA' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemD' item, '20000' amount from dual union all
select '2016-06-12' dt,  'typeC' type, 'itemC' item, '15000' amount from dual union all
select '2016-06-13' dt,  'typeC' type, 'itemA' item, '30000' amount from dual
)
select 
  type,
  item,
  sum(d1) as "2016-06-11",
  sum(d2) as "2016-06-12",
  sum(d3) as "2016-06-13"
from (
  select * from data
  PIVOT (
    SUM(AMOUNT)
    FOR DT IN ('2016-06-11' as d1,'2016-06-12' as d2,'2016-06-13' as d3)
  )
)
group by rollup(type,item)

위와같이 노란색으로 표시한 부분이 type별 합산과 토탈 합산이다. 

그런데 이런 합계를 위로 보여주고 싶을 경우에는 아래와 같이 grouping을 통해 순서를 변경해 준다. 

with data as (
select '2016-06-11' dt,  'typeA' type, 'itemA' item, '10000' amount from dual union all
select '2016-06-11' dt,  'typeA' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemD' item, '20000' amount from dual union all
select '2016-06-12' dt,  'typeC' type, 'itemC' item, '15000' amount from dual union all
select '2016-06-13' dt,  'typeC' type, 'itemA' item, '30000' amount from dual
)
select 
  type,
  item,
  sum(d1) as "2016-06-11",
  sum(d2) as "2016-06-12",
  sum(d3) as "2016-06-13"
from (
  select * from data
  PIVOT (
    SUM(AMOUNT)
    FOR DT IN ('2016-06-11' as d1,'2016-06-12' as d2,'2016-06-13' as d3)
  )
)
group by rollup(type,item)
order by 
    grouping(type) desc,
    type, 
    grouping(item) desc,
    item

근데 사실 동일한 기능이지만 decode로 처리하게 되면 아래와 같이 쿼리가 훨씬 간결해 진다. 

with data as (
select '2016-06-11' dt,  'typeA' type, 'itemA' item, '10000' amount from dual union all
select '2016-06-11' dt,  'typeA' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemB' item, '20000' amount from dual union all
select '2016-06-11' dt,  'typeB' type, 'itemD' item, '20000' amount from dual union all
select '2016-06-12' dt,  'typeC' type, 'itemC' item, '15000' amount from dual union all
select '2016-06-13' dt,  'typeC' type, 'itemA' item, '30000' amount from dual
)
select 
  type,
  item,
  sum(decode(dt,'2016-06-11',amount)) as "2016-06-11",
  sum(decode(dt,'2016-06-12',amount)) as "2016-06-12",
  sum(decode(dt,'2016-06-13',amount)) as "2016-06-13"
from data    
group by rollup(type,item)
order by     
  grouping(type) desc,
  type, 
  grouping(item) desc,
  item;

결과는 동일하다. 

PIVOT에 경우 Java iBatis에서 컬럼을 dynamic하게 만들경우 동작하지 않는다. 

decode가 깔끔하고 좋은듯 하다. 물론 성능 이슈는 확인하지 않았다. 실행계획상 cost는 거의 동일.. 

물론 데이터가 워낙에 작으니.^^

알아두변 유용한 decode로 열행 바꾸기 + rollup으로 부분합산 구하기 였다.