데이터베이스 질문과 답변
-.기초자료는 월별 행단위로 금액이 있습니다.
WITH TEST_TABLE AS (
SELECT '1월' mm, '10000' KING_NM FROM DUAL UNION ALL
SELECT '2월' mm, '20000' KING_NM FROM DUAL UNION ALL
SELECT '3월' mm, '30000' KING_NM FROM DUAL UNION ALL
SELECT '4월' mm, '40000' KING_NM FROM DUAL UNION ALL
SELECT '5월' mm, '50000' KING_NM FROM DUAL UNION ALL
SELECT '6월' mm, '60000' KING_NM FROM DUAL UNION ALL
SELECT '7월' mm, '70000' KING_NM FROM DUAL UNION ALL
SELECT '8월' mm, '80000' KING_NM FROM DUAL UNION ALL
SELECT '9월' mm, '90000' KING_NM FROM DUAL UNION ALL
SELECT '10월' mm, '100000' KING_NM FROM DUAL UNION ALL
SELECT '11월' mm, '110000' KING_NM FROM DUAL UNION ALL
SELECT '12월' mm, '120000' KING_NM FROM DUAL
)
SELECT mm, king_nm FROM TEST_TABLE
아래와 같은 표의 쿼리는 어찌작성해야 할까요? 도움 좀 부탁드립니다...
==========================================================
구분 금액 구분 금액 구분 금액 구분 금액
==========================================================
1월 10,000 4월 40,000 7월 70,000 10월 100,000
2월 20,000 5월 50,000 8월 80,000 11월 110,000
3월 30,000 6월 60,000 9월 90,000 12월 120,000
----------------------------------------------------------------------------------------------------
분기소계 60,000 150,000 240,000 330,0000
==========================================================
-- ROLLUP 안쓰고 SUM() OVER()로 간단히 할수있을것같은데 ....
WITH TEST_TABLE AS (
SELECT '1월' mm, '10000' KING_NM FROM DUAL UNION ALL
SELECT '2월' mm, '20000' KING_NM FROM DUAL UNION ALL
SELECT '3월' mm, '30000' KING_NM FROM DUAL UNION ALL
SELECT '4월' mm, '40000' KING_NM FROM DUAL UNION ALL
SELECT '5월' mm, '50000' KING_NM FROM DUAL UNION ALL
SELECT '6월' mm, '60000' KING_NM FROM DUAL UNION ALL
SELECT '7월' mm, '70000' KING_NM FROM DUAL UNION ALL
SELECT '8월' mm, '80000' KING_NM FROM DUAL UNION ALL
SELECT '9월' mm, '90000' KING_NM FROM DUAL UNION ALL
SELECT '10월' mm, '100000' KING_NM FROM DUAL UNION ALL
SELECT '11월' mm, '110000' KING_NM FROM DUAL UNION ALL
SELECT '12월' mm, '120000' KING_NM FROM DUAL
)
SELECT Q1
,SUM(AMT1) AMT1
,Q2
,SUM(AMT2) AMT2
,Q3
,SUM(AMT3) AMT3
,Q4
,SUM(AMT4) AMT4
FROM
(SELECT GB2
, MAX(CASE WHEN GB = 1 THEN MM END) AS Q1
, MAX(CASE WHEN GB = 1 THEN king_nm END) AS AMT1
, MAX(CASE WHEN GB = 2 THEN MM END) AS Q2
, MAX(CASE WHEN GB = 2 THEN king_nm END) AS AMT2
, MAX(CASE WHEN GB = 3 THEN MM END) AS Q3
, MAX(CASE WHEN GB = 3 THEN king_nm END) AS AMT3
, MAX(CASE WHEN GB = 4 THEN MM END) AS Q4
, MAX(CASE WHEN GB = 4 THEN king_nm END) AS AMT4
FROM
(
SELECT gb
, gb2
, mm
, mm2
, king_nm
FROM
(SELECT NTILE(4) OVER (ORDER BY mm2 ASC ) gb
, MOD(MM2-1,3) gb2
, mm
, mm2
, king_nm
FROM (SELECT mm, king_nm , TO_NUMBER(REGEXP_REPLACE(mm, '[^[:digit:]]+', ' ' )) mm2
FROM TEST_TABLE )
)
)
GROUP BY GB2
)
GROUP BY ROLLUP ((Q1,Q2,Q3,Q4))
--11g 이상에서 pivot
SELECT NVL(QRT1_MON,'분기소계') QRT1_MO, SUM(QRT1_KING) QRT1_KING
, QRT2_MON, SUM(QRT2_KING) QRT2_KING
, QRT3_MON, SUM(QRT3_KING) QRT3_KING
, QRT4_MON, SUM(QRT4_KING) QRT4_KING
FROM (SELECT mm, king_nm, CEIL(TO_NUMBER(TRANSLATE(MM,'1234567890'||MM,'1234567890'))/3) QRT
, MOD(TO_NUMBER(TRANSLATE(MM,'1234567890'||MM,'1234567890')) + 2,3) SEQ
FROM TEST_TABLE
)
PIVOT ( MAX(MM) MON ,MAX(KING_NM) KING FOR QRT IN ('1' QRT1,'2' QRT2,'3' QRT3,'4' QRT4))
GROUP BY ROLLUP ((QRT1_MON, QRT2_MON, QRT3_MON,QRT4_MON))
SELECT * FROM (SELECT qt , NVL(rn, 4) rn , NVL(mm, qt||'분기') mm , SUM(king_nm) king_nm FROM (SELECT CEIL(REPLACE(mm, '월') / 3) qt , MOD(REPLACE(mm, '월') - 1, 3) + 1 rn , mm , king_nm FROM test_table ) GROUP BY qt, ROLLUP((rn, mm)) ) PIVOT ( MIN(mm) qt, SUM(king_nm) amt FOR qt IN (1, 2, 3, 4) ) ORDER BY rn ;
답변 주신분들 모두 다 감사드립니다. 모두 채택해드리고 싶은데... 아쉽네요..
참고로 위 구분은 11g에서는 잘되는데..
9i에서는 흐으으......
pivot 을 제외하면 모두 9i 에서 동작됩니다.
pivot 구문만 우리집아찌님께서 사용하신 구문처럼 Group By Min(Decode 로 변경하시면 됩니다.
-- 9i -- SELECT MIN(DECODE(qt, 1, mm)) qt_1, MIN(DECODE(qt, 1, amt)) amt_1 , MIN(DECODE(qt, 2, mm)) qt_2, MIN(DECODE(qt, 2, amt)) amt_2 , MIN(DECODE(qt, 3, mm)) qt_3, MIN(DECODE(qt, 3, amt)) amt_3 , MIN(DECODE(qt, 4, mm)) qt_4, MIN(DECODE(qt, 4, amt)) amt_4 FROM (SELECT qt , NVL(rn, 4) rn , NVL(mm, qt||'분기') mm , SUM(king_nm) amt FROM (SELECT CEIL(REPLACE(mm, '월') / 3) qt , MOD(REPLACE(mm, '월') - 1, 3) + 1 rn , mm , king_nm FROM test_table ) GROUP BY qt, ROLLUP((rn, mm)) ) GROUP BY rn ORDER BY rn ;
) 버튼을 클릭하여 작성 하시면 됩니다.