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

MySQL 년월 기준 통계 데이터 CASE 문 관련 질문입니다!

by 찡스쿄 [MySQL] mysql case 통계데이터 [2021.01.13 10:17:11]

안녕하세요, 선배님들.

현재 누군가가 만들어둔 시스템을 유지보수 차원에서 인수받은 상태입니다.

엑셀 다운로드 기능을 붙이고, 검색조건 기능이 제대로 작동하는지 확인하던 도중에

연도와 월을 기준으로 출하 현황을 조회하는 쿼리를 보았는데,

1~12월까지 똑같은 조건으로 여러개의 스칼라 서브쿼리로 처리가 되어있더라구요...

(아래 쿼리에서 4~12월까지는 스크롤 압박으로 쿼리 문을 제외했습니다.)

SELECT
             I.COS_ITEM_CD
            ,I.COS_CTRY_NM
            ,(
                SELECT IFNULL(SUM(OOL2.COS_STRD_QTY),0)
                FROM COS_OORR OO2
                INNER JOIN COS_OORR_LS OOL2
                ON OO2.COS_REL_NUM = OOL2.COS_REL_NUM
                INNER JOIN COS_OBT_ORD_LS OL2
                ON OOL2.COS_OOLS_SQ = OL2.COS_OOLS_SQ
                INNER JOIN COS_ITEM I2
                ON OL2.COS_ITEM_CD = I2.COS_ITEM_CD
                WHERE OO2.COS_PROD_FG = '출고완료'
                AND OL2.COS_ITEM_CD = OL.COS_ITEM_CD
                AND DATE_FORMAT(OO2.COS_STRD_DAY,'%Y-%c') = CONCAT('2020','-1')
                GROUP BY I2.COS_ITEM_CD
            ) AS JAN
            ,(
                SELECT IFNULL(SUM(OOL2.COS_STRD_QTY),0)
                FROM COS_OORR OO2
                INNER JOIN COS_OORR_LS OOL2
                ON OO2.COS_REL_NUM = OOL2.COS_REL_NUM
                INNER JOIN COS_OBT_ORD_LS OL2
                ON OOL2.COS_OOLS_SQ = OL2.COS_OOLS_SQ
                INNER JOIN COS_ITEM I2
                ON OL2.COS_ITEM_CD = I2.COS_ITEM_CD
                WHERE OO2.COS_PROD_FG = '출고완료'
                AND OL2.COS_ITEM_CD = OL.COS_ITEM_CD
                AND DATE_FORMAT(OO2.COS_STRD_DAY,'%Y-%c') = CONCAT('2020','-2')
                GROUP BY I2.COS_ITEM_CD
            ) AS FEB
            ,(
                SELECT IFNULL(SUM(OOL2.COS_STRD_QTY),0)
                FROM COS_OORR OO2
                INNER JOIN COS_OORR_LS OOL2
                ON OO2.COS_REL_NUM = OOL2.COS_REL_NUM
                INNER JOIN COS_OBT_ORD_LS OL2
                ON OOL2.COS_OOLS_SQ = OL2.COS_OOLS_SQ
                INNER JOIN COS_ITEM I2
                ON OL2.COS_ITEM_CD = I2.COS_ITEM_CD
                WHERE OO2.COS_PROD_FG = '출고완료'
                AND OL2.COS_ITEM_CD = OL.COS_ITEM_CD
                AND DATE_FORMAT(OO2.COS_STRD_DAY,'%Y-%c') = CONCAT('2020','-3')
                GROUP BY I2.COS_ITEM_CD
            ) AS MAR
            ,SUM(OOL.COS_STRD_QTY) AS COS_STRD_QTY
        FROM COS_OORR OO
        INNER JOIN COS_OORR_LS OOL ON OO.COS_REL_NUM = OOL.COS_REL_NUM
        INNER JOIN COS_OBT_ORD_LS OL ON OOL.COS_OOLS_SQ = OL.COS_OOLS_SQ
        INNER JOIN COS_ITEM I ON OL.COS_ITEM_CD = I.COS_ITEM_CD
        INNER JOIN COS_OBT_ORD O ON OO.COS_OBT_ORD_CD = O.COS_OBT_ORD_CD
        LEFT OUTER JOIN COS_ACCT A ON O.COS_ACCT_CD = A.COS_ACCT_CD
        WHERE
            OO.COS_PROD_FG = '출고완료'
        AND
            DATE_FORMAT(OO.COS_STRD_DAY,'%Y') = '2020'
        GROUP BY I.COS_ITEM_CD

앞의 쿼리는 성능이 떨어지는 쿼리라 생각해서 제가 변경한 쿼리는 다음과 같습니다.

