개발일지/SQL

[SQL] 조건 카운팅 쿼리의 단축화

연습용365 2025. 1. 15. 00:05

 
1.조건 : 이번주 카운트만 출력
- 회사에서 쓰는 컬럼명을 노출시킬 수 없어 테스트 컬럼명으로 게시
- 여기서 작성한 COUMN6는 화면단에서 입력 받은 값이 들어감(여기서 들어간 조건의 이번주의 의미는 그저 입력 받은 값일 뿐임)

SELECT 
    COUNT(*)
FROM
    TEST_TABLE1 TT1,
    TEST_TABLE2 TT2,
    TEST_TABLE3 TT3
WHERE TT2.COLUMN1 = TT3.COLUMN1 
    AND TT1.COLUMN1 = TT3.COLUMN1
    AND TT3.COLUMN2 = '011'
    AND TT2.COLUMN3 LIKE '%G'
    AND TT1.COLUMN4 = 'Y'
    AND TT3.COLUMN5 <> 'DEL'
    AND TT3.COLUMN6 BETWEEN TO_DATE('20241230' || '00:00:00', 'YYYYMMDDHH24:MI:SS') 
                      AND TO_DATE('20250103' || '23:59:59', 'YYYYMMDDHH24:MI:SS');

 
 
2. 조건 : 3주전까지 일주일 주기로 출력
- UNION ALL 사용해서 이어줌 각 주를 연결
- 여기서부터 코드가 길어지기 때문에 간결화 준비를 한다.
- 마찬가지로 테스트 컬럼명과 테이블 사용

SELECT 
    '이번주' AS WEEK_PERIOD,
    COUNT(*) AS RESULT_COUNT
FROM
    TEST_TABLE1 TT1,
    TEST_TABLE2 TT2,
    TEST_TABLE3 TT3
