데이터 산출 쿼리에서 간과했던 부분들 (feat. 영업일, 누더기 쿼리의 시작)

최근 분석용DB 중 생산량 테이블 구축을 완료했고, 생산량 테이블을 활용하여 생산량 보고 자동화를 한 업무가 있었다. 생산량 보고를 위한 쿼리 작성 시 간과했던 부분(=영업일 개념을 반영하는 것)과 또 쿼리 수정이 필요할 때 고민이 되었던 현실(=빠른 수정 vs 더 나은 쿼리)을 기록해두고자 한다.

(*아래 내용은 실제 업무 내용을 각색 또는 간소화해서 작성)

 

1. 쿼리 작성 시 간과했던 영업일, 생산 가동일 조건

현재 회사는 제조회사이고, 생산량과는 '영업일, 생산가동일'이란 일자 개념이 연관 있다. 처음에는 영업일만 고려하여 케이스별 추출 조건을 작성하였으나, 추가적으로 중요한 '생산가동일' 개념을 놓쳤다는 것을 발견하였다. 처음에 고려했던 케이스 + 놓친 케이스, 선택할 수 있는 해결 방안과 예상 결과, 선택한 해결 방안 순으로 진행된다.

 

1.1 케이스별 추출 조건

1번 케이스. 평일(화~금). 전일 데이터 조회
2번 케이스. 평일(월). 금~일 합산 데이터 조회
3번 케이스(놓친 케이스). 휴일과 생산가동일이 다를 때

 

1번 케이스

ex) 조회 시점이 화요일이라면, 월요일 데이터를 조회한다.

 

2번 케이스

ex) 조회 시점이 월요일이라면, 금~일 데이터를 합산한다. 하지만 토, 일 데이터가 월요일 데이터로 변경되지는 않는다.(=원본 유지, 일일 보고 시에만 합산 적용)

 

3번 케이스(놓친 케이스)

ex) 조회시점이 10월 4일 금요일이라면 → 전일인 개천절(휴일)의 생산가동일 여부 판별 → 생산가동일이라면, 개천절(휴일), 10월 2일 별도로 조회 후 발송 / 생산가동일이 아니라면, 개천절(휴일) + 10월 2일 합산 발송

1.2 놓친 케이스를 해결하기 위한 방안과 예상 결과

  - 자동화팀이 3번대로 작동되도록 로직 설정 → 생산 가동일 리스트를 매월 제공해야 하는 공수가 듬

  - 쿼리에 휴일, 생산가동일 반영 → 생산가동일을 쿼리에 매번 반영해야 하는 공수가 듬. CTE구문으로 생산가동일 추가 시, 조회 시간이 증가됨.

 

1.3 선택한 해결 방안

쿼리는 최대한 가볍게 가져가기 위해, 1.2의 해결방안 중 자동화팀이 로직을 수정하는 것으로 진행하였다. 하지만 쿼리에 반영한다면, 이라고 가정하고 쿼리를 작성해두었을 때는 아래와 같이 변경하였다.

1번 케이스. 월요일이 last_business_day가 되어 조회됨
2번 케이스. 금요일이 last_business_day가 되어 조회됨
3번 케이스. 개천절 예시라면, 10월 3일이 last_business_day가 되어 조회됨

/* 기존 생산량 쿼리 */
SELECT SUM(CASE WHEN TO_DATE(생산일자) = TRUNC(SYSDATE - 1) THEN 생산량 ELSE 0 END) AS 일생산량
FROM 생산량 테이블

 

 

/* 휴일, 생산 가동일 반영 생산량 쿼리 */

WITH holidays AS (
    SELECT TO_CHAR(TO_DATE('2024-10-01', 'YYYY-MM-DD'), 'YY/MM/DD') AS holiday FROM dual -- 국군의날
    UNION ALL SELECT TO_CHAR(TO_DATE('2024-10-09', 'YYYY-MM-DD'), 'YY/MM/DD') FROM dual -- 한글날
),
-- 주말 및 공휴일을 제외한 영업일 계산
business_days AS (
    SELECT TRUNC(SYSDATE - LEVEL) AS business_day
    FROM dual
    WHERE TO_CHAR(TRUNC(SYSDATE - LEVEL), 'D') NOT IN ('1', '7')  -- 주말 제외 (일요일 = 1, 토요일 = 7)
    CONNECT BY LEVEL <= 7  -- 최근 7일 내에 영업일 탐색 (필요시 조정 가능)
    AND TRUNC(SYSDATE - LEVEL) NOT IN (SELECT holiday FROM holidays)  -- 공휴일 제외
),
-- 가장 최근 영업일 선택
last_business_day AS (
    SELECT MAX(business_day) AS last_business_day
    FROM business_days
)

SELECT SUM(CASE WHEN TO_DATE(생산일자) = (SELECT last_business_day FROM last_business_day) THEN 생산량 ELSE 0 END) AS 일생산량

 

 

2. 쿼리 수정이 필요할 때 고민이 되었던 '빠른 수정 vs 더 나은 쿼리'

