dazzilove

다시금 DB공부 시작!
"대용량 데이터베이스솔루션2"권을 읽어나가면 곧 마주치게 되는 예재 하나에 쩔쩔매는 자신을 발견. ^^;;
헤고헤고.. 이거 열심히 해야겠구먼....
PS.샘플데이터 만들기.. 내공을 키워야겠다. 추출된 데이터를 보니 가관이네. ㅋㄷ

샘플데이터 만들기 : temp.prc
쿼리 :
select '제품' || item as "제품그룹"
     , decode(floor(dd / 6), 0, 1, decode(floor(dd / 16), 0, 2, decode(floor(dd / 26), 0, 3, 4))) ||  '주기' as "주기"
     , round(sum(decode(mm, '01', tot_cnt)) / sum(decode(mm, '01', 1)), 2) as "01월"
     , round(sum(decode(mm, '02', tot_cnt)) / sum(decode(mm, '02', 1)), 2) as "02월"
     , round(sum(decode(mm, '03', tot_cnt)) / sum(decode(mm, '03', 1)), 2) as "03월"
     , round(sum(decode(mm, '04', tot_cnt)) / sum(decode(mm, '04', 1)), 2) as "04월"
     , round(sum(decode(mm, '05', tot_cnt)) / sum(decode(mm, '05', 1)), 2) as "05월"
     , round(sum(decode(mm, '06', tot_cnt)) / sum(decode(mm, '06', 1)), 2) as "06월"
     , round(sum(tot_cnt) / count(1), 2) as "전체평균"
from   (
        select decode(prd_code, 'A1', '1', 'A2', '1', 'A3', '2', 'A5', '2', 'A6', '2', 'A4', '3', '4') item
               , substr(make_dt, 5, 2) mm
               , substr(make_dt, 7, 2) dd
               , sum(make_cnt) tot_cnt
        from   tab_prd_make_hist
        where  make_dt < '20090701'
        group by decode(prd_code, 'A1', '1', 'A2', '1', 'A3', '2', 'A5', '2', 'A6', '2', 'A4', '3', '4')
               , substr(make_dt, 5, 2)
               , substr(make_dt, 7, 2)
       ) a
group by item 
       , decode(floor(dd / 6), 0, 1, decode(floor(dd / 16), 0, 2, decode(floor(dd / 26), 0, 3, 4)))
order by "제품그룹" asc, "주기" asc