더 좋게 쿼리를 변경할 수 있을까요? 0 1 668

by 와니와플 [2021.02.06 11:10:16]


# 나름대로 짜 보았는데, 생각보다 깨끗하지가 않아서 문의 드려 봅니다.. 혹 제가 짠 것 보다 더 개선의 방법이 있을 지 문의 드립니다!

SELECT
            LEFT(TA_D, 4) AS YM,
            SUM(AMT),
            SUM(CNT)
        FROM GN_SEXAGE
WHERE
    TA_D >= '20190101' #CONCAT(START_YEAR, '0101')
    AND TA_D <= '20201231' #CONCAT(FINISH_YEAR, '1231')
    AND HD_CD like '4812961000%' #CONCAT(SGG,HD)
GROUP BY
    LEFT(TA_D, 4)
ORDER BY
    LEFT(TA_D, 4);
#분기별 선택시
  SELECT CASE WHEN RIGHT(TA_D,4) BETWEEN '0101' AND '0331' THEN CONCAT(LEFT(TA_D,4),'1Q')
              WHEN RIGHT(TA_D,4) BETWEEN '0401' AND '0631' THEN CONCAT(LEFT(TA_D,4),'2Q')
              WHEN RIGHT(TA_D,4) BETWEEN '0701' AND '0931' THEN CONCAT(LEFT(TA_D,4),'3Q')
              WHEN RIGHT(TA_D,4) BETWEEN '1001' AND '1231' THEN CONCAT(LEFT(TA_D,4),'4Q')
              ELSE 'NONE'
          END AS 'YYYYQQ'
       , SUM(AMT) AS AMT
       , SUM(CNT) AS CNT
    FROM GN_SEXAGE
   WHERE TA_D >= '20190101' #CONCAT(START_YEAR,'0101')
     AND TA_D <= '20201231' #CONCAT(FINISH_YEAR,'1231')
     AND HD_CD like '4812961000%' #CONCAT(SGG,HD)
GROUP BY CASE WHEN RIGHT(TA_D,4) BETWEEN '0101' AND '0331' THEN CONCAT(LEFT(TA_D,4),'1Q')
              WHEN RIGHT(TA_D,4) BETWEEN '0401' AND '0631' THEN CONCAT(LEFT(TA_D,4),'2Q')
              WHEN RIGHT(TA_D,4) BETWEEN '0701' AND '0931' THEN CONCAT(LEFT(TA_D,4),'3Q')
              WHEN RIGHT(TA_D,4) BETWEEN '1001' AND '1231' THEN CONCAT(LEFT(TA_D,4),'4Q')
              ELSE 'NONE'
          END
ORDER BY CASE WHEN RIGHT(TA_D,4) BETWEEN '0101' AND '0331' THEN CONCAT(LEFT(TA_D,4),'1Q')
              WHEN RIGHT(TA_D,4) BETWEEN '0401' AND '0631' THEN CONCAT(LEFT(TA_D,4),'2Q')
              WHEN RIGHT(TA_D,4) BETWEEN '0701' AND '0931' THEN CONCAT(LEFT(TA_D,4),'3Q')
              WHEN RIGHT(TA_D,4) BETWEEN '1001' AND '1231' THEN CONCAT(LEFT(TA_D,4),'4Q')
              ELSE 'NONE'
          END;
#월별 선택시
SELECT
            LEFT(TA_D, 6) AS YM,
            SUM(AMT),
            SUM(CNT)
        FROM GN_SEXAGE
WHERE
    TA_D >= '20190101' #CONCAT(START_YEAR,'0101')
    AND TA_D <= '20201231' #CONCAT(FINISH_YEAR,'1231')
    AND HD_CD like '4812961000%' #CONCAT(SGG,HD)
GROUP BY
    LEFT(TA_D, 6)
ORDER BY
    LEFT(TA_D, 6);

 

by 마농 [2021.02.06 12:59:28]
-- MySQL --
SELECT gb
     , SUM(amt) amt
     , SUM(cnt) cnt
  FROM (SELECT CASE 'q'    -- #조회구분
               WHEN 'y' THEN SUBSTR(ta_d, 1, 4)
               WHEN 'm' THEN SUBSTR(ta_d, 1, 6)
               WHEN 'q' THEN CONCAT( SUBSTR(ta_d, 1, 4)
                                   , CEIL(SUBSTR(ta_d, 5, 2) / 3)
                                   , 'Q'
                                   )
                END gb
             , amt
             , cnt
          FROM gn_sexage
         WHERE ta_d >= '20190101'       -- #CONCAT(START_YEAR , '0101')
           AND ta_d <= '20201231'       -- #CONCAT(FINISH_YEAR, '1231')
           AND hd_cd LIKE '4812961000%' -- #CONCAT(SGG, HD)
        ) a
 GROUP BY gb
;

 

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