소계, 중계, 총계를 뽑는 쿼리를 작성했다. 그러던 중, 요청사항이 변경되었다. 오히려 중계를 빼고 소계와 총계를 원했다. 이 작업 과정에서 느꼈던 현실, 쿼리 예시, 선택한 해결 방안, 결론 순으로 진행된다.

 

2.1 작업 과정에서 느꼈던 현실

항상 이런 요청을 받으면 (이번 경우는 그렇지 않았지만), 으레 '10분이면 되나요? 오전 시간이면 되나요?' 라는 질문을 받는다. 물론 엑셀로 따지고 보면, 행 하나 추가하고, SUM()을 넣고, 병합한 후 소계라고 칭하고, 기존의 중계 row는 삭제하면 된다. 그래서 요청을 하는 입장에서는 10분, 오전이라고 하는 것이 이해가 간다.

그런데 사실 그렇게 기한을 받으면 초조하다. 조건을 바꾸고 → 값을 확인하고 → 특이사항이 발생하지 않는지 재검토하고 → 이 쿼리를 받아서 돌릴 자동화팀에서 문제가 없는지도 테스트가 되어야 만족스럽게 요청을 수행했다고 안심이 되기 때문이다.

그런데, 현실은 그렇게 꼼꼼하게 할 수 있는 시간이 없을 때가 많다. 빠른 변경이 필요하고 / 요청자가 대기하고 있고 / 자동화팀이 연계되어 있기 때문에 자동화팀의 수정 시간도 고려해야 한다.

 

2.2 쿼리 예시

소계, 중계, 총계 쿼리는 아래와 같이 뽑고 있었다.

CASE문이 많은 이유
1. 거래처가 제대로 구분이 되어있지 않아, 거래처A와 거래처A1이 거래처A로 합쳐져야 하는 조건이 들어가야 한다.
2. ROLLUP 만 하면 소계, 중계, 총계 부분이 NULL로 표시되는 부분이 늘어나, NULL 부분이 소계~총계라는 이름이 들어갈 수 있도록 하고 싶었다.

 

SELECT
	CASE
		WHEN (CASE WHEN 거래처 IS NULL OR 거래처 IN ('A', 'A1') THEN '거래처A' ELSE 거래처 END) IS NULL THEN '총계'
		ELSE (CASE WHEN 거래처 IS NULL OR 거래처 IN ('A', 'A1') THEN '거래처A' ELSE 거래처 END)
	END AS 거래처,
	CASE 
		WHEN (CASE WHEN 거래처 IS NULL OR 거래처 IN ('A', 'A1') THEN '거래처A' ELSE 거래처 END) IS NOT NULL AND 품목 IS NULL) THEN '중계'
		ELSE 품목
	END 품목,
	CASE
		WHEN (CASE WHEN 거래처 IS NULL OR 거래처 IN ('A', 'A1') THEN '거래처A' ELSE 거래처 END) IS NOT NULL AND 품목 IS NULL AND 세부 IS NULL) THEN '소계'
		ELSE 세부
	END AS 세부,
	SUM(값)
FROM 테이블
WHERE 조건
GROUP BY ROLLUP(CASE WHEN 거래처 IS NULL OR 거래처 IN ('A', 'A1') THEN '거래처A' ELSE 거래처 END, 품목, 세부)

2.3 선택한 해결 방안

소계, 중계, 총계 요청사항에 대해서는 내가 선택할 수 있는 것은 두 가지이다.

1. ROLLUP과 CASE 문을 적절히 바꿔 중계를 제거한다.

2. WHERE문에서 중계만 출력되지 않도록 설정한다.

욕심으로는 1번을 하고 싶은데, 2번으로 하면 10분만에 가능하다. WHERE NVL(품목, '총계') != '중계'만 추가하면 되기 떄문이다. 결국 나의 선택은 2번으로 반영하고, 1번은 업무 리스트에 올려두고, 자동화팀에 쿼리 변경 시 수정 공유 사항을 전달하기로 소통했다.

 

3. 마치며 (1, 2에서 느낀 부분)

1. 와! 업무 하나 쳐냈다! 고 생각해서 운영하고 있어도, 그 당시에는 예상하지 못했던 조건이 터지기도 한다는 것을 깨달았다.

2. 왜 쿼리가 누더기가 되어가는지도 조금 느꼈다. 그리고 더 누더기가 되기 전에 깔끔하게 정리하고 싶은데, 어떻게 하면 쿼리 개선을 운용할 수 있을지 고민을 하게 되었다.

3. 소통

2번 업무를 하면서 연계된 팀은 '상위 요청 - A의 요청 - 나 - 자동화팀'이었다. 그런데 '쿼리 작업, 검토 작업 + 자동화팀 작업, 검토 작업 + 대응이 필요하면 해당 프로세스 1번 더 추가' 를 요청자가 고려하지 못하여 스케줄링이 삐그덕거렸다. 사실 이 과정도 한 번에 요청자가 이해하기란 쉽지 않을 것이다. 이 과정에서 서로의 업무를 잘 전달하고, 소통하는 방법이 지속되어야 겠구나 생각했다.