WHERE 
    TT2.COLUMN1 = TT3.COLUMN1 
    AND TT1.COLUMN1 = TT3.COLUMN1
    AND TT3.COLUMN2 = '011'
    AND TT2.COLUMN3 LIKE '%G'
    AND TT1.COLUMN4 = 'Y'
    AND TT3.COLUMN5 <> 'DEL'
    AND TT3.COLUMN6 BETWEEN TO_DATE('20241230' || '00:00:00', 'YYYYMMDDHH24:MI:SS') 
                        AND TO_DATE('20250103' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
UNION ALL
    SELECT 
        '저번주' AS WEEK_PERIOD,
        COUNT(*) AS RESULT_COUNT
    FROM
        TEST_TABLE1 TT1,
        TEST_TABLE2 TT2,
        TEST_TABLE3 TT3
    WHERE 
        TT2.COLUMN1 = TT3.COLUMN1 
        AND TT1.COLUMN1 = TT3.COLUMN1
        AND TT3.COLUMN2 = '011'
        AND TT2.COLUMN3 LIKE '%G'
        AND TT1.COLUMN4 = 'Y'
        AND TT3.COLUMN5 <> 'DEL'
        AND TT3.COLUMN6 BETWEEN TO_DATE('20241223' || '00:00:00', 'YYYYMMDDHH24:MI:SS') 
                            AND TO_DATE('20241227' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
UNION ALL
    SELECT 
        '2주전' AS WEEK_PERIOD,
        COUNT(*) AS RESULT_COUNT
    FROM
        TEST_TABLE1 TT1,
        TEST_TABLE2 TT2,
        TEST_TABLE3 TT3
    WHERE 
        TT2.COLUMN1 = TT3.COLUMN1 
        AND TT1.COLUMN1 = TT3.COLUMN1
        AND TT3.COLUMN2 = '011'
        AND TT2.COLUMN3 LIKE '%G'
        AND TT1.COLUMN4 = 'Y'
        AND TT3.COLUMN5 <> 'DEL'
        AND TT3.COLUMN6 BETWEEN TO_DATE('20241216' || '00:00:00', 'YYYYMMDDHH24:MI:SS') 
                            AND TO_DATE('20241220' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
UNION ALL
    SELECT 
        '3주전' AS WEEK_PERIOD,
        COUNT(*) AS RESULT_COUNT
    FROM
        TEST_TABLE1 TT1,
        TEST_TABLE2 TT2,
        TEST_TABLE3 TT3
    WHERE 
        TT2.COLUMN1 = TT3.COLUMN1 
        AND TT1.COLUMN1 = TT3.COLUMN1
        AND TT3.COLUMN2 = '011'
        AND TT2.COLUMN3 LIKE '%G'
        AND TT1.COLUMN4 = 'Y'
        AND TT3.COLUMN5 <> 'DEL'
        AND TT3.COLUMN6 BETWEEN TO_DATE('20241209' || '00:00:00', 'YYYYMMDDHH24:MI:SS') 
                            AND TO_DATE('20241213' || '23:59:59', 'YYYYMMDDHH24:MI:SS');

 
 
3. 중복된 조건을 공통 테이블 표현식(CTE)로 분리하여 간결하게 표현해본다.
- 공통 쿼리를 합치기만하고, 입력값 부분은 줄지 않음
- 여기서 입력값을 하나만 사용하여 출력할 수 있게 변경해야 됨

WITH BASE_DATA AS (
    SELECT 
        TT3.COLUMN1 AS SERVICE_ID,
        TT3.COLUMN7 AS COMPANY_ID,
        TT1.COLUMN4 AS NUM_CHANGE_YN,
        TT2.COLUMN3 AS TASK_CD,
        TT3.COLUMN5 AS PROC_ST,
        TT3.COLUMN2 AS WORK_CD,
        TT3.COLUMN6 AS JUDGE_DT
    FROM 
        TEST_TABLE1 TT1
    INNER JOIN 
        TEST_TABLE2 TT2 ON TT2.COLUMN1 = TT1.COLUMN1
    INNER JOIN 
        TEST_TABLE3 TT3 ON TT1.COLUMN1 = TT3.COLUMN1
    WHERE 
        TT3.COLUMN2 = '011'
        AND TT2.COLUMN3 LIKE '%G'
        AND TT1.COLUMN4 = 'Y'
        AND TT3.COLUMN5 <> 'DEL'
),
DATE_RANGES AS (
    SELECT '12월09일~12월13일' AS WEEK_PERIOD, 
           TO_DATE('20241209' || '00:00:00', 'YYYYMMDDHH24:MI:SS') AS START_DATE, 
           TO_DATE('20241213' || '23:59:59', 'YYYYMMDDHH24:MI:SS') AS END_DATE
    FROM DUAL
    UNION ALL
    SELECT '12월16일~12월20일', 
           TO_DATE('20241216' || '00:00:00', 'YYYYMMDDHH24:MI:SS'), 
           TO_DATE('20241220' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
    FROM DUAL
    UNION ALL
    SELECT '12월23일~12월27일', 
           TO_DATE('20241223' || '00:00:00', 'YYYYMMDDHH24:MI:SS'), 
           TO_DATE('20241227' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
    FROM DUAL
    UNION ALL
    SELECT '12월30일~1월03일', 
           TO_DATE('20241230' || '00:00:00', 'YYYYMMDDHH24:MI:SS'), 
           TO_DATE('20250103' || '23:59:59', 'YYYYMMDDHH24:MI:SS')
    FROM DUAL
)
SELECT 
    DR.WEEK_PERIOD,
    COUNT(*) AS RESULT_COUNT
FROM 
    BASE_DATA BD
INNER JOIN 
    DATE_RANGES DR
ON 
    BD.JUDGE_DT BETWEEN DR.START_DATE AND DR.END_DATE
GROUP BY 
    DR.WEEK_PERIOD
ORDER BY 
    DR.WEEK_PERIOD;

 

위 쿼리를 조회하면 나오는 출력값
 
 
4. 아래와 같이 입력값을 하나만 받아 처리
- 위에서는 '12월30일~1월03일' 이런식으로 타이핑을 쳤다면 아래에서는 조회 결과인 날짜를 자동으로 들어갈 수 있게 자동화 시켜줌
- 중점적으로 봐야 하는 부분으로 쿼리단에서 출력시  한글 부분에서 에러가 날 수 있기 때문에 아래처럼 변경해주어야 한다.
- 'MM"월"DD"일"' 작은따옴표 안에 큰 따옴표를 넣어 한글 에러 방지

WITH BASE_DATA AS (
    SELECT 
        TT3.COLUMN1 AS SERVICE_ID,
        TT3.COLUMN7 AS COMPANY_ID,
        TT1.COLUMN4 AS NUM_CHANGE_YN,
        TT2.COLUMN3 AS TASK_CD,
        TT3.COLUMN5 AS PROC_ST,
        TT3.COLUMN2 AS WORK_CD,
        TT3.COLUMN6 AS JUDGE_DT
    FROM 
        TEST_TABLE1 TT1
    INNER JOIN 
        TEST_TABLE2 TT2 ON TT2.COLUMN1 = TT1.COLUMN1
    INNER JOIN 
        TEST_TABLE3 TT3 ON TT1.COLUMN1 = TT3.COLUMN1
    WHERE 
        TT3.COLUMN2 = '011'
        AND TT2.COLUMN3 LIKE '%G'
        AND TT1.COLUMN4 = 'Y'
        AND TT3.COLUMN5 <> 'DEL'
),
INPUT_DATES AS (
    SELECT 
        TO_DATE('20241230', 'YYYYMMDD') AS START_DATE,
        TO_DATE('20250103', 'YYYYMMDD') AS END_DATE
    FROM DUAL
),
DATE_RANGES AS (
    SELECT 
        LEVEL AS WEEK_NUM,
        TO_CHAR(START_DATE - (LEVEL - 1) * 7, 'MM"월"DD"일"') || '~' || 
        TO_CHAR(START_DATE - (LEVEL - 1) * 7 + 4, 'MM"월"DD"일"') AS WEEK_PERIOD,
        START_DATE - (LEVEL - 1) * 7 AS START_DATE,
        START_DATE - (LEVEL - 1) * 7 + 4 AS END_DATE
    FROM 
        INPUT_DATES
    CONNECT BY LEVEL <= 4
)
SELECT 
    DR.WEEK_PERIOD,
    COUNT(BD.SERVICE_ID) AS RESULT_COUNT
FROM 
    DATE_RANGES DR
LEFT JOIN 
    BASE_DATA BD
ON 
    BD.JUDGE_DT BETWEEN DR.START_DATE AND DR.END_DATE
GROUP BY 
    DR.WEEK_PERIOD
ORDER BY 
    DR.WEEK_PERIOD;

 
 
끝!!!!은 아니고 예전에 날짜 쿼리 관련해서 에러가 났기 때문에 신경써서 작성을 해야 함.
- 위에서는 그냥 7을 빼서 계산했다면 아래는 4주라는 기간 안에 지난 달이 있는 경우 정확도가 떨어 질 수 있기 때문에 아래처럼 TRUNC 함수를 이용해 날짜 정확도를 높여줌

WITH BASE_DATA AS (
    SELECT 
        TT3.COLUMN1 AS SERVICE_ID,
        TT3.COLUMN7 AS COMPANY_ID,
        TT1.COLUMN4 AS NUM_CHANGE_YN,
        TT2.COLUMN3 AS TASK_CD,
        TT3.COLUMN5 AS PROC_ST,
        TT3.COLUMN2 AS WORK_CD,
        TT3.COLUMN6 AS JUDGE_DT
    FROM 
        TEST_TABLE1 TT1
    INNER JOIN 
        TEST_TABLE2 TT2 ON TT2.COLUMN1 = TT1.COLUMN1
    INNER JOIN 
        TEST_TABLE3 TT3 ON TT1.COLUMN1 = TT3.COLUMN1
    WHERE 
        TT3.COLUMN2 = '011'
        AND TT2.COLUMN3 LIKE '%G'
        AND TT1.COLUMN4 = 'Y'
        AND TT3.COLUMN5 <> 'DEL'
),
INPUT_DATES AS (
    SELECT 
        TO_DATE('20241230', 'YYYYMMDD') AS START_DATE,
        TO_DATE('20250103', 'YYYYMMDD') AS END_DATE
    FROM DUAL
),
DATE_RANGES AS (
    SELECT 
        LEVEL AS WEEK_NUM,
        TO_CHAR(TRUNC(START_DATE, 'IW') - (7 * (LEVEL - 1)), 'MM"월"DD"일"') || '~' || 
        TO_CHAR(TRUNC(START_DATE, 'IW') - (7 * (LEVEL - 1)) + 4, 'MM"월"DD"일"') AS WEEK_PERIOD,
        TRUNC(START_DATE, 'IW') - (7 * (LEVEL - 1)) AS START_DATE,
        TRUNC(START_DATE, 'IW') - (7 * (LEVEL - 1)) + 4 AS END_DATE
    FROM 
        INPUT_DATES
    CONNECT BY LEVEL <= 4
)
SELECT 
    DR.WEEK_PERIOD,
    COUNT(BD.SERVICE_ID) AS RESULT_COUNT
FROM 
    DATE_RANGES DR
LEFT JOIN 
    BASE_DATA BD
ON 
    BD.JUDGE_DT BETWEEN DR.START_DATE AND DR.END_DATE
GROUP BY 
    DR.WEEK_PERIOD
ORDER BY 
    DR.WEEK_PERIOD;

 
 
화요일 밖에 안 됐는데 하루하루가 눈코뜰세 없이 몰아치듯 지나가서 고민하며 개발할 시간은 없고, 블로그엔 단축화 정도만 남겨야 할 듯하다. 어제 본의아니게 수면 조절을 못 해 월요일부터 체력관리 실패!!! 했고 오늘은 빨리 자야할 듯. 당분간은 일이 많아서 블로그에 글 쓸 여유는 없을 것 같아 평소 제일 싫어하고 약한 SQL으로 올려 본다. 그럼에도 불구하고 '강한 자가 살아 남는 게 아니라 살아 남은 자가 강한...' 에이씨 모르겠고 다 망하더라도 앞에 닥친 일부터 차근차근 해결해보련다. 얗허~~~!!!!!! 아자아자 화이팅~~~~!!~! ㅅㅂ~~~~