MAX와 KEEP

사건의 발단

• 특정 컬럼 값이 동일한 항목에 대해서 다른 컬럼을 기준으로 가장 최종 항목을 조회하는 기능

이 기능을 구현을 끝내고 테스트를 하던 과정에서 기능이 비정상적인 동작을 한다는 얘기가 들려온다.

소스를 확인해보니

SELECT A
     , MAX(B)
     , MAX(C)
     , MAX(D)
  FROM TABLE
 WHERE A = @@@
 GROUP BY A​

...할 말을 잃었다 정녕 제정신으로 작성한 쿼리인가

정신차리고 수정 한 결과

SELECT A
     , MAX(B) KEEP(DENSE_RANK LAST ORDER BY 정렬기준컬럼)
     , MAX(C) KEEP(DENSE_RANK LAST ORDER BY 정렬기준컬럼)
     , MAX(D) KEEP(DENSE_RANK LAST ORDER BY 정렬기준컬럼)
  FROM TABLE
 WHERE A = @@@
 GROUP BY A

이번 프로젝트에서 MAX와 KEEP에 대해서 자주 마주치는데,

마주 칠때마다 알아두면 굉장히 유용하고 깔끔해지는 놈이라는 생각이 든다.

이처럼 GROUP BY 절을 사용 한 경우 KEEP 키워드를 사용하면 행 그룹 내에서 특정 기준으로 최고/최하위 순위 행으로 집계를 할 수 있다.

이를 통해 전자와 같이 GROUP BY를 A기준으로 짓고 B,C,D의 값을 뒤죽박죽 엉망진창으로 만드는 대참사를 방지 할 수 있다.

KEEP 에 대한 기본 사용법

상세한 이론적인 내용은 다루지 않는다.

이를 어떤식으로 적용을 할 수 있는가 하니

1. 어떤 쿼리에 파라미터로 넘겨줘야하는 값이 있는데 그 파라미터를 코드로 관리를 하고싶다.
2. 그 파라미터 값은 기준년월을 기준으로 가장 최근의 값만 가져와야한다.

이런 상황에서 코드상세테이블에 적재된 데이터 형태는

코드 도메인 ID	코드	코드 명
CD_PARAM	2022_07_01	2
CD_PARAM	2022_07_02	3
CD_PARAM	2022_07_03	4
CD_PARAM	2022_08_01	5
CD_PARAM	2022_09_02	6

이런식이 될것이다.

여기서 지금이 7월이라는 기준으로는

- 첫번째 파라미터의 값 2
- 두번째 파라미터의 값 3
- 세번째 파라미터의 값 4

가 되는것이고

9월기준으로는

- 첫번째 파라미터의 값 5
- 두번째 파라미터의 값 6
- 세번째 파라미터의 값 4

이 되어야 하는 상황이다.

우선 단편적으로 생각했을 경우

SELECT A.CD_DMN_ID
     , A.CD_ID
     , SUBSTR(A.CD_ID,0,6) AS BAS_YM
     , A.CD_NM AS CD_VAL   -- 실제 값
  FROM 코드상세테이블 A
 WHERE SUBSTR(CD_ID,0,6) = ( 
                             SELECT MAX(SUBSTR(CD_ID,0,6)) AS BAS_YM
                   			     FROM 코드상세테이블
                              WHERE CD_DMN_ID = A.CD_DMN_ID
                                AND SUBSTR(CD_ID,0,6) <= #{basYm}  -- 파라미터 기준년월
                                AND SUBSTR(CD_ID,INSTR(CD_ID,'_')+1) = SUBSTR(A.CD_ID,INSTR(A.CD_ID,'_')+1)
                           )

이런식으로 조건절에서 코드상세 테이블을 다시 조회하여 같은 코드 중 가장 최근 기준이 되는 값을 가져오는 것으로 작성이 가능 할 것이다.

하지만 WHERE 절이 한눈에 보기에도 장황하고 PLAN을 떠보면 2중첩으로 쿼리가 풀리는 것을 알 수 있다.

이런 경우 MAX와 KEEP을 사용한다면

SELECT MAX(SUBSTR(A.CD,0,6)) AS BAS_YM
     , SUBSTR(CD,8,2)        AS CD
     , MAX(CD_NM) KEEP (DENSE_RANK LAST ORDER BY SUBSTR(A.CD,0,6)) AS CD_VAL
  FROM 코드테이블
 WHERE A.CD_DMN_ID 		  = 'CD_PARAM'
   AND SUBSTR(A.CD,0,6) <= '202208'
 GROUP BY SUBSTR(A.CD,8,2)
 
       

이런식의 코드가 완성될것이다.

다만 읽어와야 하는 데이터 양이 많아지게 된다면 이 방법은 GROUP BY를 해야하기 때문에 Cost가 상당히 필요하다.

따라서 어떤 방법이 성능에 더 적절할 지는 실제 데이터를 확인하고 플랜을 사용하며 확인을 해야한다.

++ 아래 방식을 사용한다면, 위의 방식과 다르게 CD 기준으로 정렬이 되기떄문에 우리가 원하고자 하는 정보와 좀 더 유사하다.