데이터베이스 질문과 답변
안녕하세요 선배님들!
오라클 통계쿼리 개선 관련하여 조언 좀 부탁드립니다.
날짜/지역을 기준으로 이용자 수를 집계하는 게 목적인데요.
CASE 문을 이용해서 우편번호를 기준으로 지역을 조회하는데,
어떻게 하면 해당 쿼리를 좀 더 간결하게 개선할 수 있을까요?
우선은 기존 쿼리와, 제가 나름대로 개선했다고 생각하는 쿼리를 본문에 포함시키도록 하겠습니다.
선배님들의 관심과 조언 미리 감사드립니다 :)
/* 기존 쿼리 (1번) */
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, SIDO
ORDER BY CDATE, SIDO
;
/* 개선 쿼리 (2번) */
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 T2
GROUP BY
ACCESSION_DATE, SIDO
ORDER 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]
넵! 시스템 분석 중이라, 사용자 관련 테이블 확인을 좀 해보아야 할 것 같습니다 ^^
매번 최고의 피드백 너무 감사드립니다 선생님 :)
) 버튼을 클릭하여 작성 하시면 됩니다.