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

Oracle Q&A 37883 게시물 읽기

No. 37883

맨 아랫 Row에 비율 나타내기..
작성자	아폴론(apollon)	작성일	2010-08-20 19:18ⓒ 2010-08-20 19:19ⓜ	조회수	3,892

select '예산' kubn,'0001' accd, '21' hmcd,'01' mnth, 1000000 amt from dual
union all
select '예산' kubn,'0003' accd, '21' hmcd,'02' mnth, 2000000 kubi from dual
union all
select '비용' kubn,'0001' accd, '21' hmcd,'01' mnth, 800000 kubi from dual
union all
select '비용' kubn,'0005' accd, '13' hmcd,'03' mnth, 600000 kubi from dual

이러한 데이터를 다음과 같이 나타내려 합니다.
ㅎㅎ 혼자 점심때 부터 지금까지..ㅎㅎㅎ 에이 된장.. 안되네...

구분   accd   hmcd      m01        m02       m03 . .. tot
예산   0001    21    1,000,000      0         0     1,000,000
비용   0001    21      800,000      0         0       800,000
비율   0001    21        80.00      0         0         80.00

-------------------------------------------------------------------------

예산   0003    21    2,000,000      0         0     2,000,000
비용   0003    21            0      0         0             0
비율   0003    21            0      0         0             0

-----------------------------------------------------------------------

예산   0005    13            0      0         0             0
비용   0005    13      600,000      0         0       600,000
비율   0005    13            0      0         0             0

-----------------------------------------------------------------------

예산 전체계         3,000,000      0         0      3,000,000
비용 전체계         1,400,000      0         0      1,400,000
비율 전체계             46.67      0         0          46.67

이 글에 대한 댓글이 총 5건 있습니다.

WITH t AS
(
SELECT '예산' kubn, '0001' accd, '21' hmcd, '01' mnth, 1000000 amt FROM dual
UNION ALL SELECT '예산', '0003', '21', '02', 2000000 FROM dual
UNION ALL SELECT '비용', '0001', '21', '01', 800000 FROM dual
UNION ALL SELECT '비용', '0005', '13', '03', 600000 FROM dual
)
SELECT DECODE(lv, 1, '예산', 2, '비용', '비율') kubn
     , NVL(accd, '전체계') accd
     , hmcd
     , MIN(DECODE(mnth, '01', v)) m01
     , MIN(DECODE(mnth, '02', v)) m02
     , MIN(DECODE(mnth, '03', v)) m03
     , DECODE(lv, 3
     , ROUND( SUM(SUM(DECODE(lv,2,v))) OVER(PARTITION BY accd, hmcd)
            / SUM(SUM(DECODE(lv,1,v))) OVER(PARTITION BY accd, hmcd)
            * 100, 2)
     , SUM(v) ) tot
FROM (
        SELECT accd, hmcd, mnth, lv
             , DECODE(lv, 1, v1, 2, v2, 3, v3) v
          FROM (
                SELECT accd, hmcd, mnth
                     , SUM(DECODE(kubn, '예산', amt)) v1
                     , SUM(DECODE(kubn, '비용', amt)) v2
                     , ROUND( SUM(DECODE(kubn, '비용', amt))
                            / SUM(DECODE(kubn, '예산', amt))
                            * 100, 2) v3
                  FROM t
                 GROUP BY ROLLUP((accd, hmcd)), mnth
                )
             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3)
        )
GROUP BY accd, hmcd, lv
ORDER BY accd, hmcd, lv
;

마농(manon94)님이 2010-08-23 10:56에 작성한 댓글입니다.

계산이 정확한지 확인하기 위해 데이타 몇 개 추가했습니다.

