데이터베이스 질문과 답변

Oracle SQL 월별 통계를 피벗 형태로 출력 0 6 3,315

by 옆집누렁이 [Oracle 기초] 통계 피벗 [2023.05.05 00:02:40]

?
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
WITH T1 AS
(
SELECT 'cmpy1' AS CMPY_NO, '20230101' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230201' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230211' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230321' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230321' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220101' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220201' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220211' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220321' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220321' AS STD_DT, 1000 AS ORD_PRC FROM dual
)
SELECT *
  FROM ( SELECT CMPY_NO
             , TO_CHAR(TO_DATE(STD_DT), 'FMMM')AS STS_DT
             , SUM(ORD_PRC) AS TOTAL_PRC
          FROM T1
         WHERE CMPY_NO = 'cmpy1'
           AND TO_CHAR(TO_DATE(STD_DT), 'YYYY') = 2023
         GROUP BY CMPY_NO, TO_CHAR(TO_DATE(STD_DT), 'FMMM')
        )
  PIVOT (
         SUM(TOTAL_PRC)
         FOR STS_DT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
       );

안녕하세요~ 제가 통계 쿼리를 만들고 있는데 피벗형태로 출력을 하고 싶어서 검색하면서 한번 해 보았는데 아직 부족한 부분이 있어 질문드립니다!

고객사별 누적 매출금액과, 월별 매출금액을 뽑고 싶은데 이렇게 하는게 맞을까요? 결과값이 나오긴하는데 뭔가 부족한 부분이 있는거 같습니다(현재 해당하는 월별 매출금액의 누적금액을 못 뽑겠습니다)

결과값은 이렇게 출력하고 싶습니다

| 고객사 | 누적 매출 금액(1~12월 총) | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |

|cmpy1|7000|1000|2000|4000|0|0|0|0|0|0|0|0|0|

월별 금액이 없는것은 null로 나와도 됩니다!

by 마농 [2023.05.05 09:43:09]

1. 날짜 변환
- TO_DATE(std_dt) 는 수행 환경에 따라 오류발생 가능성이 높은 잘못된 사용법입니다.
- TO_DATE(std_dt, 'yyyymmdd') 와 같이 정확한 포멧을 지정하여 사용해야 합니다.
2. 컬럼을 가공하는 조건은 좋지 않습니다. 컬럼은 그대로 두고 조건 값을 가공하세요.
- 변경전 : TO_CHAR(TO_DATE(std_dt), 'YYYY') = 2023
- 변경후 : std_dt LIKE '2023' || '%'

?
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
-- 1. ROLLUP 을 이용하는 방법
SELECT *
  FROM (SELECT cmpy_no
             , NVL(TO_NUMBER(SUBSTR(std_dt, 5, 2)), 0) mm
             , SUM(ord_prc) prc
          FROM t1
         WHERE cmpy_no = 'cmpy1'
           AND std_dt LIKE '2023' || '%'
         GROUP BY cmpy_no, ROLLUP(SUBSTR(std_dt, 5, 2))
        )
 PIVOT (SUM(prc) FOR mm IN (0 tot, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
;
-- 2. SUM() OVER() 분석함수를 이용하는 방법
SELECT *
  FROM (SELECT cmpy_no
             , TO_NUMBER(SUBSTR(std_dt, 5, 2)) mm
             , SUM(SUM(ord_prc)) OVER() tot
             , SUM(ord_prc) prc
          FROM t1
         WHERE cmpy_no = 'cmpy1'
           AND std_dt LIKE '2023' || '%'
         GROUP BY cmpy_no, SUBSTR(std_dt, 5, 2)
        )
 PIVOT (SUM(prc) FOR mm IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
;

 

by 옆집누렁이 [2023.05.09 10:21:12]

감사합니다!!! 덕분에 SQL에 대해 많이 배워가고 통계도 잘 진행되고있습니다. 다름이 아니라 혹시 PIVOT 사용할 때 누적금액에 토탈금액도 뽑을 수 있을까요?

by 마농 [2023.05.09 10:26:20]

원하시는 결과표를 그려주세요.

by 옆집누렁이 [2023.05.09 17:51:15]

결과는 이렇게 뽑고 싶습니다. 토탈 누적 금액을 앞단에서 뽑아쓰고 싶어서 전체 행에 들어가도 됩니다.

 

 

|cmpy_no|tot   |1    |2   |3   |4   |5   |6   |7   |8   |9   |10   |11   |12   |토탈 누적금액|
|cmpy1  |1000 |1000|    |     |     |     |     |    |    |     |      |      |       |3000    |
|cmpy2  |2000 |1000|    |1000|    |     |     |    |    |     |      |      |       |3000    |

by 마농 [2023.05.09 18:21:41]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH t1 AS
(
SELECT 'cmpy1' cmpy_no, '20230101' std_dt, 1000 ord_prc FROM dual
UNION ALL SELECT 'cmpy2', '20230101', 1000 FROM dual
UNION ALL SELECT 'cmpy2', '20230201', 1000 FROM dual
)
SELECT *
  FROM (SELECT cmpy_no
             , TO_NUMBER(SUBSTR(std_dt, 5, 2)) mm
             , SUM(SUM(ord_prc)) OVER() tot_all
             , SUM(SUM(ord_prc)) OVER(PARTITION BY cmpy_no) tot_sub
             , SUM(ord_prc) prc
          FROM t1
         WHERE std_dt LIKE '2023' || '%'
         GROUP BY cmpy_no, SUBSTR(std_dt, 5, 2)
        )
 PIVOT (SUM(prc) FOR mm IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
 ORDER BY cmpy_no
;

 

by 옆집누렁이 [2023.05.09 22:50:36]

원하는 결과입니다!! 마농님 항상 감사합니다^^

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