[PostgreSQL] 통계 일일 배치 실행시, 월별 누적에 당일(전일) 배치 실행 결과 더해서 처리하는 방법(DO UPDATE SET)

관리자

테이블이 월별(Monthly) 누적 테이블이고, 매일 새벽에 배치가 돌며 ‘하루 전날(어제)’ 발생한 통계
데이터만 기존 월 누적값에 더해주는(Upsert) 방식이 가장 안전하고 효율적입니다.

최적화한 전체 SQL 쿼리는 다음과 같습니다.

-- 상단 주석: p_stat_dt는 하루 전 날짜 : YYYYMMDD (예: 20261011)
INSERT INTO TB_MONTH_STAT (
    CONTRACT_NO, SERVICE_NO, STAT_DT, STAT_CNT, INSERT_DTM
)
SELECT 
    A.CONTRACT_NO, 
    A.SERVICE_NO, 
    SUBSTRING(A.STAT_DT, 1, 6) AS STAT_DT, -- YYYYMM 6자리 월로 저장
    COALESCE(SUM(A.STAT_CNT), 0) AS STAT_CNT, 
    TO_CHAR(NOW(), 'YYYYMMDDHH24MISS') AS INSERT_DTM
FROM TB_DAILY_STAT A
-- [변경 포인트 1] 매일 돌기 때문에 '어제(1일치)' 데이터만 타겟으로 조회하여 중복·부하 방지
WHERE A.STAT_DT = p_stat_dt 
GROUP BY 
    A.CONTRACT_NO, 
    A.SERVICE_NO, 
    SUBSTRING(A.STAT_DT, 1, 6)
-- 복합 유니크 키 충돌 시 (이미 해당 월 데이터가 존재할 때)
ON CONFLICT (CONTRACT_NO, SERVICE_NO, STAT_DT)
-- [변경 포인트 2] 기존 누적값에 어제 발생한 새로운 값을 더해줌 (+= 연산)
DO UPDATE SET 
    STAT_CNT = TB_MONTH_STAT.STAT_CNT + EXCLUDED.STAT_CNT,
    INSERT_DTM = EXCLUDED.INSERT_DTM; -- 최종 수정 시간 갱신

핵심 변경 및 개선 사항 설명

WHERE A.STAT_DT = p_stat_dt (성능 최적화)
- 기존: 매일 배치가 돌 때마다 당월 1일부터 어제까지의 데이터를 매번 전체 조회(Full Scan)하여 비효율적이었습니다. 월말로 갈수록 느려지는 원인이 됩니다.
- 변경: 배치가 매일 새벽에 돌기 때문에, 원천 테이블(unset TB_DAILY_STAT)에서
  어제 날짜(unset p_stat_dt) 딱 하루 치 데이터만 콕 집어서 가져옵니다. 조회 대상 데이터양이 획기적으로 줄어듭니다.
SET STAT_CNT = TB_MONTH_STAT.STAT_CNT + EXCLUDED.STAT_CNT (정합성 확보)
- 기존: 기존 값을 새로 계산된 값으로 덮어써 버렸기 때문에 월 누적이 되지 않았습니다.
- 변경: 유니크 키(계약, 고객, 장비, 해당 월 등)가 충돌했을 때, 기존 테이블에 이미 쌓여있던 값(unset TB_MONTH_STAT.STAT_CNT)에
  어제 새로 집계된 값(unset EXCLUDED.STAT_CNT)을 누적해서 더하도록 수정했습니다.