데이터베이스 질문과 답변

분기별 합산 1 5 1,898

by 스마트 정 [SQL Query] 쿼리 피봇 분기별 [2016.05.30 17:54:59]

-.기초자료는 월별 행단위로 금액이 있습니다.

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

==========================================================
 

by 우리집아찌 [2016.05.30 18:44:35]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- 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))

 

by jkson [2016.05.30 19:23:08]
?
1
2
3
4
5
6
7
8
9
10
11
--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))

 

by 마농 [2016.05.31 08:46:58]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
;

 

by 스마트 정 [2016.05.31 09:32:31]

답변 주신분들 모두 다 감사드립니다. 모두 채택해드리고 싶은데... 아쉽네요..

 

참고로 위 구분은 11g에서는 잘되는데..

9i에서는 흐으으......

by 마농 [2016.05.31 10:32:33]

pivot 을 제외하면 모두 9i 에서 동작됩니다.

pivot 구문만 우리집아찌님께서 사용하신 구문처럼 Group By Min(Decode 로 변경하시면 됩니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입