개요
mysql 쿼리문을 사용하여 목록을 추출할 때 순번을 매기는 것으로 ROWNUM을 종종 사용했다. 그런데 이번에 내가 하고 싶었던 것은 목록의 갯수만큼 무조건 +1을 하여 카운팅하는 로직이 아닌 특정 조건에만 +1을 하여 카운팅 하는 쿼리를 만들고 싶었다.
쿼리 고수분들이라면 더 간단하거나 쉬운 방법을 찾을 수도 있었겠지만 내가 찾은 방법은 이렇다.
의식의 흐름
일단 추출하고 싶은 목록을 쿼리로 짠다.
SELECT TR.REGISTER_SEQ
, TR.ORDER_DEGREE
FROM T_REGISTER TR
WHERE 1 = 1
//조건절
ORDER BY TR.REGISTER_SEQ
예시로 만든 간단한 쿼리는 이렇다.
'T_REGISTER' 이라는 테이블의 'REGISTER_SEQ, ORDER_DEGREE' 라는 컬럼 두개를 가져온다. 별다른 WHERE 조건은 없고 정렬 순서만 REGISTER_SEQ의 오름차순으로 해주었다.
그리고 그 다음은 추출한 쿼리문의 rownum를 만드는 것이다. @ROWNUM 을 이용하여 변수를 ROWNUM이라는 이름의 사용자 정의 변수를 사용한다는 것이다. 본래 컬럼에는 없는 새로운 컬럼을 만드는 것이기 때문에 위와 같이 만들어준 후 초기화를 시켜주는 과정이 필요하다.(프로그래밍 언어는 다 거기서 거기인 듯 하다.)
증가하는 ROWNO
SELECT A.*,
@ROWNUM := @ROWNUM + 1 AS ROWNO
FROM (
SELECT TR.REGISTER_SEQ
, TR.ORDER_DEGREE
FROM T_REGISTER TR
WHERE 1 = 1
//조건절
ORDER BY TR.REGISTER_SEQ
) A, (SELECT @ROWNUM := 0 ) R
기본적인 ROWNUM 생성은 이런 식으로 진행하였다. 기본 쿼리를 인라인 뷰로 생성해주고 @ROWNUM 초기값을 0으로 준 뒤 SELECT로 조회하는 쿼리를 추가로 만들었다.
그리고 SELECT 절에서 ROWNUM을 +1씩 해주면 흔히 아는 1에서 부터 하나씩 증가하는 ROWNO 컬럼을 만들 수 있다.
차감하는 ROWNO
SELECT A.*,
@ROWNUM := @ROWNUM - 1 AS ROWNO
FROM (
SELECT TR.REGISTER_SEQ
, TR.ORDER_DEGREE
FROM T_REGISTER TR
WHERE 1 = 1
//조건절
ORDER BY TR.REGISTER_SEQ
) A, (SELECT @ROWNUM := #{__totalCount} + 1 ) R
총 개수에서 하나씩 차감되는 rowno을 만들고 싶다면 다음과 같이 쿼리를 짠다. 예제에서는 이클립스에서 mybatis 환경을 이용 중이기 때문에 #{__totalCount} 라는 변수를 불러왔다. 이것은 해당 쿼리의 총 개수를 가리킨다. 쿼리로 불러온 목록의 개수를 알아야 rownum의 값을 초기화 할 수 있기 때문이다.
특정조건에서 카운팅되는 ROWNO
SET @ROWNUM := #{__divTotalCount} + 1, @PREV_REGISTER_SEQ := NULL;
SELECT B.*
FROM (
SELECT
@ROWNUM := CASE WHEN @PREV_REGISTER_SEQ = A.REGISTER_SEQ THEN @ROWNUM ELSE @ROWNUM - 1 END AS ROWNO,
@PREV_REGISTER_SEQ := A.REGISTER_SEQ,
A.*
FROM (
SELECT TR.REGISTER_SEQ
, TR.ORDER_DEGREE
FROM T_REGISTER TR
LEFT JOIN T_PRODUCT TP
ON TR.PRODUCT_SEQ = TP.PRODUCT_SEQ
GROUP BY TR.REGISTER_SEQ, TP.PRODUCT_SEQ
ORDER BY TR.REGISTER_SEQ
) A
ORDER BY ROWNO
) B
조금은 복잡한 쿼리문이 나와버렸다. 이 쿼리에 특정조건은 목록의 이전 register_seq와 현재 register_seq를 조회해서 다를 경우에만 카운팅을 해주는 것이다.
테이블은 총 2개를 사용한다. T_REGISTER 와 T_PRODUCT는 1대다 관계를 가지고 있다. T_REGISTER(접수) 테이블은 PRODUCT_SEQ 컬럼을 가지고 T_PRODUCT(제품) 테이블 데이터를 조회합니다. 그래서 LEFT JOIN을 사용하여 조회하면 데이터가 다음과 같이 나오게 된다.
해당 REGISTER_SEQ를 가진 제품이 여러개 존재할 수 있으므로 REGISTER_SEQ가 여러번 출력된다.
실행계획이 데이터를 하나씩 조회할 때 이전 REGISTER_SEQ와 비교할 수 있도록 이전 REGISTER_SEQ를 담아둘 변수가 필요했고 그것을 @prev_register_seq 라고 선언했다.
SET @ROWNUM := #{__divTotalCount} + 1, @PREV_REGISTER_SEQ := NULL;
이 부분이 선언부이다. ROWNUM은 셀 번호를 의미하며 #{__divTotalCount} 는 'REGISTER_SEQ를 GROUP BY한 데이터 개수' 이다.
해당 count 쿼리는 다음과 같다.
SELECT
COUNT(*)
FROM (
SELECT TR.REGISTER_SEQ
FROM T_REGISTER TR
LEFT JOIN T_PRODUCT TP
ON TR.PRODUCT_SEQ = TP.PRODUCT_SEQ
GROUP BY TR.REGISTER_SEQ
) A
REGISTER_SEQ가 다를 경우에만 카운트가 변하기 때문에 같은 SEQ끼리는 동일한 값으로 취급해야 하기 때문이다.
그리고 PREV_REGISTER_SEQ 는 NULL로 초기화하며 다음 데이터 조회 시 이전 REGISTER_SEQ값을 담는다. 그것을 나타낸 것이 다음 부분이다.
SELECT
@ROWNUM := CASE WHEN @PREV_REGISTER_SEQ = A.REGISTER_SEQ THEN @ROWNUM ELSE @ROWNUM - 1 END AS ROWNO,
@PREV_REGISTER_SEQ := A.REGISTER_SEQ,
A.*
@PREV_REGISTER_SEQ 는 실행계획이 데이터를 한줄씩 조회할 때 다음과 같은 상태변화를 가지고 있다.
최초:NULL(초기값) -> 1줄조회:NULL -> 2줄조회:1줄데이터의 REGISTER_SEQ -> 3줄조회:2줄데이터의 REGISTER_SEQ ...
만약 내가 정한 조건이 아닌 다른 조건을 사용하여 ROWNO 값을 컨트롤하고 싶다면 @ROWNUM의 CASE WHEN 부분을 조정하고 그에 맞춰 토탈카운트나 GROUP BY, 테이블 조인 부분을 수정하면 되지 않을까 싶다.
댓글