WITH T AS (
select '예산' kubn,'0001' accd, '21' hmcd,'01' mnth, 1000000 amt from dual
union all
select '예산' kubn,'0001' accd, '21' hmcd,'01' mnth, 3000000 amt from dual
union all
select '예산' kubn,'0001' accd, '21' hmcd,'02' mnth, 1000000 amt from dual
union all
select '예산' kubn,'0003' accd, '21' hmcd,'02' mnth, 2000000 kubi from dual
union all
select '비용' kubn,'0001' accd, '21' hmcd,'01' mnth, 800000 kubi from dual
union all
select '비용' kubn,'0005' accd, '13' hmcd,'03' mnth, 600000 kubi from dual
)
SELECT CASE WHEN ACCD IS NULL THEN KUBN||' 전체 계' ELSE KUBN END KUBN
,ACCD,HMCD,MON1,MON2,MON3,MON4,MON5,MON6,MON7,MON8,MON9,MON10,MON11,MON12,TOT
FROM(
SELECT KUBN,ACCD,HMCD
,NVL(SUM(DECODE(MNTH,'01',AMT)),0) MON1
,NVL(SUM(DECODE(MNTH,'02',AMT)),0) MON2
,NVL(SUM(DECODE(MNTH,'03',AMT)),0) MON3
,NVL(SUM(DECODE(MNTH,'04',AMT)),0) MON4
,NVL(SUM(DECODE(MNTH,'05',AMT)),0) MON5
,NVL(SUM(DECODE(MNTH,'06',AMT)),0) MON6
,NVL(SUM(DECODE(MNTH,'07',AMT)),0) MON7
,NVL(SUM(DECODE(MNTH,'08',AMT)),0) MON8
,NVL(SUM(DECODE(MNTH,'09',AMT)),0) MON9
,NVL(SUM(DECODE(MNTH,'10',AMT)),0) MON10
,NVL(SUM(DECODE(MNTH,'11',AMT)),0) MON11
,NVL(SUM(DECODE(MNTH,'12',AMT)),0) MON12
,SUM(AMT) TOT
FROM T
GROUP BY KUBN,ROLLUP((ACCD,HMCD))
)
MODEL
PARTITION BY (ACCD,HMCD)
DIMENSION BY (KUBN)
MEASURES (MON1,MON2,MON3,MON4,MON5,MON6,MON7,MON8,MON9,MON10,MON11,MON12,TOT) IGNORE NAV
RULES(
--예산
MON1['예산']=MON1['예산']
,MON2['예산']=MON2['예산']
,MON3['예산']=MON3['예산']
,MON4['예산']=MON4['예산']
,MON5['예산']=MON5['예산']
,MON6['예산']=MON6['예산']
,MON7['예산']=MON7['예산']
,MON8['예산']=MON8['예산']
,MON9['예산']=MON9['예산']
,MON10['예산']=MON10['예산']
,MON11['예산']=MON11['예산']
,MON12['예산']=MON12['예산']
,TOT['예산']=TOT['예산']
--비용
,MON1['비용']=MON1['비용']
,MON2['비용']=MON2['비용']
,MON3['비용']=MON3['비용']
,MON4['비용']=MON4['비용']
,MON5['비용']=MON5['비용']
,MON6['비용']=MON6['비용']
,MON7['비용']=MON7['비용']
,MON8['비용']=MON8['비용']
,MON9['비용']=MON9['비용']
,MON10['비용']=MON10['비용']
,MON11['비용']=MON11['비용']
,MON12['비용']=MON12['비용']
,tot['비용']=TOT['비용']
--비율
,MON1['비율']=CASE WHEN MON1['예산']=0 THEN 0 ELSE MON1['비용']/MON1['예산']*100 END
,MON2['비율']=CASE WHEN MON2['예산']=0 THEN 0 ELSE MON2['비용']/MON2['예산']*100 END
,MON3['비율']=CASE WHEN MON3['예산']=0 THEN 0 ELSE MON3['비용']/MON3['예산']*100 END
,MON4['비율']=CASE WHEN MON4['예산']=0 THEN 0 ELSE MON4['비용']/MON4['예산']*100 END
,MON5['비율']=CASE WHEN MON5['예산']=0 THEN 0 ELSE MON5['비용']/MON5['예산']*100 END
,MON6['비율']=CASE WHEN MON6['예산']=0 THEN 0 ELSE MON6['비용']/MON6['예산']*100 END
,MON7['비율']=CASE WHEN MON7['예산']=0 THEN 0 ELSE MON7['비용']/MON7['예산']*100 END
,MON8['비율']=CASE WHEN MON8['예산']=0 THEN 0 ELSE MON8['비용']/MON8['예산']*100 END
,MON9['비율']=CASE WHEN MON9['예산']=0 THEN 0 ELSE MON9['비용']/MON9['예산']*100 END
,MON10['비율']=CASE WHEN MON10['예산']=0 THEN 0 ELSE MON10['비용']/MON10['예산']*100 END
,MON11['비율']=CASE WHEN MON11['예산']=0 THEN 0 ELSE MON11['비용']/MON11['예산']*100 END
,MON12['비율']=CASE WHEN MON12['예산']=0 THEN 0 ELSE MON12['비용']/MON12['예산']*100 END
,TOT['비율']=CASE WHEN TOT['예산']=0 THEN 0 ELSE TOT['비용']/TOT['예산']*100 END
)
ORDER BY ACCD,DECODE(KUBN,'예산',1,'비용',2,'비율',3)
;

