← 개발일지

상세 쿼리를 리스트 집계 쿼리로 전환하는 Oracle SQL 패턴

oraclesqlmybatisaggregatesubquery

상세 쿼리를 리스트 집계 쿼리로 전환하는 Oracle SQL 패턴

사내 시스템을 개발하다 보면 이런 상황이 자주 생긴다.

상세 페이지는 이미 있다. 특정 회원의 예산 현황을 예산별로 보여주는 쿼리도 잘 돌아간다. 그런데 리스트 페이지에서 "회원별 사용가능 예산 합계"가 필요해졌다. 기존 쿼리를 어떻게 활용할 수 있을까?

이 글에서는 Oracle SQL과 MyBatis 환경에서 상세 쿼리를 리스트용 집계 쿼리로 변환하는 패턴을 정리한다.

문제 상황

예산 관리 시스템에서 회원별 잔여 예산 현황을 조회하는 상세 쿼리가 있다고 가정하자.

이 쿼리는 다음 구조로 동작한다.

  • BAS 서브쿼리: 예산 유형(이하 BDG_NO)별로 지급 / 회수 / 사용 금액을 집계해 잔여 금액 계산
  • 미승인 예산 서브쿼리: 결제 완료됐지만 아직 승인되지 않은 주문 금액 집계
  • 두 결과를 JOIN해 사용가능예산 = 잔여금액 - 미승인금액 계산

상세 페이지에서는 특정 회원을 WHERE MBR_NO = #{mbrNo}로 필터링하고, BDG_NO별로 행을 반환한다.

리스트 페이지에서 필요한 건 이와 다르다. 회원 목록을 보여주면서 각 회원의 사용가능 예산 합계 한 줄이 필요하다.

변환 전략

핵심은 단순하다.

상세 쿼리의 WHERE(단건 필터)를 제거하고, GROUP BY를 한 단계 더 감싼다.

구체적으로는 세 단계다.

1단계 — MBR_NO 필터 제거

-- 상세 쿼리의 이 조건을 제거
AND PNT_BAS.MBR_NO = #{mbrNo}

2단계 — GROUP BY에 MBR_NO 추가

기존 BAS 서브쿼리는 GROUP BY BDG_NO였다. 리스트에서는 회원 식별자도 함께 그룹화해야 한다.

-- 변경 전
GROUP BY BDG_NO

-- 변경 후
GROUP BY MBR_NO, BDG_NO

3단계 — 외부에서 한 번 더 SUM

BDG_NO별 잔여 금액을 회원 단위로 합산한다.

SELECT MBR_NO
     , SUM(TOT_RMN - NVL(NOT_APPR_AMT, 0)) AS USE_PSB_BUDGET
  FROM (
         -- BAS 서브쿼리 (MBR_NO + BDG_NO 기준)
       ) BAS
  LEFT JOIN (
              -- 미승인 예산 서브쿼리
            ) NOT_APPR
         ON NOT_APPR.MBR_NO = BAS.MBR_NO
        AND NOT_APPR.BDG_NO = BAS.BDG_NO
 GROUP BY MBR_NO

메인 쿼리에 연결

완성된 집계 서브쿼리를 회원 목록 쿼리에 LEFT JOIN으로 붙인다.

LEFT JOIN인 이유: 예산이 없는 회원도 목록에 표시해야 하기 때문이다. INNER JOIN이면 예산 데이터가 없는 회원이 목록에서 빠진다.

SELECT B.MBR_NO
     , B.MBR_NM
     , B.MBR_ID
     , D.DEPT_NM
     , NVL(USE_PSB.USE_PSB_BUDGET, 0) AS USE_PSB_BUDGET
  FROM 회원승인대상목록 A
 INNER JOIN 회원기본 B     ON A.MBR_NO = B.MBR_NO
 INNER JOIN 회원부서정보 C  ON B.MBR_NO = C.MBR_NO
 INNER JOIN 부서기본 D      ON C.DEPT_NO = D.DEPT_NO
  LEFT JOIN (
    -- 회원별 사용가능예산 집계 서브쿼리
    SELECT MBR_NO
         , SUM(TOT_RMN - NVL(NOT_APPR_AMT, 0)) AS USE_PSB_BUDGET
      FROM ...
     GROUP BY MBR_NO
  ) USE_PSB ON USE_PSB.MBR_NO = B.MBR_NO
 WHERE A.APPR_MBR_NO = #{apprMbrNo}

MyBatis에서 SQL 재사용 (<include>)

미승인 예산 쿼리가 <sql> 태그로 분리돼 있고, WITH CTE로 시작한다면 MyBatis <include>로 그대로 재사용할 수 있다.

LEFT JOIN (
  SELECT MBR_NO, BDG_NO, TOT_PRC
    FROM (
<include refid="패키지.매퍼.미승인예산SQL"/>
         )
) NOT_APPR ON ...

단, 여기서 Oracle 버전 이슈가 있다.

알아두면 좋은 함정 두 가지

1. Inline view 안에 WITH절 중첩 — Oracle 12c 이상만 허용

<include>로 가져온 SQL이 WITH 절(CTE)로 시작하면, 이를 inline view(FROM (...)) 안에 넣는 건 Oracle 12c 이상에서만 가능하다.

11g 이하 환경이라면 CTE를 inline view 형태로 직접 변환해야 한다.

-- 12c 이상: 이게 동작함
FROM (
  WITH CTE AS (SELECT ...) SELECT ...
)

-- 11g 이하: CTE를 인라인뷰로 변환
FROM (
  SELECT ... FROM (SELECT ...) CTE ...
)

이미 다른 쿼리에서 같은 방식으로 <include>를 쓰고 있다면, 해당 환경이 12c 이상이라는 방증이므로 걱정할 필요 없다.

2. DISTINCT가 있는 메인 쿼리 + 집계값 조합

메인 쿼리에 SELECT DISTINCT가 있을 때, 집계 서브쿼리를 LEFT JOIN으로 붙이면 대부분 문제없다. 집계 서브쿼리가 MBR_NO 기준 1행을 반환하기 때문이다.

문제는 JOIN 기준 테이블(승인 대상 목록 같은 것)에 같은 회원이 여러 행으로 들어있는 경우다. 이때는 USE_PSB_BUDGET 값이 달라 DISTINCT로 중복이 제거되지 않는다.

안전한 방법은 승인 대상 목록을 서브쿼리로 감싸 DISTINCT MBR_NO만 먼저 추출한 뒤 JOIN하는 것이다.

FROM (SELECT DISTINCT MBR_NO FROM 승인대상목록 WHERE APPR_MBR_NO = #{apprMbrNo}) A
INNER JOIN 회원기본 B ON A.MBR_NO = B.MBR_NO
LEFT JOIN (...) USE_PSB ON USE_PSB.MBR_NO = B.MBR_NO

정리

| 항목 | 상세 쿼리 | 리스트 집계 쿼리 | |---|---|---| | 필터 | WHERE MBR_NO = #{id} | 제거 | | GROUP BY | BDG_NO | MBR_NO, BDG_NO → 외부에서 MBR_NO로 재집계 | | 결과 형태 | 예산별 N행 | 회원별 1행 | | 메인 쿼리 연결 | 단독 실행 | LEFT JOIN (...) ON MBR_NO |

핵심은 "집계 단위를 올린다"는 것이다. 상세는 예산 단위, 리스트는 회원 단위. GROUP BY를 한 단계 더 감싸는 것으로 전환이 끝난다.

WITH절 중첩 이슈와 DISTINCT 충돌만 사전에 확인하면 구조 자체는 간단하다.