꿈꾸는 개발자, DBA 커뮤니티 구루비

월별 합계 쿼리 좀 봐주세요!!

by 큐니백작 [SQL Query] 월별 합계 PIVOT 오라클 [2024.08.29 16:42:33]

WITH T AS
(
    SELECT 'AAAA' PROD_CODE, '20231001' PROD_DATE, 10   PROD_QTY FROM DUAL UNION ALL
    SELECT 'BBBB' PROD_CODE, '20240211' PROD_DATE, 5    PROD_QTY FROM DUAL UNION ALL
    SELECT 'CCCC' PROD_CODE, '20240121' PROD_DATE, 24   PROD_QTY FROM DUAL UNION ALL
    SELECT 'DDDD' PROD_CODE, '20231113' PROD_DATE, 11   PROD_QTY FROM DUAL UNION ALL
    SELECT 'BBBB' PROD_CODE, '20231115' PROD_DATE, 16   PROD_QTY FROM DUAL UNION ALL
    SELECT 'AAAA' PROD_CODE, '20230903' PROD_DATE, 10   PROD_QTY FROM DUAL UNION ALL
    SELECT 'BBBB' PROD_CODE, '20240311' PROD_DATE, 5    PROD_QTY FROM DUAL UNION ALL
    SELECT 'CCCC' PROD_CODE, '20240421' PROD_DATE, 24   PROD_QTY FROM DUAL UNION ALL
    SELECT 'DDDD' PROD_CODE, '20230813' PROD_DATE, 11   PROD_QTY FROM DUAL UNION ALL
    SELECT 'BBBB' PROD_CODE, '20230715' PROD_DATE, 16   PROD_QTY FROM DUAL UNION ALL
    SELECT 'EEEE' PROD_CODE, '20231231' PROD_DATE, 6    PROD_QTY FROM DUAL
)
select *  from
( select substr(prod_date, 5, 2) as mm,
         prod_code,
         prod_qty
    from t
   where prod_date between '20230701' and '20240430' --최대 12개월까지 조회가능
)
pivot ( sum(prod_qty) for mm in( '01' as M_1, '02' as M_2, '03' as M_3, '04' as M_4, '05' as M_5, '06' as M_6, '07' as M_7, '08' as M_8, '09' as M_9, '10' as M_10, '11' as M_11, '12' as M_12 )   ) ;

- 1년씩 조회 할 경우 위 구문처럼 Pivot을 사용하든지, Decode를 사용하든지 하면 될텐데 사용자가 월로 기간을 줘서 조회하고 싶어해서 문제가 발생했습니다.

- 문제는 2023/07 ~2024/04 이렇게 조회하면 월단위 순서에서 문제가 생겨 위구문은 소용이 없어서 이리저리 궁리를 해봤지만 잘 모르겠네요...

- 원하는 결과값은 아래 표와 같이 나오도록하는 겁니다

제품	2023/07	2023/08	2023/09	2023/10	2023/11	2023/12	2024/01	2024/02	2024/03	2024/04	2024/05	2024/06
AAAA			10	10
BBBB	16				16			5	5
CCCC							24			24
DDDD		11			11			11
EEEE						6

- 고수님들 부탁드립니다. 꾸뻑!!!

by 큐니백작 [2024.08.29 16:52:19]

SELECT PROD_CODE,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 0), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M1,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 1), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M2,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 2), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M3,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 3), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M4,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 4), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M5,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 5), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M6,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 6), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M7,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 7), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M8,
       SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 8), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M9,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 9), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M10,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 10), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M11,
             SUM(CASE WHEN TO_CHAR(ADD_MONTHS(TO_DATE(MIN_DATE, 'YYYYMMDD'), 11), 'YYYYMM') = SUBSTR(PROD_DATE, 1, 6) THEN
                                                        PROD_QTY ELSE 0 END ) M12
        FROM    T A, (SELECT MIN(PROD_DATE) MIN_DATE FROM T) B
        GROUP BY PROD_CODE

갑자기 생각이 나서 해봤는데..괜찮은 방법인지 의견 부탁드립니다.

by 마농 [2024.08.29 17:23:46]

입력 조회기간이 아닌 테이블 MIN(일자)로 비교하는 것은 잘못인 듯 합니다.

by 마농 [2024.08.29 17:06:04]

WITH t AS
(
SELECT 'AAAA' prod_code, '20231001' prod_date, 10 prod_qty FROM dual
UNION ALL SELECT 'BBBB', '20240211',  5 FROM dual
UNION ALL SELECT 'CCCC', '20240121', 24 FROM dual
UNION ALL SELECT 'DDDD', '20231113', 11 FROM dual
UNION ALL SELECT 'BBBB', '20231115', 16 FROM dual
UNION ALL SELECT 'AAAA', '20230903', 10 FROM dual
UNION ALL SELECT 'BBBB', '20240311',  5 FROM dual
UNION ALL SELECT 'CCCC', '20240421', 24 FROM dual
UNION ALL SELECT 'DDDD', '20230813', 11 FROM dual
UNION ALL SELECT 'BBBB', '20230715', 16 FROM dual
UNION ALL SELECT 'EEEE', '20231231',  6 FROM dual
)
SELECT *
  FROM (SELECT MONTHS_BETWEEN( TO_DATE(SUBSTR(prod_date, 1, 6), 'yyyymm')
                             , TO_DATE('20230701', 'yyyymmdd')
                             ) + 1 mm
             , prod_code
             , prod_qty
          FROM t
         WHERE prod_date BETWEEN '20230701' AND '20240430' -- 최대 12개월까지 조회가능
        )
 PIVOT (SUM(prod_qty) FOR mm IN ( 1 m01, 2 m02, 3 m03,  4 m04,  5 m05,  6 m06
                                , 7 m07, 8 m08, 9 m09, 10 m10, 11 m11, 12 m12
                                ) )
 ORDER BY prod_code
;

by 큐니백작 [2024.08.29 17:20:59]

역시 마농님....

마농님 감사합니다,

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변