데이터베이스 질문과 답변
안녕하세요 선배님들!
오라클 통계쿼리 개선 관련하여 조언 좀 부탁드립니다.
날짜/지역을 기준으로 이용자 수를 집계하는 게 목적인데요.
CASE 문을 이용해서 우편번호를 기준으로 지역을 조회하는데,
어떻게 하면 해당 쿼리를 좀 더 간결하게 개선할 수 있을까요?
우선은 기존 쿼리와, 제가 나름대로 개선했다고 생각하는 쿼리를 본문에 포함시키도록 하겠습니다.
선배님들의 관심과 조언 미리 감사드립니다 :)
/* 기존 쿼리 (1번) */
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT CDATE, SIDO, SUM(CNT)FROM ( SELECT TO_CHAR(U.ACCESSION_DATE, 'YYYYMMDD') CDATE, (case when instr(zip_code, '-') > 1 THEN ( CASE WHEN substr(zip_code, 1, 1) = '1' THEN '서울' WHEN substr(zip_code, 1, 1) = '2' THEN '강원' WHEN substr(zip_code, 1, 2) = '30' THEN '대전' WHEN substr(zip_code, 1, 2) IN ('31', '32', '33', '34', '35') THEN '충남' WHEN substr(zip_code, 1, 2) IN ('36', '37', '38', '39') THEN '충북' WHEN substr(zip_code, 1, 2) = '40' THEN '인천' WHEN substr(zip_code, 1, 1) = '4' THEN '경기' WHEN substr(zip_code, 1, 2) IN ('51', '52', '53', '54', '55') THEN '전남' WHEN substr(zip_code, 1, 2) IN ('56', '57', '58', '59') THEN '전북' WHEN substr(zip_code, 1, 1) = '5' THEN '광주' WHEN substr(zip_code, 1, 2) IN ('60', '61') THEN '부산' WHEN substr(zip_code, 1, 2) IN ('68') THEN '울산' WHEN substr(zip_code, 1, 2) IN ('69') THEN '제주' WHEN substr(zip_code, 1, 1) = '6' THEN '경남' WHEN substr(zip_code, 1, 2) IN ('70') THEN '대구' WHEN substr(zip_code, 1, 3) IN ('711') THEN '대구' WHEN substr(zip_code, 1, 1) = '7' THEN '경북' ELSE '기타' END)ELSE CASE WHEN substr(zip_code, 1, 1) = '0' THEN '서울' WHEN substr(zip_code, 1, 2) BETWEEN '10' AND '20' THEN '경기' WHEN substr(zip_code, 1, 2) BETWEEN '21' AND '23' THEN '인천' WHEN substr(zip_code, 1, 2) BETWEEN '24' AND '26' THEN '강원' WHEN substr(zip_code, 1, 2) BETWEEN '27' AND '29' THEN '충북' WHEN substr(zip_code, 1, 2) = '30' THEN '세종' WHEN substr(zip_code, 1, 2) BETWEEN '31' AND '33' THEN '충남' WHEN substr(zip_code, 1, 2) BETWEEN '34' AND '35' THEN '대전' WHEN substr(zip_code, 1, 2) BETWEEN '36' AND '40' THEN '경북' WHEN substr(zip_code, 1, 2) BETWEEN '41' AND '43' THEN '대구' WHEN substr(zip_code, 1, 2) BETWEEN '44' AND '45' THEN '울산' WHEN substr(zip_code, 1, 2) BETWEEN '46' AND '49' THEN '부산' WHEN substr(zip_code, 1, 2) BETWEEN '50' AND '53' THEN '경남' WHEN substr(zip_code, 1, 2) BETWEEN '54' AND '56' THEN '전북' WHEN substr(zip_code, 1, 2) BETWEEN '57' AND '60' THEN '전남' WHEN substr(zip_code, 1, 2) BETWEEN '61' AND '62' THEN '광주' WHEN substr(zip_code, 1, 2) = '63' THEN '제주' ELSE '기타' end end) sido, COUNT(U.USER_ID) as CNT FROM USER_TBL U, USER_ADD_TBL A WHERE (U.ACCESSION_DATE >= TO_DATE('20220902', 'YYYYMMDD') AND U.ACCESSION_DATE < TO_DATE('20221123', 'YYYYMMDD')) AND U.USER_CLASS = '5' AND U.ACCESS_LOC IN ('11', '21') AND U.USER_ID IS NOT NULL AND U.REC_KEY = A.REC_KEY-- AND A.DSAB_DIV <> '1' GROUP BY TO_CHAR(U.ACCESSION_DATE, 'YYYYMMDD'), U.ZIP_CODE )GROUP BY CDATE, SIDOORDER BY CDATE, SIDO |
;
/* 개선 쿼리 (2번) */
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 | SELECT ACCESSION_DATE , SIDO , SUM(CNT) /* ABCDE4 */FROM ( SELECT ACCESSION_DATE , (case when instr(zip_code, '-') > 1 THEN ( CASE WHEN substr(zip_code, 1, 1) = '1' THEN '서울' WHEN substr(zip_code, 1, 1) = '2' THEN '강원' WHEN substr(zip_code, 1, 2) = '30' THEN '대전' WHEN substr(zip_code, 1, 2) IN ('31', '32', '33', '34', '35') THEN '충남' WHEN substr(zip_code, 1, 2) IN ('36', '37', '38', '39') THEN '충북' WHEN substr(zip_code, 1, 2) = '40' THEN '인천' WHEN substr(zip_code, 1, 1) = '4' THEN '경기' WHEN substr(zip_code, 1, 2) IN ('51', '52', '53', '54', '55') THEN '전남' WHEN substr(zip_code, 1, 2) IN ('56', '57', '58', '59') THEN '전북' WHEN substr(zip_code, 1, 1) = '5' THEN '광주' WHEN substr(zip_code, 1, 2) IN ('60', '61') THEN '부산' WHEN substr(zip_code, 1, 2) IN ('68') THEN '울산' WHEN substr(zip_code, 1, 2) IN ('69') THEN '제주' WHEN substr(zip_code, 1, 1) = '6' THEN '경남' WHEN substr(zip_code, 1, 2) IN ('70') THEN '대구' WHEN substr(zip_code, 1, 3) IN ('711') THEN '대구' WHEN substr(zip_code, 1, 1) = '7' THEN '경북' ELSE '기타' END)ELSE CASE WHEN substr(zip_code, 1, 1) = '0' THEN '서울' WHEN substr(zip_code, 1, 2) BETWEEN '10' AND '20' THEN '경기' WHEN substr(zip_code, 1, 2) BETWEEN '21' AND '23' THEN '인천' WHEN substr(zip_code, 1, 2) BETWEEN '24' AND '26' THEN '강원' WHEN substr(zip_code, 1, 2) BETWEEN '27' AND '29' THEN '충북' WHEN substr(zip_code, 1, 2) = '30' THEN '세종' WHEN substr(zip_code, 1, 2) BETWEEN '31' AND '33' THEN '충남' WHEN substr(zip_code, 1, 2) BETWEEN '34' AND '35' THEN '대전' WHEN substr(zip_code, 1, 2) BETWEEN '36' AND '40' THEN '경북' WHEN substr(zip_code, 1, 2) BETWEEN '41' AND '43' THEN '대구' WHEN substr(zip_code, 1, 2) BETWEEN '44' AND '45' THEN '울산' WHEN substr(zip_code, 1, 2) BETWEEN '46' AND '49' THEN '부산' WHEN substr(zip_code, 1, 2) BETWEEN '50' AND '53' THEN '경남' WHEN substr(zip_code, 1, 2) BETWEEN '54' AND '56' THEN '전북' WHEN substr(zip_code, 1, 2) BETWEEN '57' AND '60' THEN '전남' WHEN substr(zip_code, 1, 2) BETWEEN '61' AND '62' THEN '광주' WHEN substr(zip_code, 1, 2) = '63' THEN '제주' ELSE '기타' end end) AS SIDO , COUNT(*) AS CNT FROM ( SELECT TO_CHAR(TO_DATE(SUBSTRING(U.ACCESSION_DATE, 1, 10)), 'YYYYMMDD') AS ACCESSION_DATE , ZIP_CODE FROM USER_TBL AS U INNER JOIN USER_ADD_TBL AS UA ON U.REC_KEY = UA.REC_KEY WHERE (U.ACCESSION_DATE >= '20220902' AND U.ACCESSION_DATE < '20221123') AND U.USER_CLASS = '5' AND U.ACCESS_LOC IN (11, 21) AND U.USER_ID IS NOT NULL ) AS T1 GROUP BY ACCESSION_DATE, ZIP_CODE ) AS T2GROUP BY ACCESSION_DATE, SIDOORDER BY ACCESSION_DATE, SIDO |
by 마농
[2022.11.23 13:38:04]
USER_ADD_TBL 테이블은 쓰임새가 없어 보입니다. 빼도 될 듯 하네요.
INSTR(-) 을 이용해 구분한 이유가 뭔가요? - 의 유무에 따라 다른체계인가요? - 가 있으면 과거 6자리? 없으면 현재 5자리?
우편번호 구간별로 나누어 지역테이릅를 관리하면 case 문을 조인으로 대체 가능합니다.
by 도뎡이
[2022.11.23 13:45:33]
안녕하세요, 선생님!
INSTR은 기존에 작성되어 있던 쿼리에 포함된 함수인데, 말씀하신 게 맞는 듯합니다 ^^..
선생님께서 해주신 말씀에서 포인트는 우편번호와 지역을 컬럼으로 갖는 별도의 테이블로 분리하라는 말씀이신 걸까요?
by 마농
[2022.11.23 13:52:06]
테이블 분리가 아니라 별도의 테이블 생성이죠. 코드테이블 생성
테이블 구조는 (구분(신/구), 시작번호, 종료번호, 지역) 형태
아니면 아예 전체 우편번호 테이블을 관리하고 거기에 지역 항목을 관리해도 되구요.
by 도뎡이
[2022.11.23 14:08:42]
피드백 감사드립니다 선생님!
테이블을 별도로 생성할 생각은 전혀 하지 못했었네요 ^^..
오늘도 도움 주셔서 감사드립니다 :)
좋은 하루 보내세요 ^^
by 마농
[2022.11.23 14:19:06]
우편번호 테이블이 이미 있다면 "시도" 항목이 "지역" 이 되겠네요.
by 도뎡이
[2022.11.23 15:19:48]
넵! 시스템 분석 중이라, 사용자 관련 테이블 확인을 좀 해보아야 할 것 같습니다 ^^
매번 최고의 피드백 너무 감사드립니다 선생님 :)
) 버튼을 클릭하여 작성 하시면 됩니다.