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

 
※ 칼럼명과 테이블명은 테스트용으로 변경 ※
 
1. 합치기 전 분리된 쿼리를 각각 짠다.
- 각 조건들을 카운팅 하는 쿼리를 합치려고 함
 
- 첫번째 쿼리는 JOIN문을 써서 가독성을 높이고 최적화가 높아 요즘 권장하는 방식으로 코드를 짰고, 세번째는 기존에 사용하던 익숙한 방식으로 쿼리를 짬
 

-- 첫번째
SELECT 
    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 TO_CHAR(TS.TEST_JUDGE_DATE, 'YYYYMMDD') BETWEEN  '20241201' AND  '20241231';

-- 두번째
SELECT
    COUNT(*)
FROM
    TEST_NUMPLATE_LIST TM, 
    TEST_SERVICE TS
WHERE
    TM.TEST_SERVICE_ID = TS.TEST_SERVICE_ID    
    AND TO_CHAR(TM.TEST_USE_DATE, 'YYYYMMDD') BETWEEN  '20241201' AND  '20241231'                                                     
    AND TM.TEST_NUM_TYPE =  'F'
    AND TS.TEST_WORK_CODE =  '011';
    
-- 세번째
SELECT
    COUNT(*)
FROM
    TEST_NUMPLATE_LIST TM, 
    TEST_SERVICE TS
WHERE
    TM.TEST_SERVICE_ID = TS.TEST_SERVICE_ID    
    AND TO_CHAR(TM.TEST_USE_DATE, 'YYYYMMDD') BETWEEN  '20241201' AND  '20241231'                                                     
    AND TM.TEST_NUM_TYPE LIKE  '%G%'
    AND TS.TEST_WORK_CODE =  '011';

 
 
 
2. 같은 테이블과 비슷한 조건끼리 묶인 2-3번째의 쿼리를 합침
- 조인으로 묶어주고, 공통된 조건들은 WHERE 절에서 합쳐 줌
- 다른 조건인 경우 조회 부분인 SELECT문에 적용
 
· COUNT 함수에서 SUM 함수로 변경 :
- 조건을 기반으로 계산되는 경우 SUM을 사용하고, 단순히 카운팅 하는 경우만 COUNT로 사용하기 때문에 CASE WHEN과 같이 조건 기반의 함수 사용시 SUM을 사용하는 것이 적합하다.

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
    TO_CHAR(TM.TEST_USE_DATE, 'YYYYMMDD') BETWEEN '20241201' AND '20241231'
    AND TS.TEST_WORK_CODE = '011';

 
 
CASE문은 아래와 같이 사용하면 되는데 THEN 1을 한 후 ELSE 0와 같이 0을 더해주는 기본값은 생략 가능

/*
 *  CASE
 *      WHEN 조건1 THEN 반환 1 
 *      ELSE 기본값 
 *  END
*/

SUM(CASE WHEN TM.TEST_NUM_TYPE = 'F' THEN 1 END) AS FILM_CNT_F

 
 
 
3. 공동된 조건이 아닌 완전히 다른 조건의 쿼리를 합칠 때 JOIN으로 쿼리를 이어줌
 
· LEFT JOIN을 사용한 이유 :
- 왼쪽 테이블은 모든 데이터를 가져오고, 오른쪽 테이블에서 매칭되지 않는 데이터는 NULL로 반환
- 일반적인 INNER JOIN을 사용할 경우,  매칭되지 않는 데이터는 NULL 값을 갖고 오는 게 아닌 생략을 하기 때문에 여기서는 해당하지 않지만 습관화를 해두는 것이 좋다. NULL인 경우에도 계산식에 포함해야 하는 경우 계산에 문제를 일으킬 수 있으니 중요하다~
- 값이 없어 NULL을 반환하는 경우 0으로 만들어주기 위해 NVL을 사용

SELECT
    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
    (
        -- 첫 번째 쿼리: 전체 카운트 계산
        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 계산
        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;

 
 
4. 지난달도 아래쪽 칼럼에서 출력할 수 있도록 UNION ALL로 추가 
- 중복된 데이터는 삭제하면서 출력하려면 UNION, 중복된 행도 포함하려면 UNION ALL

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;

 
 
WHIT 함수를 이용해서 이 엄청난 쿼리를 이제 줄여야 하는데 그건 내일 마저 작성해야겠동~~~
 
내가 제일 시러하는 쿼리 짜기 하기 싫으니까 더 열심히 붙잡고 있는데 좀 재미가 붙은 듯? 푸하하 ㅋㅋ막이래~