개발일지/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으로 올려 본다. 그럼에도 불구하고 '강한 자가 살아 남는 게 아니라 살아 남은 자가 강한...' 에이씨 모르겠고 다 망하더라도 앞에 닥친 일부터 차근차근 해결해보련다. 얗허~~~!!!!!! 아자아자 화이팅~~~~!!~! ㅅㅂ~~~~
