[Oracle SQL] 쿼리 합치기 연습2 (WITH절 사용)

 

[Oracle SQL] 쿼리 합치기 연습1 (JOIN, UNION ALL 사용)

 

 

지난 번 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 여러개)