SELECT
      I.COS_ITEM_CD AS '아이템 코드'
    , I.COS_CTRY_NM AS '아이템 이름'
    , DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') AS '출고 년월'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-01' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'JAN'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-02' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'FEB'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-03' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'MAR'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-04' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'APR'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-05' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'MAY'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-06' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'JUN'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-07' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'JUL'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-08' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'AUG'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-09' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'SEP'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-10' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'OCT'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-11' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'NOV'
    , (CASE DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m') WHEN '2020-12' THEN IFNULL(SUM(OOL.COS_STRD_QTY), 0) END) AS 'DEC'
    , IFNULL(SUM(OOL.COS_STRD_QTY), 0) AS '전체 합계'
FROM COS_OORR OO
INNER JOIN COS_OORR_LS OOL ON OO.COS_REL_NUM = OOL.COS_REL_NUM
INNER JOIN COS_OBT_ORD_LS OL ON OOL.COS_OOLS_SQ = OL.COS_OOLS_SQ
INNER JOIN COS_ITEM I ON OL.COS_ITEM_CD = I.COS_ITEM_CD
INNER JOIN COS_OBT_ORD O ON OO.COS_OBT_ORD_CD = O.COS_OBT_ORD_CD
LEFT OUTER JOIN COS_ACCT A ON O.COS_ACCT_CD = A.COS_ACCT_CD
WHERE
    OO.COS_PROD_FG = '출고완료'
AND
    DATE_FORMAT(OO.COS_STRD_DAY,'%Y') = '2020'
GROUP BY
      I.COS_ITEM_CD
    , I.COS_CTRY_NM
    , DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m')

여기서 궁금한 것이 있습니다.

개별 조건은 CASE 문으로 처리하는 게 효율적이라고 배웠는데,

변경된 쿼리에서 CASE의 DATE_FORMAT(OO.COS_STRD_DAY, '%Y-%m')과

THEN의 IFNULL(SUM(OOL.COS_STR_QTY), 0)은 모두 동일하고,

다른 조건으로는 WHEN의 연도-월만 다르게 SELECT 하는 상황입니다.

다음의 쿼리문을 축약해서 사용할 수 있는 방법이 있을까요?

답변 미리 감사드립니다. ^^

by 마농 [2021.01.13 10:58:52]

1. GROUP BY 및 CASE 사용 문제
- GROUP BY 에서는 월이 빠져야 하고
- CASE(SUM) 이 아닌 SUM(CASE) 형태가 되어야 합니다.
2. 조건절 컬럼 가공 비효율
- 컬럼을 가공하여 조건에 맞추지 말고
- 조건을 가공하여 컬럼에 맞추세요.
3. 표준 쿼리 사용
- 알리아스에는 홑따옴표가 아닌 쌍따옴표 사용하세요.

SELECT i.cos_item_cd AS "아이템 코드"
     , i.cos_ctry_nm AS "아이템 이름"
--???, DATE_FORMAT(oo.cos_strd_day, '%Y-%m') AS "출고 년월"
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '01' THEN ool.cos_strd_qty END), 0) JAN
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '02' THEN ool.cos_strd_qty END), 0) FEB
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '03' THEN ool.cos_strd_qty END), 0) MAR
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '04' THEN ool.cos_strd_qty END), 0) APR
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '05' THEN ool.cos_strd_qty END), 0) MAY
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '06' THEN ool.cos_strd_qty END), 0) JUN
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '07' THEN ool.cos_strd_qty END), 0) JUL
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '08' THEN ool.cos_strd_qty END), 0) AUG
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '09' THEN ool.cos_strd_qty END), 0) SEP
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '10' THEN ool.cos_strd_qty END), 0) OCT
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '11' THEN ool.cos_strd_qty END), 0) NOV
     , IFNULL(SUM(CASE DATE_FORMAT(oo.cos_strd_day, '%m') WHEN '12' THEN ool.cos_strd_qty END), 0) DEC
     , SUM(ool.cos_strd_qty) AS "전체 합계"
  FROM cos_oorr oo
 INNER JOIN cos_oorr_ls    ool ON oo.cos_rel_num    = ool.cos_rel_num
 INNER JOIN cos_obt_ord_ls ol  ON ool.cos_ools_sq   = ol.cos_ools_sq
 INNER JOIN cos_item       i   ON ol.cos_item_cd    = i.cos_item_cd
 INNER JOIN cos_obt_ord    o   ON oo.cos_obt_ord_cd = o.cos_obt_ord_cd
  LEFT OUTER JOIN cos_acct a   ON o.cos_acct_cd     = a.cos_acct_cd
 WHERE oo.cos_prod_fg = '출고완료'
   AND oo.cos_strd_day >= CONCAT('2020', '-01-01')
   AND oo.cos_strd_day <  DATE_ADD(CONCAT('2020', '-01-01'), INTERVAL 1 YEAR)
 GROUP BY i.cos_item_cd, i.cos_ctry_nm
;

by 찡스쿄 [2021.01.13 11:10:11]

마농 선생님,, 오늘도 커다란 것을 하나 배워가네요...

정말 감사드립니다!

행복한 한 주가 되시길 바랍니다. ^^~

감사합니다!

by 마농 [2021.01.13 11:29:43]

다시 보니 이상한 구문이 있네요.
"출고 년월" 부분이 표준에 어긋나는 구문이네요.
불필요한 항목이라면 삭제하시고
필요한 항목이라면? "년월"이 아닌 "년도"가 필요하거나
아니면 최종 년월이 필요하다면 MAX 를 사용하거나 해야 합니다.

by 찡스쿄 [2021.01.13 13:01:49]

마농 선생님, 답변 감사드립니다.

SELECT 절의 "출고 년월"은 예시를 보여드리기 위해 포함되어 있었던 것입니다!

신경써 주셔서 감사합니다. ^^~

댓글등록

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

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

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

데이터베이스 질문과 답변