꼬랑지님이 2010-08-23 12:58에 작성한 댓글입니다. Edit Ｘ

WITH t AS
(
SELECT '예산' kubn, '0001' accd, '21' hmcd, '01' mnth, 1000000 amt FROM dual
UNION ALL SELECT '예산', '0003', '21', '02', 2000000 FROM dual
UNION ALL SELECT '비용', '0001', '21', '01', 800000 FROM dual
UNION ALL SELECT '비용', '0005', '13', '03', 600000 FROM dual
UNION ALL SELECT '예산', '0003', '21', '03', 2000000 FROM dual
UNION ALL SELECT '비용', '0003', '21', '03', 800000 FROM dual
)
SELECT kubn, accd, hmcd
     , MIN(DECODE(mnth, '01', amt)) m01
     , MIN(DECODE(mnth, '02', amt)) m02
     , MIN(DECODE(mnth, '03', amt)) m03
     , MIN(DECODE(mnth, '', amt)) tot
FROM (SELECT *
          FROM (SELECT kubn, accd, hmcd, mnth
                     , SUM(amt) amt
                  FROM t
                 GROUP BY kubn, CUBE(mnth, (accd, hmcd))
                )
         MODEL
         PARTITION BY (accd, hmcd, mnth)
         DIMENSION BY (kubn)
         MEASURES (amt)
         RULES ( amt['예산'] = amt[cv()]
               , amt['비용'] = amt[cv()]
               , amt['비율'] = ROUND(amt['비용'] / amt['예산'] * 100, 2)
               )
        )
GROUP BY accd, hmcd, kubn
ORDER BY accd, hmcd, DECODE(kubn,'예산',1,'비용',2,'비율',3)
;

마농(manon94)님이 2010-08-25 08:49에 작성한 댓글입니다.

WITH t AS
(
SELECT '예산' kubn, '0001' accd, '21' hmcd, '01' mnth, 1000000 amt FROM dual
UNION ALL SELECT '예산', '0003', '21', '02', 2000000 FROM dual
UNION ALL SELECT '비용', '0001', '21', '01', 800000 FROM dual
UNION ALL SELECT '비용', '0005', '13', '03', 600000 FROM dual
UNION ALL SELECT '예산', '0003', '21', '03', 2000000 FROM dual
UNION ALL SELECT '비용', '0003', '21', '03', 800000 FROM dual
)
SELECT DECODE(lv, 1, '예산', 2, '비용', 3, '비율') v
     , accd, hmcd
     , MIN(DECODE(mnth, '01', v)) m01
     , MIN(DECODE(mnth, '02', v)) m02
     , MIN(DECODE(mnth, '03', v)) m03
     , MIN(DECODE(mnth, '', v)) tot
FROM (SELECT accd, hmcd, mnth
             , lv
             , DECODE(lv, 1, v1, 2, v2, 3, v3) v
          FROM (SELECT accd, hmcd, mnth
                     , SUM(DECODE(kubn, '예산', amt)) v1
                     , SUM(DECODE(kubn, '비용', amt)) v2
                     , ROUND(SUM(DECODE(kubn, '비용', amt))
                     / SUM(DECODE(kubn, '예산', amt)) * 100, 2) v3
                  FROM t
                 GROUP BY CUBE(mnth, (accd, hmcd))
                )
             , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3)
        )
GROUP BY accd, hmcd, lv
ORDER BY accd, hmcd, lv
;

마농(manon94)님이 2010-08-25 09:05에 작성한 댓글입니다.
이 댓글은 2010-08-25 09:09에 마지막으로 수정되었습니다.

이렇게 답변 달아 주시는 것이 절대쉬운것은 아닌데..

신경 써 주셔서 감사드립니다.

좋은 나날들 되세요..

아폴론(apollon)님이 2010-08-26 10:43에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
37886	Grouping, Group_id 질문입니다.. [1]	아폴론	2010-08-23	4199
37885	특정날짜에서 시간대별 통계 쿼리문. [3]	박하나무	2010-08-23	9311
37884	오늘 마지막 시간가져오기	박주현	2010-08-23	3083
37883	맨 아랫 Row에 비율 나타내기.. [5]	아폴론	2010-08-20	3892
37882	이건 오라클 버그인건가요? [2]	류신	2010-08-20	2679
37881	INSERT ~ SELECT 구문에서 INSERT 할 때 PK를 지정할 수 있을까요? [1]	용세중	2010-08-20	2989
37880	쿼리 속도 개선 및 DataSet 의 검색속도개선점	아이오	2010-08-20	2748