데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Row 생성하기..세번째

작성자

아폴론(apollon)

작성일

2010-08-13 14:49ⓒ
2010-08-13 16:17ⓜ

조회수

3,344

WITH table1 AS
(
SELECT '1010' clcd, '2010' year, '01' mnth, '신문' menm, 1000000 amt, 400000 fee FROM dual
UNION ALL SELECT '1010', '2010', '01', '잡지', 500000, 50000 FROM dual
UNION ALL SELECT '1010', '2010', '02', '신문', 1000000, 400000 FROM dual
UNION ALL SELECT '1010', '2010', '02', '잡지', 500000, 50000 FROM dual
UNION ALL SELECT '1010', '2010', '03', '신문', 1000000, 400000 FROM dual
UNION ALL SELECT '1010', '2010', '03', '잡지', 500000, 50000 FROM dual
UNION ALL SELECT '1020', '2010', '01', '신문', 1000000, 400000 FROM dual
UNION ALL SELECT '1020', '2010', '01', '잡지', 500000, 50000 FROM dual
UNION ALL SELECT '1020', '2010', '02', '신문', 1000000, 400000 FROM dual
UNION ALL SELECT '1020', '2010', '02', '잡지', 500000, 50000 FROM dual
UNION ALL SELECT '1020', '2010', '03', '신문', 1000000, 400000 FROM dual
UNION ALL SELECT '1020', '2010', '03', '잡지', 500000, 50000 FROM dual
)
, table2 AS
(
SELECT '1010' clcd, '2010' year, '01' mnth, 'CATV' menm, 900000 amt, 135000 fee FROM dual
UNION ALL SELECT '1010', '2010', '02', 'CATV', 900000, 135000 FROM dual
UNION ALL SELECT '1010', '2010', '03', 'CATV', 900000, 135000 FROM dual
)
, table3 AS
(
SELECT clcd, year, mnth, menm, amt, fee FROM table1
UNION ALL
SELECT clcd, year, mnth, menm, amt, fee FROM table2
)
, code0 AS
(
SELECT DISTINCT clcd FROM table3 WHERE year = '2010'
)
, code1 AS
(
SELECT '01' kubn2, '신문' kunm FROM dual
UNION ALL SELECT '02', '잡지' FROM dual
UNION ALL SELECT '03', 'TV'    FROM dual
UNION ALL SELECT '04', 'Radio' FROM dual
UNION ALL SELECT '05', 'CATV' FROM dual
UNION ALL SELECT '06', '옥외' FROM dual
UNION ALL SELECT '07', '제작' FROM dual
)
SELECT clcd
     , DECODE(GROUP_ID(),0,'billing',1,'Ravinue',2,'비율') kubn
     , kubn2
     , kunm
     , DECODE(GROUP_ID(),0,MIN(DECODE(mnth,'01',amt))
                        ,1,MIN(DECODE(mnth,'01',fee))
                        ,2,MIN(DECODE(mnth,'01',rat)) ) m01
     , DECODE(GROUP_ID(),0,MIN(DECODE(mnth,'02',amt))
                        ,1,MIN(DECODE(mnth,'02',fee))
                        ,2,MIN(DECODE(mnth,'02',rat)) ) m02
     , DECODE(GROUP_ID(),0,MIN(DECODE(mnth,'03',amt))
                        ,1,MIN(DECODE(mnth,'03',fee))
                        ,2,MIN(DECODE(mnth,'03',rat)) ) m03
     , DECODE(GROUP_ID(),0,SUM(amt)
                        ,1,SUM(fee)
                        ,2,ROUND(SUM(fee)/SUM(amt)*100,2) ) m_tot
FROM (
        SELECT c0.clcd
             , NVL(c1.kubn2, '14') kubn2
             , NVL(c1.kunm, '합계') kunm
             , mnth
             , SUM(amt) amt
             , SUM(fee) fee
             , ROUND(SUM(fee) / SUM(amt) * 100, 2) rat
          FROM code0 c0
         CROSS JOIN code1 c1
          LEFT JOIN (SELECT clcd, mnth, menm, amt, fee
                       FROM table3
                      WHERE year = '2010'
                     ) a
            ON c0.clcd = a.clcd
           AND c1.kunm = a.menm
         GROUP BY c0.clcd, a.mnth, ROLLUP((c1.kubn2, c1.kunm))
        )
GROUP BY clcd, kubn2, kunm, ROLLUP(clcd, clcd)
ORDER BY clcd, GROUP_ID(), kubn2
;