[Oracle SQL] 쿼리 합치기 연습1 (JOIN, UNION ALL 사용)
※ 칼럼명과 테이블명은 테스트용으로 변경 ※ 1. 합치기 전 분리된 쿼리를 각각 짠다.- 각 조건들을 카운팅 하는 쿼리를 합치려고 함 - 첫번째 쿼리는 JOIN문을 써서 가독성을 높이고 최적화가 높
120ne.xyz
지난 번 UNION ALL로 합쳐 주었던 쿼리를 WITH절로 줄여주어야 한다. 중복된 문장을 단축시켜 가독성을 높일 수 있고 실행 시간도 최소화 가능
오케이 콜 시작~!
※ 회사에서 사용하는 칼럼명과 테이블명은 테스트명으로 변경함 ※
-- 아래는 줄이기 전 코드
SELECT
'2024-12' AS QUERY_MONTH,
TOTAL_COUNT - NVL(FD.FILM_CNT_F, 0) - NVL(FD.FILM_CNT_G, 0) AS PAINT_NUM,
NVL(FD.FILM_CNT_F, 0) AS FILM_NUM,
NVL(FD.FILM_CNT_G, 0) AS CORP_NUM
FROM
(
-- 첫 번째 쿼리: 전체 카운트 계산 (2024년 12월)
SELECT
COUNT(TN.TEST_SERVICE_ID) AS TOTAL_COUNT
FROM
TEST_TRNSNAME TN
INNER JOIN
TEST_SERVICE TS
ON
TN.TEST_SERVICE_ID = TS.TEST_SERVICE_ID
WHERE
TS.TEST_WORK_CODE = '011'
AND TS.TEST_PROC_STATUS <> 'DEL'
AND (
TN.TEST_NUM_CHANGE = 'Y'
OR TN.TEST_ETC5 = 'Y'
OR INSTR(TS.TEST_CAR_NO, '하') > 0
OR INSTR(TS.TEST_CAR_NO, '허') > 0
OR INSTR(TS.TEST_CAR_NO, '호') > 0
)
AND TS.TEST_JUDGE_DATE BETWEEN TO_DATE('20241201' || '00:00:00', 'YYYYMMDDHH24:MI:SS')
AND TO_DATE('20241231' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
) TOTAL
LEFT JOIN
(
-- 두 번째 쿼리: FILM_CNT_F와 FILM_CNT_G 계산 (2024년 12월)
SELECT
SUM(CASE WHEN TM.TEST_NUM_TYPE = 'F' THEN 1 ELSE 0 END) AS FILM_CNT_F,
SUM(CASE WHEN TM.TEST_NUM_TYPE LIKE '%G%' THEN 1 ELSE 0 END) AS FILM_CNT_G
FROM
TEST_NUMPLATE_LIST TM
JOIN
TEST_SERVICE TS
ON
TM.TEST_SERVICE_ID = TS.TEST_SERVICE_ID
WHERE
TM.TEST_USE_DATE BETWEEN TO_DATE('20241201' || '00:00:00', 'YYYYMMDDHH24:MI:SS')
AND TO_DATE('20241231' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
AND TS.TEST_WORK_CODE = '011'
) FD
ON 1=1
UNION ALL
SELECT
'2024-11' AS QUERY_MONTH,
TOTAL_COUNT - NVL(FD.FILM_CNT_F, 0) - NVL(FD.FILM_CNT_G, 0) AS PAINT_NUM,
NVL(FD.FILM_CNT_F, 0) AS FILM_NUM,
NVL(FD.FILM_CNT_G, 0) AS CORP_NUM
FROM
(
-- 첫 번째 쿼리: 전체 카운트 계산 (2024년 11월)
SELECT
COUNT(TN.TEST_SERVICE_ID) AS TOTAL_COUNT
FROM
TEST_TRNSNAME TN
INNER JOIN
TEST_SERVICE TS
ON
TN.TEST_SERVICE_ID = TS.TEST_SERVICE_ID
WHERE
TS.TEST_WORK_CODE = '011'
AND TS.TEST_PROC_STATUS <> 'DEL'
AND (
TN.TEST_NUM_CHANGE = 'Y'
OR TN.TEST_ETC5 = 'Y'
OR INSTR(TS.TEST_CAR_NO, '하') > 0
OR INSTR(TS.TEST_CAR_NO, '허') > 0
OR INSTR(TS.TEST_CAR_NO, '호') > 0
)
AND TS.TEST_JUDGE_DATE BETWEEN TO_DATE('20241101' || '00:00:00', 'YYYYMMDDHH24:MI:SS')
AND TO_DATE('20241130' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
) TOTAL
LEFT JOIN
(
-- 두 번째 쿼리: FILM_CNT_F와 FILM_CNT_G 계산 (2024년 11월)
SELECT
SUM(CASE WHEN TM.TEST_NUM_TYPE = 'F' THEN 1 ELSE 0 END) AS FILM_CNT_F,
SUM(CASE WHEN TM.TEST_NUM_TYPE LIKE '%G%' THEN 1 ELSE 0 END) AS FILM_CNT_G
FROM
TEST_NUMPLATE_LIST TM
JOIN
TEST_SERVICE TS
ON
TM.TEST_SERVICE_ID = TS.TEST_SERVICE_ID
WHERE
TM.TEST_USE_DATE BETWEEN TO_DATE('20241101' || '00:00:00', 'YYYYMMDDHH24:MI:SS')
AND TO_DATE('20241130' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
AND TS.TEST_WORK_CODE = '011'
) FD
ON 1=1;
WITH절은 반복되는 서브쿼리를 임시/가상 테이블을 통해 블록으로 만들어서 사용하는 개념이다. 아래와 같이 MONTHLY_DATA 라는 가상 테이블을 만들어 아래 SELECT문에서 조회 할 수 있다.
WITH MONTHLY_DATA AS (
SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'YYYYMMDD') AS PERIOD_DESC,
CASE
WHEN LEVEL = 1 THEN
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') || '월 (영업일:' || :THIS_WORK_DAY || '일)'
ELSE
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') || '월 (영업일:' || :PREV_WORK_DAY || '일)'
END AS DISPLAY_MONTH,
TRUNC(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') AS START_DATE,
LAST_DAY(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL)) AS END_DATE
FROM DUAL
CONNECT BY LEVEL <= 2
)
SELECT *
FROM MONTHLY_DATA;
- 함수 LEVEL 사용시 동적으로 ROW 값을 +1 증가시킴. CONNECT BY를 이용해 반복 횟수를 조절할 수 있다.
- TO_CHAR 대신 TO_DATE 함수를 사용하면 입력한 달이 01월인 경우 지난달을 자동으로 12월로 계산해 줌
- LAST_DAY를 통해 END_DATE를 입력받지 않고, START_DATE만 입력 받아도 해당 월의 마지막 날을 자동으로 갖고 올 수 있다.
- 따라서 해당 쿼리는 시작 월만 입력받아 이번달 말일과 지난달, 지난달 말일을 출력 할 수 있는 쿼리이다. 영업일의 경우 대체 공휴일 등이 있기 때문에 수기로 입력 받아야만 함
WITH문은 아래처럼 콤마(,)를 기준으로 테이블을 선언하고, 아래 SELECT문에서 JOIN으로 이어준 뒤 출력하면 끝~!!!
참 쉽죠?
WITH MONTHLY_DATA AS (
SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'YYYYMMDD') AS PERIOD_DESC,
CASE
WHEN LEVEL = 1 THEN
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') || '월 (영업일:' || :THIS_WORK_DAY || '일)'
ELSE
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') || '월 (영업일:' || :PREV_WORK_DAY || '일)'
END AS DISPLAY_MONTH,
TRUNC(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') AS START_DATE,
LAST_DAY(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL)) AS END_DATE
FROM DUAL
CONNECT BY LEVEL <= 2
),
INPUT_DATES AS (
SELECT
:START_DATE AS START_DATE,
:END_DATE AS END_DATE
FROM DUAL
)
SELECT *
FROM MONTHLY_DATA
INNER JOIN INPUT_DATES
ON 1=1;
위와 같이 공통적으로 쓰여지는 조건들을 모아 가상 테이블로 만든 뒤 테이블명을 지정하고, 가장 하단의 SELECT문에서 출력해서 사용하면 끝!!
- 저번 게시글에서 썼던 것처럼 계산이 필요한 경우 JOIN시 INNER JOIN이 아닌 LEFT JOIN을 통해 조회 값이 NULL이더라도 계산식에 포함하도록 연결해주어야 함.
WITH MONTHLY_DATA AS (
SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'YYYYMM') AS PERIOD_DESC,
CASE
WHEN LEVEL = 1 THEN
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') || '월 (영업일:' || :THIS_WORK_DAY || '일)'
ELSE
TO_CHAR(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') || '월 (영업일:' || :PREV_WORK_DAY || '일)'
END AS DISPLAY_MONTH,
TRUNC(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL), 'MM') AS START_DATE,
LAST_DAY(ADD_MONTHS(TO_DATE(:START_DATE, 'YYYYMMDD'), 1 - LEVEL)) AS END_DATE
FROM DUAL
CONNECT BY LEVEL <= 2
),
TOTAL_DATA AS (
SELECT
COUNT(T1.COL1) AS TOTAL_COUNT,
TO_CHAR(T2.COL2, 'YYYYMM') AS PERIOD_DESC
FROM
TEST_TABLE1 T1
LEFT JOIN TEST_TABLE2 T2
ON T1.COL1 = T2.COL1
WHERE
T2.COL3 = '011'
AND T2.COL4 <> 'DEL'
AND (
T1.COL5 = 'Y'
OR T1.COL6 = 'Y'
OR INSTR(T2.COL7, '하') > 0
OR INSTR(T2.COL7, '허') > 0
OR INSTR(T2.COL7, '호') > 0
)
GROUP BY TO_CHAR(T2.COL2, 'YYYYMM')
),
FILM_DATA AS (
SELECT
SUM(CASE WHEN T3.COL8 = 'F' THEN 1 ELSE 0 END) AS FILM_CNT_F,
SUM(CASE WHEN T3.COL8 LIKE '%G%' THEN 1 ELSE 0 END) AS FILM_CNT_G,
TO_CHAR(T3.COL9, 'YYYYMM') AS PERIOD_DESC
FROM
TEST_TABLE3 T3
LEFT JOIN TEST_TABLE2 T2
ON T3.COL1 = T2.COL1
WHERE T2.COL3 = '011'
GROUP BY TO_CHAR(T3.COL9, 'YYYYMM')
)
SELECT
MD.DISPLAY_MONTH AS PERIOD_DESC,
NVL(TOTAL.TOTAL_COUNT, 0) - NVL(FD.FILM_CNT_F, 0) - NVL(FD.FILM_CNT_G, 0) AS PAINT_NUM,
NVL(FD.FILM_CNT_F, 0) AS FILM_NUM,
NVL(FD.FILM_CNT_G, 0) AS CORP_NUM
FROM MONTHLY_DATA MD
LEFT JOIN TOTAL_DATA TOTAL
ON MD.PERIOD_DESC = TOTAL.PERIOD_DESC
LEFT JOIN FILM_DATA FD
ON MD.PERIOD_DESC = FD.PERIOD_DESC;
중복된 값들을 효과적으로 줄일 수 있으니 WITH 함수는 앞으로 쓸 일이 많을 것 같다.
구웃^__^b
WITH절 참고 :
[Oracle] 오라클 WITH 절 사용법 및 동작방식 (WITH 여러개)
오라클 9i R2부터 WITH 절을 사용할 수 있도록 기능이 추가되었다. WITH 절은 임시 테이블 또는 가상 테이블이라고 생각하면 된다. 반복되는 서브쿼리 블록을 하나의 WITH 절 블록으로 만들어서 사용
gent.tistory.com
'개발일지 > SQL' 카테고리의 다른 글
[Oracle SQL] 프로시저 인수에 default값 설정시 생략 가능 (0) | 2025.03.04 |
---|---|
[Oracle SQL] 쿼리 합치기 연습1 (JOIN, UNION ALL 사용) (0) | 2025.01.18 |
[SQL] 조건 카운팅 쿼리의 단축화 (0) | 2025.01.15 |
[ SQL 에러 ] 테이블에 파일 import 시 한글 깨짐 현상 (1) | 2025.01.02 |
[Oracle SQL] 31일 에러가 있다면 믿어지십니까? (0) | 2024.12.31 |